Protect sheet when xlsx

[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]

Found it. Had to XSSF on the prototype for the protect sheet.

[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]

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]

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.

[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]

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]

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