HSSF how can format cell with currency "$"

[shivadevasani]

I am using HSSF and creating new book, new sheet and new row
i am able to write data from file but what i need is format one Colum with currency "$ or euro"
how can i do using HSSF. please see the below example

Invoice# Commodity Country of Destination Country of Origin Mode of Transportayion Nature of Trans Invoice Value Ð Invoice Value $ Dlvy Terms Weight Units
123 NL Europe USA Ship 0 € 110.00 $50.00 LTS 10.00 10

[jonboy49]

This is a WAG (Wild-Ass-Guess) but to format numbers suppressing leading zeros you use a format string of "#,##0.0000" - So my best guess is that to float in a currency symbol it would logically be "$,$$0.0000".

I have never done it and don't have time to play but ...

[Scott Klement]

I don't know what you mean by "$ or euro". But when I've done euro, it's been like this:

Code: Select all

[$€-x-euro2] #,##0.00

For dollar sign it'd be like this:

Code: Select all

$#,##0.00

Note that you can go into Excel and ask it to format a cell the way you want... then look under "custom" to see what format it chose. That will be the one you want to pass to HSSF (XSSF).

[shivadevasani]

Hi Scott
i have 2 fields in xls one of it should be €20.00 and other field $50.00
I am unable to use Euro symbol in AS400 but i have tried the same for $ but did not work. what i need is both € and $ in xls.
Please find the below code from HSSFR4

D Currusd s like(HSSFCellStyle)
D CreateCellStyles...
D PI

D ChFont s like(HSSFFont)
D DataFmt s like(HSSFDataFormat)
D TempStr s like(jString)
D NumFmt s 5I 0
D DateFmt s 5I 0

Currusd = HSSFWorkbook_createCellStyle(wb);
DataFmt = HSSFWorkbook_createDataFormat(wb);
TempStr = new_String('$#,###0.00');
NumFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Currusd: NumFmt);

[Scott Klement]

It's interesting that you are mentioning XLS (an obsolete format no longer used) and AS400 (an obsolete computer no longer used.)

You say that your code doesn't work, but it works for me. Please explain enough detail to make it fail the way it is failing for you.

[shivadevasani]

Hi Scott

The code works for the "$" but for "€" it is not working. when i add "€" in AS400 it is changing to "?".
TempStr = new_String('[$€-x-euro2] #,##0.00');
this is not working and once i save or file the program € is changing to ? and excel also same ? before the number example instead of €10.00 it is coming as ?10.00

[Scott Klement]

To me, this means you are probably using a form of EBCDIC that doesn't contain the euro symbol. Please consider either using Unicode or switching to a form of EBCDIC that does have the Euro symbol.

[Scott Klement]

Here's a quick and dirty example of using RPG's built-in support for Unicode to solve the problem. Notice that I used the hex codes for dollar and euro sign, so if your system doesn't have an EBCDIC that supports those symbols, it's okay, because the hex code for Euros and Dollars in Unicode will be the same regardless.

Also, I apologize that part of this is in fixed-format. This is taken from an example I wrote in 2007 before we had fully free format, so it should demonstrate the solution, but I'd advise converting it to free format.

