.NET PowerTip 2: Writing Data to Excel

Every now and then, developers have to write data to excel. There are several approaches:

1. Full fledged Excel Interop:

Add the interop assembly to your project, connect to your local excel application, create workbook, sheet, fill data, etc.



Drawback 1: You need excel on the client.
Drawback 2: The Excel version installed must match the interop assembly version
Drawback 3: Interop is expensive. Try to minimize interop calls and avoid cell-by-cell updates. Interesting blog post regarding performance of Excel interop here:

2. Using Open XML SDK:

Use the Open XML standard for interaction. Example here:

Drawback 1: Fiddling with the Open XML object model is not funny.

3. Using a 3rd party library such as ClosedXML

ClosedXML (http://closedxml.codeplex.com/) is a wrapper around the Open XML Specification. It is by far my favorite because it is incredibly easy to use. In this example I write data into a ADO.NET DataTable and then save it into Excel.

Documentation is great as well: http://closedxml.codeplex.com/documentation

Just add the ClosedXML Nuget Package to your project and write this code:

DataTable table = new DataTable();
table.Columns.Add("FirstName", typeof(string));
table.Columns.Add("LastName", typeof(string));
table.Columns.Add("Age", typeof(string));
table.Rows.Add("Manuel", "Meyer", 36);
table.Rows.Add("John", "Doe", 22);

var workbook = new XLWorkbook();
var sheet = workbook.Worksheets.Add("My Data");
sheet.Cell(7, 1).Value = "Data from DataTable"; //Set a title
sheet.Range(7, 1, 7, 3).Merge().AddToNamed("People");  //Define a section
var tableWithData = sheet.Cell(8, 1).InsertTable(table.AsEnumerable()); //Insert the data


Super easy to use. The result looks like this:


Great stuff!