Handling Excel Files with NPOI
Tags: netcore, core, dotnet, excel
Published: 2020-02-14 20:09:15

---
title: Handling Excel Files with NPOI
published: true
date: 2020-01-13 23:36:27 UTC
tags: .Net Core,core,dotnet,excel
canonical_url: https://blog.torib.io/2020/01/13/handling-excel-files-with-npoi/
---

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](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.

<script src="https://gist.github.com/eatskolnikov/8059590b02c01d6f38f010085679515e.js"></script>

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](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<T>() where you specify the class that would represent a row of data.

<script src="https://gist.github.com/eatskolnikov/f3915bb118c473f397bde20a6f7814e8.js"></script>

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](https://blog.torib.io/2020/01/13/handling-excel-files-with-npoi/) appeared first on [Enmanuel Toribio](https://blog.torib.io).