Lock single column in Excel using Apache POI

问题:

I want to create a Excel in which only a specific column is locked(Read-only), and the rest are editable,

I am using the following approach, but that doesn't seem to work.

Create two CellStyles, one with setLocked(true) and other with setLocked(false).

Then apply the locked style for all the cells in the column which needs to be locked, and the unlocked style for all the other cells.

Protect the sheet using sheet.protectSheet("");

But when I open the created Excel in open office, I notice that all the cells are locked!

None of them are editable.

How can I achieve the above requirement?

P.S : I cant use the data validation approach.

回答1:

If you do the opposite it works. Protect the whole sheet and call setLocked(false) for the cells which should be editable.

String file = "c:\\poitest.xlsx";
FileOutputStream outputStream = new FileOutputStream(file);
Workbook wb = new XSSFWorkbook();

CellStyle unlockedCellStyle = wb.createCellStyle();
unlockedCellStyle.setLocked(false);

Sheet sheet = wb.createSheet();
sheet.protectSheet("password");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("TEST");
cell.setCellStyle(unlockedCellStyle);

wb.write(outputStream);
outputStream.close();