You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by James Fedor <jf...@axian.com> on 2010/01/27 20:42:15 UTC
POI 3.6 XSSFSheet - how can I lock specific user edit actions on a
given sheet?
I'm new to this group, so I apologize if this question has already been answered. Thanks in advance for any assistance.
Question:
I'm using the POI 3.6-XSSF API<http://poi.apache.org/spreadsheet/index.html> to write Excel 2007 documents, and i'm having trouble getting specific user edit actions of a given sheet to lock (i.e. lockDeleteRows). The API says that<http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#lockDeleteColumns%28%29> you need to call enableLocking() on the sheet but when I do that it locks everything and ignores my specific locking settings (example below). When I don't call it nothing is locked.
It seems this should work given the fact that specific methods are provided to give one this granular locking control at a sheet level. So how can I lock specific user edit actions of a workbook/worksheet using this API?
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
XSSFCell cell;
XSSFRow row = sheet.createRow(0);
cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("Sku");
cell = row.createCell(1, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("Category");
cell = row.createCell(2, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("SubCategory");
cell = row.createCell(3, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("Name");
cell = row.createCell(4, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("DATE_ADDED");
cell = row.createCell(5, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("SEO_DESCRIPTION");
row = sheet.createRow(1);
cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("4bd2c24571534e098589");
cell = row.createCell(1, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("CLUBS");
cell = row.createCell(2, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("DRIVERS");
cell = row.createCell(3, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("1112222 -- ASFASDF");
cell = row.createCell(4, XSSFCell.CELL_TYPE_STRING); cell.setCellValue(new Date());
cell = row.createCell(5, XSSFCell.CELL_TYPE_STRING); cell.setCellValue("asdfhavsdvf absdfhvbashdv asvdfhavsfasdf");
sheet.lockDeleteColumns();
sheet.lockInsertColumns();
sheet.lockDeleteRows();
sheet.enableLocking(); // why does this call ignore the last 3 lock settings i just set???????????
OutputStream out;
try {
out = new FileOutputStream("c:\\temp\\TestWB.xlsx", false);
wb.write(out);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Re: POI 3.6 XSSFSheet - how can I lock specific user edit actions on a given sheet?
Posted by David Fisher <df...@jmlafferty.com>.
This if off the top of my head, it may not help at all, but do you get
what you want if you use this sequence:
XSSFSheet sheet = wb.createSheet("Sheet1");
sheet.enableLocking();
sheet.lockDeleteColumns();
sheet.lockInsertColumns();
sheet.lockDeleteRows();
XSSFCell cell;
I'm just thinking that order might make a difference.
Good luck.
Regards,
Dave
On Jan 27, 2010, at 11:42 AM, James Fedor wrote:
> I'm new to this group, so I apologize if this question has already
> been answered. Thanks in advance for any assistance.
>
> Question:
>
> I'm using the POI 3.6-XSSF API<http://poi.apache.org/spreadsheet/index.html
> > to write Excel 2007 documents, and i'm having trouble getting
> specific user edit actions of a given sheet to lock (i.e.
> lockDeleteRows). The API says that<http://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#lockDeleteColumns%28%29
> > you need to call enableLocking() on the sheet but when I do that
> it locks everything and ignores my specific locking settings
> (example below). When I don't call it nothing is locked.
>
>
>
> It seems this should work given the fact that specific methods are
> provided to give one this granular locking control at a sheet level.
> So how can I lock specific user edit actions of a workbook/worksheet
> using this API?
>
>
> XSSFWorkbook wb = new XSSFWorkbook();
> XSSFSheet sheet = wb.createSheet("Sheet1");
> XSSFCell cell;
>
> XSSFRow row = sheet.createRow(0);
> cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("Sku");
> cell = row.createCell(1, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("Category");
> cell = row.createCell(2, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("SubCategory");
> cell = row.createCell(3, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("Name");
> cell = row.createCell(4, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("DATE_ADDED");
> cell = row.createCell(5, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("SEO_DESCRIPTION");
>
> row = sheet.createRow(1);
> cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("4bd2c24571534e098589");
> cell = row.createCell(1, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("CLUBS");
> cell = row.createCell(2, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("DRIVERS");
> cell = row.createCell(3, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("1112222 -- ASFASDF");
> cell = row.createCell(4, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue(new Date());
> cell = row.createCell(5, XSSFCell.CELL_TYPE_STRING);
> cell.setCellValue("asdfhavsdvf absdfhvbashdv asvdfhavsfasdf");
>
> sheet.lockDeleteColumns();
> sheet.lockInsertColumns();
> sheet.lockDeleteRows();
>
> sheet.enableLocking(); // why does this call ignore the last 3 lock
> settings i just set???????????
>
> OutputStream out;
> try {
> out = new FileOutputStream("c:\\temp\\TestWB.xlsx", false);
> wb.write(out);
> } catch (Exception e) {
> // TODO Auto-generated catch block
> e.printStackTrace();
> }
>
>
>
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: POI 3.6 XSSFSheet - how can I lock specific user edit actions
on a given sheet?
Posted by MSB <ma...@tiscali.co.uk>.
I must admit that I do not know the answer Jaems but do have a question to
ask. When you open the workbook using Excel, visit the protection options
can you see whether any of the boxes have been checked? I am wondering if
XWPF is completing part of the process and it is necessary to call another
method to actually 'complete' the protection. If you thonk about protecting
a single cell as an example, you apply a format that locks the cell and then
protect the worksheet; I am wondering if the equivalent of 'protecting the
worksheet' is the missing step in this process. Having said that, if XWPF is
performing part of the process, I do not know how to make it complete,
sorry.
Yours
Mark B
James Fedor wrote:
>
> Thanks Dave, I did try that too and it didn't seem to make a difference.
> Any other ideas?
>
> -James
>
>
>
--
View this message in context: http://old.nabble.com/POI-3.6-XSSFSheet---how-can-I-lock-specific-user-edit-actions-on-a-given-sheet--tp27345689p27367118.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: POI 3.6 XSSFSheet - how can I lock specific user edit actions
on a given sheet?
Posted by MSB <ma...@tiscali.co.uk>.
Sorry to hear that James but there are other lockXXX() methods decalred on
the workbook. Have you tried any of those? I do not know if they will work
but it should be worth trying them out and I am sorry to sound so uncertain
but I have bot yet had the opportunity to play around with XSSF in earnest.
Yours
Mark B
PS. One thing you could do is to produce a very simple workbook using POI
and try to lock it as you are doing now. Next, do the same thing using Excel
and have a look at the files to see if you can spot any obvious difference;
the files are simply zipped xml so you can unpack the archive and then use a
simple text editor to have a dig around and anything you discover will nhelp
the developers if you have to log this as a bug.
James Fedor wrote:
>
> Thanks, but Unfortunately that doesn't seem to work either. I still can
> find no examples of how to do this on the web...
>
> -----Original Message-----
> From: MSB [mailto:markbrdsly@tiscali.co.uk]
> Sent: Friday, January 29, 2010 6:57 AM
> To: user@poi.apache.org
> Subject: RE: POI 3.6 XSSFSheet - how can I lock specific user edit actions
> on a given sheet?
>
>
> I think that may be the answer James. Have a look at the lockStructure()
> method defined on the XSSFWorkbook class. I have not tried it but I reckon
> that you need to call this method in addition - and most likely last of
> all
> - to actually 'activate' the lock.
>
> Yours
>
> Mark B
>
>
> James Fedor wrote:
>>
>> Thanks Dave, I did try that too and it didn't seem to make a difference.
>> Any other ideas?
>>
>> -James
>>
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/POI-3.6-XSSFSheet---how-can-I-lock-specific-user-edit-actions-on-a-given-sheet--tp27345689p27373113.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
>
>
>
--
View this message in context: http://old.nabble.com/POI-3.6-XSSFSheet---how-can-I-lock-specific-user-edit-actions-on-a-given-sheet--tp27345689p27511485.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: POI 3.6 XSSFSheet - how can I lock specific user edit actions
on a given sheet?
Posted by James Fedor <jf...@axian.com>.
Thanks, but Unfortunately that doesn't seem to work either. I still can find no examples of how to do this on the web...
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Friday, January 29, 2010 6:57 AM
To: user@poi.apache.org
Subject: RE: POI 3.6 XSSFSheet - how can I lock specific user edit actions on a given sheet?
I think that may be the answer James. Have a look at the lockStructure()
method defined on the XSSFWorkbook class. I have not tried it but I reckon
that you need to call this method in addition - and most likely last of all
- to actually 'activate' the lock.
Yours
Mark B
James Fedor wrote:
>
> Thanks Dave, I did try that too and it didn't seem to make a difference.
> Any other ideas?
>
> -James
>
>
>
--
View this message in context: http://old.nabble.com/POI-3.6-XSSFSheet---how-can-I-lock-specific-user-edit-actions-on-a-given-sheet--tp27345689p27373113.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: POI 3.6 XSSFSheet - how can I lock specific user edit actions
on a given sheet?
Posted by MSB <ma...@tiscali.co.uk>.
I think that may be the answer James. Have a look at the lockStructure()
method defined on the XSSFWorkbook class. I have not tried it but I reckon
that you need to call this method in addition - and most likely last of all
- to actually 'activate' the lock.
Yours
Mark B
James Fedor wrote:
>
> Thanks Dave, I did try that too and it didn't seem to make a difference.
> Any other ideas?
>
> -James
>
>
>
--
View this message in context: http://old.nabble.com/POI-3.6-XSSFSheet---how-can-I-lock-specific-user-edit-actions-on-a-given-sheet--tp27345689p27373113.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