How to export data to multiple Excel spreadsheets?

Question

How can I export fields to different sheets of a single Excel file?

Answer

Export to different sheets of an Excel workbook can be configured using script export.

Below is a C# sample of such custom export to Excel. It exports data to different sheets depending on the value of one of the fields  - FieldDocType. 

It is required to attach Microsoft.Office.Interop.Excel.dll as .NET reference in Document Definition properties > .NET References

using Excel = Microsoft.Office.Interop.Excel;

//Start Excel and get Application object
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;

//open Workbook from the specified path
string path = @"\\localhost\export\test.xlsx";  
System.Reflection.Missing misVal = System.Reflection.Missing.Value;
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(path, misVal,misVal,misVal,misVal,misVal,
misVal,misVal,misVal,misVal,misVal,misVal,misVal,misVal,misVal);  


// Sheets 
string[] SheetName = {"Sheet1", "Sheet2", "Sheet3"};
Excel.Worksheet currentSheet = (Excel.Worksheet)excelWorkbook.Sheets[SheetName[0]];

// columns order
string[] columns = {"Document Section 1\\Field4", 
                    "Document Section 1\\Field1",
                    "Document Section 1\\Field3", 
                    "Document Section 1\\Field2"};
                    
// Saving to different sheets depending on FieldDocType field value 
if (Document.Field("Document Section 1\\FieldDocType").Text == "01") 
    currentSheet = (Excel.Worksheet)excelWorkbook.Sheets[SheetName[0]];
else if (Document.Field("Document Section 1\\FieldDocType").Text == "02")
    currentSheet = (Excel.Worksheet)excelWorkbook.Sheets[SheetName[1]];
else         //if (Document.Field("Document Section 1\\FieldDocType").Text == "03")
    currentSheet = (Excel.Worksheet)excelWorkbook.Sheets[SheetName[2]];
 

// Figuring out the last used row of the sheet
Excel.Range last = currentSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, misVal);
int lastUsedRow = last.Row;    

// Saving field values to current sheet after the last row
for (int cell_y=0; cell_y<columns.Length; cell_y++) {
    currentSheet.Cells[lastUsedRow+1, (cell_y+1)] = Document.Field(columns[cell_y]).Text;
}
  

// Save and close .xlsx
excelWorkbook.Save();
excelApp.Quit();

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.