ClosedXML is a fine library for exporting data to Excel. It is very simple to use and fast enough for smaller sets of data. But it also has some cons:

1.) It uses OpenXML – that is not a con by itself, but the problem appears when you want to save file as Xls. XML concept of Office files was presented with Office 2007 and is not compatible with previous versions of Office. But if you really need Xls, you can create Excel file using ClosedXML and than open and save it as Xls using Interop. That is only possible if you are exporting data on client side with installed PIA and Office. It is strongly advised against using Interop on server side due to the fact that Office is client side application!
2.) It is very slow for big datasets (more than few 10 thousand rows). For exporting big datasets you should use OpenXML (maybe I will talk about that in one of my next posts).
3.) On project’s CodePlex site it is written (quote): “ClosedXML makes it easier for developers to create Excel 2007/2010 files.”. But I believe Excel 2013 and newer should also open files generated with ClosedXML (haven’t tested it though).

Firstly you have to add reference to ClosedXML. You can do that using NuGet Package Manager or you can download ClosedXML DLL from CodePlex and add a reference to your project.

You also need to add a namespace to your CS file (using ClosedXML.Excel;).

Using ClosedXML you can set specific cell values, you can insert data from DataTable or from some other collection. In this post I will show you how to create generic function, which inserts data to Excel from IEnumerable of objects. I will pass column titles in separate List of string arrays.

---

Info: I could probably set column titles using descriptors on object properties – something like this:

[MyAttribute("First property")]
public string FstProperty
{
  get {....}
  set {....}
}

But for the sake of the example I won’t complicate – I will pass the titles to the function in a separate List.

---

Our function should look something like this:

public byte[] ExportToExcel<T>(IEnumerable<T> data, string worksheetTitle, List<string[]> titles)
{  
  var wb = new XLWorkbook(); //create workbook
  var ws = wb.Worksheets.Add(worksheetTitle); //add worksheet to workbook

  ws.Cell(1, 1).InsertData(titles); //insert titles to first row
            
  if (data != null && data.Count() > 0)
  {
    //insert data to from second row on
    ws.Cell(2, 1).InsertData(data); 
  }

  //save file to memory stream and return it as byte array
  using (var ms = new MemoryStream())
  {
    wb.SaveAs(ms);

    return ms.ToArray();
  }
}

And that is it! If we wish to add some special styling to our heading row, we have to modify our code snippet a little:

var rangeTitle = ws.Cell(1, 1).InsertData(titles); //insert titles to first row
rangeTitle.AddToNamed("Titles");

var titlesStyle = wb.Style;
titlesStyle.Font.Bold = true; //font must be bold
titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //align text to center
titlesStyle.Fill.BackgroundColor = XLColor.Red; //background must be red

wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; //attach style to the range

We can also adjust column width to maximum content width using function AdjustToContents right before we save the file:

ws.Columns().AdjustToContents();