Save a table from 1s 8.2 to excel. Tricks of saving a spreadsheet document in Excel for further work with it. Create a new Excel workbook with a list

Send this article to my mail

The procedure for transferring data from 1C (a list of elements of a directory or document, a tabular part of a document, etc.) to Excel format is a very convenient 1C function, because allows you to save almost any data in Excel for further analysis.

1s enterprise version 8.2:

For example, we want to upload data from the Nomenclature directory, for this we open the Nomenclature directory, display the necessary data (we use filters, open the desired group):

And press the OK button.

After that, an Excel file with the necessary data from 1C will be created at the specified path.

But, you may not save the data to a file, but simply (after the list has been displayed) select the data, then save it to the clipboard (right-click - Copy), open the Excel program and paste the data from the clipboard into a blank sheet (right click - Paste).

In the same way, you can save both lists of documents and tabular parts in them.

If you want to save the report in Excel, then create a new report, and in order to upload it to Excel, you no longer need to list it, but simply click "File" - "Save a copy" in the main menu and then follow the scheme described above . Or just copy it through the clipboard.

1s enterprise version 8.3:

On platform 1c version 8.3 - the technology for saving data in Excel is slightly different from version 8.2.

Here, if you right-click on the list, you will no longer see the item "Display a list ...", instead you need to click on the "All actions" menu and select the item "Display a list ..." already familiar to us:

In the same way, you mark the necessary columns with flags, but there is also a wonderful flag "Only selected", when it is set, only selected rows are displayed in the table. To select several lines in the nomenclature directory list - click on the lines with the "Ctrl" key pressed, you get something like this (the selected lines are highlighted in blue):

In the end, it should look something like this:

Further, you can save this table to Excel via the clipboard (as I wrote above), or save it directly to an Excel file, for this, in 1s (in the list displayed), click on the special button of the main menu (with the down arrow) and select the item "File" - "Save as ...", then a standard window for saving the file opens, specify the file name and type "Excel Sheet" in it and click "Save".

To save the report to a file, do the same as I wrote just above:

Very often it is required to get data from 1C 8.3 and save the information to an external file, in other words, to upload from 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.

In 1C, there are several methods by which you can get information without involving specialists:

Unloading the entire database from 1C 8.3 - for subsequent download on another computer
Save reports and printables to Excel, Word or PDF
Unloading tables from 1C
Getting Information Using the Query Console

More about them:

How to unload the entire database from 1s 8.3 (to a .dt file)

To do this, you need to enter the configurator mode, then select the item "Administration" - "Upload infobase" in the main menu:

Then you only need to specify the name of the future file and its location on the disk.

For subsequent upload to another database, click on the "Upload infobase" button and select the previously uploaded file.

Unloading reports and printables from 1C to Excel

This method is universal and very simple. To do this, it is enough when you open a printable form or report.

For example, open the TORG 12 printable, press ctrl+S (or in the menu File - Save As) and select the file type and its name:

For the same system operates - the report is unloaded in two clicks:

Get 267 1C video lessons for free:

Saving to the main formats is available - excel, word, pdf, html, txt, etc.

Unloading tables from 1C 8.3

Often there are such banal tasks as “get a list of payments for the current year”, “unload a book of purchases or sales”, or “unload goods from invoice No. 256”. Solving such problems is quite simple.

For example, I need to receive all receipts from the "Vector" counterparty with an amount of more than 1000. Let's open the list form and set the necessary filters in the list:

The system will offer an output option - to a spreadsheet document or a text document, we will select a spreadsheet. The 1C program will display information in such a spreadsheet document, which, like reports, can be saved in the format we need:

Similar actions can be performed for almost any tabular part in 1C 8.2. For example, we needed to upload a list of goods from receipt No. МСК00003, no problem. This is done very simply - open the document, call the context menu and after a couple of clicks we get the desired upload in the desired format:

Uploading a journal of transactions from 1C to Excel:

Getting Information Using the Query Console

However, not all information can be found in a simple tabular part of 1C 8.2. Sometimes you need to collect data from several data sources, connect 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 result of the query using and saving it in the format we need.

For example, it is required to receive all contractors-suppliers and their telephone number. To do this, write the following query:

The resulting table can also be uploaded to the desired file format:

You can upload the received data to another 1C database using "". Watch the video instruction on the example of the nomenclature:

For further work with the data obtained in a spreadsheet document from the 1C system of any version, they can be saved in the xls (x) format.

Everything would be fine, but when opened in Excel-e, the user immediately understands that something is wrong with the table, well, yes, there are no book sheet labels. %)

So it was in the 7th version of 1C and migrated to the 8th, yes, there is progress, in 8-ke you can save in the format xlsx, the native format of recent versions of Excel-i.

To solve the problem, there are several simple manipulations, the first thing that comes to mind for any user is to resave the table in a different format, but even here there is no desired result, then we start copy-pasting into a new book Excel.
There is another option, as it were for advanced users, is to go to the settings Excel and in the Advanced section check the box "Show sheet labels". It is correct, but also routine, in light of the frequent processing of data received from 1C in the form of flat tables and further data analysis in the form of pivot tables and other ways of presenting data in Excel.

