Style Format

Scott Klement's open source interface to the POI HSSF/XSSF Spreadsheet Project for RPG Programmers. http://www.scottklement.com/poi/
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Style Format

Post by wdwisser »

Hello,
I created a service program to allow my programs to directly call the different routines for creating an excel document using your examples in POI36. When I try to apply the style to the cell for the row/column it doesn't seem to take. For example I an pushing a numeric value and applying the Numeric style from the createCellStyles routine but it just comes out plain text, I want to say. The same thing happens for the dates and I'm unsure why... Help?!

Below is some sample code where I am passing it to the setColumns procedure to set the cell format. The "row" is a global value that is set prior to calling this routine.

P setColumn B Export

D setColumn PI
D Incolumn 10I 0 Const
D Instring 1024A Const Varying
D Instyle 10A Const

D style S Like(SSCellStyle)

/Free

Select;
When Instyle = 'Text';
style = Text;
When Instyle = 'Dates';
style = Dates;
When Instyle = 'Numeric';
style = Numeric;
EndSl;

ss_text( row: Incolumn: %Trim(Instring): style);

/End-Free

P E
Attachments
TestExcel.jpg
TestExcel.jpg (160.96 KiB) Viewed 6392 times
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Style Format

Post by Scott Klement »

I don't understand this code. What are Text, Dates and Numeric?
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

They are global variables from this code...

// ----------------------------------------------------
// Create a cell style for numbers so that they are
// right-aligned and the number is formatted nicely.
// ----------------------------------------------------
Numeric = SSWorkbook_createCellStyle(book);

DataFmt = SSWorkbook_createDataFormat(book);
TempStr = new_String('#,##0.00');
NumFmt = SSDataFormat_getFormat(DataFmt: TempStr);
SSCellStyle_setDataFormat(Numeric: NumFmt);

SSCellStyle_setAlignment(Numeric: ALIGN_RIGHT);

// -------------------------------------------
// Create a cell style for text so that it's
// left-aligned
// -------------------------------------------
Text = SSWorkbook_createCellStyle(book);
SSCellStyle_setAlignment(Text: ALIGN_LEFT);

// -------------------------------------------------
// Create a cell style for dates. Dates in Excel
// are numbers that are formatted in a particular
// way.
// -------------------------------------------------
Dates = SSWorkbook_createCellStyle(book);

DataFmt = SSWorkbook_createDataFormat(book);
TempStr = new_String('m/d/yy');
DateFmt = SSDataFormat_getFormat(DataFmt: TempStr);
SSCellStyle_setDataFormat(Dates: DateFmt);
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Style Format

Post by Scott Klement »

I see.

That seems like it should work. Is it possible that something is freeing up these cell styles before they are used? For example, is there an "end object group" or "free local ref" somewhere that might be freeing them up?
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

I've attached my Excel service program and TestExcel program that creates the Excel file. Let me know your thoughts when you have a few moments to review. I don't see any "free" until after I save the Excel file.
Last edited by wdwisser on Tue May 03, 2022 1:02 pm, edited 3 times in total.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Style Format

Post by Scott Klement »

I can't see any obvious problems just by looking at it. (But, I have never been good at finding errors that way.)

Can you provide actual loadable/runnable code? I'm not going to try to re-type all of that in. Not sure why you created .JPG files from an SEU print out. Just send the actual code as-is.

Just open the member in RDi (or VS Code, or ILEditor, or MiWorkplace... any modern editor), select all of the code, and copy/paste it into the forum. Put [code] before the first line and [/code] after the last line so that it looks nice in the forum.

It's a million times easier than making an SEU printout, and then creating .JPGs of each page. Plus, it'll be something that other people can work with.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

Excel Service Program

