Exporting datatable to multiple sheets

[sconly]

I am familiar with exporting a datatable to Excel (single sheet).

However, as Excel has a row limit of 1,048,576 rows, is there a way to export a datatable and splitting the data into multiple Excel sheets when the row limit is reached?

Thanks

[Annika.Zhou]

Hello,

Thank you for your inquiry.
To meet your requirements, please refer to the following code snippet for implementation:

Code: Select all

// Create a new instance of Workbook
Workbook workbook = new Workbook();
 // Calculate the number of sheets needed based on the number of rows in the DataTable
 int count = dataTable.Rows.Count / 1048576;

 // If there is a remainder, increment the count by 1
 if (dataTable.Rows.Count % 1048576 > 0)
 {
     count += 1;
 }

 // If the count is 0, create an empty sheet in the workbook and insert the DataTable
 if (count == 0)
 {
     workbook.CreateEmptySheets(1);
     Worksheet sheet = workbook.Worksheets[0];
     sheet.InsertDataTable(dataTable, true, 1, 1);
 }
 else
 {
     // Otherwise, create the required number of empty sheets in the workbook
     workbook.CreateEmptySheets(count);

     // Iterate through each sheet index
     for (int i = 0; i < count; i++)
     {
         // Starting index (from zero)
         int startIndex = 1048576 * i;
         //Use the Skip() and Take() methods to cut
         DataTable slicedTable = dataTable.AsEnumerable().Skip(startIndex).Take(1048576).CopyToDataTable();
         // Get the current worksheet and insert the data table on it
         Worksheet sheet = workbook.Worksheets[i];
         sheet.InsertDataTable(slicedTable, true, 1, 1);
     }
 }

 // Save the workbook to "ImportDataFromDataTable_output.xlsx"
 string result = "ImportDataFromDataTable_output.xlsx";
 workbook.SaveToFile(result, ExcelVersion.Version2013);


If you have any further questions or need additional assistance, please don't hesitate to reach out to us.

Sincerely,
Annika
E-iceblue support team