Very often it is required to obtain data from 1C 8.3 and save the information to an external file, in other words, to download it from the 1C enterprise. The task is almost always feasible without the involvement of 1C programmers.
Consider a number of useful tips with which you can upload data from 1C to an external information system.
There are several methods in 1C with the help of which you can obtain information without involving specialists:
- Unloading the entire database from 1C 8.3 - for subsequent loading on another computer
- Saving reports and printable forms to Excel, Word or PDF
- Unloading tables from 1C
- Retrieving Information Using the Query Console
More about them:
How to unload the entire database from 1c 8.3 (to file.dt)
To do this, you need to enter the configurator mode, then select the "Administration" - "Unload information base" item in the main menu:
Then you only need to specify the name of the future file and its location on disk.
For subsequent loading into another database, click on the "Load information base" button and select the previously uploaded file.
Export of reports and printed forms from 1C to Excel
This method is universal and very simple. To do this, it is enough to open a printable or report.
For example, open the TORG 12 printable, press ctrl + S (or in the File menu - Save As) and select the file type and its name:
For the same system works - the report is uploaded in two clicks:
Get 267 1C video tutorials for free:
Saving in basic formats is available - excel, word, pdf, html, txt, etc.
Unloading tables from 1C 8.3
Often there are such trivial tasks as “get a list of payments for the current year”, “unload the book of purchases or sales” or “unload goods from invoice No. 256”. It is quite simple to solve such problems.
For example, I need to receive all receipts from the counterparty "Vector" with an amount of more than 1000. Open the list form and set the required selections in the list:
The system will offer an output option - in a tabular document or in a text document, select tabular. The 1C program will display information in such a tabular document, which, like reports, can be saved in the format we need:
Similar actions can be performed for almost any tabular section in 1C 8.2. For example, we needed to unload a list of goods from receipt No. MSK00003, no problem. This is done very simply - we open the document, call the context menu and after a couple of clicks we get the desired export in the required format:
Uploading a posting log from 1C to Excel:
Retrieving Information Using the Query Console
However, not all information can be found in a simple tabular section of 1C 8.2. Sometimes it is required to collect data from several data sources, to combine them by some fields.
This method is suitable for tech-savvy people who know the basics of the query language. It consists in getting the query result with the help and saving it in the format we need.
For example, you need to get all supplier contractors and their phone number. To do this, write the following query:
The resulting table can also be exported to the desired file format:
You can upload the received data to another 1C database using "". Watch the video tutorial for an example of the nomenclature:
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:
- So that Microsoft Office and, in particular, Excel are 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 a query result, a printed form of a 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 a name for 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 by simply 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 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.
2018-11-15T19: 32: 35 + 00: 00Universal processing "Upload and download XML data" performs full or partial upload of infobase data into a file in XML format. Subsequently, this file can be loaded into the infobase using the same processing. The format of the upload file differs from the format of the file created when uploading according to the exchange plan by the header part.
Processing can be used only in cases where the infobase in which the data was downloaded and the one in which the data is loaded are homogeneous (the configurations are identical, the data may differ), or all unloaded objects are almost completely identical in composition and types of attributes and tabular sections, properties of the "lead" metadata object, and so on.
The use of this processing is possible, for example, to create a full or partial backup of data, to exchange data between infobases, and also as an auxiliary tool in restoring failed infobases.
Processing supports data uploading with the ability to specify a selection by period. Also, objects are checked for invalid characters when exchanged via XML.
Best regards, (teacher and developer).
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 Form Actions 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;
For each page from the TP cycle
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
If TypeZnch (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:" +
fetch [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
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 Excel 97-2003) and xlsx (new) formats, 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 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 the selection of items by Groups, properties, names ... etc.
For uploading to excel, we will add a new column, namely the VAT rate, it seems not really necessary, but we are practicing.