Code: Select all

     H NoMain
     H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
     H THREAD(*SERIALIZE)
     H BNDDIR('HSSF')
     ******************************************************************
     **
     ** Excel - Generate an excel file based on user parameters.
     ** Description: This program will allow a user to generate an excel
     **              file and store it on the IFS or send the file via
     **              email. The Excel file can be either .xls or .xlsx
     **
     ******************************************************************
     **
     **   Written by - B. Wisser    ---  APS Medical Billing    ---
     **   Date written - 04/27/22   ---  5620 Southwyck Blvd.   ---
     **                             ---  Toledo, OH  43614      ---
     **                             ---  (419) 866-1804         ---
     **
     **  Modification Log:
     ** ===============================================================
     ** Date      Programmer     Description
     ** ========  =============  ======================================
     ** 04/27/22  B.Wisser       Initial Version
     ******************************************************************

     D createCellStyles...
     D                 PR

     D* ------------------
     D* Global Variables
     D* ------------------
     D xssf            S               N   Inz(*Off)
     D book            S                   Like(SSWorkbook)
     D sheet           S                   Like(SSSheet)
     D row             S                   Like(SSRow)
     D LgHeading       S                   Like(SSCellStyle)
     D SmHeading       S                   Like(SSCellStyle)
     D ColHeading      S                   Like(SSCellStyle)
     D Numeric         S                   Like(SSCellStyle)
     D Text            S                   Like(SSCellStyle)
     D Dates           S                   Like(SSCellStyle)

     D/Copy QSrc,ExcelC
     D/Copy POI36/QRpgLESrc,hssf_h

     P*****************************************************************
     P*
     P* crtExcelObj: This routine will start the process of creating
     P*   an Excel document, either .xlsx or .xls, based on the parm
     P*   being passed.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    InXssf         Style Sheet Format (boolean) - Optional
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P crtExcelObj     B                   Export

     D crtExcelObj     PI
     D  InXssf                         N   Const Options(*NoPass)

      /Free

        xssf = *Off;
        If %Parms >= 1 And Inxssf;
          xssf = *On;
        EndIf;
        ss_begin_object_group(100);

        // -------------------------------
        // Generate .xlsx or .xls Object
        // -------------------------------
        If (xssf);
           book = new_XSSFWorkbook();
        Else;
           book = new_HSSFWorkbook();
        EndIf;

        // ---------------------------------
        // Create Cell Styles for Workbook
        // ---------------------------------
        createCellStyles();

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* saveExcelObj: This routine will save the Excel document to the
     P*   IFS based on the parameters passed.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    filePath       File Path       (50A)
     P*    fileName       File Name       (50A)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P saveExcelObj    B                   Export

     D saveExcelObj    PI
     D   filePath                   100A   Const Varying
     D   fileName                   100A   Const Varying

     D  fileType       S             10A

      /Free

        If (xssf);
          fileType = '.xlsx';
        Else;
          fileType = '.xls';
        EndIf;

        SS_save(book: %Trim(filePath) + %Trim(fileName) + %Trim(fileType));
        ss_end_object_group();

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* createCellStyles: Create the different display styles used in
     P*   the cells for the Excel workbook.
     P*
     P* Note: Uses the following global variables:
     P*       Book, LgHeading, SmHeading, ColHeading, Numeric
     P*       Text, Dates.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    None
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P createCellStyles...
     P                 B

     D createCellStyles...
     D                 PI

     D LgFont          S                   Like(SSFont)
     D SmFont          S                   Like(SSFont)
     D ChFont          S                   Like(SSFont)
     D DataFmt         S                   Like(SSDataFormat)
     D TempStr         S                   Like(jString)
     D NumFmt          S              5I 0
     D DateFmt         S              5I 0

      /Free

       // ----------------------------------------------
       //  Create a cell style for the large, centered
       //   title at the top of the report.
       // ----------------------------------------------
       LgHeading = SSWorkbook_createCellStyle(book);

       LgFont = SSWorkbook_createFont(book);
       SSFont_setBoldweight(LgFont: BOLDWEIGHT_BOLD);
       SSFont_setFontHeightInPoints(LgFont: 16);
       SSCellStyle_setFont(LgHeading: LgFont);

       SSCellStyle_setAlignment(LgHeading: ALIGN_CENTER);

       // -----------------------------------------------
       // Create a cell style for the smaller text that
       //  will be printed below the main heading.
       // -----------------------------------------------
       SmHeading = SSWorkbook_createCellStyle(book);

       SmFont = SSWorkbook_createFont(book);
       SSFont_setFontHeightInPoints(SmFont: 8);
       SSCellStyle_setFont(SmHeading: SmFont);

       SSCellStyle_setAlignment(SmHeading: ALIGN_CENTER);

       // -----------------------------------------------
       // Create a cell style for the column headings.
       //  These are bold and have a border line at the
       //  bottom.
       // -----------------------------------------------
       ColHeading = SSWorkbook_createCellStyle(book);

       ChFont = SSWorkbook_createFont(book);
       SSFont_setBoldweight(ChFont: BOLDWEIGHT_BOLD);
       SSCellStyle_setFont(ColHeading: ChFont);

       SSCellStyle_setAlignment(ColHeading: ALIGN_CENTER);
       SSCellStyle_setBorderBottom(ColHeading: BORDER_THIN);

       // ----------------------------------------------------
       // Create a cell style for numbers so that they are
       //  right-aligned and the number is formatted nicely.
       // ----------------------------------------------------
       Numeric = SSWorkbook_createCellStyle(book);

       DataFmt = SSWorkbook_createDataFormat(book);
       TempStr = new_String('#,##0.00');
       NumFmt  = SSDataFormat_getFormat(DataFmt: TempStr);
       SSCellStyle_setDataFormat(Numeric: NumFmt);

       SSCellStyle_setAlignment(Numeric: ALIGN_RIGHT);

       // -------------------------------------------
       // Create a cell style for text so that it's
       //  left-aligned
       // -------------------------------------------
       Text = SSWorkbook_createCellStyle(book);
       SSCellStyle_setAlignment(Text: ALIGN_LEFT);

       // -------------------------------------------------
       // Create a cell style for dates. Dates in Excel
       //  are numbers that are formatted in a particular
       //  way.
       // -------------------------------------------------
       Dates = SSWorkbook_createCellStyle(book);

       DataFmt = SSWorkbook_createDataFormat(book);
       TempStr = new_String('m/d/yy');
       DateFmt = SSDataFormat_getFormat(DataFmt: TempStr);
       SSCellStyle_setDataFormat(Dates: DateFmt);

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* addSheet: Creates a new sheet or tab within the specified
     P*   workbook based on the passed in parameters.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    Inbook         Workbook Object (SSWorkbook)
     P*    InsheetName    Sheet Name      (20A)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P addSheet        B                   Export

     D addSheet        PI
     D   InsheetName                 20A   Const Varying

     D rowcount        S             10I 0 Inz(*Zeros)
     D start           S              5A   Varying
     D end             S              5A   Varying
     D AddFormula      S              1N   Inz(*Off)

      /Free

       // ------------------------------------
       // Create new Sheet / Tab on Workbook
       // ------------------------------------
       sheet = SS_newSheet(book: InsheetName);

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* createRow: Creates a row within the worksheet of a workbook
     P*   based on the passed in parameters.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    Inrow          Row Number      (10I:0)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P createRow       B                   Export

     D createRow       PI
     D   Inrow                       10I 0 Const

      /Free

        row = SSSheet_createRow(sheet: Inrow);

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* setColumn: Set data into cells for each row in a worksheet
     P*   for a workbook based on the passed in parameters.
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    Incolumn       Column Number   (10I:0)
     P*    Instring       String Data     (1024A)
     P*    Instyle        String Style    (10A)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P setColumn       B                   Export

     D setColumn       PI
     D   Incolumn                    10I 0 Const
     D   Instring                  1024A   Const Varying
     D   Instyle                     10A   Const

     D style           S                   Like(SSCellStyle)

      /Free

        Select;
        When Instyle = 'Text';
          style = Text;
        When Instyle = 'Dates';
          style = Dates;
        When Instyle = 'Numeric';
          style = Numeric;
        EndSl;

        ss_text( row: Incolumn: %Trim(Instring): style);

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* setColumnWidth: Set the column widths of a worksheet of a
     P*   workbook based on the passed in parameters.
     P*
     P*   Column numbering starts with 0, so Column A = 0, B = 1, etc.
     P*   Width of a column is 1/256th of a character
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    Incolumn       Column Number   (10I:0)
     P*    Inwidth        Width (1/256th) (10I:0)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P setColumnWidth  B                   Export

     D setColumnWidth  PI
     D   Incolumn                    10I 0 Const
     D   Inwidth                     10I 0 Const

      /Free

        // -------------------------------------------
        // The column width setting is in units that
        //  are approximately 1/256th of a character
        // -------------------------------------------
        SSSheet_setColumnWidth( sheet: Incolumn: Inwidth * 256 );

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* setColumnMerge: Set the columns that you want to be merged of
     P*   a worksheet for a workbook based on the parameters passed.
     P*
     P*   RowFrom = Row of upper-left corner of area to merge
     P*   ColumnFrom = Column of upper-left corner of area to merge
     P*   RowTo = Row of lower-right corner of area to merge
     P*   ColumnTo = Column of lower-right corner of area to merge
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    InrowFrom      Row Number      (10I:0)
     P*    IncolumnFrom   Column Number   (10I:0)
     P*    InrowTo        Row Number      (10I:0)
     P*    IncolumnTo     Column Number   (10I:0)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P setColumnMerge  B                   Export

     D setColumnMerge  PI
     D   InrowFrom                   10I 0 Const
     D   IncolumnFrom                10I 0 Const
     D   InrowTo                     10I 0 Const
     D   IncolumnTo                  10I 0 Const

      /Free

        // ---------------------------------------------------
        //  Merge columns / rows to make room for larger
        //   elements of data such as headings or titles
        // ---------------------------------------------------
        ss_merge(sheet: InrowFrom: IncolumnFrom: InrowTo: IncolumnTo);

      /End-Free

     P                 E

     P*****************************************************************
     P*
     P* setHeadings: Set the heading type of a row / column for a
     P*   worksheet of a workbook based on the parameters passed
     P*
     P*****************************************************************
     P*
     P*  Incoming:
     P*    Incolumn       Column Number   (10I:0)
     P*    Intext         Text Value      (40A)
     P*    Inheading      Heading Type    (1A)
     P*
     P*  Outgoing:
     P*    None
     P*
     P*  Returns:
     P*    None
     P*
     P*****************************************************************
     P setHeadings     B                   Export

     D setHeadings     PI
     D   Incolumn                    10I 0 Const
     D   Instring                    40A   Const Varying
     D   Inheading                    1A   Const Options(*NoPass)

     D heading         S                   Like(SSCellStyle)

      /Free

       // ------------------------------------------------
       // Set the heading type, Default to column heading
       // ------------------------------------------------
       heading = ColHeading;
       If %Parms >= 3;
         Select;
          When Inheading = '0';
            heading = ColHeading;
          When Inheading = '1';
            heading = LgHeading;
          When Inheading = '2';
            heading = SmHeading;
         EndSl;
       EndIf;

       // -----------------------------------------
       // Set the row/colmn text and heading type
       // -----------------------------------------
       ss_text( row: Incolumn: Instring: heading);

      /End-Free

     P                 E


 
