The NPOI library allows .NET applications to generate Excel spreadsheets without requiring Excel to be installed on the server. This guide demonstrates how to programmatically create complex .xls files by defining specific styles, fonts, borders, and cell alignments.

Installation

Include the NPOI library in your project via the NuGet Package Manager console:

Install-Package NPOI -Version 2.4.1

Configuring Cell Styles and Fonts

To achieve a polished look, you must configure HSSFCellStyle and HSSFFont objects. The CellStyle controls layout properties such as horizontal and vertical alignment, text wrapping, borders, and background fills.

When setting background colors, both FillForegroundColor and FillPattern must be assigned; otherwise, the color will not render. Borders are applied individually to the top, bottom, left, and right, and can be assigned specific colors using HSSFColor indexes.

The Font object manages typeface details, including bold weight, font height, color, italics, and underline styles. Once a font is configured, it is attached to the cell style via the SetFont method.

Helper Class for Excel Generation

The following utility class encapsulates the logic for creating rows, cells, and applying styles, reducing boilerplate code in the main export logic.

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

public static class ExcelDocumentBuilder
{
    public static HSSFRow GetOrCreateRow(ISheet worksheet, int rowIndex, float height)
    {
        HSSFRow row = (HSSFRow)worksheet.GetRow(rowIndex);
        if (row == null) 
        {
            row = (HSSFRow)worksheet.CreateRow(rowIndex);
            row.HeightInPoints = height;
        }
        return row;
    }

    public static HSSFCell CreateStyledCell(HSSFRow row, HSSFCellStyle style, int colIndex, string content)
    {
        HSSFCell cell = (HSSFCell)row.CreateCell(colIndex);
        cell.CellStyle = style;
        if (!string.IsNullOrEmpty(content))
        {
            cell.SetCellValue(content);
        }
        return cell;
    }

    public static HSSFCellStyle BuildStyle(HSSFWorkbook workbook, HorizontalAlignment hAlign, VerticalAlignment vAlign, short fontSize, bool isBold, string fontName, bool applyBackground, short backColorIndex, bool applyBorder)
    {
        HSSFCellStyle newStyle = (HSSFCellStyle)workbook.CreateCellStyle();
        newStyle.Alignment = hAlign;
        newStyle.VerticalAlignment = vAlign;
        newStyle.WrapText = true;

        if (applyBackground)
        {
            newStyle.FillForegroundColor = backColorIndex;
            newStyle.FillPattern = FillPattern.SolidForeground;
        }

        if (applyBorder)
        {
            newStyle.BorderBottom = BorderStyle.Thin;
            newStyle.BorderTop = BorderStyle.Thin;
            newStyle.BorderLeft = BorderStyle.Thin;
            newStyle.BorderRight = BorderStyle.Thin;
            newStyle.BottomBorderColor = HSSFColor.Black.Index;
            newStyle.TopBorderColor = HSSFColor.Black.Index;
            newStyle.LeftBorderColor = HSSFColor.Black.Index;
            newStyle.RightBorderColor = HSSFColor.Black.Index;
        }

        HSSFFont font = (HSSFFont)workbook.CreateFont();
        font.FontHeightInPoints = fontSize;
        font.FontName = fontName;
        font.Color = HSSFColor.White.Index;
        font.Boldweight = isBold ? (short)FontBoldWeight.Bold : (short)FontBoldWeight.Normal;

        newStyle.SetFont(font);
        return newStyle;
    }
}

Export Service Implementation

The service below utilizes the helper class to generate a training schedule report. It initializes the workbook, creates the sheet, applies merged regions for headers, and iterates through data to populate the grid.

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

public class ReportExportService
{
    private readonly string _rootPath;

    public ReportExportService(string webRootPath)
    {
        _rootPath = webRootPath;
    }

    public string GenerateTrainingScheduleExcel()
    {
        string fileName = $"TrainingSchedule_{DateTime.Now:yyyyMMddHHmmss}.xls";
        string directory = Path.Combine(_rootPath, "exports", DateTime.Now.ToString("yyyyMMdd"));
        
        if (!Directory.Exists(directory))
        {
            Directory.CreateDirectory(directory);
        }

        string fullPath = Path.Combine(directory, fileName);

        using (var fs = new FileStream(fullPath, FileMode.Create, FileAccess.ReadWrite))
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Schedule");

            // Define Styles
            var titleStyle = ExcelDocumentBuilder.BuildStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 18, true, "Arial", true, HSSFColor.Coral.Index, false);
            var headerStyle = ExcelDocumentBuilder.BuildStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 12, true, "Arial", true, HSSFColor.Grey25Percent.Index, true);
            var bodyStyle = ExcelDocumentBuilder.BuildStyle(workbook, HorizontalAlignment.Center, VerticalAlignment.Center, 10, false, "Arial", false, HSSFColor.White.Index, true);

            // Create Title
            var titleRow = ExcelDocumentBuilder.GetOrCreateRow(sheet, 0, 30);
            var titleCell = titleRow.CreateCell(0);
            titleCell.SetCellValue("Employee Training Curriculum");
            titleCell.CellStyle = titleStyle;
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

            // Create Headers
            string[] headers = { "Category", "No.", "Date", "Topic", "Summary", "Instructor" };
            var headerRow = ExcelDocumentBuilder.GetOrCreateRow(sheet, 1, 25);
            for (int i = 0; i < headers.Length; i++)
            {
                ExcelDocumentBuilder.CreateStyledCell(headerRow, headerStyle, i, headers[i]);
                sheet.SetColumnWidth(i, 4000);
            }

            // Populate Data
            var courses = new[] { "Safety Basics", "Communication", "Leadership", "Time Management" };
            for (int i = 0; i < courses.Length; i++)
            {
                var dataRow = ExcelDocumentBuilder.GetOrCreateRow(sheet, i + 2, 20);
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 0, "General");
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 1, (i + 1).ToString());
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 2, DateTime.Now.AddDays(i).ToString("yyyy-MM-dd"));
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 3, courses[i]);
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 4, "Skill enhancement session");
                ExcelDocumentBuilder.CreateStyledCell(dataRow, bodyStyle, 5, $"Trainer #{i + 1}");
            }

            workbook.Write(fs);
        }

        return fullPath;
    }
}