Excel Generation in C# using NPOI Library

Installation

NPOI is a popular package for reading and manipulating Excel in C#. To get started with NPOI, you need to install the NPOI NuGet package into your project. You can do this using the NuGet Package Manager Console by dotnet add package NPOI or Visual Studio's NuGet Package Manager.

Basic Usage

• Create a new workbook
• Add sheets to the workbook
• Populate cells with data
• Add formula
• Merge Cells
• Formatting and styling

Note: Accessing cells is similar to accessing a 2D array.

using System;
using System.IO;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

class Program
{
    static void Main()
    {
        // Create a new workbook and sheet
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet 1");

        IRow row = sheet.CreateRow(0);
        ICell cell = row.CreateCell(0);
        cell.SetCellValue("List of employee");

        // lets make a simple table with header
        IRow headers = sheet.CreateRow(1);
        ICell header_id_cell = headers.CreateCell(0);
        ICell header_name_cell = headers.CreateCell(1);

        // assign value to header cell
        header_id_cell.SetCellValue("ID");
        header_name_cell.SetCellValue("Name");

        // add background color to header
        ICellStyle headerStyle = workbook.CreateCellStyle();
        headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
        headerStyle.FillPattern = FillPattern.SolidForeground;

        header_id_cell.CellStyle = headerStyle;
        header_name_cell.CellStyle = headerStyle;


        // Create some data
        for (int i = 2; i < 10; i++)
        {
            row = sheet.CreateRow(i);
            row.CreateCell(0).SetCellValue(i - 1);
            row.CreateCell(1).SetCellValue($"Name {i - 1}");
        }

        // Save the workbook to a file
        string filePath = "Output.xlsx";
        using (FileStream fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fileStream);
        }

        Console.WriteLine($"Excel file created at: {Path.GetFullPath(filePath)}");
    }
}

We can do lots of things with this package. Here are some examples.

Lock specific cell

// Protect the sheet
sheet.ProtectSheet("password");
//Pass the cell you want to make read-only
static void SetCellReadOnly(ICell cell)
{
    // Create a new style with the "locked" property set to true
    ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
    style.IsLocked = true;

    // Apply the style to the cell
    cell.CellStyle = style;
}

Merge Cell

static void MergeCells(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol)
{
    sheet.AddMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
}
// example:
// Merge cells A1 to D1
MergeCells(sheet, 0, 0, 0, 3);
// Merge cells B2 to D5
MergeCells(sheet, 1, 4, 1, 3);

Add Formula

static void AddFormulaToCell(ICell cell, string formula)
{
    cell.CellFormula = formula;
}
// Example formula: Sum of values in cells B3 to B9
AddFormulaToCell(totalCell, "SUM(B3:B9)");
For using the formula into another sheet: `"Sheet1!A2+Sheet1!A3";`

Auto size row and column

static void SetAutosize (ISheet sheet, int colNo)
{
    sheet.AutoSizeColumn(colNo);  // for row: AutosizeRow(rowNo)
}
// example :
SetAutosize(sheet, 0) // this  will auto size first col

Read Existing Excel

// Load the existing workbook
string existingFilePath = "plan.xlsx";
using (FileStream existingFileStream = new FileStream(existingFilePath, FileMode.Open, FileAccess.Read))
{
    IWorkbook workbook = new XSSFWorkbook(existingFileStream);

    // Create a new sheet named "erosNext"
    ISheet erosNextSheet = workbook.CreateSheet("erosNext");
}

Hope this blog will help you. For more examples refer to this github repository.

Comments

[Saika]

it's really helpful man! great!