NPOI Overview

NPOI is an open-source library that enables reading and writing Excel (.xls), Word (.doc), and PowerPoint (.ppt) files with out requiring Microsoft Office to be installed. Built on top of Apache POI 3.x, NPOI provides complete functionality for manipulating Office 97-2003 documents, supporting formats such as .xls, .doc, and .ppt.

Project URL: http://npoi.codeplex.com/ NuGet Package: https://www.nuget.org/packages/NPOI

Installation

Install via NuGet Package Manager console:

Install-Package NPOI

Alternatively, search for and install the package through the NuGet Package Manager GUI in Visual Studio.

Excel File Format Selection

Excel 2003 format (.xls) uses the HSSFWorkbook clas and supports a maximum of 65,536 rows:

HSSFWorkbook workbook = new HSSFWorkbook();

Excel 2007 and later formats (.xlsx) use the XSSFWorkbook class and support up to 1,048,576 rows:

XSSFWorkbook workbook = new XSSFWorkbook();

Creating an Excel File with Data

// Create workbook
HSSFWorkbook wk = new HSSFWorkbook();

// Create sheet named "dataSheet"
ISheet table = wk.CreateSheet("dataSheet");

// Create second row (index 1)
IRow dataRow = table.CreateRow(1);

for (int col = 0; col < 20; col++)
{
    ICell cell = dataRow.CreateCell(col);
    cell.SetCellValue(col);
}

// Write to file
using (FileStream fs = File.OpenWrite(filePath + "\\output.xls"))
{
    wk.Write(fs);
    Console.WriteLine("File generated successfully!");
}

Creating an Excel File with Styled Cells

public static void GenerateStyledExcel()
{
    IWorkbook workbook = new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("products");

    // Set column widths
    sheet.SetColumnWidth(0, 15 * 256);
    sheet.SetColumnWidth(1, 35 * 256);
    sheet.SetColumnWidth(2, 15 * 256);
    sheet.SetColumnWidth(3, 10 * 256);

    // Merged header row
    sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3));
    IRow titleRow = sheet.CreateRow(0);
    titleRow.Height = 400;
    ICell titleCell = titleRow.CreateCell(0);
    titleCell.CellStyle = CreateCellStyle(workbook, CellStyleType.Header);
    titleCell.SetCellValue("Product Information");

    // Table headers
    IRow headerRow = sheet.CreateRow(1);
    headerRow.Height = 400;

    ICell cell1 = headerRow.CreateCell(0);
    cell1.CellStyle = CreateCellStyle(workbook, CellStyleType.Header);
    cell1.SetCellValue("Product Name");

    ICell cell2 = headerRow.CreateCell(1);
    cell2.CellStyle = CreateCellStyle(workbook, CellStyleType.Header);
    cell2.SetCellValue("URL");

    ICell cell3 = headerRow.CreateCell(2);
    cell3.CellStyle = CreateCellStyle(workbook, CellStyleType.Header);
    cell3.SetCellValue("Price");

    ICell cell4 = headerRow.CreateCell(3);
    cell4.CellStyle = CreateCellStyle(workbook, CellStyleType.Header);
    cell4.SetCellValue("Stock");

    using (FileStream stream = File.OpenWrite(outputPath + "\\products.xls"))
    {
        workbook.Write(stream);
        Console.WriteLine("Export completed");
    }
}

public enum CellStyleType
{
    Header,
    Currency,
    Percentage,
    Date,
    Default
}

static ICellStyle CreateCellStyle(IWorkbook workbook, CellStyleType styleType)
{
    ICellStyle style = workbook.CreateCellStyle();

    // Create fonts
    IFont headerFont = workbook.CreateFont();
    headerFont.FontHeightInPoints = 12;
    headerFont.FontName = "Arial";

    IFont bodyFont = workbook.CreateFont();
    bodyFont.FontName = "Arial";

    IFont linkFont = workbook.CreateFont();
    linkFont.Color = HSSFColor.Blue.Index;
    linkFont.IsItalic = true;
    linkFont.FontName = "Arial";

    // Border styles
    style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
    style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

    // Background color
    style.FillForegroundColor = HSSFColor.LightGreen.Index;
    style.FillBackgroundColor = HSSFColor.White.Index;

    // Alignment
    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
    style.VerticalAlignment = VerticalAlignment.Center;

    // Text wrapping
    style.WrapText = true;

    switch (styleType)
    {
        case CellStyleType.Header:
            style.SetFont(headerFont);
            style.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
            break;
        case CellStyleType.Date:
            IDataFormat dateFormat = workbook.CreateDataFormat();
            style.DataFormat = dateFormat.GetFormat("yyyy-mm-dd");
            style.SetFont(bodyFont);
            break;
        case CellStyleType.Currency:
            IDataFormat currencyFormat = workbook.CreateDataFormat();
            style.DataFormat = currencyFormat.GetFormat("$#,##0.00");
            style.SetFont(bodyFont);
            break;
        case CellStyleType.Percentage:
            style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
            style.SetFont(bodyFont);
            break;
        case CellStyleType.Default:
            style.SetFont(bodyFont);
            break;
    }

    return style;
}

Reading Excel File Data

StringBuilder content = new StringBuilder();

using (FileStream fs = File.OpenRead(inputPath + "\\products.xls"))
{
    HSSFWorkbook workbook = new HSSFWorkbook(fs);

    for (int sheetIndex = 0; sheetIndex < workbook.NumberOfSheets; sheetIndex++)
    {
        ISheet currentSheet = workbook.GetSheetAt(sheetIndex);

        for (int rowIndex = 0; rowIndex <= currentSheet.LastRowNum; rowIndex++)
        {
            IRow currentRow = currentSheet.GetRow(rowIndex);

            if (currentRow != null)
            {
                content.Append("\r\n================================\r\n");

                for (int colIndex = 0; colIndex <= currentRow.LastCellNum; colIndex++)
                {
                    ICell currentCell = currentRow.GetCell(colIndex);
                    if (currentCell != null)
                    {
                        content.Append(currentCell.ToString() + "\t");
                    }
                }
            }
        }
    }
}

// Export to text file
using (StreamWriter writer = new StreamWriter(new FileStream(outputPath + "\\results.txt", FileMode.Append)))
{
    writer.Write(content.ToString());
    writer.Flush();
    Console.WriteLine("Data extraction completed!");
}

MissingCellPolicy Enumeration

When iterating through columns in a row, some cells may be empty or non-existent, which can cause NullReferenceException errors. The MissingCellPolicy enumerasion, passed to the GetCell method, instructs NPOI how to handle missing or blank cells:

• CREATE_NULL_AS_BLANK: Returns a new blank cell when the requested cell does not exist. This prevents NullReferenceException and creates cells with "blank" type.
• RETURN_BLANK_AS_NULL: Returns null even when a cell exists but has a blank type. Useful for skipping empty cells during processing.
• RETURN_NULL_AND_BLANK: Default behavior. Returns null for non-existent cells and returns existing cells with blank type unchanged.

ICell cell = row.GetCell(0, MissingCellPolicy.RETURN_BLANK_AS_NULL);