B4J Question jPOI: using template and protecting workbooks/worksheets

Discussion in 'B4J Questions' started by DMW, Jan 14, 2017.

  1. DMW

    DMW Member Licensed User

    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,
  2. Erel

    Erel Administrator Staff Member Licensed User

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

    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)?
  3. DMW

    DMW Member Licensed User

    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!
  4. Erel

    Erel Administrator Staff Member Licensed User

    It might be a limitation of Apache POI.
  5. DMW

    DMW Member Licensed User

    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
    "protectSheet",Array As Object(stSecret))
    Case closed and now I can move on with the tutorial!
    Erel likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice