使用Apache POI在受保护的工作表中启用过滤和排序 [英] Enable Filtering and sorting in a protected sheet using Apache POI
问题描述
我创建了一个带有受保护工作表的工作簿,因为我只需要很少的几列即可编辑.尽管我想让用户能够对列进行排序和过滤.
到目前为止,Google搜索使我失败了.任何帮助将不胜感激.
如果它是XSSFSheet
,则 XSSFSheet.lockSort(false)将设置属性,以在受保护的工作表中启用自动过滤和排序功能.>
当然,在保护纸张之前,必须先设置自动过滤器本身.设置lockAutoFilter(false)
仅在受保护的工作表中启用用法自动过滤器.
为使用排序,必须设置一个允许用户编辑的范围.这是因为在排序时,单元格的值将被更改,因为排序时可能必须交换行的内容以及该行中所有单元格的内容.
在Excel
GUI中,这是通过Review tab
-> Allow Users to Edit Ranges
进行的.在apache poi
中,我们必须添加 CTWorksheet .
请注意,如常见问题解答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屋!