Archive for August, 2011

Changing the formatting of an Excel cell with NPOI

We use NPOI to generate Excel files. The old format for negative numbers was to have parentheses around them, but a new requirement was to instead use a minus sign.

The way we handle cell formatting is a custom function, GetCellStyle, that returns the new DataFormat object to adjust the way the field appears. Here is the code to do that:

HSSFWorkbook workbook;
private GetCellStyle(string cellStyle)
{
 CellStyle cs = workbook.CreateCellStyle();
 cs.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
}

And a cell style is set like so:

CellStyle currency = GetCellStyle("currency");
Row r7 = WS.GetRow(7);
r7.CreateCell(0).SetCellValue(dSomeCurrencyValue);
r7.GetCell(0).CellStyle = currency ;

So the current format surrounds negative numbers with parentheses. It was a small change to remove the parentheses and replace it with a minus sign:

cs.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);-$#,##0.00_)");

Leave a comment