Thursday, May 26, 2011

Free Programatic Interface to Excel in C# - EPPlus

Needing a way to programatically create Excel spreadsheets, I found EPPlus and it works great. It took about 10 minutes to create my first multi-workbook excel file.
You can download it at epplus.codeplex.com.
My toy code:
private static void WriteExcelBrandsList(BrandAndCodeRepository brandsRepository, Choice choice)
{
  using (ExcelPackage pck = new ExcelPackage()) {
    //Create the worksheet for each list
    foreach (var listName in brandsRepository.GetLists())
    {
      ExcelWorksheet ws = pck.Workbook.Worksheets.Add(listName);
      ws.Cells["A1"].Value = "Brand";
      ws.Cells["B1"].Value = "Fieldwork";
      var brandAndCodes = brandsRepository.GetList(listName);
      int i = 2;
      foreach (var brandAndCode in brandAndCodes)
      {
          ws.Cells["A" + i].Value = brandAndCode.BrandId;
          ws.Cells["B" + i].Value = "2011-01";
          i++;
      }
    }
    string fileName = string.Format("C:\\BrandLists_{0}_{1}.xlsx", choice.Category, choice.Locale);
    Console.Out.WriteLine("writing to fileName = {0}", fileName);
    pck.SaveAs(new FileInfo(fileName));
  }
}

1 comment:

ASPNet Razor said...

I have been struggling in creating an Excel spreadsheet using EPPLUS in MVC. Using the many examples on the internet, nothing worked except yours. Thanks for providing a simple workable example.