I am trying to generate excel file using the ExcelPackage DLL. I have a dataset, with multiple data table. I want to put all the data from different tables to one excel file. It is working fine with few records in data table. But when my data table has 5000+
records, it is taking more than 15 minutes to generate the excel file.
I am not sure whether I am doing correct or not, I used the sample code which is given on net.
using (ExcelPackage xlPackage = new ExcelPackage(invoiceFileInfo))
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Test");
//// First generate the columns
worksheet.Cell(excelRow, 6).Value = "ABC";
worksheet.Cell(excelRow, 7).Value = "PQR";
worksheet.Cell(excelRow, 8).Value = "XYZ";
//// Set the width
worksheet.Column(1).Width = 25;
worksheet.Column(2).Width = 15;
worksheet.Column(3).Width = 30;
//// First go by Row
for (int rowIndex = 0; rowIndex < ds.Tables.Rows.Count; rowIndex++, excelRow++)
DataRow drow = ds.Tables.Rows[rowIndex];
//// then set the data as per Columns
for (int cellIndex = 0, excelCol = 1; cellIndex < 4; cellIndex++, excelCol++)
if (drow[cellIndex] == DBNull.Value)
worksheet.Cell(excelRow, excelCol).Value = Convert.ToString(drow[cellIndex]);
// // save our new workbook and we are done!
I know, it is taking time because we are using 2 for loops, first for Row and then for Columns. But is there any way to fill the excel data in excel ?