Uploading data to Excel with setting various parameters. Uploading data into Excel with setting various parameters Uploading items from 1s 8.3 into excel

For example, let's create Turnover balance sheet (Reports - Standard - Balance Sheet).

Specify the folder where to save the document, set the file name and select File type — Excel sheet (*.xls) or Sheet Excel2007-…(*.xlsx)- These are different versions of Excel. You can save in both formats and see which one suits you best. Also, when saving, other file formats are available. Press the button Save .

In our example, we selected the folder Documents from 1C and set the file name - SALT for 9 months. For example, we save in different Excel formats.

After saving, the documents will be located in the specified folder.

Both files will open, because Earlier versions of documents are supported by later versions of programs.

How to save a document in Excel in 1C

Let's look at saving using a document as an example Payment order . Let's create its printed form and among the buttons on the toolbar we see a button with a floppy disk.

When clicked, a window for saving the document opens, where we specify the folder where to save it and select the desired format. In our case it is - Microsoft Excel Sheet. Check the box and press the button Ready. The document is saved in a folder.

Uploading tables from 1C to Excel

Sometimes it becomes necessary to generate a list from the data in the tabular parts of document journals, directories, etc. In this case, you can set the selection of the necessary elements (date, name of the counterparty). Let's look at an example.

We want to display a list Payment orders for the payment of taxes and contributions for the period.

To select documents, you can select them by holding down the button Ctrl or Shift, and also used to select groups of lines. Then press the button More and select a team List .

In the window that opens, we configure which columns we need to display and select - display in spreadsheet document . If we have selected a group of lines in the list, then check the box - display Selected only . Click OK and the list is ready.

It looks like this:

The resulting report can be saved in any format, including Excel - *.xls. To do this (as described above), select the command Save(or Save as ) on the menu File and set the parameters.

These actions can be performed with any lists of the 1C program.

When working with large lists, for example, a directory Nomenclature , you can install additional selection and grouping, set the order of sorting the data.

Therefore, before creating any list, you should pay attention to the command List setup in the button More .

How to save table cell data from 1C in Excel

Sometimes we need to copy part of the data from a report or document. This can be done by regular copying. To do this, select some of the required cells and right-click and select the command Copy(or Ctrl+C).

Going to the program Excel using the command Insert, add the copied fragment to the table. This method works with all data in 1C, both in generated tables and with form cells - in directories, magazines, documents.

Did the article help?
Get another secret bonus and full access to the BukhExpert8 help system for 14 days free of charge

In the last lesson we looked at.

However, the most common example is the exchange between accounting and payroll. These bases are not subordinate to each other and may not even be connected initially.

All we need is to periodically unload from the salary to the accounting department.

Let's consider the process of downloading data from 1C.

Principles of uploading and downloading 1C data

In Accounting, the document Sales of goods and services can have 30 fields. This is accounting, it has its own goals, many fields are devoted to the chart of accounts and subaccounts.

In Trade Management, the same document Sales of goods can have 25. This is operational accounting, it has its own goals. But the accounts from the chart of accounts are not indicated there.

The names of the fields in these two databases may be different. Then how to upload one document from one database, and load another into another database?

To describe how a document must be converted so that it can be loaded from one database into another, there are exchange rules.

They are a file with an XML extension. To create it, there is a special 1C configuration called “Data Conversion”.

For exchange between standard configurations, there are standard rules for data exchange, which are embedded in standard exchange settings (Service/Exchange with xxx) or are supplied separately with.

Unload BU-ZP 1C

From the standard configuration Salary and personnel management to the standard configuration Accounting usually periodically upload information about the results of payroll, including for the payment of taxes.

In order for this to be possible, you must first upload the settings from Accounting to the payroll.

To upload 1C from 1C Accounting to 1C Salary - select the menu item Service/Data Exchange with Salary/Upload Data in Accounting.

