使用 Apache POI 在受保护的工作表中启用过滤和排序

问题描述

我创建了一个带有受保护工作表的工作簿，因为我只需要很少的可编辑列.虽然我想让用户能够对列进行排序和过滤.

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 搜索还没有让我失望.任何形式的帮助将不胜感激.

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

推荐答案

如果是XSSFSheet，则XSSFSheet.lockAutoFilter(false) 和 XSSFSheet.lockSort(false) 将设置启用自动过滤和排序的属性受保护的工作表.

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.

当然，在保护工作表之前必须设置自动过滤器本身.设置 lockAutoFilter(false) 仅启用使用受保护工作表中的自动过滤器.

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.

在 Excel GUI 中，这是通过 Review tab -> Allow Users to Edit Ranges 实现的.在 apache poi 我们必须添加一个 CTProtectedRange 到 CTWorksheet.

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.

注意 CTProtectedRange 的使用需要 ooxml-schemas-1.3.jar 中提到的所有模式的完整 jar 包，如 faq-N10025.

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

完整示例:

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

 }

}

这篇关于使用 Apache POI 在受保护的工作表中启用过滤和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！