Working with xls files in C#

Wed, Apr 11, 2018

There is a really neat library for working with xls/xlsx from C# called NPOI.

The biggest selling point of NPOI is that you don’t need to have Office installed on your server in order to use it.

There is more information on the github project’s page and most importantly there is also a lot of samples. The API is not always simple and intuitive, so the samples are often great start.

Next I will show some samples that worked for my use case and can get you started.

Basic building block is the IWorkbook interface. Depending on whether you want to work with xls or xlsx, you would use HSSFWorkbook for xls and XSSFWorkbook for xlsx, which implement mentioned interface.

Reading

Below is a very simple way of reading all values as strings from the first sheet of the workbook. There are also overloads for working with typed value of each cell (DateTime, double, etc.).

List<string[]> ReadFile(string fileName)
{
    using(var excelFile = File.OpenRead(fileName))
    {
        var workBook = new XSSFWorkbook(excelFile);
        var sheet = workBook.GetSheetAt(0);

        var rows = new List<string[]>();
        var cellValues = new List<string>();
        foreach (IRow row in sheet)
        {
            foreach (var cell in headerRow)
            {
                cellValues.Add(cell?.ToString());
            }
            rows.Add(cellValues.ToArray());
            cellValues.Clear();
        }    
    }
}

Writing

Let’s start with the basics - create sheet from list of arrays.

ISheet CreateSheet()
{
    IWorkbook workbook = new XSSFWorkbook();
    var sheet = workbook.CreateSheet();
    return sheet;
}

void SetData(ISheet sheet, List<string[]> data)
{
    var firstDataRowNumber = sheet.LastRowNum + 1;
    for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
    {
        var row = sheet.CreateRow(firstDataRowNumber + rowIndex);
        var rowData = data[rowIndex];
        for (
            int cellIndex = 0; 
            cellIndex < rowData.Length; 
            cellIndex++)
        {
            row
                .CreateCell(cellIndex)
                .SetCellValue(rowData[cellIndex]);
        }
    }
}

void WriteDemo()
{

    var sheet = CreateSheet();
    var data = new List<string[]>
    {
        "Jimmy", "15", "dog"
        "Timmy", "9", "cat"
    };
    SetData(sheet, data);
}

Styling

It would be nice to add a header row with some styling.

void SetSheetHeaderRow(ISheet sheet, string[] headers)
{
    var headerRow = sheet.CreateRow(0);
    var headerStyle = CreateHeaderStyle(sheet.Workbook);

    for (int i = 0; i < headers.Length; i++)
    {
        var cell = headerRow.CreateCell(i);
        cell.CellStyle = headerStyle;
        cell.SetCellValue(headers[i]);
    }
}

ICellStyle CreateHeaderStyle(IWorkbook workbook)
{
    var font = workbook.CreateFont();
    font.IsBold = true;
    var style = workbook.CreateCellStyle();
    style.SetFont(font);
    return style;
}

void WriteHeaderDemo()
{
    var sheet = CreateSheet();
    var headers = new[] { "NAME", "AGE", "ANIMAL" };
    SetSheetHeaderRow(sheet, headers);
    var data = new List<string[]>
    {
        "Jimmy", "15", "dog"
        "Timmy", "9", "cat"
    };
    SetData(sheet, data);
}

Little bit more advanced to achieve, but quite common requirement, is to make the editor choose from a list of values when filling or editing data.

const int MaxRowsPerSheet = 1024 * 1024;

void AddOneFromManyValidation(int columnIndex, string sheetWithValuesName, string[] options, ISheet sheet, string validationMessage)
{
    var workbook = sheet.Workbook;
    var valuesSheet = workbook.CreateSheet(sheetWithValuesName);
    var valuesSheetIndex = workbook.GetSheetIndex(valuesSheet);
    workbook.SetSheetHidden(valuesSheetIndex, SheetState.VeryHidden);
    for (int i = 0; i < options.Length; i++)
    {
        valuesSheet.CreateRow(i)
            .CreateCell(0)
            .SetCellValue(options[i]);
    }
    var addressList = new CellRangeAddressList(1, MaxRowsPerSheet - 1, columnIndex, columnIndex);
    var validationHelper = sheet.GetDataValidationHelper();
    var validationConstraint = validationHelper.CreateFormulaListConstraint($"{sheetWithValuesName}!$A$1:$A$" + options.Length);
    var validation = validationHelper.CreateValidation(validationConstraint, addressList);
    validation.ShowErrorBox = true;
    validation.CreateErrorBox("Validation failed", validationMessage);
    sheet.AddValidationData(validation);
}

The above function will assign validation rule to given column, allowing only one of the options to be filled. The options will be stored in a separate (hidden) sheet.

For small number of options, you can include the values directly, otherwise you will need to use the reference approach as shown in the AddOneFromManyValidation method.

Final touches

To make the data more user friendly, you can adjust the width of the columns so the content fits.

void AdjustColumnsSize(ISheet sheet, int[] columns)
{
    foreach(var columnIndex in columns)
    {
        sheet.AutoSizeColumn(columnIndex);
    }
}

Export

After all is ready you can export the workbook like this.

byte[] GetWorkbookBytes(IWorkbook workbook)
{
    using (var ms = new MemoryStream())
    {
        workbook.Write(ms);
        return ms.ToArray();
    }
}

Tom Rucki

Working with xls files in C#

Reading

Writing

Styling

Final touches

Export

Comments

Tom Rucki

Working with xls files in C#

Reading

Writing

Styling

Dropdown with validation

Final touches

Export

Comments