4

I’m creating an Excel file and it should be read-only. To do this I use: (sheetName).getSettings().setProtected(true); for every new sheet added.

Only someone with admin rights can get full access to the file, so after confirming a user has admin rights I do: (workbookName).getSheet(0).getSettings().setProtected(false);

After someone with admin rights opens the file, it is not read-only anymore, until an ordinary user adds some more content to it – .getSettings().setProtected(true); again. In other words, someone can change the file after admin accesses it and before non-admin does so. I don’t have a clue how to go around it, maybe you could help me out?

--EDIT--
Users are adding data to the excel file, filling cells with information file - read workbook, copy, add some info, write and close workbook. Non-admin users can open it directly to see if what they added is there, or to see what other users added, but they should NOT be able to change anything in the file.

It is a wizard-like app that asks users for data and then adds cells to the file. One of the methods for adding data is here. The file gathers personal information from the users. The app is used only to add data, not to access it. Users can access the file only directly by double-clicking the output, and only to view the data. Admins can change the file, other users should not be able to do so.

Community
  • 1
  • 1
Hurdler
  • 891
  • 3
  • 15
  • 30
  • 1
    http://stackoverflow.com/questions/2609301/password-protected-excel-file shows how to loop through worksheets. To solve your problem with non-admin users, you could set the protection (true/false depending on access level) every time the file is opened. – Tim Williams Apr 16 '12 at 23:21
  • Thanks for the link, I sorted out looping through the sheets. Regarding your suggestion on the access issue, however, I can't see how can I do that. I set the protection every time after the file is altered (`true` if non-admin users change it and `false` if admins do). The problem is that someone can "manually" (not through the software) open and modify the file after admin set the protection to `false` and before non-admin sets it back to `true`, when it should be read-only. – Hurdler Apr 17 '12 at 19:48
  • 1
    If you know when the admin is done, you could re-enable the protection at that point – Tim Williams Apr 17 '12 at 21:55
  • The problem is I don't know it – Hurdler Apr 17 '12 at 23:10
  • 1
    You'll need to add a bit more detail about how your application interacts with the Excel file - what tasks are users performing via your app, and why do users also need to open it directly? – Tim Williams Apr 17 '12 at 23:31
  • It's still very unclear from your description how data gets into the Excel file, and how and why people are accessing it, either directly or via your java app. For example, it would help to explain what the file is for, what is the role of the java app, why would someone access the file directly vs. through the java app, etc. – Tim Williams Apr 22 '12 at 01:12
  • Why not serve a copy of the main file to non-admin users? That way they can view the contents, but it won't matter if they can edit it. – Tim Williams Apr 23 '12 at 23:20
  • 2
    Why not serve a copy of the main file to non-admin users? That way they can view the contents, but it won't matter if they can edit it. I'm still unclear on how your app is set up: where is the Excel file hosted - on a network share, on a web server, or what? Is there just one "master" file for all users? Can any user just double-click the file to open it, or does your java app provide them access? If anyone can just open the file in read mode, that seems like basically a bad idea which it's going to be difficult to fix. – Tim Williams Apr 23 '12 at 23:25
  • The copied file approach will not work, because the file is changed quite often throughout the day. The Excel file is stored in a shared folder; and yes, it is just one "master" file. The access issue is a problematic one - anyone can open the file by double-clicking, but it is read-only by default. Then, through the program, admins get full access to the file. But it stays like that until a non-admin user uses the app, so meantime someone would be able to make changes. – Hurdler Apr 24 '12 at 17:27
  • If changes are only made though the app then why not keep a master copy (edited via the app) and then every time an admin makes a change, save a "read-only" copy for non-admin users to open? You're going to have to make a change somwhere in how you've set this up. – Tim Williams Apr 24 '12 at 23:59

2 Answers2

1

If you're willing to pay for commercial software, the latest version of ExtenXLS has full read and write support for all the encryption formats supported by Excel. Just construct an EncryptedWorkBookHandle instead of the normal WorkBookHandle. That will use the strongest possible cipher supported by an unmodified JRE, RC4 for XLS and 128-bit AES for XLSX. If you want to use 256-bit AES with OOXML and you've installed the JCE unlimited policy you can do so with the MSOfficeEncrypter class.

JExcelAPI, a popular open-source Java spreadsheet API, does not appear to support encryption at all. Aspose.Cells, a commercial offering, supports stong encryption. The documentation for Actuate's e.Spreadsheet seems to have disappeared from the 'net, so I can't tell whether it supports encryption or not.

Since none of the freely available Java spreadsheet APIs seems to support writing encrypted spreadsheets, if you're not willing to use commercial software you'll need to come up with a workaround. You could, for example, write the spreadsheet into an encrypted ZIP file. java.util.zip doesn't support encryption, but it looks like Zip4j does.

vikiiii
  • 9,246
  • 9
  • 49
  • 68
0

Here's the thing since you told that users can simply edit it when and administrator removes protection there seem to be no standard alternative. However if this file is in use then no one else can modify it outside right? So simply just protect it again when administer exit the excel file, and remove protection when an administrator logs in. Like Tim said. You can create separate functions to edit the excel file and then do

disable protection write modifications enable protection

in all those functions. If this is feasible you can use something like AspectJ to simplify the disable protection and enable protection parts;

Thihara
  • 7,031
  • 2
  • 29
  • 56