Code: Select all

      *  Demonstration of using POI to create an excel worksheet with both
      *  dollars and euro formatting
      *
      *  To compile:
      *      Make sure you've already created HSSFR4. See the instructions
      *      on that source member for details.
      *
      *>       CRTBNDRPG PGM(XLEURO) SRCFILE(QRPGLESRC) DBGVIEW(*LIST)
      *
      *
     H DFTACTGRP(*NO)
     H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
     H THREAD(*SERIALIZE)
     H BNDDIR('HSSF')

     FDIVSALES  IF   E           K DISK

      /copy hssf_h

     D CreateCellStyles...
     D                 PR

     D book            s                   like(SSWorkbook)
     D sheet           s                   like(SSSheet)
     D row             s                   like(SSRow)
     D rowcount        s             10I 0 inz(0)

     D Text            s                   like(SSCellStyle)
     D MoneyDollars    s                   like(SSCellStyle)
     D MoneyEuros      s                   like(SSCellStyle)

     D Money           s              7p 2 inz(12345.67)

      /free

        ss_begin_object_group(100);

        book = new_XSSFWorkbook();

        CreateCellStyles();

        sheet = SS_newSheet(book: 'Money');
        SSSheet_setColumnWidth( sheet: 0: 20 * 256 );
        SSSheet_setColumnWidth( sheet: 1: 20 * 256 );

        rowcount += 1;
        row = SSSheet_createRow(sheet: rowcount);
        ss_text( row: 0 : 'In dollars'   : Text);
        ss_num ( row: 1 : Money          : MoneyDollars);

        rowcount += 1;
        row = SSSheet_createRow(sheet: rowcount);

        ss_text( row: 0 : 'In euros'     : Text);
        ss_num ( row: 1 : Money          : MoneyEuros);

        SS_save(book: 'moneyFormats.xlsx');

        ss_end_object_group();
        *inlr = *on;
      /end-free


      *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      * CreateCellStyles(): Create the different display styles
      *    used for cells in this Excel workbook.
      *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P CreateCellStyles...
     P                 B
     D CreateCellStyles...
     D                 PI

     D new_StringC     PR                  like(jString)
     D                                     EXTPROC(*JAVA
     D                                     :'java.lang.String'
     D                                     :*CONSTRUCTOR)
     D   create_from              16383c   VARYING const

      * Unicode constants for the dollar and euro signs

     D DOLLAR          C                   U'0024'
     D EURO            C                   U'20AC'

     D DataFmt         s                   like(SSDataFormat)
     D TempStr         s                   like(jString)
     D NumFmt          s              5I 0

      /free

         Text = SSWorkbook_createCellStyle(book);
         SSCellStyle_setAlignment(Text: ALIGN_LEFT);

         //
         // Create a cell style for numbers so that they are
         //  right-aligned and the number is formatted nicely
         //  with a dollar sign in front
         //

         MoneyDollars = SSWorkbook_createCellStyle(book);

         DataFmt = SSWorkbook_createDataFormat(book);
         TempStr = new_StringC(DOLLAR + %ucs2('##,##0.00'));
         NumFmt  = SSDataFormat_getFormat(DataFmt: TempStr);
         SSCellStyle_setDataFormat(MoneyDollars: NumFmt);

         SSCellStyle_setAlignment(MoneyDollars: ALIGN_RIGHT);

         //
         // Create a cell style for numbers so that they are
         //  right-aligned and the number is formatted nicely
         //  with a euro sign in front
         //

         MoneyEuros = SSWorkbook_createCellStyle(book);

         DataFmt = SSWorkbook_createDataFormat(book);
         TempStr = new_StringC(EURO + %ucs2('##,##0.00'));
         NumFmt  = SSDataFormat_getFormat(DataFmt: TempStr);
         SSCellStyle_setDataFormat(MoneyEuros: NumFmt);

         SSCellStyle_setAlignment(MoneyEuros: ALIGN_RIGHT);

      /end-free
     P                 E

[shivadevasani]

Hello Scott

Thank you, i have used the same logic you provided (unicode), but in XLS instead of €112.23 i received this ¬112.23.
below is the code

D new_StringC PR like(jString)
D EXTPROC(*JAVA
D :'java.lang.String'
D :*CONSTRUCTOR)
D create_from 16383c VARYING const

D DOLLAR C U'0024'
D EURO C U'20AC'

D ChFont s like(HSSFFont)
D DataFmt s like(HSSFDataFormat)
D TempStr s like(jString)
D NumFmt s 5I 0
D DateFmt s 5I 0

Curreur = HSSFWorkbook_createCellStyle(wb);
DataFmt = HSSFWorkbook_createDataFormat(wb);
TempStr = new_StringC(EURO + %ucs2('##,##0.00'));
NumFmt = HSSFDataFormat_getFormat(DataFmt: TempStr);
HSSFCellStyle_setDataFormat(Curreur: NumFmt);

[Scott Klement]

The fact that you're seeing the ¬ character means that, somehow, it is being translated into EBCDIC (and translated incorrectly at that.) Nothing in my code -- or the code that you just posted -- translates the text to EBCDIC.

Please provide a full, complete, program that I can load and run on my system. I've already provided you with a full program that works correctly, and the code you posted doesn't explain a problem. So unless I can run your program and reproduce the problem, I can only assume it's a problem in code you haven't provided for us.