Hey Mark, thx for the hints. I'll try that after the weekend ;-)
I'll create a workbook with one sheet and the settings you suggested. Hopefully I can clone the sheet to create other sheets in the workbook, with the same settings... Cheers daniel -----Ursprüngliche Nachricht----- Von: MSB [mailto:[email protected]] Gesendet: Samstag, 10. April 2010 11:11 An: [email protected] Betreff: Re: Unlock all cells in a sheet Well, that would seem to sort of/kind of work and I would suggest you try something similar to see if it meets your requirement. I did the following; 1. Open Excel and a new blank workbook. 2. Select all of the cells on the sheet - if you look to the upper left hand corner of the sheet, the point at which the column header row and row number column both meet, there is a button and clicking on that will select all of the cell in the sheet. 3. Select Format > Protection and then de-select the Locked checkbox. 4. Save the workbook away and this becomes your template file. I did not use the .xlt extension choosing instead to save the file as a workbook but I have no reason to believe that saving the file as a template would not also work. Next, I put together a very simple bit of code to open that template file, get the sheet then a row and finally to populate a few cells on that row with data. I created an HSSFCellStyle object to use to lock the cells and protected the sheet at the end. When I opened the resulting file with Excel, the only cells I was prevented from editing were the ones I had created and applied the style to. Here is 'my' code, give it a run and see what happens. You will notice I am getting the row and cell and testing for null before creating them if necessary. I do not know if this cautious approach is needed and I adopted it as I assumed that the input file would contain records for every row and cell. Maybe you could simplify the code to test this assumption out. My template file was 26KB in size after removing the locking which is bigger then normal but not huge IMO. Anyway, here is that complex code; public static void main(String[] args) { File inputFile = null; File outputFile = null; FileInputStream fis = null; FileOutputStream fos = null; HSSFWorkbook workbook = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle lockedStyle = null; try { inputFile = new File("C:/temp/Unlocked Protection.xls"); fis = new FileInputStream(inputFile); workbook = new HSSFWorkbook(fis); fis.close(); lockedStyle = workbook.createCellStyle(); lockedStyle.setLocked(true); sheet = workbook.getSheetAt(0); row = sheet.getRow(0); if(row == null) { row = sheet.createRow(0); } for(int i = 0; i < 10; i++) { cell = row.getCell(i); if(cell == null) { cell = row.createCell(i); } cell.setCellValue(i); cell.setCellStyle(lockedStyle); } sheet.protectSheet("password"); outputFile = new File("C:/temp/Protection Test 2.xls"); fos = new FileOutputStream(outputFile); workbook.write(fos); } catch(IOException ioEx) { System.out.println("Caught an: " + ioEx.getClass().getName()); System.out.println("Message: " + ioEx.getMessage()); System.out.println("Stacktrace follows:....."); ioEx.printStackTrace(System.out); } finally { if(fos != null) { try { fos.close(); } catch(IOException ioEx) { // Ignore - too late to recover anything now. } } } } } just stuffed into a main() method for testing. Hope it helps. Yours Mark B Daniel Meier-3 wrote: > > Hi there, > > is there a way to unlock all cells in a sheet. By default every single > cell > is locked. > The problem is, after enabling the sheet protection, you can not delete a > row (even if it is allowed in the worksheet protection settings). > > Because cell styles are set only after createCell() was called, i would > have > to create a sheet and all cells in it with a "unlockedCellStyle". But this > leads to several other problems (ex. 16'000 cols an 100'000 rows). > > I didnt found a way to set a default cell style. Even DefaultColumnStyle > works only for new created cells. > > Does anybody now a solution for this. > thank you > > -- View this message in context: http://old.nabble.com/Unlock-all-cells-in-a-sheet-tp28192938p28200791.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] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
