CODEWITHSUNDEEP
javaapache-poi
Katrin Krabbes
Katrin Krabbes

Reputation: 3

Locking some Excel Cells/Rows with POI others editable

I have to write an excel File where some rows and Columns are locked and the rest is editable. I know this was asked and answered beofre, but the latest answer is from 2012 and the solution given there doesn't work anymore. Can anyone who worked with this give a solution that works now?

This is the code that was given as solution

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();

The effect now is a sheet that is locked completely.

Upvotes: 0

Views: 4467

Answers (1)

kiwiwings
kiwiwings

Reputation: 3446

You wrote, you'd like to lock certain cells and the default should be unlocked, but your code actually unlocks a given cell.

So I go for your original request and have kind of a quick hack as I haven't found a decent method on a quick view to set a whole range of columns:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol;

public class XSSFLockTest {
    public static void main(String args[]) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();

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

        CellStyle lockedCellStyle = wb.createCellStyle();
        lockedCellStyle.setLocked(true);

        XSSFSheet sheet = wb.createSheet();
        CTCol col = sheet.getCTWorksheet().getColsArray(0).addNewCol();
        col.setMin(1);
        col.setMax(16384);
        col.setWidth(9.15);
        col.setStyle(unlockedCellStyle.getIndex());

        sheet.protectSheet("password");

        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("TEST");
        cell.setCellStyle(lockedCellStyle);

        FileOutputStream outputStream = new FileOutputStream("bla.xlsx");
        wb.write(outputStream);
        outputStream.close();
    }
}

Upvotes: 4

Related Questions