Writing Excel Files From .NET with NPOI

Posted by Tom on 2011-07-24 22:27

NPOI is a .Net port of the Apache POI project for reading and writing Microsoft Office files. I've been using it recently to generate reports in Excel format so the accounting department don't burst into tears, and while I'm not exactly pushing the boundaries of XLS so far it's acquitted itself admirably.

The naming is pretty zany. SS is the namespace which contains all of the interfaces for Excel files. HSSF is for XLS files and XSSF is the OOXML version. Since the Workbook class is the means for constructing all of the other objects you can simply make a decision between XLS and OOXML right at the start and then access everything else through the interface. So once you've done the initial:

Workbook workbook = new HSSFWorkbook();

your Workbook becomes your one stop shop for creating the other necessary objects: sheets, rows and cells.

Sheet sheet = workbook.CreateSheet("SheetName");
Row row = sheet.CreateRow(rowIndex);
Cell cell = row.CreateCell(columnIndex);
cell.SetCellValue("foo");

I didn't find any gotchas in their object model, so it was pretty simple to produce code for creating sheets based on DataTables:

public static Sheet Write(Workbook workbook, string sheetName, DataTable data)
{
    sheetName = String.IsNullOrEmpty(sheetName) ? data.TableName : sheetName;
    sheetName = String.IsNullOrEmpty(sheetName) ? "Sheet" : sheetName;
    Sheet sheet = workbook.CreateSheet(sheetName);
 
    Row header = sheet.CreateRow(0);
    for (int i = 0; i < data.Columns.Count; i ++)
        header.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);
 
    int rowIndex = 0;
    foreach (DataRow dataRow in data.Rows)
    {
        Row row = sheet.CreateRow(rowIndex + 1);
        for (int i = 0; i < data.Columns.Count; i ++)
            row.CreateCell(i).SetCellValue(data.Rows[rowIndex][i].ToString());
        rowIndex++;
    }
 
    return sheet;
}

And also for objects, using a little reflection:

public static Sheet Write(Workbook workbook, string sheetName, IEnumerable data)
{
    IEnumerator foo = data.GetEnumerator();
    foo.MoveNext();
    Type t = foo.Current.GetType();
 
    Sheet sheet = workbook.CreateSheet(sheetName);
 
    Row header = sheet.CreateRow(0);
    PropertyInfo[] properties = t.GetProperties();
    for (int i = 0; i < properties.Length; i++)
        header.CreateCell(i).SetCellValue(properties[i].Name);
 
    int rowIndex = 0;
    foreach (object o in data)
    {
        Row row = sheet.CreateRow(rowIndex + 1);
        for (int i = 0; i < properties.Length; i++)
            row.CreateCell(i).SetCellValue(properties[i].GetValue(o, null).ToString());
        rowIndex++;
    }
 
    return sheet;
}

The above code is ripe for improvement. It's all currently stringly typed, although NPOI will allow us to specify types for each cell. It would also be simple enough to write up a property attribute to exclude a property in the same vein as XmlIgnore and ScriptIgnore to reduce some of the cruft in the second example. Or just use anonymous types.