.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 sheet.Columns().AdjustToContents(); workbook.SaveAs("CoolPeople.xlsx");
Super easy to use. The result looks like this: