CsvHelper for Excel (using EPPlus) is an extension that links two excellent libraries: CsvHelper and EPPlus.
It provides implementations of IParser and IWriter from CsvHelper that read and write Excel documents using EPPlus. Encrypted/password-protected Excel documents are supported.
Setup
You have a choice of two packages. It'll probably come down to your licensing requirements:
CsvHelper.Excel.EPPlus4 depends on EPPlus 4. This version of EPPlus is LGPL licensed. Consider this version if the other one is not available for your use.
Install the appropriate package from NuGet.org into your project. E.g.:
dotnet add package CsvHelper.Excel.EPPlus
Or using the Package Manager Console with the following command:
PM> Install-Package CsvHelper.Excel.EPPlus
Add the CsvHelper.Excel.EPPlus namespace to your code and check the examples below.
If you need to parse or write to a password-protected Excel document you will need to first create an instance of ExcelPackage yourself (e.g. new ExcelPackage("file.xlsx", password)) and then use one of the constructor overloads described below which take that as a parameter.
Using ExcelParser
ExcelParser implements IParser and allows you to specify the path of an Excel package, pass an instance of ExcelPackage, ExcelWorkbook, ExcelWorksheet, ExcelRange or a Stream that you have already loaded to use as the data source.
All constructor overloads have an optional parameter for passing your own CsvConfiguration (IParserConfiguration), otherwise a default constructed using the InvariantCulture is used.
By default the first worksheet is used as the data source, though you can specify a particular worksheet using the sheetName parameter.
Unless you set leaveOpen to true, disposing ExcelParser will also automatically dispose the provided Stream.
using var reader = new CsvReader(new ExcelParser(File.Open("path/to/file.xlsx", FileMode.Open)));
var people = reader.GetRecords<Person>();
Or explicitly managing all the dependency lifetimes rather than relying on the library to do it:
using var stream = File.Open("path/to/file.xlsx", FileMode.Open);
using var parser = new ExcelParser(stream, leaveOpen:true);
using var reader = new CsvReader(parser, leaveOpen:true);
var people = reader.GetRecords<Person>();
By default the first worksheet is used as the data source, though you can specify a particular worksheet using the sheetName parameter.
Unless you set leaveOpen to true, disposing ExcelParser will also automatically dispose the provided ExcelPackage.
using var reader = new CsvReader(new ExcelParser(new ExcelPackage("path/to/file.xlsx")));
var people = reader.GetRecords<Person>();
Or explicitly managing all the dependency lifetimes rather than relying on the library to do it:
using var package = new ExcelPackage("path/to/file.xlsx");
using var parser = new ExcelParser(package, leaveOpen:true);
using var reader = new CsvReader(parser, leaveOpen:true);
var people = reader.GetRecords<Person>();
By default the first worksheet is used as the data source, though you can specify a particular worksheet using the sheetName parameter.
Unless you set leaveOpen to true, disposing ExcelParser will also automatically dispose the provided ExcelWorkbook.
With this overload, ExcelParser has no access to, or even knowledge of, the ExcelPackage which the workbook belongs to so you still need to ensure the ExcelPackage is appropriately disposed.
using var package = new ExcelPackage("path/to/file.xlsx");
using var reader = new CsvReader(new ExcelParser(package.Workbook));
var people = reader.GetRecords<Person>();
Or explicitly managing all the dependency lifetimes rather than relying on the library to do it:
using var package = new ExcelPackage("path/to/file.xlsx");
using var parser = new ExcelParser(package.Workbook, leaveOpen:true);
using var reader = new CsvReader(parser, leaveOpen:true);
var people = reader.GetRecords<Person>();
Unless you set leaveOpen to true, disposing ExcelParser will also automatically dispose the ExcelWorkbook that owns the provided ExcelWorksheet.
With this overload, ExcelParser has no access to, or even knowledge of, the ExcelPackage which the worksheet belongs to so you still need to ensure the ExcelPackage is appropriately disposed.
using var package = new ExcelPackage("path/to/file.xlsx");
using var reader = new CsvReader(new ExcelParser(package.Workbook.Worksheets.First(sheet => sheet.Name == "Folk")));
var people = reader.GetRecords<Person>();
Or explicitly managing all the dependency lifetimes rather than relying on the library to do it:
using var package = new ExcelPackage("path/to/file.xlsx");
var worksheet = package.Workbook.Worksheets.First(sheet => sheet.Name == "Folk");
using var parser = new ExcelParser(worksheet, leaveOpen:true);
using var reader = new CsvReader(parser, leaveOpen:true);
var people = reader.GetRecords<Person>();
This overload allows you to restrict the parsing to a specific range of cells within an Excel worksheet.
With this overload, ExcelParser has no access to, or even knowledge of, the ExcelPackage which the range belongs to so you still need to ensure the ExcelPackage is appropriately disposed.
Unless you set leaveOpen to true, disposing ExcelParser will also automatically dispose the ExcelWorkbook that owns the provided ExcelRange.
using var package = new ExcelPackage("path/to/file.xlsx");
var range = package.Workbook.Worksheets.First(sheet => sheet.Name == "Folk").Cells[2, 5, 400, 33];
using var reader = new CsvReader(new ExcelParser(range));
var people = reader.GetRecords<Person>();
Or explicitly managing all the dependency lifetimes rather than relying on the library to do it:
using var package = new ExcelPackage("path/to/file.xlsx");
var range = package.Workbook.Worksheets.First(sheet => sheet.Name == "Folk");
using var parser = new ExcelParser(range, leaveOpen:true);
using var reader = new CsvReader(parser, leaveOpen:true);
var people = reader.GetRecords<Person>();
Using ExcelWriter
ExcelWriter implements IWriter and, like ExcelParser, allows you to specify the path to (eventually) save the workbook, pass an instance of ExcelPackage that you have already created, or pass a specific instance of ExcelWorksheet, ExcelRange or Stream to use as the destination.
Unlike ExcelParser and CsvReader however where CsvReader wraps ExcelParser, here ExcelWriter inherits from CsvWriter and should be used directly instead.
All constructor overloads have an optional parameter for passing your own CsvConfiguration (IWriterConfiguration), otherwise a default constructed using the InvariantCulture is used.
When the path is passed to the constructor the writer manages the creation & disposal of the workbook and worksheet (named "Export" by default). The workbook is saved only when the writer is disposed.
using var writer = new ExcelWriter("path/to/file.xlsx");
writer.WriteRecords(people);
Important: The data is saved only when the ExcelWriter is disposing or the consumer manually calls package.Save() or package.SaveAs(...).
This overload is the same as the one which takes ExcelPackage and sheetName parameters, but accepts a worksheet reference rather than name.
Unless you set leaveOpen to true, disposing ExcelWriter will also automatically dispose the provided ExcelPackage.
using var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Folk");
using var writer = new ExcelWriter(package, worksheet);
writer.WriteRecords(people);
package.SaveAs("path/to/file.xlsx");
Or
using var package = new ExcelPackage("path/to/file.xlsx");
var worksheet = package.Workbook.Worksheets.Add("Folk");
using var writer = new ExcelWriter(package, worksheet);
writer.WriteRecords(people);
Important: The data is saved only when the ExcelWriter is disposing or the consumer manually calls package.Save() or package.SaveAs(...).
This overload is similar to the previous ones but accepts an ExcelRange instead, allowing targeting a specific range of cells within an Excel worksheet.
Unless you set leaveOpen to true, disposing ExcelWriter will also automatically dispose the provided ExcelPackage.
using var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Folk");
using var writer = new ExcelWriter(package, worksheet.Cells[2, 5, 400, 33]);
writer.WriteRecords(people);
package.SaveAs("path/to/file.xlsx");
Or
using var package = new ExcelPackage("path/to/file.xlsx");
var worksheet = package.Workbook.Worksheets.Add("Folk");
using var writer = new ExcelWriter(package, worksheet.Cells[2, 5, 400, 33]);
writer.WriteRecords(people);
