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
Style Format
Style Format
- Attachments
-
- TestExcel.jpg (160.96 KiB) Viewed 61178 times
-
Scott Klement
- Site Admin
- Posts: 872
- Joined: Sun Jul 04, 2021 5:12 am
Re: Style Format
I don't understand this code. What are Text, Dates and Numeric?
Re: Style Format
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);
// ----------------------------------------------------
// 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: 872
- Joined: Sun Jul 04, 2021 5:12 am
Re: Style Format
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?
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?
Re: Style Format
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: 872
- Joined: Sun Jul 04, 2021 5:12 am
Re: Style Format
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
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.
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.
Re: Style Format
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.
Re: Style Format
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;
Re: Style Format
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.
Re: Style Format
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)