When uploading 1C for the first time, do not forget to check the box “Information for uploading 1C to a new information base.”

How to upload initial information to Salary - see the next article Uploading 1C.

Unload ZP-BU 1C

After the initial information from the Accounting Department is loaded into the Salary, you can upload accruals from the Salary to the Accounting Department.

In Salary, select the menu Service/Data exchange/Download data into an accounting program.

On the 1C upload settings form, by default, you only need to select the period and path where you want to upload the data file.

The 1C upload format is displayed as a hyperlink - in blue text. You can change it by clicking on the hyperlink and selecting a different accounting configuration.

Unload BU-UT 1C

To upload 1C from Accounting to Trade Management, you need to create exchange settings. This will allow you to do not only one-time 1C uploads, but also set up a full-fledged periodic exchange, as in.

To make settings, select the menu item Service/Data exchange with Trade management/Set up data exchange in Accounting.

Settings are created using a “wizard”.

We will not take the easy route, and will control all the settings ourselves; for this you need to select the Expert operating mode.

Since we are creating a setup from scratch, we will select “Create an exchange setup” (accordingly, you can “continue” if you interrupted the wizard earlier or change if the wizard was completed).

This exchange setup is done in two databases - first in one, then in the other.

Accordingly, we will select “Create using file” if you started making a new one from scratch in Trade Management, and here you need to complete the setup (similarly, if you start in Accounting, you will need to complete it in Trade Management).

The settings for “via file” exchanges are simple and are not shown here in the pictures. But for a “direct” COM connection there are examples here.

Example, if Trade Management is “file” - it is located on disk. .

Example, if Trade Management is located on the 1C server. The name of the server and database is .

The path to the file database, as well as the server and the name of the database on the server, are displayed in the database selection window in 1C.

Here you need to indicate whether you plan to download something from Trade Management. If so, then this is a “two-way” exchange.

We can specify filters for uploaded documents. For example, in terms of organizations.

The next page indicates the period from which the exchange is expected, as well as the list of documents that will be uploaded.

This completes the first part of the setup. Based on its results, 1C creates a settings file. Now you need to go to the Trade Management and continue the setup there, selecting the “Continue using file” option and specifying this file.

After setting up the exchange from both databases, 1C offers to perform an initial upload of 1C directories and compare objects. This is an analogue of the primary unloading of 1C for the further normal functioning of the exchange.

Pay attention to this point, if Trade Management is not an empty database, it has its own directories, especially if they have been maintained for a long time.

If you are not sure about this point:

Make backups of both databases (especially if the exchange is two-way)
Ask users not to work in these databases while you are doing the initial unloading of 1C (weekends and evenings :)
Unload 1C, check that the directories are not duplicated
If something is wrong, go back to the backup.

Unload UT-Retail 1C

The typical 1C Retail configuration is often used in conjunction with the Trade Management configuration. Typically, UT is used in retail stores, and Retail in the office.

We are looking at an example of setting up the exchange of UT edition 11 with Retail.

Select Administration in the interface, then Data Exchanges, Add/Create Exchange with Retail configuration.

Setting up the exchange is done in two databases - first in one, then in the other.

Accordingly, we will select “Continue” if you started making a new one from scratch in Retail, and here you need to complete the setup (similarly, if you start in Trade Management, you will need to complete it in Retail).

On this tab, you select the method of transferring information between two databases - through a file (share). Skip this page if you want to use another option.

On this tab, you select the method of transferring information between two databases - via a file (FTP). Skip this page if you want to use another option.

Final settings page. To specify clarifications, click the “Configure restrictions” button.

Here you can specify the date from which documents will be uploaded, as well as selections (filters) by organizations, warehouses, etc.

On the correspondence tab, you can configure the correspondence of some directories (stores = departments and cash desks).

This completes the first stage of setup. As a result, a settings file was created. You need to complete the setup in the Retail database by specifying Continue using the file.

