Protect sheet when xlsx

Scott Klement's open source interface to the POI HSSF/XSSF Spreadsheet Project for RPG Programmers. http://www.scottklement.com/poi/
Post Reply
jeqberry
Posts: 5
Joined: Wed Jan 19, 2022 10:53 pm

Protect sheet when xlsx

Post 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);
jeqberry
Posts: 5
Joined: Wed Jan 19, 2022 10:53 pm

Re: Protect sheet when xlsx

Post by jeqberry »

Found it. Had to XSSF on the prototype for the protect sheet.
HEB768
Posts: 6
Joined: Thu May 16, 2024 7:04 pm

Re: Protect sheet when xlsx

Post 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.
Scott Klement
Site Admin
Posts: 732
Joined: Sun Jul 04, 2021 5:12 am

Re: Protect sheet when xlsx

Post 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.
HEB768
Posts: 6
Joined: Thu May 16, 2024 7:04 pm

Re: Protect sheet when xlsx

Post 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.
Attachments
Problem Method ProtectSheet.zip
(95.2 KiB) Downloaded 65 times
Scott Klement
Site Admin
Posts: 732
Joined: Sun Jul 04, 2021 5:12 am

Re: Protect sheet when xlsx

Post 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)
Scott Klement
Site Admin
Posts: 732
Joined: Sun Jul 04, 2021 5:12 am

Re: Protect sheet when xlsx

Post 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.
HEB768
Posts: 6
Joined: Thu May 16, 2024 7:04 pm

Re: Protect sheet when xlsx

Post by HEB768 »

Hi Scott. The protect Sheet with Excel XLSX working now with your solution. Thank you very much for you help.
Post Reply