This project has moved and is read-only. For the latest updates, please go here.

Performance - EPPlus versus Spreadsheet Gear

Nov 22, 2012 at 11:47 AM

First off: Thanks for a great product!

Secondly: I've made a simple performance test between EPPlus and Spreadsheet Gear to see if there is any significant difference that would justify buying Spreadsheet Gear.

I am no expert at either application so it's possible the tests aren't written the most efficient way.

The test does the following:

1. Opens an existing Excel-file with 1000 rows and 3 columns. Saves the three values into an entity that is saved into a List<>.

2. Open a new Excel-object

3. Create a header row (bold) with the title of each column.

4. Write back the 1000 entities.

5. Save the new Excelfile.

If I run this test once EPPlus comes out the winner (approx times are EPPlus = 280ms, SG = 500ms). If I run the test 10 times in a row instead (a for-loop opening, copying, saving 10 seperate times) Spreadsheet Gear is faster instead (approx times per file: EPPlus = 165ms, SG = 95ms). For 20 tests the approx times are EPPlus = 160ms / file and SG = 60ms / file.

It seems like (to a certain extent at least) Spreadsheet Gears gets faster and faster the more files I create. Could anyone explain why EPPlus is the slower one when running consecutive tests? And can I make changes to the code to change this?

EPPlus test function:

  var timer = new Stopwatch();
  timer.Start();
  var data = new List<Item>();
  using (var excelIn = new ExcelPackage(new FileInfo(folder + fileIn)))
  {
    var sheet = excelIn.Workbook.Worksheets[1];
    var row = 2;
    while (sheet.Cells[row, 1].Value != null)
    {
      data.Add(new Item()
        {
          Id = int.Parse(sheet.Cells[row, 1].Text),
          Title =  sheet.Cells[row, 2].Text,
          Value = int.Parse(sheet.Cells[row, 3].Text)
        });

      row++;
    }
  }

  using (var excelOut = new ExcelPackage())
  {
    var sheet = excelOut.Workbook.Worksheets.Add("Out");
    sheet.Cells.LoadFromCollection(data);
    sheet.InsertRow(1, 1);
    sheet.Cells[1, 1, 1, 3].Style.Font.Bold = true;
    sheet.Cells[1, 1].Value = "Id";
    sheet.Cells[1, 2].Value = "Title";
    sheet.Cells[1, 3].Value = "Value";

    excelOut.SaveAs(new FileInfo(folder + "EPPlus_" + Guid.NewGuid() + ".xlsx"));
  }

  timer.Stop();
  return timer.ElapsedMilliseconds;

Spreadsheet Gear:

  var timer = new Stopwatch();
  timer.Start();

  var data = new List<Item>();
  var excelIn = Factory.GetWorkbook(folder + fileIn);
  var sheetIn = excelIn.Worksheets[0];
  var rowIn = 1;
  while (sheetIn.Cells[rowIn, 0].Value != null)
  {
    data.Add(new Item()
    {
      Id = int.Parse(sheetIn.Cells[rowIn, 0].Text),
      Title = sheetIn.Cells[rowIn, 1].Text,
      Value = int.Parse(sheetIn.Cells[rowIn, 2].Text)
    });

    rowIn++;
  }
  excelIn.Close();

  var excelOut = Factory.GetWorkbook();
  var sheetOut = excelOut.Worksheets.Add();
  sheetOut.Name = "Out";
  var rowOut = 0;
  sheetOut.Cells[rowOut, 0, rowOut, 2].Font.Bold = true;
  sheetOut.Cells[rowOut, 0].Value = "Id";
  sheetOut.Cells[rowOut, 1].Value = "Title";
  sheetOut.Cells[rowOut++, 2].Value = "Value";

  foreach (var item in data)
  {
    sheetOut.Cells[rowOut, 0].Value = item.Id;
    sheetOut.Cells[rowOut, 1].Value = item.Title;
    sheetOut.Cells[rowOut++, 2].Value = item.Value;
  }

  excelOut.SaveAs(folder + "SpreadsheetGear_" + Guid.NewGuid() + ".xlsx", FileFormat.OpenXMLWorkbook);
  excelOut.Close();

  timer.Stop();
  return timer.ElapsedMilliseconds;

Main function

  var runs = 1;
  var testerG = new TestSpreadsheetGear();
  var testerE = new TestEpPlus();
  var msE = 0.0;
  var msG = 0.0;
  var i = 0;
  for (i = 0; i < runs; ++i)
  {
    msG += new TestSpreadsheetGear().Run(folder, originalFile);
  }

  for(i = 0; i < runs; ++i)
  {
    msE += new TestEpPlus().Run(folder, originalFile);
  }

  Console.WriteLine("Spreadsheet time: " + msG + ". Per file: " + msG / runs);
  Console.WriteLine("EP Plus time: " + msE + ". Per file: " + msE / runs);
  Console.ReadKey();

Nov 22, 2012 at 12:38 PM
Edited Nov 22, 2012 at 1:03 PM

Probably, EPPlus is faster for editing cells but not for opening and closing file, when you execute your test once, this is not a problem, but when you are running a dozen times, it is.

I am guessing here =) 

The only way to discover the truth is running your code with a profilling tool, like jetbrain dotTrace.

Nov 23, 2012 at 7:15 AM

Good idea, I should try that! Thank you!