使用Apache POI在受保护的工作表中启用过滤和排序 [英] Enable Filtering and sorting in a protected sheet using Apache POI

问题描述

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

到目前为止，Google搜索使我失败了.任何帮助将不胜感激.

解决方案

如果它是XSSFSheet，则常见问题解答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();

 }

}

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.

解决方案

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

 }

}

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