I have code that was using hssf procedures as it was an xls (old excel).
Management want the excel to now be xlsx. I used the ss_formula procedure call and it did not abend.
The issue is when I go to open the xlsx it indicates something is wrong with spreadsheet.
It allows me to open it and with this message: Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)
After it is open, the formulas are not in the cells where they are expected.
When it was an xls using the HSSF_formula call it opens fine and the formulas are there.
There must be a step I am missing and I do not see it.
This is an existing excel that gets opened, updated, and written back with a new name.
trouble with formulas
-
- Site Admin
- Posts: 763
- Joined: Sun Jul 04, 2021 5:12 am
Re: trouble with formulas
I must admit, it has been a long time since I used HSSFR4. That said, when I did I was able to use ss_formula (I used it in many projects back then) and I did not have any problems.
In order to help you, we'll need to know how to reproduce the problem you're having. Can you provide an exact, step-by-step, set of instructions that we can use to reproduce it? (preferably with a code sample.)
In order to help you, we'll need to know how to reproduce the problem you're having. Can you provide an exact, step-by-step, set of instructions that we can use to reproduce it? (preferably with a code sample.)
Re: trouble with formulas
Could be back when you did it, things did work. I tried opening with Excel 2013 and in Teams. Teams will open it, and prompts to "Fix It". Excel 2013 opens it and removes the formulas.
I went to the web, and one place indicated I needed poi-ooxml, and I do see that jar on the system and gets loaded with the class envvar.
ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk+
70/64bit') REPLACE(*YES)
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/poi/poi3.7/poi-3.7-20101029.jar+
:/poi/poi3.7/poi-ooxml-3.7-20101029.jar+
:/poi/poi3.7/poi-ooxml-schemas-3.7-20101029.jar+
:/poi/poi3.7/dom4j-1.6.1.jar+
:/poi/poi3.7/jsr173_1.0_api.jar+
:/poi/poi3.7/xmlbeans-2.3.0.jar+
:/poi/poi3.7/xbean.jar') +
LEVEL(*JOB) +
REPLACE(*YES)
Below is the code, and if you need additional information I can get it.
Here is the opening of the file:
book = ss_open('/users/' + %TRIMR('Supplier_Rat_Rep_No_+
Formula.xlsx'));
sheet = ss_getSheet(book: 'SUMMARY');
sheet2 = ss_getSheet(book: 'DETAIL');
sheet3 = ss_getSheet(book: 'PASTDUE');
sheet4 = ss_getSheet(book: 'REJECTED');
Then here is the setting of the formulas and saving the files.
For the Start and End, I saw the proper values as I ran it in debug.
Also it started out without the %trim and received the same errors.
c eval filepath = '/USERS/SRR_' + %TRIM(FLENAM) +
C '_' + ADTFRYY + '_' + TIME6 + '.XLSX'
/free
// update formulas
ROWNUM = 12;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 8: 1);
end = ss_cellname( 11: 1);
ss_formula( ROW: 1: 'SUM('+%trim(start)+':'+%trim(end)+')':
Numeric2L);
start = ss_cellname( 8: 3);
end = ss_cellname( 11: 3);
ss_formula( ROW: 3: 'SUM('+%trim(start)+':'+%trim(end)+')':
Numeric3L);
ROWNUM = 8;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 8: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 8: 3);
end = ss_cellname( 12: 3);
IF TOT_QTY > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
ROWNUM = 9;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 9: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 9: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
// Delivery rating
ROWNUM = 0;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 9: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 14: 'SUM('+start+'/'+END+')':
Numeric5F);
ELSE;
ss_num ( row: 14 : 0 : Numeric2L);
ENDIF;
ROWNUM = 10;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 10: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 10: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
ROWNUM = 11;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 11: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 11: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
//hssfFormulaEvaluator(book);
password = new_String('JBC123');
ssSheet_protectSheet(sheet: password);
B2 IF INCL_DETL <> 'Y' OR INCL_OVRDUE <> 'Y' OR INCL_REJ <> 'Y';
DeleteSheet();
ENDIF;
ss_save(book: %trim(FILEPATH));
ss_end_object_group();
I went to the web, and one place indicated I needed poi-ooxml, and I do see that jar on the system and gets loaded with the class envvar.
ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk+
70/64bit') REPLACE(*YES)
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/poi/poi3.7/poi-3.7-20101029.jar+
:/poi/poi3.7/poi-ooxml-3.7-20101029.jar+
:/poi/poi3.7/poi-ooxml-schemas-3.7-20101029.jar+
:/poi/poi3.7/dom4j-1.6.1.jar+
:/poi/poi3.7/jsr173_1.0_api.jar+
:/poi/poi3.7/xmlbeans-2.3.0.jar+
:/poi/poi3.7/xbean.jar') +
LEVEL(*JOB) +
REPLACE(*YES)
Below is the code, and if you need additional information I can get it.
Here is the opening of the file:
book = ss_open('/users/' + %TRIMR('Supplier_Rat_Rep_No_+
Formula.xlsx'));
sheet = ss_getSheet(book: 'SUMMARY');
sheet2 = ss_getSheet(book: 'DETAIL');
sheet3 = ss_getSheet(book: 'PASTDUE');
sheet4 = ss_getSheet(book: 'REJECTED');
Then here is the setting of the formulas and saving the files.
For the Start and End, I saw the proper values as I ran it in debug.
Also it started out without the %trim and received the same errors.
c eval filepath = '/USERS/SRR_' + %TRIM(FLENAM) +
C '_' + ADTFRYY + '_' + TIME6 + '.XLSX'
/free
// update formulas
ROWNUM = 12;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 8: 1);
end = ss_cellname( 11: 1);
ss_formula( ROW: 1: 'SUM('+%trim(start)+':'+%trim(end)+')':
Numeric2L);
start = ss_cellname( 8: 3);
end = ss_cellname( 11: 3);
ss_formula( ROW: 3: 'SUM('+%trim(start)+':'+%trim(end)+')':
Numeric3L);
ROWNUM = 8;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 8: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 8: 3);
end = ss_cellname( 12: 3);
IF TOT_QTY > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
ROWNUM = 9;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 9: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 9: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
// Delivery rating
ROWNUM = 0;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 9: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 14: 'SUM('+start+'/'+END+')':
Numeric5F);
ELSE;
ss_num ( row: 14 : 0 : Numeric2L);
ENDIF;
ROWNUM = 10;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 10: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 10: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
ROWNUM = 11;
row = SSSheet_getRow(sheet: ROWNUM);
start = ss_cellname( 11: 1);
end = ss_cellname( 12: 1);
IF TOT_QTY > 0;
ss_formula( ROW: 2: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 2 : 0 : Numeric2L);
ENDIF;
start = ss_cellname( 11: 3);
end = ss_cellname( 12: 3);
IF TOT_DOL > 0;
ss_formula( ROW: 4: 'SUM('+start+'/'+END+')':
Numeric5L);
ELSE;
ss_num ( row: 4 : 0 : Numeric2L);
ENDIF;
//hssfFormulaEvaluator(book);
password = new_String('JBC123');
ssSheet_protectSheet(sheet: password);
B2 IF INCL_DETL <> 'Y' OR INCL_OVRDUE <> 'Y' OR INCL_REJ <> 'Y';
DeleteSheet();
ENDIF;
ss_save(book: %trim(FILEPATH));
ss_end_object_group();
Re: trouble with formulas
I found the issue.
After a bit of google searching I found that the original sheet cannot have any formulas in it.
The source sheet I was using had 2 simple formulas in a couple of cells =b13 and =C12.
With xlsx you need to have no formulas in the sheet you are going to update , or you have to set the formulas to NULL in the POI program.
The cells that had the formulas were ones I did not know already had formulas, and I was writing RPG calculated data there.
This program I was modifying originally used an xls file, and the HSSF calls. The client wanted it in xlsx, so it seemed as easy as changing the hssf calls to ss calls. This did work for the data and the formulas I added to the sheet, and it caused the CalcChain issue.
After I removed all the formulas form the source sheet, which is only used as a template, it works as expected.
The reason for the template is it was already laid out with borders, logo, and charts.
All it needed to work was the data.
After a bit of google searching I found that the original sheet cannot have any formulas in it.
The source sheet I was using had 2 simple formulas in a couple of cells =b13 and =C12.
With xlsx you need to have no formulas in the sheet you are going to update , or you have to set the formulas to NULL in the POI program.
The cells that had the formulas were ones I did not know already had formulas, and I was writing RPG calculated data there.
This program I was modifying originally used an xls file, and the HSSF calls. The client wanted it in xlsx, so it seemed as easy as changing the hssf calls to ss calls. This did work for the data and the formulas I added to the sheet, and it caused the CalcChain issue.
After I removed all the formulas form the source sheet, which is only used as a template, it works as expected.
The reason for the template is it was already laid out with borders, logo, and charts.
All it needed to work was the data.
Re: trouble with formulas
I'm having something of a similar problem. I have some cells with formulas that are I want to have the Spreadsheet use once I've loaded the data in cells that the formulas are referencing. For some reason the pre-set formulas will not calculate until I open the spreadsheet and click on the formula cell and hit enter and then it will produce the results based on the formula using the data I loaded using the POI RPG program.
Can someone tell me why this is the case and why the formulas won't pre-calculate the values once the spreadsheet is opened? Thanks for any help/advice you can give.
Can someone tell me why this is the case and why the formulas won't pre-calculate the values once the spreadsheet is opened? Thanks for any help/advice you can give.
-
- Site Admin
- Posts: 763
- Joined: Sun Jul 04, 2021 5:12 am
Re: trouble with formulas
I haven't worked with HSSF/POI in many years... but I seem to recall that if there's any sort of error, it'll do what you describe. Excel will open the sheet and give you a note explaining what the error is -- but then when you click in the cell, it will try to "fix" it for you, and if successful will do the calculation correctly.
So please look at the error it gives you explaining the problem.
So please look at the error it gives you explaining the problem.
Re: trouble with formulas
I have this spreadsheet called TestExcelReplace.xlsx that has the formulas pre-loaded as follows:
=SUMIF(D:D,BJ4,J:J)/2000 in column BK4
=SUMIF(D:D,BJ5,J:J)/2000 in column BK5
=SUMIF(D:D,BJ6,J:J)/2000 in column BK6
etc.
I then load the data in the columns A through J from my iSeries program and the columns BK4 through BK15 still have no data from the resulting formula with the data loaded (as you can see) and there are no errors when I open the file it just doesn't calculate the values from the formulas.
I'm unsure why the formulas don't work and if I click on the cell BK4 through BK15 and press Enter the formulas will process and give results. Any help would be appreciated and thanks for looking.
=SUMIF(D:D,BJ4,J:J)/2000 in column BK4
=SUMIF(D:D,BJ5,J:J)/2000 in column BK5
=SUMIF(D:D,BJ6,J:J)/2000 in column BK6
etc.
I then load the data in the columns A through J from my iSeries program and the columns BK4 through BK15 still have no data from the resulting formula with the data loaded (as you can see) and there are no errors when I open the file it just doesn't calculate the values from the formulas.
I'm unsure why the formulas don't work and if I click on the cell BK4 through BK15 and press Enter the formulas will process and give results. Any help would be appreciated and thanks for looking.
Re: trouble with formulas
I do not know if it would make any difference but your formula is using an entire column.
Just for a test could you change your formula like this
=SUMIF(D4:D40,BJ4,J4:J40)/2000 in column BK4
Where 40 is the last row of data your program writes.
And see if that makes any difference.
Cheers, Don
Just for a test could you change your formula like this
=SUMIF(D4:D40,BJ4,J4:J40)/2000 in column BK4
Where 40 is the last row of data your program writes.
And see if that makes any difference.
Cheers, Don
Re: trouble with formulas
I changed the formula to the following: =SUMIF(D$2:D$1900,BJ4,J$2:J$1900)/2000
This didn't change anything on how the spreadsheet calculates the cell when it opened and it still shows as it did with the entire row being in the formula. Again, if I click on the cell and press Enter it will execute the formula and all is good. Very odd and I do appreciate the input and I'll try to respond as quickly as I can to see if we can correct this issue.
Thanks again.
Bill
This didn't change anything on how the spreadsheet calculates the cell when it opened and it still shows as it did with the entire row being in the formula. Again, if I click on the cell and press Enter it will execute the formula and all is good. Very odd and I do appreciate the input and I'll try to respond as quickly as I can to see if we can correct this issue.
Thanks again.
Bill
Re: trouble with formulas
But was there data in every row up to 2000 ?
I was suggesting the sum just include the rows with data
I was suggesting the sum just include the rows with data