NPOI setting different cell format

Kazenga picture Kazenga · Dec 6, 2013 · Viewed 25.9k times · Source

i have problem setting different format in each cell, i want to set number format to thousand separator and thousand separator with 3 decimals, when number is not integer, here is my code, i think problem look like each cell format is set by last fomat setting in for cycle

so output should be like this 12345 -> 12 345; 425 -> 425; 41,2 -> 41,2; 4578,25 -> 4 578,25

        short doubleFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat("#,##0.###");
        short intFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat("#,##0");
        for (i = 0; i <= unorderedSheet.LastRowNum; i++)
        {
            NPOI.SS.UserModel.IRow newRow = orderedSheet.CreateRow(i);
            NPOI.SS.UserModel.IRow oldRow = unorderedSheet.GetRow(i);
            if (oldRow != null)
            {
                foreach (ICell oldCell in oldRow.Cells)
                {
                    ICell newCell = newRow.CreateCell(mapping[n]);
                    switch (oldCell.CellType)
                    { 
                        case CellType.NUMERIC:
                            newCell.SetCellType(CellType.NUMERIC);
                            newCell.SetCellValue(oldCell.NumericCellValue);
                            if (numberHasDecimals(oldCell.NumericCellValue))
                            {
                                newCell.CellStyle.DataFormat = doubleFormat;
                            }
                            else
                            {
                                newCell.CellStyle.DataFormat = intFormat;                
                            }
                            break;
                        default:
                            newCell.SetCellValue(oldCell.ToString());
                            break;
                    }}}}

Answer

underscore picture underscore · Mar 10, 2014
static ICellStyle _doubleCellStyle = null;
static ICellStyle _intCellStyle    = null;
static void SettingDifferentCellFormat( HSSFWorkbook hssfOutputWorkBook, ISheet orderedSheet, ISheet unorderedSheet ) {

   //short doubleFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat( "#,##0.###" );
   //short intFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat( "#,##0" );

   if ( _doubleCellStyle == null ) { 
      _doubleCellStyle = hssfOutputWorkBook.CreateCellStyle();
      _doubleCellStyle.DataFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat( "#,##0.###" );
   }

   if ( _intCellStyle == null ) { 
      _intCellStyle = hssfOutputWorkBook.CreateCellStyle();
      _intCellStyle.DataFormat = hssfOutputWorkBook.CreateDataFormat().GetFormat( "#,##0" ); 
   }

   for ( int i = 0; i <= unorderedSheet.LastRowNum; i++ ) {

      NPOI.SS.UserModel.IRow newRow = orderedSheet.CreateRow( i );
      NPOI.SS.UserModel.IRow oldRow = unorderedSheet.GetRow( i );

      const int mapping_n_ = 0;

      if ( oldRow != null ) {
         foreach ( ICell oldCell in oldRow.Cells ) {

            ICell newCell = newRow.CreateCell( mapping_n_ );
            bool numberHasDecimals = true;

            switch ( oldCell.CellType ) {

               case CellType.NUMERIC:

                  newCell.SetCellType( CellType.NUMERIC );
                  newCell.SetCellValue( oldCell.NumericCellValue );

                  if ( numberHasDecimals ) {
                     //newCell.CellStyle.DataFormat = doubleFormat;
                     newCell.CellStyle = _doubleCellStyle;
                  } else {
                     //newCell.CellStyle.DataFormat = intFormat;
                     newCell.CellStyle = _intCellStyle;
                  }
                  break;
               default:
                  newCell.SetCellValue( oldCell.ToString() );
                  break;
            }
         }
      }
   }

}//SettingDifferentCellFormat