.NET PowerTip 2: Writing Data to Excel

Posted by

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.

Example:

http://stackoverflow.com/questions/19933135/writing-to-excel-using-c-sharp

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:
http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx

2. Using Open XML SDK:

Use the Open XML standard for interaction. Example here:
http://stackoverflow.com/questions/527028/open-xml-sdk-2-0-how-to-update-a-cell-in-a-spreadsheet

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:

excel

Great stuff!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.