Basic formatting doesn't seem to work

Aug 25, 2010 at 11:20 PM

I'm creating a spreadsheet from scratch and I can't seem to control the formatting in any way. All cells end up with a "custom" format that is "d-mmm-yy" which makes all numbers appear as a date of course.

Code basically looks like below. The function "HSSFDataType" returns CellType based on the data types for my "FieldBase" object, and it works fine.

If I look at the contents of a cell object after the whole loop is finished, everything looks absolutely fine. An example is after the code -- no changes are made to the spreadsheet after that point.  Yet still, no formatting seems to take hold when I open the spreadsheet in Excel. (The output shown is for a cell with numeric value "6")

Any ideas?

 

	wkbOut = new HSSFWorkbook();
	 shtOut = wkbOut.CreateSheet(SheetName);
         int rowNumber = 0;

 

 

            Row outRow;
            while (ListObject.NextItem())
            {
                ItemBase curItem = ListObject.GetItemFromDataReader();

                outRow = GetRow(shtOut, rowNumber);

               
                for (int colNumber = 0; colNumber < EffectiveFieldList.Count; colNumber++)
                {
                    FieldBase curField = curItem.Fields[EffectiveFieldList[colNumber]];
                    if (curField.IsReadable)
                    {
                        Cell outCell = outRow.CreateCell(colNumber);

 

 

                        SetHSSFCellValue(outCell, curField);
                       outCell.SetCellType(HSSFDataType(curField));
} } rowNumber++;
}

?wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0)
{6-Jan-00}
    [NPOI.HSSF.UserModel.HSSFCell]: {6-Jan-00}
    BooleanCellValue: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).BooleanCellValue' threw an exception of type 'System.InvalidOperationException'
    CachedFormulaResultType: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).CachedFormulaResultType' threw an exception of type 'System.InvalidOperationException'
    CellComment: null
    CellErrorValue: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).CellErrorValue' threw an exception of type 'System.InvalidOperationException'
    CellFormula: ""
    CellStyle: {NPOI.HSSF.UserModel.HSSFCellStyle}
    CellType: NUMERIC
    ColumnIndex: 0
    DateCellValue: {1/6/1900 12:00:00 AM}
    ErrorCellValue: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).ErrorCellValue' threw an exception of type 'System.InvalidOperationException'
    Hyperlink: null
    NumericCellValue: 6.0
    RichStringCellValue: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).RichStringCellValue' threw an exception of type 'System.InvalidOperationException'
    Row: {NPOI.HSSF.UserModel.HSSFRow}
    RowIndex: 1
    Sheet: {NPOI.HSSF.UserModel.HSSFSheet}
    StringCellValue: 'wkbOut.GetSheet(SheetName).GetRow(1).GetCell(0).StringCellValue' threw an exception of type 'System.InvalidOperationException'

 

 

 

Aug 27, 2010 at 9:39 PM
It sounds like your style object probably isn't correct.
CellStyle style;
style = _thisBook.CreateCellStyle();
DataFormat dataformat = _thisBook.CreateDataFormat();
style.DataFormat = dataformat.GetFormat("d-mmm-yy");
cell.CellStyle = style;
_thisBook is your NPOI.SS.UserModel.Workbook object.  
That should work.