Re: POI/HSSF default cell formatting

["Leif Guldbrand" <guldbrand@xxxxxxxxx>]

Hi Peter,

> I'm using the POI/HSSF classes on an IBM iSeries (AS/400) to add/update
> cells in a worksheet.  The problem I'm having is that the existing worksheet
> has column formatting, which I would like to apply to new cells when I add
> them (no problems with existing cells - they retain their formatting).
> 

I'll try to show what I have done, formatting new cells.
I have five different formats and the standard format for cells.
I have used Scott Klements articles and examples - and his help to do it :-)

Looks like this:
      **********************************************************************
      * Prototypes.                                                        *
      **********************************************************************
     D HSSFCellStyle_setFillForegroundColor...
     D                 PR                  ExtProc(*JAVA:
     D                                     'org.apache.poi.hssf.-
     D                                     usermodel.HSSFCellStyle':
     D                                     'setFillForegroundColor')
     D   fgcolor                           like(jShort) value

     D HSSFCellStyle_setFillPattern...
     D                 PR                  ExtProc(*JAVA:
     D                                     'org.apache.poi.hssf.-
     D                                     usermodel.HSSFCellStyle':
     D                                     'setFillPattern')
     D   fillpattern                       like(jShort) value

      **********************************************************************
      * Constants.                                                         *
      **********************************************************************
     D SOLID_FOREGROUND...
     D                 c                   1
     D HSSFCOLOR_GREEN...
     D                 c                   11
     D HSSFCOLOR_YELLOW...
     D                 c                   34

     D style1          s                   like(HSSFCellStyle)
     D style2          s                   like(HSSFCellStyle)
     D style3          s                   like(HSSFCellStyle)
     D style4          s                   like(HSSFCellStyle)
     D style5          s                   like(HSSFCellStyle)

        // Set different styles in the book
        style1 = HSSFWorkbook_createCellStyle(book);
                 HSSFCellStyle_setFillForegroundColor(style1: HSSFCOLOR_GREEN);
                 HSSFCellStyle_setFillPattern(style1: SOLID_FOREGROUND);

        style2 = HSSFWorkbook_createCellStyle(book); // numeric 0-dec.
                 HSSFCellStyle_setAlignment(style2: ALIGN_RIGHT);
                 HSSFCellStyle_setFillForegroundColor(style2: HSSFCOLOR_YELLOW);
                 HSSFCellStyle_setFillPattern(style2: SOLID_FOREGROUND);

        style3 = HSSFWorkbook_createCellStyle(book);
                 HSSFCellStyle_setFillForegroundColor(style3: HSSFCOLOR_YELLOW);
                 HSSFCellStyle_setFillPattern(style3: SOLID_FOREGROUND);

        style4 = HSSFWorkbook_createCellStyle(book);  // numeric 0-dec.
                 HSSFCellStyle_setAlignment(style4: ALIGN_RIGHT);

        style5 = HSSFWorkbook_createCellStyle(book);  // numeric 2-dec.
                 HSSFCellStyle_setAlignment(style5: ALIGN_RIGHT);
                 HSSFCellStyle_setDataFormat(style5: 2 );

        Row = HSSFSheet_getRow(Sheet: RowCount);
              if row = *NULL;     // build a new row
                 row = HSSFSheet_createRow(Sheet: RowCount);
              endif;

        // use style1 formatting the cell
        cell = HSSFRow_GetCell(Row:   0);
               if cell = *NULL;    // build a new cell
                  cell = HSSFRow_createCell(Row:   0);
                  HSSFCell_setCellType(cell: CELL_TYPE_STRING);
                  HSSFCell_setCellStyle(cell: style1);
               endif;
        TempStr = new_String(EU0001);
        HSSFCell_setCellValueStr(cell: TempStr);

        // use standard formatting the cell
        cell = HSSFRow_GetCell(Row:   3);
               if cell = *NULL;   // build a new cell
                  cell = HSSFRow_createCell(Row:   3);
                  HSSFCell_setCellType(cell: CELL_TYPE_STRING);
               endif;
        TempStr = new_String(EU0004);
        HSSFCell_setCellValueStr(cell: TempStr);

        // use style2 formatting the cell
        cell = HSSFRow_GetCell(Row:   4);
               if cell = *NULL;   // build a new cell
                  cell = HSSFRow_createCell(Row:   4);
                  HSSFCell_setCellType(cell: CELL_TYPE_NUMERIC);
                  HSSFCell_setCellStyle(cell: style2);
               endif;
        TempStr = new_String(%Char(EU0005));
        HSSFCell_setCellValueStr(cell: TempStr);

        // use style3 formatting the cell
        cell = HSSFRow_GetCell(Row:   5);
               if cell = *NULL;   // build a new cell
                  cell = HSSFRow_createCell(Row:   5);
                  HSSFCell_setCellType(cell: CELL_TYPE_STRING);
                  HSSFCell_setCellStyle(cell: style3);
               endif;
        TempStr = new_String(EU0006);
        HSSFCell_setCellValueStr(cell: TempStr);

As you can see, I only do the cell formatting when I build a new cell,
because - as you say... existing cell's retains their formattings.

Hopes it can help you.

Best regards,
Leif