Enable Filtering and sorting in a protected sheet

问题:

I have created a workbook with protected sheets because I only need a very few columns as editable. Although I want to let the user to be able to sort and filter columns.

Google searches have failed me as of yet. Any kind of help will be appreciated.

回答1:

If it is a XSSFSheet, then XSSFSheet.lockAutoFilter(false) and XSSFSheet.lockSort(false) will set the properties for enabling auto-filtering and sorting in protected sheets.

Of course the auto-filter itself must be set before protecting the sheet. The setting lockAutoFilter(false) does only enabling the usage the auto-filter in protected sheets.

And for using sorting there must be set a range which is enabled for users to edit. This is because while sorting the cell values will be changed since contents of rows and so of all cells in that rows probably must be exchanged while sorting.

In Excel GUI this is made via Review tab -> Allow Users to Edit Ranges. in apache poi we have to add a CTProtectedRange to the CTWorksheet.

Note the usage of CTProtectedRange needs the full jar of all of the schemas ooxml-schemas-1.3.jar as mentioned in faq-N10025.

Complete example:

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTProtectedRange;

import java.util.Arrays;

public class CreateExcelXSSFProtectedSheetAllowFilteringAndSorting {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet();
  Row row;
  Cell cell;

  row = sheet.createRow(0);
  for (int c = 0 ; c < 4; c++) {
   cell = row.createCell(c);
   cell.setCellValue("Field " + (c+1));
  }

  for (int r = 1; r < 10; r++) {
   row = sheet.createRow(r);
   for (int c = 0 ; c < 4; c++) {
    cell = row.createCell(c);
    cell.setCellValue("Data R" + (r+1) + "C" + (c+1));
   }
  }

  sheet.setAutoFilter(CellRangeAddress.valueOf("A1:D10"));
  ((XSSFSheet)sheet).lockAutoFilter(false);

  CTProtectedRange protectedRange = ((XSSFSheet)sheet).getCTWorksheet()
   .addNewProtectedRanges()
   .addNewProtectedRange();
  protectedRange.setName("enableSorting");
  protectedRange.setSqref(Arrays.asList(new String[]{"A1:D10"}));

  ((XSSFSheet)sheet).lockSort(false);

  sheet.protectSheet(""); 

  for (int c = 0 ; c < 4; c++) {
   sheet.autoSizeColumn(c);
  }

  FileOutputStream out = new FileOutputStream("CreateExcelXSSFProtectedSheetAllowFilteringAndSorting.xlsx");
  workbook.write(out);
  out.close();
  workbook.close();

 }

}