G

G

  • 1.8k
  • 102
  • 16.1k

Excel Sheet generation is taking long time using CSharp

Jun 10 2019 2:00 AM
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 

Answers (1)