I am receiving the following message "RPG procedure SS_OPEN in program POI36/HSSFR4 received Java exception "java.lang.RuntimeException: Failed to find a matching shared formula record" when calling method "create" with signature "Ljava.io.InputStream;)Lorg.apache.poi.ss.usermodel.Workbook;" in class "org.apache.poi.ss.usermodel.WorkbookFactory".
The file I am trying to open is in the IFS in the specified folder and named exactly what is trying to be opened. I'm not sure what this all means and I could not find anything of substance on the ole internet. Any help would be appreciated.
Bill
Code: Select all
* Demonstration of using POI to create a complex Excel workbook
*
* To compile:
* Make sure you've already created HSSFR4. See the instructions
* on that source member for details.
*
*
H DFTACTGRP(*NO)
H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
H THREAD(*SERIALIZE)
H BNDDIR('HSSF')
FWMIPBFG10 IF E K DISK
F Rename(WMIPBFG10:WMIPBFG)
/Copy POI36/QRpgLESrc,hssf_h
D IFSDIR C '/tmp'
D TESTEXCEL4 PR ExtPgm('TESTEXCEL4')
D peXSSF 1n const options(*nopass)
D TESTEXCEL4 PI
D peXSSF 1n const options(*nopass)
D CreateCellStyles...
D PR
D AddSheet PR
D book like(SSWorkbook)
D sheetname 20A varying const
D FormatColumns PR
D sheet like(SSSheet)
D SetHeadings PR
D sheet like(SSSheet) const
D sheetname 20A varying const
D rowcount 10I 0
D xssf s 1n inz(*off)
D book s like(SSWorkbook)
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)
/free
if %parms>=1 and peXSSF;
xssf = *on;
endif;
ss_begin_object_group(100);
if (xssf);
book = ss_open(IFSDIR + '/TestExcelReplace.xlsx');
else;
book = ss_open(IFSDIR + '/TestExcelReplace.xls');
endif;
CreateCellStyles();
AddSheet(book: 'Total Shipments');
if (xssf);
SS_save(book: IFSDIR + '/TestExcelReplace.xlsx');
else;
SS_save(book: IFSDIR + '/TestExcelReplace.xls');
endif;
ss_end_object_group();
*inlr = *on;
/end-free
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* CreateCellStyles(): Create the different display styles
* used for cells in this Excel workbook.
*
* NOTE: Uses the following global variables:
* Book, LgHeading, SmHeading, ColHeading, Numeric
* Text, Dates.
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* AddSheet(): Add a sheet of data to the given sheet
*
* sheetname = (input) human-readable sheet name
* book = (input) Workbook to add sheet to
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P AddSheet B
D AddSheet PI
D book like(SSWorkbook)
D sheetname 20A varying const
D sheet s like(SSSheet)
D row s like(SSRow)
D rowcount s 10I 0 inz(0)
D start s 5A varying
D end s 5A varying
D AddFormula s 1N inz(*OFF)
/free
sheet = SS_getSheet(book: sheetname);
FormatColumns(sheet);
SetHeadings(sheet: sheetname: rowcount);
read WMIPBFG10;
dow not %eof(WMIPBFG10);
rowcount += 1;
row = SSSheet_getRow(sheet: rowcount);
ss_text( row: 0 : %trimr(BreakLvl): Text);
ss_text( row: 1 : %trimr(OverFlow): Text);
ss_text( row: 2 : %trimr(Class) : Text);
ss_text( row: 3 : %trimr(Prdno) : Text);
ss_num ( row: 4 : Quant01 : Numeric);
ss_num ( row: 5 : Stdc1 : Numeric);
ss_num ( row: 6 : Cost01 : Numeric);
ss_num ( row: 7 : Mwght02 : Numeric);
start = ss_cellname( rowcount: 4);
end = ss_cellname( rowcount: 7);
ss_formula( row: 8: start+'*'+end: Numeric);
read WMIPBFG10;
enddo;
/end-free
P E
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* FormatColumns(): Set the column widths & merged cells
* in a given worksheet.
*
* sheet = (input) sheet to set the column widths in
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P FormatColumns B
D FormatColumns PI
D sheet like(ssSheet)
/free
//
// The column width setting is in units that are approx
// 1/256 of a character.
//
SSSheet_setColumnWidth( sheet: 0: 10 * 256 );
SSSheet_setColumnWidth( sheet: 1: 10 * 256 );
SSSheet_setColumnWidth( sheet: 2: 10 * 256 );
SSSheet_setColumnWidth( sheet: 3: 10 * 256 );
SSSheet_setColumnWidth( sheet: 4: 10 * 256 );
SSSheet_setColumnWidth( sheet: 5: 10 * 256 );
SSSheet_setColumnWidth( sheet: 6: 10 * 256 );
SSSheet_setColumnWidth( sheet: 7: 10 * 256 );
SSSheet_setColumnWidth( sheet: 8: 10 * 256 );
/end-free
P E
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SetHeadings(): Set the text in the first few rows of
* the given worksheet so that they appear like "headings"
* to someone viewing the sheet.
*
* sheet = (input) sheet that cells are set in.
* rowcount = (input/output) row count (updated)
*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
P SetHeadings B
D SetHeadings PI
D sheet like(SSSheet) const
D sheetname 20A varying const
D rowcount 10I 0
D row s like(SSRow)
/free
row = SSSheet_getRow(sheet: rowcount);
ss_text( row: 0 : 'Breaklvl' : ColHeading);
ss_text( row: 1 : 'Overflow' : ColHeading);
ss_text( row: 2 : 'Class' : ColHeading);
ss_text( row: 3 : 'Prdno' : ColHeading);
ss_text( row: 4 : 'Quant01' : ColHeading);
ss_text( row: 5 : 'Stdc1' : ColHeading);
ss_text( row: 6 : 'Cost01' : ColHeading);
ss_text( row: 7 : 'Mwght02' : ColHeading);
ss_text( row: 8 : 'Total Lbs' : ColHeading);
/end-free
P E