Instructions
1C: Enterprise allows you to unload any directory without resorting to writing complex loading / unloading processing, using only built-in tools. First, we need to decide in what format we want to receive data:
* .mxl - internal format of 1C tabular data, suitable for loading into another 1C database;
* .xls - Excel spreadsheets;
* .pdf - electronic document Adobe Reader;
* .html - web page for publishing on the Internet;
* .txt is a simple text file, useful for loading into any database.
If the directory is hierarchical, then the list will be unloaded into the * .mxl and * .xls formats with the preservation of the hierarchy and the ability to collapse / expand groups. All other formats will be uploaded with a simple list, with the list of group elements after its name.
Let's open the directory that we need to unload. In our case, this is "Nomenclature". Operations -\u003e References -\u003e Nomenclature. You can download not the entire directory, but only a certain group, for this you need to go to this group. With large amounts of data, it makes sense to upload in groups to different files to speed up work.
On any line of the opened list, right-click and select the "Display list" item. The settings window will appear.
In the settings window, leave the checkboxes only for those fields that we need in the final document. In the field "Output to ..." we will leave the item "Tabular document" (you can also select "Text document", but tabular document is more universal). Click the "OK" button.
The mechanism for creating spreadsheet documents and printable forms in 1C is quite thought out and well-developed, however, it is certainly far from the functionality implemented in Microsoft Excel. Therefore, many users prefer to proceed according to the following algorithm:
- Create a printed form in 1C;
- Save data as an Excel sheet (Fig. 1) or copy the table;
- Continue working with information in another program.
And, often, they have a question: how to directly upload information from 1C to Excel, bypassing additional steps?
Two main requirements that must be met
For the error-free functioning of our processing, it is necessary:
- That Microsoft Office and, in particular, Excel be installed on the user's computer (we will connect to the application by creating a COM object);
- Imagine the data storage structure in Excel (understand how cell names are formed, know what a sheet, workbook, etc.).
Only after making sure that these two points are fulfilled, you should proceed to further development.
Algorithm for unloading and its implementation in 1C
The sequence of actions when unloading from 1C to Excel can be represented by the following sequence of steps:
- We form a set of data that needs to be saved in Excel;
- We create a connection to the application (run it);
- We fill the file with the uploaded data;
- We save the file;
- Close the open application.
Now on this list in more detail.
Data set formation
The data source can be the query execution result, the printed form of the spreadsheet document, any selection of elements. In our case, we will unload the register of documents "Receipt of goods and services" for the current month (Fig. 2)
Fig. 2
We carry out the program launch of the application
As mentioned above, in order to start, we will use the Com-connection mode. The code in Figure 3 shows how this can be done.
Fig. 3
We deliberately inserted the procedure for creating a new object into the Attempt - Exception -EndTry construction, in order to exclude all sorts of non-standard situations in the further course of processing.
Filling in the file, saving it and exiting the application
First of all, we need to decide what we want to do: upload data to an existing file or create a new one:
- In the first case, we need to start the file selection dialog and get the full name of the existing information storage;
- In the second case, we must independently come up with the name of the file to be created and determine its location (it is from these two terms that the file name will be created).
Having decided where we will store our data, we will proceed to the direct filling of the file, for this we will create a procedure and pass our selection of documents for the current month into it as a parameter. (Fig. 4)
Fig. 4
As you can see from the code, first of all we created a workbook that will store our table. By default, Excel creates three sheets in a workbook when it first starts up. To enter data, we can choose any of them, simply by specifying the sheet number as a parameter to the WorkSheets () method. If we needed to add a new sheet to the book, we would have to write a line like this:
NewList \u003d NewBook.Sheets.Add ();
At this point, we have finished forming the general structure of the document and are starting to fill it out.
In a loop with a counter, we iterate over the rows and columns of our table of values \u200b\u200bto obtain the data of a specific cell and transfer them to the final table.
It is important to keep in mind here that the indices of the rows and columns of the table of values \u200b\u200bin 1C start at 0 and end with a number 1 less than the number of these rows and columns, and the numbering of rows and columns of cells in Exel starts at 1.
After going through our table and moving it to a new location, we must save the created file. The FilePath parameter specified in the SaveAs procedure must contain the full path to the file, including its name.
If Microsoft Office 2007 or later is installed on your computer, you may probably need to save a spreadsheet document in Excel 2003 format, you can programmatically do this by specifying the value (-4143) as the second parameter of the SaveAs procedure. It is this number that indicates the format of the saved file corresponding to the xls extension.
In the event of an error (let's say the file being saved is already open), our processing should warn the user about a non-standard situation, which is written in the Report () procedure.
Since we do not plan to continue working with Excel further, we must close the application. It should be noted here that you can visually track the progress of our processing only by using the task manager, it will not be visible in the list of open windows. Again, if you do not prescribe the closing of the application, it will take up memory space and may lead to conflicts in the launch of additional copies of Excel.
An additional way to save a spreadsheet document from 1C to Excel
Another way to unload is to directly save a spreadsheet document to disk without displaying it on the screen or before outputting, the code of the corresponding procedure is shown in (Fig. 5).
Fig. 5
One very important note: in 1C andExcel distinguishes between integer and fractional separators in numerical values, this point should be taken into account when unloading in order to avoid skewing in the data.
Configuration:1c accounting
Configuration version:3.0.44.177
Date of publication: 20.01.2017
In the 1C: Accounting program, it became possible to routinely upload and download data from Excel spreadsheet documents, without the use of third-party processing and configuration changes. Loading and unloading is so easy that experienced users will quickly fall in love with this handy mechanism, and new users will quickly master it.
Download from Excel 1C: Accounting 8.3 ed. 3.0
For example, we have such an Excel spreadsheet document for loading a list of items and prices (retail and purchase).
Come in.
At the top, click Nomenclature and Prices - Download... This is where loading and unloading from spreadsheet documents is hidden.
The download supports xls (old format Excel 97-2003) and xlsx (new format), as well as mxl and ods formats. Now we select our file with the nomenclature and wait for the download.
The spreadsheet document has been loaded in the form as we saw it in Excel, now we will assign the columns and delete the excess.
Click on Specify the requisite and select the desired item from the list.
Select the line containing unnecessary records, namely the table header and click Delete line.
Now remove the purchase price column, note that processing is able to fill in only 1n type of price for 1n time !!!Then you can do this operation again and download the 2nd type of price, for the 1st download Retail for the 2nd purchase.
Fill in blank data, Price type, Set prices for. The buttons All, Found, New, Duplicates will greatly facilitate the search and help to match the item. Fill in and click Download after which the data will be uploaded to 1C: Accounting.
New items appeared in the list of items, filled and with assigned prices. If you need to load a different price type, do all the points again, but select the column with a different price, 1C itself will find already created positions and will not try to duplicate them, but will simply add a new price type.
As you can see, the download has created 2 item pricing documents, for the Purchase price and for the Retail price. The difference between this processing is Downloading from Excel to 1C: Accounting 8.3, the fact that this is a regular processing and it works without failures and taking into account all the features of filling out the document.
Export to Excel from 1C: Accounting 8.3 ed. 3.0
A very useful function in edition 3.0.44.177 and higher is uploading to Excel from 1C: Accounting 8.3, this processing will help you create a company's price list or transfer your list of items to suppliers or customers with prices right away.
All in the same directory Nomenclature - Nomenclature and Prices - Upload.
In point Loudspeakers, select those columns to be built in the Excel document. Selection serves for selection of items by Groups, properties, names ... and so on.
For uploading to excel, we will add a new column, namely the VAT rate, it seems not really necessary, but we are practicing.
In the last lesson, we reviewed it.
However, the most common example is an exchange between accounting and salary. These bases are not subordinate to each other and may not even be connected initially.
All we need is to periodically upload from the salary to the accounting department.
Let's consider the process of unloading data from 1C.
Principles of uploading and downloading data 1C
In Accounting, the document Sales of goods and services can have 30 fields. This is accounting, it has its own goals, many fields are dedicated to the chart of accounts and subconto.
In the Department of Trade, the same document Sales of goods can be 25. This is operational accounting, it has its own goals. But the accounts from the chart of accounts are not listed there.
The names of the fields in these two bases can be different. Then how to unload one document from one database and load another to 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 the exchange between typical configurations, there are standard data exchange rules that are hardcoded into the standard exchange settings (Service / Exchange with xxx) or supplied separately with.
Unload BU-ZP 1C
From a typical configuration of Payroll and personnel management to a typical configuration, Accounting usually periodically upload information on the results of payroll, including for the payment of taxes.
In order for this to be possible, you must first upload the settings from the Accounting Department to the payroll.
To unload 1C from 1C Accounting to 1C Salary - select the Service / Data exchange with Salary / Upload data menu item in Accounting.
When unloading 1C for the first time, do not forget to check the box "Information for unloading 1C into a new infobase".
How to upload initial information to Salary - see the next article Downloading 1C.
Unload ZP-BU 1C
After the initial information from the Accounting Department has been loaded into the Salary, you can upload the accruals from the Salary to the Accounting Department.
In Salary, select the menu Service / Data exchange / Upload data to the accounting program.
On the 1C upload setting 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 choosing a different accounting configuration.
Unload BU-UT 1C
To unload 1C from Accounting to Trade Management, you need to create exchange settings. This will allow you to do not only one-time uploads of 1C, but set up a full-fledged periodic exchange, as in.
To make settings, select the menu item Service / Data exchange with Trade management / Configure data exchange in the Accounting department.
The settings are created using the "wizard".
We will not go the easy way, and will control all the settings ourselves, for this you need to select the Expert mode of operation.
Since we are creating a setting from scratch, we will select "Create an exchange setting" (accordingly, you can "continue" if you interrupted the wizard earlier or change if the wizard was completed).
This exchange setting is done in two bases - 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 exchanges "through a file" are simple and are not shown here in the pictures. But for a "direct" COM connection, examples are here.
For example, if Trade Management is "file" - it is 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 the Trade Office. If so, then this is a "two-way" exchange.
We can specify filters for uploaded documents. For example, in the context of organizations.
The next page indicates the period from which the exchange is supposed, as well as the list of documents that will be unloaded.
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 Department and continue the configuration there, indicating the option "Continue using a file" and specify this file.
After setting up the exchange from both bases, 1C proposes to carry out the initial unloading of 1C directories and to compare objects. This is an analogue of the primary unloading of 1C in for the further normal functioning of the exchange.
Pay attention to this point, if Trade Management is not an empty base, it has its own reference books, especially if they have been conducted for a long time.
If you are not sure about this moment:
- Make backups of both bases (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 🙂
- Upload 1C, check that the directories are not duplicated
- If something is wrong, go back to the backup.
Make unloading UT-Retail 1C
A typical 1C Retail configuration is often used in conjunction with the Trade Management configuration. Usually UT is used in retail stores, and Retail is used in the office.
We are considering an example of setting up the exchange of UT edition 11 with Retail.
Select the Administration item in the interface, then Data exchanges, the Add / Create exchange with the Retail configuration item.
The exchange setup is done in two bases - 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 can select the method of transferring information between the two bases - through a file (ball). Skip this page if you want to use another option.
On this tab, you can select the method of transferring information between the two databases - via a file (FTP). Skip this page if you want to use another option.
Summary page of settings. To specify refinements - click the "Configure constraints" button.
Here you can specify the date from which the 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 \u003d departments and cash desks).
This completes the first stage of configuration. As a result, a settings file was created. You need to complete the setup in the Retail database by specifying Continue using file.
Code 1C v 8.x // The procedure unloads data from the specified directory in export mode to a file in Microsoft Excel format
// with the specified name, using data conversion to type number and type string;
Procedure Basic Actions Forms Export (Button)
Attempt
Attempt
// Load the Microsoft Excel object
State ("Uploading data from 1C to Microsoft Excel ...");
ExcelApplication \u003d New COMObject ("Excel.Application");
An exception
Report ("Error starting Microsoft Excel."
+ Symbols.PS + DescriptionErrors (), MessageStatus.Attention);
Return;
End of Attempts;
// Create a book, by default it already contains sheets
Workbook \u003d ExcelApplication.WorkBooks.Add ();
// Use the first sheet of the Excel workbook
Sheet \u003d Book.WorkSheets (1);
// Form the head of the document in the first line of the sheet
Column \u003d 1;
If page Unload Then
Sheet.Cells (1, Column) .Value \u003d string.AttributeName;
Column \u003d Column + 1;
EndIf;
End of Cycle;
// Unload directory data
selection \u003d Directories [DirectoryName] .Select ();
SheetRow \u003d 2;
While sampling.Next () Loop
Column \u003d 1;
For each page from the TP cycle
If page unload Then
Attempt
// Set value in row and column
IfTypeZnch (sample [page AttributeName]) \u003d Type ("Number") Then
// Set format for Number type
Sheet.Cells (SheetRow, Column) .NumberFormat \u003d "0,00";
Format (selection [page AttributeName], "CH \u003d 0");
otherwise
// For all other types, set the format to "Text"
Sheet.Cells (ListRow, Column) .NumberFormat \u003d "@";
Sheet.Cells (SheetRow, Column) .Value \u003d
String (selection [page AttributeName]);
EndIf;
an exception
// in case of an error, display the line and column numbers
Report ("Error setting value for:" +
selection [page AttributeName]);
Report ("Row:" + RowList + "Count:" +
Column + "Recs:" + page.Name of the Attribute);
End of Attempts;
Column \u003d Column + 1;
EndIf;
End of Cycle;
SheetRow \u003d SheetRow + 1;
Status ("Export from 1C to Excel" + List Line);
End of Cycle;
// Save the created book to xls file
Book.SaveAs (filename);
// Be sure to close the connection to the COM object to free memory
ExcelApplication.Quit ();
Report ("File uploaded successfully:" + filename);
An exception
// Handling data export errors from 1C to Excel
Report ("Error writing file data:" + filename);
Report (Error Description ());
Attempt
ExcelApplication.Quit ();
An exception
End of Attempts;
End of Attempts;
End of Procedure
Code 1C v 7.x // The simplest example of output from 1C: Enterprise 7.7 in MS Excel. Ensuring that the workbook has a single sheet,
// setting the size, weight and color of the font, the width of the columns, borders and cell fill. Text alignment
// cells, grouping rows, setting the number format of cells, pinning an area.
Procedure Output ()
Attempt
Application \u003d CreateObject ("Excel.Application");
An exception
Report (DescriptionBugs (), "!!!");
Report ("Perhaps MS Excel is not installed on this computer.");
Return;
End of Attempts;
// Create a workbook
WorkBook \u003d Application.WorkBooks.Add ();
// Get the window
Window \u003d WorkBook.Windows (1);
// Set the invisibility of the window to speed up the output
Window.Visible \u003d 0;
// Ensure the workbook has a single sheet
If WorkBook.Worksheets.Count \u003d 0 Then
WorkBook.Worksheets.Add ();
Otherwise
Application.DisplayAlerts \u003d 0; // do not issue warnings
While WorkBook.Worksheets.Count\u003e 1 Loop
WorkBook.Worksheets (1) .Delete ();
End of Cycle;
Application.DisplayAlerts \u003d -1; // restore the warning flag
EndIf;
// Get the worksheet
Worksheet \u003d WorkBook.Worksheets (1);
// Set the name of the worksheet
Worksheet.Name \u003d "Sample Output";
// Document title
Worksheet.Range ("A1"). Value \u003d "(! LANG: Example of output from 1C: Enterprise to MS Excel";
!}
Worksheet.Range ("A2"). Value \u003d "(! LANG: today"+ТекущаяДата();
!}
Worksheet.Range ("A1: A2"). Font.Size \u003d 14; // font size
Worksheet.Range ("A1: A2"). Font.Bold \u003d -1; // bold font
// Set the width of the columns
Worksheet.Columns (1) .ColumnWidth \u003d 60;
Worksheet.Columns (2) .ColumnWidth \u003d 15;
Worksheet.Columns (3) .ColumnWidth \u003d 15;
// Document header
Worksheet.Range ("A4"). Value \u003d "(! LANG: First column";
!}
For s \u003d 7 to 10 Loop // frames
Worksheet.Range ("A4"). Borders (s) .LineStyle \u003d 1;
Worksheet.Range ("A4"). Borders (s) .Weight \u003d -4138; // xlMedium
End of Cycle;
Worksheet.Range ("A4"). Interior.ColorIndex \u003d 15; // gray fill
Worksheet.Range ("A4"). HorizontalAlignment \u003d -4108; // center align the text
Worksheet.Range ("B4"). Value \u003d "(! LANG: Second";
!}
For s \u003d 7 to 10 Cycle
Worksheet.Range ("B4"). Borders (s) .LineStyle \u003d 1;
Worksheet.Range ("B4"). Borders (s) .Weight \u003d -4138;
End of Cycle;
Worksheet.Range ("B4"). Interior.ColorIndex \u003d 15;
Worksheet.Range ("B4"). HorizontalAlignment \u003d -4108;
Worksheet.Range ("C4"). Value \u003d "(! LANG: Third";
!}
For s \u003d 7 to 10 Cycle
Worksheet.Range ("C4"). Borders (s) .LineStyle \u003d 1;
Worksheet.Range ("C4"). Borders (s) .Weight \u003d -4138;
End of Cycle;
Worksheet.Range ("C4"). Interior.ColorIndex \u003d 15;
Worksheet.Range ("C4"). HorizontalAlignment \u003d -4108;
// To group lines:
Worksheet.Outline.SummaryRow \u003d 0; // display "summary" rows on top (if bottom, then 1)
// Output lines
Worksheet.Range ("A5"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A5"). Font.ColorIndex \u003d 5; // font color - blue
Worksheet.Range ("B5"). Value \u003d 123.45;
Worksheet.Range ("B5"). Font.ColorIndex \u003d 5;
Worksheet.Range ("C5"). Value \u003d 678.9;
Worksheet.Range ("C5"). Font.ColorIndex \u003d 5;
Worksheet.Range ("A6"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A6"). Font.ColorIndex \u003d 3; // font color - red
Worksheet.Range ("B6"). Value \u003d 123.45;
Worksheet.Range ("B6"). Font.ColorIndex \u003d 3;
Worksheet.Range ("C6"). Value \u003d 678.9;
Worksheet.Range ("C6"). Font.ColorIndex \u003d 3;
Worksheet.Range ("A7"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B7"). Value \u003d 123.45;
Worksheet.Range ("C7"). Value \u003d 678.9;
Worksheet.Range ("A8"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B8"). Value \u003d 123.45;
Worksheet.Range ("C8"). Value \u003d 678.9;
Worksheet.Range ("A9"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A9"). Font.ColorIndex \u003d 3; // font color - red
Worksheet.Range ("B9"). Value \u003d 123.45;
Worksheet.Range ("B9"). Font.ColorIndex \u003d 3;
Worksheet.Range ("C9"). Value \u003d 678.9;
Worksheet.Range ("C9"). Font.ColorIndex \u003d 3;
Worksheet.Range ("A10"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B10"). Value \u003d 123.45;
Worksheet.Range ("C10"). Value \u003d 678.9;
Worksheet.Range ("A11"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B11"). Value \u003d 123.45;
Worksheet.Range ("C11"). Value \u003d 678.9;
Worksheet.Range ("A12"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A12"). Font.ColorIndex \u003d 5; // font color - blue
Worksheet.Range ("B12"). Value \u003d 123.45;
Worksheet.Range ("B12"). Font.ColorIndex \u003d 5;
Worksheet.Range ("C12"). Value \u003d 678.9;
Worksheet.Range ("C12"). Font.ColorIndex \u003d 5;
Worksheet.Range ("A13"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A13"). Font.ColorIndex \u003d 3; // font color - red
Worksheet.Range ("B13"). Value \u003d 123.45;
Worksheet.Range ("B13"). Font.ColorIndex \u003d 3;
Worksheet.Range ("C13"). Value \u003d 678.9;
Worksheet.Range ("C13"). Font.ColorIndex \u003d 3;
Worksheet.Range ("A14"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B14"). Value \u003d 123.45;
Worksheet.Range ("C14"). Value \u003d 678.9;
Worksheet.Range ("A15"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B15"). Value \u003d 123.45;
Worksheet.Range ("C15"). Value \u003d 678.9;
Worksheet.Range ("A16"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("A16"). Font.ColorIndex \u003d 3; // font color - red
Worksheet.Range ("B16"). Value \u003d 123.45;
Worksheet.Range ("B16"). Font.ColorIndex \u003d 3;
Worksheet.Range ("C16"). Value \u003d 678.9;
Worksheet.Range ("C16"). Font.ColorIndex \u003d 3;
Worksheet.Range ("A17"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B17"). Value \u003d 123.45;
Worksheet.Range ("C17"). Value \u003d 678.9;
Worksheet.Range ("A18"). Value \u003d "(! LANG: Analytics Object";
!}
Worksheet.Range ("B18"). Value \u003d 123.45;
Worksheet.Range ("C18"). Value \u003d 678.9;
// Set frames
For No.Str \u003d 5 to 18 Cycle
For s \u003d 7 to 10 Cycle
Worksheet.Range ("A" + NomStr) .Borders (s) .LineStyle \u003d 1;
Worksheet.Range ("B" + NomStr) .Borders (s) .LineStyle \u003d 1;
Worksheet.Range ("C" + NomStr) .Borders (s) .LineStyle \u003d 1;
End of Cycle;
End of Cycle;
// Grouping lines
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 groupings to the first level
Worksheet.Outline.ShowLevels (1);
// Set the number format
Attempt
Worksheet.Range ("B5: C18"). NumberFormat \u003d "# ## 0.00";
An exception
Attempt
Worksheet.Range ("B5: C18"). NumberFormat \u003d "# ## 0,00";
An exception
End of Attempts;
End of Attempts;
// Set the visibility of the application and its window
Application.Visible \u003d -1;
Window.Visible \u003d -1;
// Freeze the area
Worksheet.Range ("A5"). S_elect ();
Window.FreezePanes \u003d -1;
EndProcedure // Output
Information taken from the site
