You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by kevintong <to...@hotmail.com> on 2009/07/03 04:14:40 UTC
Protect workbook for structure
I have a JAVA program which uses POI library to generate a xls file, and I
want to prevent users to add, delete or copy the worksheets. I try to use
the writeProtectWorkbook function in HSSFWorkbook class, but it doesn't
work. Is there any other way to do so?
Please advice, thank you very much!
Kevin
--
View this message in context: http://www.nabble.com/Protect-workbook-for-structure-tp24316602p24316602.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Protect workbook for structure
Posted by MSB <ma...@tiscali.co.uk>.
You could try calling the HSSFWorkbook.writeProtectWorkbook(String, String)
method but I cannot guarantee that will work.
Glad you managed to make progress because I believe that it is not possible
to access all of the finer grained protection options from the API. Here I
am referring to preventing users from selecting locked cells, or invoking
the Protect Structure and Windows option. This morning, I have been playing
around with this test code;
File file = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
String cellContents = null;
HSSFCellStyle lockedNumericStyle = null;
HSSFCellStyle lockedTextStyle = null;
Random randomNumberGen = null;
try {
// Random number generator for the numeric cell
randomNumberGen = new Random(System.currentTimeMillis());
workbook = new HSSFWorkbook();
// Cell styles. Note the setLocked(true) method call.
lockedNumericStyle = workbook.createCellStyle();
lockedNumericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
lockedNumericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(
"(#,##0.00_);[Red](#,##0.00)"));
lockedNumericStyle.setLocked(true);
lockedTextStyle = workbook.createCellStyle();
lockedTextStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
lockedTextStyle.setLocked(true);
// Create a sheet and populate a few cells with data.
// Note how the locked cell styles are applied to the
// cells.
sheet = workbook.createSheet("Protection Test");
for(int i = 0; i < 10; i++) {
row = sheet.createRow(i);
cell = row.createCell(0);
cellContents = ("First Cell in row " + (i + 1));
cell.setCellValue(cellContents);
cell.setCellStyle(lockedTextStyle);
cell = row.createCell(1);
cell.setCellValue(randomNumberGen.nextDouble() * 1000);
cell.setCellStyle(lockedNumericStyle);
}
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
// Protect the sheet - prevent anyone from editing the locked cells.
sheet.protectSheet("password");
// Commented out as I am not too sure what effect this
// has.
//workbook.writeProtectWorkbook("Password", "Mark B");
file = new File(filename);
fos = new FileOutputStream(file);
workbook.write(fos);
}
which did allow me to populate the cells with data and prevent users from
modifying them but it did not protect the structure of the workbook. As you
will also see, I have called the writeProtectWorkbook() method in this piece
of code but not had the time to determine just what it did.
Yours
Mark B
kevintong wrote:
>
> Hello Mark B,
>
> What I'm trying to do now is protect the worksheet, disable all the user
> function(even selecting cell). And then protect the workbook for
> structure.
>
> I'm now using a empty excel file for templete, both worksheet and workbook
> are protected with out password. I use my JAVA programe to read that
> templete and save as another file after input all the data in it.
> I use "protectSheet()" in HSSFSheet class to set the password for the
> worksheet. However, I cannot set the password for the workbook, is there
> any function to do so?? Or is there any function to protect the workbook
> for structure??
>
> Thanks
>
> kevin
>
>
> MSB wrote:
>>
>> Hello,
>>
>> Is it possible to do this using Excel itself? The reason I ask is that I
>> am not sure you could prevent anyone from copying information from a
>> sheet but I could be wrong.
>>
>> The first thing I do if I am unsure about how to accomplish anything is
>> to try and do it using Excel. That often indicates whether the setting I
>> am after applies to the workbook, a sheet, etc. Also, it helps to point
>> me in the direction of the setting I am after. If I have the time, and I
>> cannot promise anything as I am working on another project for a list
>> member currently, then I will take a look.
>>
>> Yours
>>
>> Mark B
>>
>>
>> kevintong wrote:
>>>
>>> I have a JAVA program which uses POI library to generate a xls file, and
>>> I want to prevent users to add, delete or copy the worksheets. I try to
>>> use the writeProtectWorkbook function in HSSFWorkbook class, but it
>>> doesn't work. Is there any other way to do so?
>>> Please advice, thank you very much!
>>>
>>> Kevin
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Protect-workbook-for-structure-tp24316602p24322511.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Protect workbook for structure
Posted by MSB <ma...@tiscali.co.uk>.
This probably will not matter but I did find a 'hole' in the protection. If
the user turns off the worksheet protection, selects a cell by clicking on
it and then turns the protection on again they can copy and paste the
contents of the cell they clicked on. Of course, they will not be able to
paste onto the same sheet but I managed to copy data between applications -
Excel and Wordpad - quite by accident in this manner. It is likely that will
not affect your application as the users may not be able to turn the
protection off but I found it - sadly - quite interesting.
kevintong wrote:
>
> Hello Mark B,
>
> What I'm trying to do now is protect the worksheet, disable all the user
> function(even selecting cell). And then protect the workbook for
> structure.
>
> I'm now using a empty excel file for templete, both worksheet and workbook
> are protected with out password. I use my JAVA programe to read that
> templete and save as another file after input all the data in it.
> I use "protectSheet()" in HSSFSheet class to set the password for the
> worksheet. However, I cannot set the password for the workbook, is there
> any function to do so?? Or is there any function to protect the workbook
> for structure??
>
> Thanks
>
> kevin
>
>
> MSB wrote:
>>
>> Hello,
>>
>> Is it possible to do this using Excel itself? The reason I ask is that I
>> am not sure you could prevent anyone from copying information from a
>> sheet but I could be wrong.
>>
>> The first thing I do if I am unsure about how to accomplish anything is
>> to try and do it using Excel. That often indicates whether the setting I
>> am after applies to the workbook, a sheet, etc. Also, it helps to point
>> me in the direction of the setting I am after. If I have the time, and I
>> cannot promise anything as I am working on another project for a list
>> member currently, then I will take a look.
>>
>> Yours
>>
>> Mark B
>>
>>
>> kevintong wrote:
>>>
>>> I have a JAVA program which uses POI library to generate a xls file, and
>>> I want to prevent users to add, delete or copy the worksheets. I try to
>>> use the writeProtectWorkbook function in HSSFWorkbook class, but it
>>> doesn't work. Is there any other way to do so?
>>> Please advice, thank you very much!
>>>
>>> Kevin
>>>
>>>
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Protect-workbook-for-structure-tp24316602p24322619.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Protect workbook for structure
Posted by kevintong <to...@hotmail.com>.
Hello Mark B,
What I'm trying to do now is protect the worksheet, disable all the user
function(even selecting cell). And then protect the workbook for structure.
I'm now using a empty excel file for templete, both worksheet and workbook
are protected with out password. I use my JAVA programe to read that
templete and save as another file after input all the data in it.
I use "protectSheet()" in HSSFSheet class to set the password for the
worksheet. However, I cannot set the password for the workbook, is there any
function to do so?? Or is there any function to protect the workbook for
structure??
Thanks
kevin
MSB wrote:
>
> Hello,
>
> Is it possible to do this using Excel itself? The reason I ask is that I
> am not sure you could prevent anyone from copying information from a sheet
> but I could be wrong.
>
> The first thing I do if I am unsure about how to accomplish anything is to
> try and do it using Excel. That often indicates whether the setting I am
> after applies to the workbook, a sheet, etc. Also, it helps to point me in
> the direction of the setting I am after. If I have the time, and I cannot
> promise anything as I am working on another project for a list member
> currently, then I will take a look.
>
> Yours
>
> Mark B
>
>
> kevintong wrote:
>>
>> I have a JAVA program which uses POI library to generate a xls file, and
>> I want to prevent users to add, delete or copy the worksheets. I try to
>> use the writeProtectWorkbook function in HSSFWorkbook class, but it
>> doesn't work. Is there any other way to do so?
>> Please advice, thank you very much!
>>
>> Kevin
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Protect-workbook-for-structure-tp24316602p24319489.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Protect workbook for structure
Posted by MSB <ma...@tiscali.co.uk>.
Hello,
Is it possible to do this using Excel itself? The reason I ask is that I am
not sure you could prevent anyone from copying information from a sheet but
I could be wrong.
The first thing I do if I am unsure about how to accomplish anything is to
try and do it using Excel. That often indicates whether the setting I am
after applies to the workbook, a sheet, etc. Also, it helps to point me in
the direction of the setting I am after. If I have the time, and I cannot
promise anything as I am working on another project for a list member
currently, then I will take a look.
Yours
Mark B
kevintong wrote:
>
> I have a JAVA program which uses POI library to generate a xls file, and I
> want to prevent users to add, delete or copy the worksheets. I try to use
> the writeProtectWorkbook function in HSSFWorkbook class, but it doesn't
> work. Is there any other way to do so?
> Please advice, thank you very much!
>
> Kevin
>
>
--
View this message in context: http://www.nabble.com/Protect-workbook-for-structure-tp24316602p24319095.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org