Code 1C v 8.x // The procedure downloads data from the specified directory in export mode to a file in Microsoft Excel format
// with the specified name, using data conversion to number type and string type;
Procedure BasicActionsFormsExport(Button)
Attempt
Attempt
// Load a Microsoft Excel object
Status("Uploading data from 1C to Microsoft Excel...");
ExcelApplication = New COMObject("Excel.Application");
Exception
Report("Error starting Microsoft Excel."
+ Symbols.PS + ErrorDescription(), MessageStatus.Attention);
Return;
EndAttempt;

// Create a book, by default it already has sheets
Book = ExcelApplication.WorkBooks.Add();

// Use the first sheet of the Excel workbook
Sheet = Book.WorkSheets(1);

// Create a document header in the first line of the sheet
Column = 1;
For each page from the TP cycle
If page.Unload Then
Sheet.Cells(1, Column).Value = page.AttributeName;
Column = Column + 1;
endIf;
EndCycle;

// Upload directory data
selection = Directories[Directory Name].Select();
SheetString = 2;
While sampling.Next() Loop
Column = 1;
For each page from the TP cycle

If page.Unload Then
Attempt
// Set the value in row and column
If TypeValue(selection[page.AttributeName])=Type("Number") Then
// Set the format for the Number type
Sheet.Cells(SheetRow, Column).NumberFormat = "0.00";

Format(selection[page.AttributeName],"CH=0");
otherwise
// For all other types, set the format to "Text"
Sheet.Cells(SheetRow, Column).NumberFormat = "@";
Sheet.Cells(SheetRow, Column).Value =
Line(selection[page.AttributeName]);
endIf;
exception
// in case of error, display the row and column number
Report("Error setting value for: " +
selection[pageAttributeName]);
Report("Page: " +SheetLine+ "Count: " +
Column + "Rec: " + page.Name of Props);
EndAttempt;
Column = Column + 1;
endIf;

EndCycle;
SheetString = SheetString + 1;
State("Export from 1C to Excel" + SheetString);
EndCycle;

// Save the created book to an xls file
Book.SaveAs(Filename);

// Be sure to close the connection to the COM object to free up memory
ExcelApplication.Quit();

Notify("File uploaded successfully: " + Filename);

Exception
// Handling errors in exporting data from 1C to Excel
Report("Error writing file data:" + Filename);
Report(ErrorDescription());
Attempt
ExcelApplication.Quit();
Exception
EndAttempt;
EndAttempt;
End of Procedure
Code 1C v 7.x //The simplest example of output from 1C:Enterprise 7.7 to MS Excel. Ensuring that the workbook has a single sheet,
//set the size, boldness and color of the font, width of columns, frames and cell fill. Text alignment
//cells, grouping rows, setting the number format of cells, freezing the area.

Procedure Output()
Attempt
Application=CreateObject("Excel.Application");
Exception
Report(ErrorDescription(),"!!!");
Report("MS Excel may not be installed on this computer.");
Return;
EndAttempt;

//Create a workbook

WorkBook=Application.WorkBooks.Add();
//Getting a window

Window=WorkBook.Windows(1);
//Set the window invisible to speed up output

Window.Visible=0;
//Ensuring that the workbook has a single sheet

If WorkBook.Worksheets.Count=0 Then
WorkBook.Worksheets.Add();
Otherwise
Application.DisplayAlerts=0;//do not issue warnings

While WorkBook.Worksheets.Count>1 Cycle
WorkBook.Worksheets(1).Delete();
EndCycle;
Application.DisplayAlerts=-1;//restore the warning flag

endIf;
//Getting a worksheet

Worksheet=WorkBook.Worksheets(1);
//Set the worksheet name

Worksheet.Name="Example Output";

//Document title

Worksheet.Range("A1").Value="Example output from 1C:Enterprise in MS Excel";!}
Worksheet.Range("A2").Value="today"+ТекущаяДата();!}
Worksheet.Range("A1:A2").Font.Size=14;//font size

