How to Output Data from 1C to Different Sheets in an Excel File
1C developers often need to write code to export data to Excel.
This could be for various reasons, such as unloading a product list with codes and descriptions to import into another IT system or providing a key user with a complex Excel report populated with data from 1C.
In short, plenty of scenarios call for a developer to export data from 1C to Excel.
Currently, the 1C platform offers several ways to work with Excel, but the best way is to use the SpreadsheetDocument object.
This object has a Write method, whose second parameter, SpreadsheetFileType, specifies the saved file type:
As the screenshot below shows, the available formats include Excel formats:
Let's create a simple example. First, create a new data processor and a basic template within it:
We'll keep the template simple for now, focusing on how to save it to Excel rather than populating it with data.
So, here's the template:
The following code saves this template to an XLSX file:
|
&AtClient Procedure cmdSaveToExcel(Command)
spreadsheetDocument = cmdSaveToExcelAtServer();
spreadsheetDocument.Write("C:\Temp\OurExcelFile.xlsx", SpreadsheetDocumentFileType.XLSX);
EndProcedure
&AtServer Function cmdSaveToExcelAtServer()
spreadsheetDocument = New SpreadsheetDocument;
objectTemplate = FormAttributeToValue("Object");
Template = objectTemplate.GetTemplate("Template");
areaRow = Template.GetArea("Row");
spreadsheetDocument.Put(areaRow);
Return spreadsheetDocument;
EndFunction |
Here's how the saved file looks when opened in Excel:
As you can see, it's pretty straightforward.
However, an experienced Excel user might have a few questions...
First, how to set a custom name for the Excel sheet. By default, 1C saves data to a sheet named TDSheet:
But sometimes, you'll want to use a different name.
Second, 1C defaults to saving data to only one sheet. What if you want to output information from 1C to multiple sheets in the same Excel file?
Is this even possible?
Yes, it is!
The 1C object RepresentableDocumentBatch is the key.
Unfortunately, this object has a limited description in the built-in help, so not all developers leverage it:
The main property of this object is the Content
collection:
As the description indicates, this collection holds a list of documents to be processed: printed or saved.
So, the developer first creates a RepresentableDocumentBatch
object, adds the data to the Content
collection, and then saves everything.
Let's implement this. The code below saves spreadsheet document data to an Excel file, creating three individual sheets:
|
&AtClient Procedure cmdSaveToExcel2(Command)
spreadsheetDocument = cmdSaveToExcelAtServer();
newBook = New RepresentableDocumentBatch;
newSheet = newBook.Content.Add(); newSheet.Data = PutToTempStorage(spreadsheetDocument); newSheet.Title = "ourSheet_1";
newSheet = newBook.Content.Add(); newSheet.Data = PutToTempStorage(spreadsheetDocument); newSheet.Title = "ourSheet_2";
newSheet = newBook.Content.Add(); newSheet.Data = PutToTempStorage(spreadsheetDocument); newSheet.Title = "ourSheet_3";
newBook.Write("C:\Temp\OurExcelFile_2.xlsx", RepresentableDocumentBatchFileType.XLSX);
EndProcedure |
It's simple! First, a RepresentableDocumentBatch
object is created. Then, print forms created by the cmdSaveToExcelAtServer procedure are added to the Content collection. Each added form becomes an individual sheet in Excel.
We also set the name for each future Excel sheet.
Once the Content collection is filled, the Write method is called, specifying the file name and format.
Here's the result:
As you can see, the resulting Excel file has three sheets, each with its specified name.
Today, we looked at a brief example of working with the 1C platform object RepresentableDocumentBatch. This object enables developers to output information to an Excel file flexibly, including placing data on multiple sheets.
I hope this example helps simplify your development process. Feel free to download the source code of the example from the article using the link.
Stay tuned for more 1C tips and tricks!
