The issue

I am trying to create and populate an Excel sheet while locking specific columns and adding autofilter to the header rows.

The application currently generates the Excel sheet with the correct values and when using LibreOffice autofilter works, and the two columns I've chosen to be unlocked are unlocked. When using Microsoft Excel 365, autofilter is shown but not clickable.

Earlier I encountered an issue where, if I were using the column styling sheet.setDefaultColumnStyle(2, unlockedCellStyle) to unlock a column, it would only work when using LibreOffice, and not when using Microsoft Excel 365. I later resolved this by unlocking each cell, instead of the entire column.

I'm now facing a similar issue where I want to apply an autofilter to the header row. The current code applies an autofilter correctly when using LibreOffice, but the cells are locked when using Excel 365, and the filter is therefore not clickable.

Freezing the header row, and setting custom column width works in both LibreOffice and Excel 365.

What I have tried

• I have tried protecting the sheet using sheet.protectSheet("x"); and sheet.enableLocking(); both not solving the issue.
• Using XSSSF instead of the faster version SXSSF, with no difference.
• Removing the protection from the sheet fixes the autofilter issue, but naturally does not lock cells.
• Using the online version of Excel 365, as well as the desktop application. Produces the same result.
• Unlocking the header row cell, where the filter is applied. The cell becomes editable, but I am still not able to click the filter button.
• Unlocking the header row cell, and manually trying to apply autofilter using Excel 365. The option to filter is greyed out.

The code

   public class WriteDatabaseToExcelSheet {

    private final HostIDService hostIDService;

    public Workbook databaseEntriesToExcelSheet() {
        SXSSFWorkbook workbook = new SXSSFWorkbook();

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

        SXSSFSheet sheet = workbook.createSheet("HostIDs");

        List<HostID> allFromDatabase = hostIDService.findAll().stream().sorted(
                Comparator.comparingInt(HostID::getProductMarket)
                        .thenComparing(HostID::getPropertyID)).collect(Collectors.toList());

        SXSSFRow headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Product Market");
        headerRow.createCell(1).setCellValue("Property ID");
        headerRow.createCell(2).setCellValue("Host ID");
        headerRow.createCell(3).setCellValue("Change Date");
        headerRow.createCell(4).setCellValue("Comments");

        AtomicInteger i = new AtomicInteger(1);
        AtomicReference<SXSSFCell> cell = new AtomicReference<>();
        AtomicReference<SXSSFRow> row = new AtomicReference<>();
        allFromDatabase.forEach(hostID -> {
            row.set(sheet.createRow(i.getAndIncrement()));
            row.get().createCell(0).setCellValue(hostID.getProductMarket());
            row.get().createCell(1).setCellValue(hostID.getPropertyID());
            cell.set(row.get().createCell(2));
            cell.get().setCellValue(hostID.getHostId());
            cell.get().setCellStyle(unlockedCellStyle);
            if (hostID.getChangeDate() == null) {
                row.get().createCell(3).setCellValue(" ");
            } else {
                row.get().createCell(3).setCellValue(hostID.getChangeDate().toString());
            }
            cell.set(row.get().createCell(4));
            cell.get().setCellValue(hostID.getCommentsByUser());
            cell.get().setCellStyle(unlockedCellStyle);
        });

        sheet.protectSheet("password");
        sheet.setAutoFilter(new CellRangeAddress(0,0,0,3));
        sheet.setColumnWidth(3, 6800);
        sheet.setColumnWidth(2, 3500);
        sheet.createFreezePane(0, 1);

        return workbook;
    }
   }