Export To Excel in c#

Vamsi k

Hi All,

I need a help, We have an application which gives us data while search with a value.

so when we give a date range from start date and to end date,huge amount of data is coming into UI. Page gets freezed, so to avoid this we were used jquery table, lazy loading. After we implemented this lazy loading, data is coming with out any issues and page is not freezed. But our Project owner is asking to provide a Export To Excel functionality of that data and send it as excel to client via mail with an attachment,. Here the problem is how can we export such a huge amount of data a because excel support 10lakhs rows only and how can we send it via mail attachment.

We have data more than 10 lakhs for a given input and a given date range.

if we copied data to excel also it will take time and for opening it also will take time.

Can anyone please suggest your ideas to resolve our issue? We are struggiling with this from last 3 months

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pusting6y ago
Excel files are limited to 1,048,576 rows per sheet:
https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

So, the only way to include more rows then that is to add more sheets.
For example, something like this:
const int rowsCount = 2000000;
const int maxRowsPerSheet = 1048576;

ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = null;

for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
{
    int relativeRowIndex = rowIndex % maxRowsPerSheet;
    if (relativeRowIndex == 0)
        worksheet = workbook.Worksheets.Add("Sheet" + workbook.Worksheets.Count + 1);

    worksheet.Rows[relativeRowIndex].Cells[0].Value = "Value " + rowIndex + 1;
}

workbook.Save("output.xlsx");
The above code uses GemBox.Spreadsheet to write an Excel file:
https://www.gemboxsoftware.com/spreadsheet/examples/c-sharp-create-write-excel-file/402
0
Viorel S6y ago
Hi, how about exporting 1 million rows of data to the fisrt worksheet and the rest to the second sheet.

This article explains how to export a selected range of data to the specified worhsheet by using Spire.XLS.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
0