I myself got stuck in the same way running on the checkboxes and solved the problem, rummaging through the Internet, for example, and looking at some other developments, it turned out to implement a simple (for some it's a button accordion, for some it's a crutch, however, they don't argue about tastes) automation mechanism solving the problem with displaying sheet labels.

All you need is to use two properties of the Excel window:

DisplayWorkbookTabs // flag for displaying book sheet tabs TabRatio // ratio of the width of the workbook tab area to the width of the window's horizontal scroll bar (as a number between 0 (zero) and 1, default value 0.6)

TabDocument.Write(FullFileName, FileTypeSpreadDocument.XLS); Excel = New COMObject("Excel.Application"); Excel.WorkBooks.Open(FullFileName); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 //Excel.Visible = 1; // if you need to work further with the book
//Excel.Application.Quit() // if we just quit

TabDocument.Write(FullFileName, "XLS"); Excel = CreateObject("Excel.Application"); Excel.WorkBooks.Open(FullFileName); Excel.Visible = 0; Excel.ActiveWindow.DisplayWorkbookTabs = 1; Excel.ActiveWindow.TabRatio = 0.6; FullName = Excel.ActiveWorkbook.FullName; Excel.DisplayAlerts = false; Excel.ActiveWorkbook.SaveAs(FullName, 18); // 18 - xls 97-2003; 51 - xlsx 2007-2013 //Excel.Visible = 1; // if you need to work further with the book
//Excel.Application.Quit() // if we just quit

Simple task:

Organize file selection on the form (txt file format)
Fill the file with data
Well, save naturally (if the file is with data, then ask to overwrite it or not).

How to do it?

We organize the form of a thick client 1C

Let's perform this task on the example of an external processing form. We create an external processing, add a form, throw a field with a string type on the form without a length limit.

The field we added needs to include a select button.

Let's add a click handler.

Please note that we have two functions in the module - one is the handler for clicking the "Run" button, which was automatically added to the processing form when it was created. The other is the handler we added.

We organize the form of a thin client 1C

An example of the same form organization on a 1C thin client.

We add an attribute to processing - an unlimited string (i.e. length = 0).

Just added props are pulled out on the form. To open its properties, right-click on the pulled out attribute.

Set the SelectButton property to Yes.

The thin client does not automatically add a button. Therefore, we need to add the Record button to the processing form ourselves.

On the Commands tab, add a new command and click the magnifying glass in its properties to add a handler.

Also drag the added command onto the form (to the left).

We also have two functions in the module - one is the "Record" button click handler, which we added. The other is the select event handler we added.

Programming the selection of the [name and location of the recording] file

Windows has a common file selection dialog.

From 1C there is access to manage this dialog. There is a special object "FileChoiceDialog" for this purpose.

Dialog = NewFileSelectionDialog(FileSelectionDialogMode.Save);

Dialog.Title = "Select a file name to save";

Dialog.MultipleChoice = False;

Dialog.Filter = "Text file(*.txt)|*.txt";

// Calling the actual dialog to the screen

If Dialog.Select() Then

// Get here only if the user clicked OK

//Save the selected file name (full name, that means including the path to it)

FileName = Dialog.FullFileName;

EndIf;

If you want to be able to select multiple file extensions, such as txt and rtf, then you need to write like this:

Dialog.Filter = "Microsoft Word Document (*.txt)|*.txt|"
+ "Microsoft Excell document (*.rtf)|*.rtf|";

Programming to write a file to disk

To write text files in 1C there is an object "Write Text".

Working with him is very simple.

Here is a sample code for our case:

File = NewTextWriter(FileName);
File.WriteString("Hi!");
File.WriteString("How are you?");
File.Close();

Download ready-made example

You can download a ready-made example of such processing for study.

Many office workers often have to use 1C and Excel programs. They have to export data from one application to another. Today we will look at how to unload from 1C to Excel.

Uploading data from 1C to Excel

Uploading data from 1C to Microsoft Excel is a process consisting of simple steps. To do this, the tools built into these applications are sufficient. There are several ways to export data.

Uploading a large amount of data

In an open document, right-click and select "Display List". After that, all elements will be displayed as plain text. It can be copied and pasted into an MS Excel document, but it's better to open the "File" menu and go to the "Save As" section.

It remains only to select the file format and save it.

Uploading printable documents

How to insert a list into an Excel workbook

To transfer the list, use the following method:

We open the desired list in the 1C program. Then you need to right-click on "All actions", and then "Display list".

A list box will appear. In the "Outputs to" field, the option "Spreadsheet document" should be set.
Just below you can select the columns that should be moved.

Advice! You should not change anything in this window when you are going to transfer the entire document.

After all this, the user will see a list on the screen in the form of a table. It needs to be selected and copied.
Then a new document is created in Excel and the data is inserted into it using "CTRL+V".

Create a new Excel workbook with a list

You can not mess around for a long time and immediately display the list from 1C to Excel:

1. We do everything the same as in the previous method until the list is formed. Then you need to go to the menu, open the "File" section and select "Save As".

Conclusion

Now you know several ways to upload a table from 1C to Excel. The procedure is quite easy, and absolutely any user can handle it. The main thing is to study our instructions well, choose the appropriate method and follow the instructions clearly. To make it easier, look at the inserted screenshots.