Allowing edit of only few columns in a protected Excel sheet is increasing file size tremendously in Java

Hi,

I need a protected worksheet where only the first column is editable.

I am acheiving this as:

Style style = worksheet.getCells().getColumns().get(0).getStyle();
style.setLocked(false);

StyleFlag styleFlag = new StyleFlag();
styleFlag.setLocked(false);
worksheet.getCells().getColumns().get(0).applyStyle(style, styleFlag);
Range colRange = worksheet.getCells().createRange(“A:A”);
colRange.setStyle(style);
worksheet.protect(ProtectionType.ALL, “password1”, null);

This creates a proected worksheet where I can edit values only in the first column. But it is increasing the file size tremendously.
eg: I have a workbook of size 350kb. On protecting the sheets and allowing edit on only 3 columns, the size is becoming 15MB.

Note - As per my requirement, I cannot fix the no of rows. I need all rows of that column to be editable.

@sandipanghosh111,

Thanks for the sample code segment and details.

I checked your sample code a bit and found your sample code is not right. Why you are applying style to the range (after re-creating it) when you have already applied the style to the first column. So you do not need to use the following lines which is consequently adding styles again to each cell of the range “A1: A1048576” to produce bigger sized output file:

Please try the following sample code as it works fine and the output file is rendered with minimum size:
e.g
Sample code:

Workbook excel = new Workbook("f:\\files\\Book1.xlsx");

				WorksheetCollection worksheets = excel.getWorksheets();
				Worksheet worksheet = worksheets.get(0);

				Protection protection = worksheet.getProtection();

				Style style = worksheet.getCells().getColumns().get(0).getStyle();
				style.setLocked(false);

				StyleFlag styleFlag = new StyleFlag();
				styleFlag.setLocked(true);
				worksheet.getCells().getColumns().get(0).applyStyle(style, styleFlag);

				 
				worksheet.protect(ProtectionType.ALL, "password1", null);
				
				excel.save("f:\\files\\out1.xlsx");

				// Print Message
				System.out.println("Worksheet protected successfully."); 

Hope, this helps a bit.

Thanks. It worked perfectly.

@sandipanghosh111,

Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.