Handling Excel Files with NPOI

#netcore #core #dotnet #excel

NPOI is a library for working with Office documents like Word and Excel. I mainly been using it for reading and writing Excel files so that’s what I’m going to write about. It works with .csv and .xlsx formats and is based on the Apache POI project for Java.

It works for .net core and you can check their repo here https://github.com/dotnetcore/NPOI

To use it you have to include the Nuget package DotNetCore.NPOI

Install-Package DotNetCore.NPOI

The NPOI package defines a few interfaces and classes that you will find very familiar if you have ever worked on a spreadsheet. There are the IWorkbook , ISheet and IRow interfaces that respectively represent Workbooks, Sheets and Rows of the document.

To create a spreadsheet you need to instantiate a Workbook ( XSSFWorkbook ), you can then add Sheets to the workbook and then rows and cells into the Sheets you have created. You can even define the format of the cells, the value types and even combine them if necessary.

Finally to write the content of the workbook to a file you have to copy its content to a FileStream.

If you want to read the content of a file, pass its route or a stream to an instance of XSSFWorkbook and then proceed to retrieve the sheets, rows and cells as you wish. Take into consideration that you will only get as many instance of cells in a row as there are cells with values. Empty cells won’t be instantiated so you have to be very careful about how you read the data.

While NPOI gives you a lot of control on how you want to do things, you may find yourself wanting to just map a collection of a certain object type to an Excel file with named columns. For that specific case we have this other library NPOI.Mapper https://github.com/donnytian/Npoi.Mapper

To use NPOI.Mapper just include the nuget package

Install-Package Npoi.Mapper

Now you can instantiate a Mapper with the route of the file you want to read or a stream to it as a parameter. To read the data just call the method Take() where you specify the class that would represent a row of data.

If a column doesn’t have a row with a name the Mapper will set the name to the Excel’s display name, like A,B,C, …, AD and so on.

I hope you find this useful and if you have any doubts the repositories are very well documented and pretty straight forward. If you still have some confusion you can write to me on twitter @eatskolnikov . Have a good day

The post Handling Excel Files with NPOI appeared first on Enmanuel Toribio.