Is it possible to lock excel sheet leaving group outline feature unlocked in apache poi?

user_4110320 0
Viewed 90

I have created the below sample using apache poi

enter image description here

using the following code

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

public class Main {
    public static final String file = "C:\\Users\\Deepak\\IdeaProjects\\Test\\src\\main\\resources\\test.xlsx";

    public static void main(String[] args) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        for(int row = 0;row < 10;row++) {
            Row rowI = sheet.createRow(row);
            Cell cell = rowI.createCell(0);
            cell.setCellValue("cell"+row+'0');
        }
        sheet.groupRow(1, 5);
        sheet.setRowGroupCollapsed(1, true);
        sheet.protectSheet("dummy");
        workbook.write(new FileOutputStream(new File(file)));
    }
}

The sheet is locked but the collapse(+) feature is also locked. I don't want the collapse button to be locked. Is there a way to solve this problem?

I googled this but didn't find anything for apache POI. I found a solution for VBA here and tried to find a similar method (enableOutlining) in apache poi but didn't find any.

I am using Apache POI 3.9

Any help/guidance will be appreciated

apache-poi java
1 Answers

No, this is not possible. There is no setting to enable outlining while sheet is protected. At least not one which is stored in the file.

There is Worksheet.EnableOutlining. But this only can be set if sheet protection is set UserInterfaceOnly:=True. And that means that the protection is active only while the workbook is opened in Excel's GUI.

See http://www.spreadsheet1.com/outlining.html. And also Excel: EnableOutlining seems to default to False on opening workbook.

Conclusion: Only possible if the workbook contains VBA and is able to run macros and runs that VBA protection code while Workbook_Open().

user_39154310
Related