1. Products
  2.   Spreadsheet
  3.   .NET
  4.   NPOI
 
  

Free .NET Library for Excel® Spreadsheets

Read, write, manipulate & convert XLS & XLSX files via open-source .NET library.

What is NPOI?

NPOI is a .NET version of POI Java project. It is an open source .NET library to read and write Microsoft Excel file formats. NPOI.HSSF namespace provides the ability to manipulate XLS file format, while NPOI.XSSF namespace allows you to create & modify XLSX files.

NPOI allows you to add text, insert hyperlinks, create & style cells & columns, insert images and read content from existing XLS & XLSX files without any external dependency.

Previous Next

How to Install NPOI?

Install NPOI from NuGet

 Install-Package NPOI -Version 2.4.1

Manipulate XLSX File via C#

NPOI allows .NET programmers to create as well as modify spreadsheets from their own .NET applications. In order to modify an existing file, you can load the file and update text, tables, styles, and more.

Edit XLSX with NPOI - C#

IWorkbook wb = new XSSFWorkbook();

// Create a Worksheet
ISheet ws = wb.CreateSheet("FileFormat");

ICellStyle style = wb.CreateCellStyle();

//Setting the line of the top border
style.BorderTop = BorderStyle.Thick;
style.TopBorderColor = 256;

style.BorderLeft = BorderStyle.Thick;
style.LeftBorderColor = 256;

style.BorderRight = BorderStyle.Thick;
style.RightBorderColor = 256;

style.BorderBottom = BorderStyle.Thick;
style.BottomBorderColor = 256;

IRow row = ws.CreateRow(0);
ICell cell = row.CreateCell(1);
cell.CellStyle = style;

FileStream sw = File.Create("fileformat.xlsx");
wb.Write(sw);
sw.Close();

Convert XLS to XLSX with NPOI

Follow the steps in order to save XLS file as XLSX after you opened and modified it using NPOI.

  1. Create new XSSFWorkbook
  2. Create appropriate XSSFSheet for each worksheet of XLS
  3. Copy data from XLS worksheet to XLSX worksheet
  4. Copy formatting from XLS worksheet to XLSX worksheet
  5. Save workbook in XLSX format

Convert XLS to XLSX with NPOI - C#

HSSFWorkbook retVal = new HSSFWorkbook();
for (int i = 0; i < source.NumberOfSheets; i++)
{
	HSSFSheet hssfSheet = (HSSFSheet)retVal.CreateSheet(source.GetSheetAt(i).SheetName);

	XSSFSheet xssfsheet = (XSSFSheet)source.GetSheetAt(i);
	CopySheets(xssfsheet, hssfSheet, retVal);
}

Add Image to XLSX via C#

The API allows the developers to add images in spreadsheet documents. You can add an image and set image properties. The API allows various methods to manipulate images in XLSX file format easily. IClientAnchor allows you to set the top, bottom, left, and right positioning of the image inside the worksheet.

Create Table with XSSF NPOI - C#

IWorkbook wb = new XSSFWorkbook();
ISheet sheet1 = wb.CreateSheet("First Sheet");

// Add picture data to this workbook.
byte[] bytes = File.ReadAllBytes("fileformat.png");
int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);

ICreationHelper helper = wb.GetCreationHelper();

// Create the drawing patriarch. This is the top level container for all shapes.
IDrawing drawing = sheet1.CreateDrawingPatriarch();

// add a picture shape
IClientAnchor anchor = helper.CreateClientAnchor();

// set top-left corner of the picture,
// subsequent call of Picture#resize() will operate relative to it
anchor.Col1 = 3;
anchor.Row1 = 2;
IPicture pict = drawing.CreatePicture(anchor, pictureIdx);
// auto-size picture relative to its top-left corner
pict.Resize();

FileStream sw = File.Create("image.xlsx");
wb.Write(sw);
sw.Close();
 English