B4J Question jPOI: using template and protecting workbooks/worksheets

[DMW]

Hi all,

Working extensively with jPOI has created two issues which I found to be hard to find workarounds for. At least when only using jPOI.

With MS Excel we can create true templates by using xlt and xltx file extensions. Whenever we open a xlt/xltx it creates a copy of the workbook. But jPOI cannot work with these file extensions. The workaround is to use a xls/xlsx file as a "template" and save the workbook under a different name.

I prefer to work with true templates so the question is; is it possible at all?

Another issue is protecting workbooks and worksheets. When creating Excel reports it's common to protect the worksheets' reports.

Looking into POI it seems that it's possible via JavaFX. Can anyone show me an example on how to do it in code?

Later on I will publish a tutorial about creating professional Excel Reports. The two issues are part of the tutorial.

Thanks in advance and all the best,
Dennis

 

[Erel]

Which error did you get when you try to open the template file?

Looking into POI it seems that it's possible via JavaFX

Apache POI is not related to JavaFX. Maybe you meant JavaObject.

Have you tried to set the the locking in the template file (or pseudo template file)?

 

[DMW]

Hello,
Sorry, I meant JavaObject and nothing else.

#1 Using xlt/xltx template
Everything seems to work well, even upon saving. But when trying to open the file in Excel it says the file is corrupted and if I want to restore it. When restoring it almost everything seems to be correctly only some part "under the hood" is removed. The issue, at least from my point of view, is that I first open an xltx file, add data to it and then save it as xlxs file. This is doable in Excel while jPOI get confused. (Saving a template file to a new template file doesn't make any sense.) I have not yet find any additional information on the subject.

Aha! It seems that I have overlooked the option to pass a password when open the file. Great! I'll test it and come back. Thanks!

 

[Erel]

It might be a limitation of Apache POI.

 

[DMW]

Yes, I agree it's a limitation in the present version. Since the workaround is rather smooth I can live with it ;-)

I finally managed to find the information to protect an individual worksheet:

....
Dim stSecret As String = "Dennis"
Dim jo As JavaObject = wsData
jo.RunMethod("protectSheet",Array As Object(stSecret))
....

Case closed and now I can move on with the tutorial!