Making excel sheet read only

问题:

I want read only excel sheet after creating it using Apache POI HSSF. How can I do that?

回答1:

A detailed description can be found here: http://systeminetwork.com/article/locking-cells-hssf

Basically you have to assign your cells a custom CellStyle with CellStyle.setLocked(true)

Edited

Hi Gaurav, here is the complete and working code:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("sheet1");
/* password required for locks to become effective */
sheet.protectSheet("secretPassword");

/* cell style for locking */
CellStyle lockedCellStyle = workbook.createCellStyle();
lockedCellStyle.setLocked(true);
/* cell style for editable cells */
CellStyle unlockedCellStyle = workbook.createCellStyle();
unlockedCellStyle.setLocked(false);

/* cell which will be locked */
Cell lockedCell = sheet.createRow(0).createCell(0);
lockedCell.setCellValue("Hi, I'm locked...");
lockedCell.setCellStyle(lockedCellStyle);

/* unlocked cell */
Cell unlockedCell = sheet.createRow(1).createCell(0);
unlockedCell.setCellValue("Just edit me...");
unlockedCell.setCellStyle(unlockedCellStyle);

OutputStream out = new FileOutputStream("sample.xls");
workbook.write(out);
out.flush();
out.close();

回答2:

Here is some tested code that works in making the specific cell readonly. Based on your comment in @Thomas Weber's answer.

This sets an initial value in a cell, then it uses a data constraint to ensure that fixed value cannot be modified by the user in Excel. Try it out.

HSSFWorkbook      workBook = new HSSFWorkbook ();
HSSFSheet         sheet1    = workBook.createSheet();

HSSFRow row1 = sheet1.createRow(10); 
HSSFCell cell1 = row1.createCell(0);
cell1.setCellValue("text: The new line which should be locked"); // SETTING INITIAL VALUE

HSSFCell displayNameCell = cell1;

String[] displayNameList = new String[]{"text: The new line which should be locked"}; //ADDING SAME VALUE INTO A STRING ARRAY AS THE RESTRICTED VALUE 

DVConstraint displayNameConstraint = DVConstraint.createExplicitListConstraint(displayNameList);

CellRangeAddressList displayNameCellRange = new CellRangeAddressList(displayNameCell.getRowIndex(),displayNameCell.getRowIndex(),displayNameCell.getColumnIndex(),displayNameCell.getColumnIndex());

HSSFDataValidation displayNameValidation = new HSSFDataValidation(displayNameCellRange,displayNameConstraint);

displayNameValidation.createErrorBox("Not Applicable","Cannot change the value");

displayNameValidation.setSuppressDropDownArrow(true);
displayNameCell.getSheet().addValidationData(displayNameValidation);





  // Write the output to a file
     FileOutputStream fileOut1 = new FileOutputStream("D:\\book.xls");
     workBook.write(fileOut1);
     fileOut1.close();      

This code is based on this thread http://osdir.com/ml/user-poi.apache.org/2009-07/msg00056.html

回答3:

new File("/path/to/file.xls").setReadOnly();