easyExcel使用记录

  1. 使用createFormulaListConstraint解决下拉栏过多的问题

主要内容是创建一个单独的sheet来保存下拉的值，然后在createFormulaListConstraint方法中指定下拉选项的取值范围

 1 public class TestHandler implements SheetWriteHandler {
 2 
 3 
 4     /**
 5      * 市
 6      */
 7     private List<String> cityNames;
 8    
 9 
10 
11     public TestHandler(){
12 
13     }
14 
15     public TestHandler( List<String> cityNames) {
16         this.cityNames = cityNames;
17     }
18     
19 
20     @Override
21     public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
22 
23     }
24 
25     /**
26      * Excel下拉栏从字典表查询进行动态填充
27      * @param writeWorkbookHolder
28      * @param writeSheetHolder
29      */
30     @Override
31     public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
32         DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
33         Workbook workbook = writeWorkbookHolder.getWorkbook();
34         if(cityNames != null && cityNames.size()>0){
35             Sheet citySheet = workbook.createSheet("citySheet");
36             for (int i = 0; i < cityNames.size(); i++) {
37                 Row row = citySheet.createRow(i);
38                 Cell cell = row.createCell(0);
39                 cell.setCellValue(cityNames.get(i));
40             }
41             CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 100, 10, 10);
42             DataValidationConstraint formulaListConstraint = helper.createFormulaListConstraint("citySheet!$A$1:$A$" + cityNames.size());
43             DataValidation dataValidation = helper.createValidation(formulaListConstraint, cellRangeAddressList);
44             dataValidation.createErrorBox("错误", "请按右侧下拉箭头选择！");
45             dataValidation.setShowErrorBox(true);
46             writeSheetHolder.getSheet().addValidationData(dataValidation);
47         }
48 
49     }
50 }

 

posted @ 2020-06-12 09:25  可是我不能去流浪  阅读(433)  评论(0)    收藏  举报