Worksheet.Range("A1:A2").Font.Bold=-1;//bold font

//Set column width

Worksheet.Columns(1).ColumnWidth=60;
Worksheet.Columns(2).ColumnWidth=15;
Worksheet.Columns(3).ColumnWidth=15;

//Document header

Worksheet.Range("A4").Value="First column";!}
For s=7 to 10 Cycle//frames

Worksheet.Range("A4").Borders(s).LineStyle=1;
Worksheet.Range("A4").Borders(s).Weight=-4138;//xlMedium

EndCycle;
Worksheet.Range("A4").Interior.ColorIndex=15;//fill with gray

Worksheet.Range("A4").HorizontalAlignment=-4108;//align text to center

Worksheet.Range("B4").Value="Second";!}
For s=7 to 10 Cycle
Worksheet.Range("B4").Borders(s).LineStyle=1;
Worksheet.Range("B4").Borders(s).Weight=-4138;
EndCycle;
Worksheet.Range("B4").Interior.ColorIndex=15;
Worksheet.Range("B4").HorizontalAlignment=-4108;

Worksheet.Range("C4").Value="Third";!}
For s=7 to 10 Cycle
Worksheet.Range("C4").Borders(s).LineStyle=1;
Worksheet.Range("C4").Borders(s).Weight=-4138;
EndCycle;
Worksheet.Range("C4").Interior.ColorIndex=15;
Worksheet.Range("C4").HorizontalAlignment=-4108;

//To group rows:
Worksheet.Outline.SummaryRow=0;//"summary" rows displayed at the top (if at the bottom, then 1)

//Output strings
Worksheet.Range("A5").Value="Analytics Object";!}
Worksheet.Range("A5").Font.ColorIndex=5;//font color - blue

Worksheet.Range("B5").Value=123.45;
Worksheet.Range("B5").Font.ColorIndex=5;
Worksheet.Range("C5").Value=678.9;
Worksheet.Range("C5").Font.ColorIndex=5;

Worksheet.Range("A6").Value="Analytics Object";!}
Worksheet.Range("A6").Font.ColorIndex=3;//font color - red

Worksheet.Range("B6").Value=123.45;
Worksheet.Range("B6").Font.ColorIndex=3;
Worksheet.Range("C6").Value=678.9;
Worksheet.Range("C6").Font.ColorIndex=3;

Worksheet.Range("A7").Value="Analytics Object";!}
Worksheet.Range("B7").Value=123.45;
Worksheet.Range("C7").Value=678.9;

Worksheet.Range("A8").Value="Analytics Object";!}
Worksheet.Range("B8").Value=123.45;
Worksheet.Range("C8").Value=678.9;

Worksheet.Range("A9").Value="Analytics object";!}
Worksheet.Range("A9").Font.ColorIndex=3;//font color - red

Worksheet.Range("B9").Value=123.45;
Worksheet.Range("B9").Font.ColorIndex=3;
Worksheet.Range("C9").Value=678.9;
Worksheet.Range("C9").Font.ColorIndex=3;

Worksheet.Range("A10").Value="Analytics Object";!}
Worksheet.Range("B10").Value=123.45;
Worksheet.Range("C10").Value=678.9;

Worksheet.Range("A11").Value="Analytics Object";!}
Worksheet.Range("B11").Value=123.45;
Worksheet.Range("C11").Value=678.9;

Worksheet.Range("A12").Value="Analytics Object";!}
Worksheet.Range("A12").Font.ColorIndex=5;//font color - blue

Worksheet.Range("B12").Value=123.45;
Worksheet.Range("B12").Font.ColorIndex=5;
Worksheet.Range("C12").Value=678.9;
Worksheet.Range("C12").Font.ColorIndex=5;

Worksheet.Range("A13").Value="Analytics Object";!}
Worksheet.Range("A13").Font.ColorIndex=3;//font color - red

