A while back I was tasked with extending some existing export/import functionality to support Excel, in addition to Csv. I had my fingers crossed that the existing code used Josh Close’s excellent CsvHelper, and I was not disappointed.
We already had a partially implemented module that exported certain entities using ClosedXml, but it was largely repeating code that was already written for the Csv exports, so was a prime target for refactoring. I was very impressed with the model provided by the ClosedXml team, so I wanted to continue using it to create and format the spreadsheets themselves, while using CsvHelper to handle the data.
Fortunately, CsvHelper is very extensible. Just implement your own ICsvParser and ICsvSerializer and you can read and/or write from whatever source you want. I’ve put my work on Github and it’s also available as a NuGet package. Both the parser and serialiser offer overloaded constructors allowing broader or finer grained control over the underlying workbook depending on the situation. Want to just dump the data into a new workbook? Just pass in the path and let the serialiser handle creation of the workbook and worksheet, and subsequent disposal. Prefer to specify the worksheet, so you can apply formatting after? Simply pass in the workbook or worksheet itself, and handle creation and disposal yourself. Its very similar for reading with the parser.
Using the code is simple.
public void Export(IEnumerable<Foo> foos, string path)
{
using (var writer = new CsvWriter(new ExcelSerialiser(path)))
{
writer.Configuration.AutoMap<Foo>();
writer.WriteRecords(foos);
}
}
public IEnumerable<Foo> Import(string path)
{
using (var reader = new CsvReader(new ExcelParser(path)))
{
reader.Configuration.AutoMap<Foo>();
return reader.GetRecords<Foo>().ToList();
}
}