Hello Daniel, I think you are perfectly correct to create a single sheet and then clone it if you reqauire additional sheets in the workbook. That approach has been used with templates before when we were faced with a different protection issue and it did seem to work perfectly well.
After I sent my original email, I did a little further testing and an interesting wrinkle raised it's ugly head - if you will forgive me mixing metaphors madly. With the intorduction of Excel 2007, it was possible to exercise mush finer levels of control over what you could protect. I noticed that the template I created and used still had some of these restrictive options set; I could not change the formatting on a cell even though it was not locked and Excel did allow me to change the value the cells contained. Thinking about it this morning, I do not feel this is a particular problem as it should still be possible to set the protection options when the template is created. All the best and if you have any further problems, just drop a message onto the list. Yours Mark B Daniel Meier-4 wrote: > > 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] > > > -- View this message in context: http://old.nabble.com/Unlock-all-cells-in-a-sheet-tp28192938p28208038.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]
