Excel Generation in C# using NPOI Library
Tags: npoi, csharp
Published: 2024-01-16 11:46:19

## Installation
To get started with [NPOI](https://github.com/nissl-lab/npoi), you need to install the [NPOI NuGet package](https://www.nuget.org/packages/NPOI/#readme-body-tab) 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
- Formating 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](https://github.com/nissl-lab/npoi-examples/tree/main).