Page 1 of 1
Protect sheet when xlsx
Posted: Fri Mar 04, 2022 7:02 pm
by jeqberry
I have a program that was writing xls. I changed it to use the new HSSFR4 in order to handled xlsx. It works well, except for the Protect Sheet. Is does not want to protect the sheet. I do not see any errors. Maybe is should not be hssf any longer?
D ssSheet_protectSheet...
D PR ExtProc( *Java
D : 'org.apache.poi.hssf-
D .usermodel.HSSFSheet'
D : 'protectSheet' )
D password like(jString) const
D password s like(jString)
sheet = ss_getSheet(book: 'SUMMARY');
password = new_String('JBC123');
ssSheet_protectSheet(sheet: password);
Re: Protect sheet when xlsx
Posted: Fri Mar 04, 2022 8:15 pm
by jeqberry
Found it. Had to XSSF on the prototype for the protect sheet.
Re: Protect sheet when xlsx
Posted: Tue Jun 11, 2024 6:16 pm
by HEB768
Hi, I have the same problem of you when I want to use xlsx. I can not protected the sheet. Can you capture the protoype of you solution please to watch if I have the same pleased.
Re: Protect sheet when xlsx
Posted: Tue Jun 11, 2024 7:02 pm
by Scott Klement
Haven't tried it.... but I suspect you just have to replace the class name in the first post in this thread with SHEET_CLASS from the HSSF_H copybook. The only mistake I see is hard-coding the HSSF class, which means it'll only work for old-school xls sheets. Changing it to SHEET_CLASS should make it work for either XLS or XLSX.
Re: Protect sheet when xlsx
Posted: Wed Jun 12, 2024 1:31 pm
by HEB768
Hi,
I changed the class of the 'protectSheet' method in the prototype using the XSSFSheet class and I end up with an error that it can't find the method. On the other hand, when I use the 'protectSheet' method with the HSSFSheet class with an XLSX file of the XSSFWorkbook class, the program ends normally but no sheet is in protection mode.
I put attachments to demontraste the definition and the error message. Thank you.
Re: Protect sheet when xlsx
Posted: Thu Jun 13, 2024 3:59 am
by Scott Klement
Indeed, if I look in the documentation for POI 3.6 (which seems to be what you're using) neither the Sheet interface (which I recommended to you) or the XSSFSheet class (which is what you used) contains a protectSheet() method.
However, the documentation for poi 3.8 does show this method. And, I recommend using the the Sheet interface instead of the XSSFSheet class (i.e. use the SHEET_CLASS constant I have defined in HSSF_H, as I recommended in the prior post -- and use it with POI 3.8)
Re: Protect sheet when xlsx
Posted: Thu Jun 13, 2024 4:28 am
by Scott Klement
I tried it using the following CLASSPATH (after downloading and putting POI 3.8 in the appropriate directories)
Code: Select all
ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/home/sklement/java/poi3.8/xlparse.jar+
:/home/sklement/java/poi3.8/poi-3.8-20120326.jar+
:/home/sklement/java/poi3.8/poi-ooxml-3.8-20120326.jar+
:/home/sklement/java/poi3.8/+
poi-ooxml-schemas-3.8-20120326.jar+
:/home/sklement/java/dom4j/dom4j-1.6.1.jar+
:/home/sklement/java/xmlbeans/jsr173_1.0_api.jar+
:/home/sklement/java/xmlbeans/xbean.jar') +
LEVEL(*JOB) +
REPLACE(*YES)
And the following prototype:
Code: Select all
D ssSheet_protectSheet...
D PR ExtProc( *Java: SHEET_CLASS
D : 'protectSheet')
D password like(jString) const
And the following code to enable protection:
Code: Select all
str = new_String('my-password');
SSSheet_protectSheet(sheet: str);
ss_freeLocalRef(str);
Worked fine with 3.8, doesn't work with 3.6.
Re: Protect sheet when xlsx
Posted: Thu Jun 13, 2024 1:04 pm
by HEB768
Hi Scott. The protect Sheet with Excel XLSX working now with your solution. Thank you very much for you help.