1
Vote

Generate xlsx file with lot of data

description

Hello There,

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.
For example

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[0].Rows.Count; rowIndex++, excelRow++)
{
DataRow drow = ds.Tables[0].Rows[rowIndex];
//// then set the data as per Columns
for (int cellIndex = 0, excelCol = 1; cellIndex < 4; cellIndex++, excelCol++)
{
  if (drow[cellIndex] == DBNull.Value)
    {
      continue;
     }                                
  else
   {                                    
      worksheet.Cell(excelRow, excelCol).Value = Convert.ToString(drow[cellIndex]);
    }
}
}

// // save our new workbook and we are done!
xlPackage.Save();
}

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 ?


Thank You
Ashwin

comments