0

I need to improve performance of the code that maps datatable to excel spreadsheet.

The old code loops through the datatable and creates excel. I created a timer too print out the time it takes to generate the report for this approach:

var watch = System.Diagnostics.Stopwatch.StartNew();
System.Data.DataTable dt = new System.Data.DataTable();
dt = (System.Data.DataTable)Session["ISOReport"];

TableCell td;
TableRow tr;
HyperLink a;
TableHeaderRow thr;
TableHeaderCell thc;

thr = new TableHeaderRow();
thr.BorderStyle = BorderStyle.Solid;

thr.BorderColor = System.Drawing.Color.White;

for (int i = 0; i < dt.Columns.Count; i++)
{
    thc = new TableHeaderCell();
    thc.Text = dt.Columns[i].ColumnName;
    thr.Cells.Add(thc);
}

tblReport.Rows.Add(thr);

for (int j = 0; j < dt.Rows.Count; j++)
{
    tr = new TableRow();
    tr.BorderStyle = BorderStyle.Solid;
    tr.BorderColor = System.Drawing.Color.White;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        td = new TableCell();
        td.Text = dt.Rows[j][i].ToString();

        tr.Cells.Add(td);
    }
    tblReport.Rows.Add(tr);
}
watch.Stop();
var elapsedMs = watch.ElapsedMilliseconds;
lblInfo.Text = ReportName + " Generation Time: " + elapsedMs.ToString();

Then, I created another method using EPPlus approach:

private void NewExport(string name, System.Data.DataTable dt)
{
    //DataSet ds = (DataSet)Session["DSIsoReport"];
    var watch = System.Diagnostics.Stopwatch.StartNew();

    using (pck = new ExcelPackage())
    {
        ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
        ws.Cells["A5"].LoadFromDataTable(dt, true);

        ws.DefaultColWidth = 25;

        int totalRow = ws.Dimension.End.Row;
        int totalCol = ws.Dimension.End.Column;

        var startCell = (ExcelRangeBase)ws.Cells["A5"];
        var endCell = startCell.Offset(0,0,1,totalCol);
        var headerCell = ws.Cells[endCell.ToString()];
        headerCell.Style.Fill.PatternType = ExcelFillStyle.Solid; 
        headerCell.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.BurlyWood);
        var headerFont = headerCell.Style.Font;
        headerFont.Bold = true;

        watch.Stop();
        var elapsedMs = watch.ElapsedMilliseconds;
        ws.Cells["A4"].LoadFromText(name + " Generation Time: " + elapsedMs.ToString());
    }
}

I have a REnder method that actually is responsible for generating excel spreadsheet out of the aspx page:

protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
    Response.AddHeader("content-disposition", "inline;filename=" + ReportName + ".xls");
    Response.ContentType = "application/vnd.ms-excel";
    //Response.BinaryWrite(pck.GetAsByteArray());
    base.Render(writer);
}

When running the application and generating the report with old way with looping, elapsed time is 355

But when using the EPPlus, elapsed time is 1676.

Looks like EPPlus is taking much longer time to execute.

From what I researched I thought EPPlus is efficient.

Is there any way to improve the code?

gene
  • 2,098
  • 7
  • 40
  • 98
  • What technology is your first approach using? It appears to be trying to create the table in HTML, not Excel. If that is the case, your comparison is not anywhere near apples-to-apples. I was expecting to see Excel Interop code for your original method, but I don't see that part. Also, what type of object is `tblReport`? That's not defined in the code you pasted. – devuxer Mar 15 '16 at 21:56
  • tblReport is an id of html table that is built with the code. Then I have also `Render` method that set content type of the page to execel. Look at the code modifications in the buttom – gene Mar 16 '16 at 13:54
  • Have a look at the ClosedXML library too, you can get it from NuGet and it uses OpenXML to generate Excel workbooks (as opposed to COM). It seems pretty efficient I think (though I don't have any metrics to back up that statement)... – Neil Hibbert Mar 16 '16 at 14:01
  • @NeilHibbert EPPlus uses OpenXML too, not COM. In fact, it is probably the most popular library. – Panagiotis Kanavos Mar 16 '16 at 14:03
  • @gene the first approach is a fake, it doesn't generate an Excel sheet at all. You can't compare the two approaches. You'll also notice that the HTML page generated by the first approach is far larger than the actual (compressed) xlsx file generated by EPPlus – Panagiotis Kanavos Mar 16 '16 at 14:05
  • @PanagiotisKanavos yes I am aware, it poor phrasing on my part, I meant that ClosedXML uses OpenXML as opposed to using COM, not that EPPlus didn't also use OpenXML... – Neil Hibbert Mar 16 '16 at 14:05
  • Possible duplicate of [Generating an Excel file in ASP.NET](http://stackoverflow.com/questions/150339/generating-an-excel-file-in-asp-net) – devuxer Mar 16 '16 at 19:16
  • LoadFromDataTable (and the other LoadFrom* functions in EPPlus) adds overhead so it will never be better then writing your own for loops in terms of speed (best case it is the same). I have actually written my own for loops to avoid using it with BIG datasets. The difference in time can be quite dramatic - I have shaved off minutes doing it this way so if you are seeing a problem you will probably want to do the same. – Ernie S Mar 16 '16 at 19:57

0 Answers0