Worksheet.Range("B13").Value=123.45;
Worksheet.Range("B13").Font.ColorIndex=3;
Worksheet.Range("C13").Value=678.9;
Worksheet.Range("C13").Font.ColorIndex=3;

Worksheet.Range("A14").Value="Analytics Object";!}
Worksheet.Range("B14").Value=123.45;
Worksheet.Range("C14").Value=678.9;

Worksheet.Range("A15").Value="Analytics Object";!}
Worksheet.Range("B15").Value=123.45;
Worksheet.Range("C15").Value=678.9;

Worksheet.Range("A16").Value="Analytics Object";!}
Worksheet.Range("A16").Font.ColorIndex=3;//font color - red

Worksheet.Range("B16").Value=123.45;
Worksheet.Range("B16").Font.ColorIndex=3;
Worksheet.Range("C16").Value=678.9;
Worksheet.Range("C16").Font.ColorIndex=3;

Worksheet.Range("A17").Value="Analytics Object";!}
Worksheet.Range("B17").Value=123.45;
Worksheet.Range("C17").Value=678.9;

Worksheet.Range("A18").Value="Analytics Object";!}
Worksheet.Range("B18").Value=123.45;
Worksheet.Range("C18").Value=678.9;

//Set frames

For NomStr=5 to 18 Cycle
For s=7 to 10 Cycle
Worksheet.Range("A"+LineNumber).Borders(s).LineStyle=1;
Worksheet.Range("B"+LineNumber).Borders(s).LineStyle=1;
Worksheet.Range("C"+LineNumber).Borders(s).LineStyle=1;
EndCycle;
EndCycle;

//Grouping rows

Worksheet.Range("7:8").Rows.Group();
Worksheet.Range("10:11").Rows.Group();
Worksheet.Range("14:15").Rows.Group();
Worksheet.Range("17:18").Rows.Group();
Worksheet.Range("6:11").Rows.Group();
Worksheet.Range("13:18").Rows.Group();

//Collapse of groupings to the first level

Worksheet.Outline.ShowLevels(1);

//Set number format

Attempt
Worksheet.Range("B5:C18").NumberFormat="# ##0.00";

Exception
Attempt
Worksheet.Range("B5:C18").NumberFormat="# ##0.00";

Exception
EndAttempt;
EndAttempt;

//Set the visibility of the application and its window

Application.Visible=-1;
Window.Visible=-1;

//Pin the area

Worksheet.Range("A5").S_elect();
Window.FreezePanes = -1;
EndProcedure //Output

It was a small discovery for me that 1C users do not know how to quickly upload almost any data from 1C to Excel. Recently I came across what my colleagues did, and then using FineReader they translated the data into text from a picture, because the save button in 1C did not work (see picture). Long way, isn't it? But there are more convenient ways. There is no secret in unloading from 1C; there are quite a lot of possibilities.

Let's look point by point at how we can save data from 1C to Excel:

The easiest way to save data from 1C to Excel is to select one cell in a document or journal and click (copy), then in Excel select the cell where you want to save the data and click v. This way you will save the contents of that cell. For the example picture it will be 42,289.23

This method works for any version of 1C.

2. How to download a large amount of data from 1C to Excel?

The previous method is good, but you can’t accumulate a large amount of data this way. Therefore, it is better to use the List tool.

In almost any document journal, reference book, or document, right-click and look for List.

For versions 8.3 and the “Taxi” interface, the button to display the list is in the More menu

A list of these elements will appear as text (you may need to select which columns to select first). This text can already be copied into any document. But I advise you to use the File - Save As menu (since large amounts of data will take a long time to process and an error may occur when copying to the buffer).

Then select the desired file format.

All files are saved in the required format.

This method also works for any version of 1C.

For version 1c 8.2 and higher in the thin client or Taxi interface, the File - Save As tool is available in the panel in the upper right corner in the form of a floppy disk