Last edited by wdwisser on Tue May 03, 2022 8:57 pm, edited 2 times in total.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

TestExcel program

Code: Select all

     FDIVSALES  IF   E           K DISK

      /Copy QSrc,ExcelC

     D rowcount        S             10I 0 inz(*Zeros)
     D month           S              2P 0 inz(*Zeros)

     C     *Entry        Plist
     C                   Parm                    InXssf            1
      /Free

         crtExcelObj(InXssf);

         For month = 1 to 2;
           If Month = 1;
           addSheet('January');
           EndIf;
           If Month = 2;
           addSheet('February');
           EndIf;

           setColumnWidth( 0: 04);
           setColumnWidth( 1: 30);
           setColumnWidth( 2: 10);
           setColumnWidth( 3: 15);

           setColumnMerge( 0: 0: 0: 3);
           setColumnMerge( 1: 0: 1: 3);
           setColumnMerge( 2: 0: 2: 3);

           rowcount = *Zeros;
           createRow(rowcount);
           setHeadings( 0: 'Big Big Corporation': '1');
           rowcount += 1;
           createRow(rowcount);
           setHeadings( 0: 'Weekly Sales Figures': '2');
           rowcount += 1;
           createRow(rowcount);
           setHeadings( 0: 'for the month of ': '2');
           rowcount += 2;
           createRow(rowcount);
           setHeadings( 0: 'Div');
           setHeadings( 1: 'Div Name');
           setHeadings( 2: 'Date');
           setHeadings( 3: 'Sales');

         setll (month) DIVSALES;
         reade (month) DIVSALES;

         dow not %eof(DIVSALES);

            rowcount += 1;
            createRow(rowcount);

            setColumn( 0 : %char(DIVNO)   : 'Text');
            setColumn( 1 : %trimr(DIVNAME): 'Text');
            setColumn( 2 : %char(PostDate): 'Dates');
            setColumn( 3 : %char(Sales): 'Numeric');
         // AddFormula = *On;

            reade (month) DIVSALES;
         enddo;

         endfor;

         saveExcelObj('/tmp/': 'TextExcel');

         *InLR = *On;
         Return;
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

