No, there is no way to say - in effect - "give me all of the protected
cells". What you will have to do is open the workbook, get a worksheet from
that book, iterate through all of the cells in the workbook and for each
cell, get the cell style object associated with the cell (assuming that
there is one) and then check to see if that cell style has the locked
attribute set. As far as I know, protection is actually a function of the
workbook, not the cell. When you protect the workbook, those cells that are
locked cannot be changed by the user and that is why I chose to focus on
this attribute. Of course, I could be wrong and if you are not satisfied
with my response - as it seems to me you are not from your reply - then
simply wait for another list memeber to post another suggestion.
Having said that, here is some code to get you started. I have no idea what
type of workbook you are targeting - the binary .xls or OpenXML .xlsx file -
and so the example code below will target the so called SS stream as that
does not 'care' which type you are working with.
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
public class TestProtection {
public static void main(String[] args) {
File file = null;
FileInputStream fis = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
CellStyle cellStyle = null;
Iterator<Row> rowIter = null;
Iterator<Cell> cellIter = null;
int numSheets = 0;
try {
// Open the workbook. This code will work equally well for the
older
// binary and newer OpenXML format files.
file = new File(".......... your file name here
.................");
fis = new FileInputStream(file);
workbook = WorkbookFactory.create(fis);
// Find out how many sheets there are and step through each.
numSheets = workbook.getNumberOfSheets();
for(int i = 0; i < numSheets; i++) {
sheet = workbook.getSheetAt(i);
// Get an Iterator to step through the rows on the worksheet.
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
row = rowIter.next();
// Then an Iterator to step through the cells on the row.
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
// Get the cell and it's cell style.
cell = cellIter.next();
cellStyle = cell.getCellStyle();
// Make sure we have a cell style object then 'ask'
it if the
// cell is locked. Protection is a function of the
workbook not
// the cell but only cells that ARE locked will be
protected.
if(cellStyle != null) {
// If the cell is locked, just print a message
identifying the cells
// row and column numbers.
if(cellStyle.getLocked()) {
System.out.println("The cell at column " +
cell.getColumnIndex() +
" row " +
cell.getRowIndex() +
" is locked. Therefore, if the workbook
is protected so will the cell be.");
}
}
}
}
}
}
catch(Exception ex) {
System.out.println("Caught: " + ex.getClass().getName());
System.out.println("Message: " + ex.getMessage());
System.out.println("Stacktreace follows:................");
ex.printstacktrace(System.out);
}
finally {
if(fis != null) {
try {
fis.close();
fis = null
}
catch(IOException ioeEx) {
// NOTHING TO DO NOW
}
}
}
}
}
That should get you started. If you need to know anything else, take a look
at the how to (http://poi.apache.org/spreadsheet/how-to.html) and/or at the
quick guide (http://poi.apache.org/spreadsheet/quick-guide.html) both
contain lots of examples that can help you work with the cell once you have
the reference to it.
Yours
Mark B
Santhosh.R wrote:
>
> Dear MSB
>
> I want the complete picture i need not want to compare each cell whether
> it has been protected or not is there any other way to get all the
> protected cells values at once
> if not give me the some more idea to over come this
>
>
>
> MSB wrote:
>>
>> Just about to walkout of the office to start work so I cannot test this
>> hypothesis myself. However, I believe that I am correct when I say that
>> for a cell to be protected, it must be locked. So, all you need to do is
>> get the cell, get the cell style from the cell and call the getLocked()
>> method of the cell style.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Santhosh.R wrote:
>>>
>>> Dear All
>>> How to get only the protected cells data value using HSSF
>>>
>>
>>
>
>
--
View this message in context:
http://old.nabble.com/How-to-get-Protected-cells-value-using-HSSF-tp26474374p26480222.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
