As the nature of projects go, suddenly I wasn’t only reading a spreadsheet, but also needing to generate a spreadsheet. For the client’s convenience, of course. Although in this case, it takes a minute to fill out two fields by hand so it’s a pretty trivial perk of the app, but it was a good exercise.

I recently found out that using Interop for “Automation” of Office products is a bad idea, server-side. The word “Automation” is capitalized but not very clear. I understood it to mean any code (e.g., ASP.NET) that attempts to read from or write to an Office product (e.g., using the Interop libraries). MS Office is meant for human-interaction and should be used that way.

So, I sucked it up and went with a third-party library: NPOI

The baseline limitation for the spreadsheet file I needed to support was .xls. Not .xlsx, so no Open XML file format. That limited my choices to ExcelLibrary and NPOI. I ended up choosing NPOI because it seems more maintained and offers the features I am looking for. Plus, if I do need to use .xlsx in the future, they are working on that functionality now.

NPOI is a .NET port of Apache’s POI Java library. The developers of NPOI kept many of the acronyms used by the original POI, which seems to be tongue-in-cheek jabs at Microsoft. For example, POI stands for “Poor Obfuscation Implemenetation” and HSSF stands for “Horrible SpreadSheet Format.” However, prior to finding what these acronyms stood for, it was a headache to figure what points to what and which namespaces you want to use. This is a prime example of bad naming conventions.

NPOI 1.2.5 is packaged with NPOI.dll, Ionic.Zip.dll, and NPOI.XML. Many of the tutorials I read had different package contents (at one time, it looked like the NPOI.dll was split into multiple DLLs) and were not very clear on which references they added. I just added NPOI.dll because I am not planning to use the zip functionality of Ionic.Zip.dll. I’m not sure why NPOI.XML is included, but the contents do look intimidating.

NPOI provides an excellent set of examples (listed under NPOI.Examples). I started simple and created an empty Excel file to verify I didn’t actually need Ionic.Zip or the XML file. It worked. So, I moved on to my next task, generating a spreadsheet from an existing one (a template). This was also pretty simple, but I ran into a snag when I tried inputting data.

Turns out a row has cells up to the last-filled column for that row or the last cell that has a style (e.g., a border). That is, if row 0 only has a value in column A and you try to access a value in column B (sheet.GetRow(0).GetCell(1).SetCellValue("some value")), you’ll get a null-reference error. But if row 0 has a value in column F and nothing in columns A-E, accessing those cells A-E will not return a null-reference error. Similarly, if Column F is empty but has a border, it is still a valid cell object.

The simple fix to setting values in a cell that doesn’t exist?
sheet.GetRow(i).CreateCell(i+1).SetCellValue("some value here");

That’s it. Simple, straightforward.

And the plus side? NPOI is 0-based. No more silly arithemtic translations from Interop’s 1-based counting scheme.

The one thing I do want to mention is that the example code is a little bit out of date. Instead of having a Sheet object, GetSheet() or GetSheetAt() now returns an ISheet object, which can be cast to HSSFSheet. I imagine this is for .xlsx support.

The final thing I needed to do was to lock cells. sheet.ProtectSheet("aPassword"); is unfortunately at the mercy of Excel, which makes the default state of each cell set to locked. This means I have to iterate through the spreadsheet and unlock the necessary cells. In my project, I have counted this to be 30 cells per “table” with at least 10 “tables” on the sheet. Meaning, 300+ cells to unlock before writing to file.

Below is a sample of my code, which generates a new .xls file from an existing template, protects the sheet with just two cells being editable. Note that the template file and the new file cannot be open in a separate instance or an exception will be thrown (“The process cannot access the file ‘template.xls’ because it is being used by another process.”).

string basePath = HttpContext.Current.Server.MapPath("~/xls");
string fileName = "newFile.xls";

HSSFWorkbook wb;
using (FileStream fileTemplate = new FileStream(basePath + @"/template.xls",
	FileMode.Open, FileAccess.Read))
{
	wb = new HSSFWorkbook(fileTemplate);
}
			
ISheet sheet = wb.GetSheet("Sheet1");

// CellStyle to set unlocked cells
ICellStyle unlockedCell = wb.CreateCellStyle();
unlockedCell.IsLocked = false;

sheet.GetRow(1).CreateCell(1).SetCellValue("Hello");
sheet.GetRow(2).CreateCell(1).SetCellValue("There");
sheet.GetRow(1).GetCell(1).CellStyle = unlockedCell;
sheet.GetRow(2).GetCell(1).CellStyle = unlockedCell;

sheet.ProtectSheet("");

FileStream file = new FileStream(basePath + fileName, FileMode.Create);
try
{
	wb.Write(file);
}
catch (Exception e)
{
	Console.Write(e.Message);
}
finally {
	file.Close();
}

Further reading:
–StackOverflow discussion on XLS/XLSX .NET third-party libraries (2008)
–Creating Excel spreadsheets .XLS and .XLSX in C# (2009)
–NPOI Excel Template tutorial (2010)
–Locking cells (2011)
–Apache POI