Excel Service Program Binding Source

Code: Select all

STRPGMEXP  PGMLVL(*CURRENT)
   EXPORT     SYMBOL(crtExcelObj)
   EXPORT     SYMBOL(saveExcelObj)
   EXPORT     SYMBOL(addSheet)
   EXPORT     SYMBOL(createRow)
   EXPORT     SYMBOL(setColumn)
   EXPORT     SYMBOL(setColumnWidth)
   EXPORT     SYMBOL(setColumnMerge)
   EXPORT     SYMBOL(setHeadings)
ENDPGMEXP
Last edited by wdwisser on Tue May 03, 2022 1:00 pm, edited 1 time in total.
wdwisser
Posts: 30
Joined: Tue Feb 15, 2022 5:12 pm

Re: Style Format

Post by wdwisser »

Excel Service Program copy book

Code: Select all


     D*****************************************************************
     D crtExcelObj     PR
     D   Inxssf                        N   Const Options(*NoPass)

     D*****************************************************************
     D saveExcelObj    PR
     D   filePath                   100A   Const Varying
     D   fileName                   100A   Const Varying

     D*****************************************************************
     D addSheet        PR
     D   InsheetName                 20A   Const Varying

     D*****************************************************************
     D createRow       PR
     D   Inrow                       10I 0 Const

     D*****************************************************************
     D setColumn       PR
     D   Incolumn                    10I 0 Const
     D   Instring                  1024A   Const Varying
     D   Instyle                     10A   Const

     D*****************************************************************
     D setColumnWidth  PR
     D   Incolumn                    10I 0 Const
     D   Inwidth                     10I 0 Const

     D*****************************************************************
     D setColumnMerge  PR
     D   InrowFrom                   10I 0 Const
     D   IncolumnFrom                10I 0 Const
     D   InrowTo                     10I 0 Const
     D   IncolumnTo                  10I 0 Const

     D*****************************************************************
     D setHeadings     PR
     D   Incolumn                    10I 0 Const
     D   Instring                    40A   Const Varying
     D   Inheading                    1A   Const Options(*NoPass)

Post Reply