Excel Sheet generation is taking long time using CSharp

using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(templateStream, true))

{

//workbook

WorkbookPart workbookPart = spreadSheet.WorkbookPart;

//cell styles

uint headerCellIndex = SetBorderAndFill(workbookPart, "F5F5F5", "bold", "header");

// ReSharper disable once UnusedVariable

uint greyCellIndex = SetBorderAndFill(workbookPart, "F5F5F5");

uint whiteCellIndex = SetBorderAndFill(workbookPart, "FFFFFF", "normal", "right");

uint white = SetBorderAndFill(workbookPart, "FFFFFF");

uint greyRightCellIndex = SetBorderAndFill(workbookPart, "F5F5F5", "bold", "right");

//first sheet

WorksheetPart worksheetPartFirst =

GetWorksheetPartByName(spreadSheet, "Sheet1", "Compliance Report");

worksheetPartFirst.Worksheet = new Worksheet();

worksheetPartFirst.Worksheet.AppendChild(new SheetData());

worksheetPartFirst.Worksheet.First().AppendChild(new Row() { RowIndex = 1 });

AppendCell(0, companyName ?? "" + " - " + today, worksheetPartFirst).StyleIndex =

headerCellIndex;

AppendCell(0, "BaseCourse Name ", worksheetPartFirst).StyleIndex = headerCellIndex;

AppendCell(0, "Number of " + empLabel, worksheetPartFirst).StyleIndex = headerCellIndex;

AppendCell(0, "Completed", worksheetPartFirst).StyleIndex = headerCellIndex;

AppendCell(0, "Still to complete", worksheetPartFirst).StyleIndex = headerCellIndex;

AppendCell(0, "Compliance %", worksheetPartFirst).StyleIndex = headerCellIndex;

uint rowIndex = 2;

foreach (var baseCourse in data.Data)

{

foreach (var home in baseCourse.Data)

{

worksheetPartFirst.Worksheet.First().AppendChild(new Row() { RowIndex = rowIndex });

AppendCell(rowIndex - 1, home.HomeName, worksheetPartFirst).StyleIndex = white;

AppendCellNumber(rowIndex - 1, baseCourse.BaseCourseName, worksheetPartFirst).StyleIndex = whiteCellIndex;

AppendCellNumber(rowIndex - 1, home.TotalAssigned.ToString(), worksheetPartFirst).StyleIndex = whiteCellIndex;

AppendCellNumber(rowIndex - 1, home.Completed.ToString(), worksheetPartFirst).StyleIndex = whiteCellIndex;

AppendCellNumber(rowIndex - 1, home.ToComplete.ToString(), worksheetPartFirst).StyleIndex = whiteCellIndex;

// ReSharper disable once SpecifyACultureInStringConversionExplicitly

AppendCellNumber(rowIndex - 1, home.Compliance.ToString(), worksheetPartFirst).StyleIndex = whiteCellIndex;

rowIndex++;

}

worksheetPartFirst.Worksheet.First().AppendChild(new Row() { RowIndex = rowIndex });

AppendCell(rowIndex - 1, baseCourse.BaseCourseName + " Total", worksheetPartFirst).StyleIndex = greyRightCellIndex;

AppendCellNumber(rowIndex - 1, "", worksheetPartFirst).StyleIndex = greyRightCellIndex;

AppendCellNumber(rowIndex - 1, baseCourse.TotalAssigned.ToString(), worksheetPartFirst).StyleIndex = greyRightCellIndex;

AppendCellNumber(rowIndex - 1, baseCourse.Completed.ToString(), worksheetPartFirst).StyleIndex = greyRightCellIndex;

AppendCellNumber(rowIndex - 1, baseCourse.ToComplete.ToString(), worksheetPartFirst).StyleIndex = greyRightCellIndex;

// ReSharper disable once SpecifyACultureInStringConversionExplicitly

AppendCellNumber(rowIndex - 1, baseCourse.Compliance.ToString(), worksheetPartFirst).StyleIndex = greyRightCellIndex;

rowIndex++;

}

worksheetPartFirst.Worksheet.First().AppendChild(new Row() { RowIndex = rowIndex });

worksheetPartFirst.Worksheet.First().AppendChild(new Row() { RowIndex = rowIndex + 1 });

AppendCell(rowIndex, data.Title, worksheetPartFirst).StyleIndex = headerCellIndex;

AppendCellNumber(rowIndex, " ", worksheetPartFirst).StyleIndex =

greyRightCellIndex;

AppendCellNumber(rowIndex, data.TotalAssigned.ToString(), worksheetPartFirst).StyleIndex =

greyRightCellIndex;

AppendCellNumber(rowIndex, data.Completed.ToString(), worksheetPartFirst).StyleIndex =

greyRightCellIndex;

AppendCellNumber(rowIndex, data.ToComplete.ToString(), worksheetPartFirst).StyleIndex =

greyRightCellIndex;

// ReSharper disable once SpecifyACultureInStringConversionExplicitly

AppendCellNumber(rowIndex, data.Compliance.ToString(), worksheetPartFirst).StyleIndex =

greyRightCellIndex;

//set columns width on the end

SetColumnWidth(worksheetPartFirst.Worksheet, 1, 42);

for (uint i = 2; i < 7; i++)

{

SetColumnWidth(worksheetPartFirst.Worksheet, i, 22);

}

//save document on the end

spreadSheet.WorkbookPart.Workbook.Save();

}

The foreach loops are taking around 15 minutes to execute.
Is there any way to improve the performance for generating the excel sheet

Try using EPPlus nuget package to create a excel sheet with out interop DLLs. It is using openxml so it should have better performance than using interop dlls.

https://www.nuget.org/packages/EPPlus/

Sample Code

https://github.com/JanKallman/EPPlus/blob/master/EPPlusWebSample/GetSample.aspx.cs

ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample2");
ws.Cells["A1"].Value = "Sample 2";
ws.Cells["A1"].Style.Font.Bold = true;
var shape = ws.Drawings.AddShape("Shape1", eShapeStyle.Rect);
shape.SetPosition(50, 200);
shape.SetSize(200, 100);
shape.Text = "Sample 2 outputs the sheet using the Response.BinaryWrite method";
Response.BinaryWrite(pck.GetAsByteArray());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Sample2.xlsx");

2 Replies

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

Mageshwaran R6y ago
Hi,
Can you check with the below link:
https://www.c-sharpcorner.com/UploadFile/vendettamit/fastest-way-to-read-and-write-to-excel/
Jeeva Subburaj7y ago
Try using EPPlus nuget package to create a excel sheet with out interop DLLs. It is using openxml so it should have better performance than using interop dlls.

https://www.nuget.org/packages/EPPlus/

Sample Code

https://github.com/JanKallman/EPPlus/blob/master/EPPlusWebSample/GetSample.aspx.cs
ExcelPackage pck = new ExcelPackage();
           var ws = pck.Workbook.Worksheets.Add("Sample2");

           ws.Cells["A1"].Value = "Sample 2";
           ws.Cells["A1"].Style.Font.Bold = true;
           var shape = ws.Drawings.AddShape("Shape1", eShapeStyle.Rect);
           shape.SetPosition(50, 200);
           shape.SetSize(200, 100);
           shape.Text = "Sample 2 outputs the sheet using the Response.BinaryWrite method";

           Response.BinaryWrite(pck.GetAsByteArray());
           Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
           Response.AddHeader("content-disposition", "attachment;  filename=Sample2.xlsx");