Is EPPlus faster then regular loop logic to map datatable to excel spreadsheet

I'm looking for the way to improve Datatable mapping to Excel spreadsheet.

Current code using looping technique to import data from DataTable to Excel:

for (int i = 0; i < dt.Columns.Count; i++)
{
       thc = new TableHeaderCell();
       thc.BorderWidth = 1;
       thc.BorderStyle = BorderStyle.Solid;
       //thc.Style.Add("backgroundColor", "#cacab5");
       thc.BorderColor = System.Drawing.Color.White;
       thc.BackColor = System.Drawing.Color.BurlyWood;
       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;
     if (bt == "IE")
         tr.BorderWidth = 1;
     else
         tr.BorderWidth = 2;
     tr.BorderColor = System.Drawing.Color.White;

     long n;

     for (int i = 0; i < dt.Columns.Count; i++)
     {
         td = new TableCell();
         td.BorderWidth = 1;
         td.BorderStyle = BorderStyle.Solid;
         td.BorderColor = System.Drawing.Color.White;
         td.BackColor = System.Drawing.Color.Bisque;
         td.Text = dt.Rows[j][i].ToString();

         n = 0;
         bool isNumeric = long.TryParse(td.Text, out n);
         if (isNumeric && td.Text.Length > 10)
                 td.Attributes.Add("style", @"mso-number-format:\@");

         tr.Cells.Add(td);
     }
     tblReport.Rows.Add(tr);
}

I found another way to do it through EPPlus.

Which way is faster?

Answers


EPPlus is very fast and efficient as is. Anything that is going to make it look like it copies your recordset to the workbook in one method is going to internally be looping over columns and rows in one fashion or another. So, the answer is to look at how many things you are doing and how you might optimize them.

From the code snippet you provided, two things I would recommend: First, you do not have to explicitly add rows to the worksheet before inserting values into the cells. This will slow things down quite a lot as your in memory EPPlus object gets bigger. If you are always just writing to the next row, then you never have to explicitly add a row, just reference your next row by index.

Next do not set the display attributes on every cell each time you write a value to the cell. Set it for the whole row (or column) using a range. Or better yet, do it for the whole worksheet at once after you have written out all the data in the cells for all your columns and rows. An example might be like the following which sets a Style setting for the columns A through E in one method call:

using (ExcelRange er = myWorksheet.Cells["A" + lineNo.ToString() + ":E" + lineNo.ToString()]) { er.Style.Font.Bold = true; }


The fastest way I have found is to pass the DataTable into the LoadFromDataTable function for a range of cells.

        _workSheet.Cells[startRow,startColumn,endRow,endColumn].LoadFromDataTable(dataTable);

Need Your Help

How do I use a variable to build an array name?

jquery arrays function

I have several sets of arrays. Each set begins with the same word:

Using Php to execute command line

php command-line cmd

I got some problem while using php to execute command line.