Java 导出excel,大量数据下拉
在做excel下拉时,当下拉数据少时,直接定义字符数据,进行操作下拉数据。但是当需要下拉的数据达到一定量的时候,会发现下拉的数据为空。那么就需要我们换一种方式进行操作了。
使用依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
导出excel基本技能
1、创建excel
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("sheetName");
Row row = sheet.createRow(0);
row.createCell(0).setCellValue("cell内容");
CellRangeAddress rangeAddress = new CellRangeAddress(开始行(integer), 结束行(integer), 开始列(integer), 结束列(integer));
sheet.addMergedRegionUnsafe(rangeAddress);
2、创建下拉组件
DataValidationHelper helper = sheet.getDataValidationHelper();
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(开始行, 结束行, 开始列, 结束列);
DataValidationConstraint explicitListConstraint = helper.createExplicitListConstraint(strArray);
DataValidation validation = helper.createValidation(explicitListConstraint,cellRangeAddressList);
if (validation instanceof XSSFDataValidation) {
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
} else {
validation.setSuppressDropDownArrow(false);
}
validation.setShowErrorBox(true);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
sheet.addValidationData(validation);
大批量数据下拉
大批量数据下拉,将下拉数据放在字符串数组中进行下拉,会发现下拉数据为空;我们采用将数据放入另一个sheet页,然后进行配置有效性进行下拉数
Sheet hideSheet = wb.createSheet("hideSheet");
wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
for (int i = 0; i < list.size() ; i++) {
Row row = hideSheet.createRow(i);
row.createCell(0).setCellValue(list.get(i));
}
DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint formulaListConstraint1 = dataValidationHelper.createFormulaListConstraint(("hideSheet!$A$1:$A + "$" + list.size()));
CellRangeAddressList regions = new CellRangeAddressList(a, b, StartCell, endCell);
DataValidation validation = dataValidationHelper.createValidation(formulaListConstraint1, regions);
validation.setShowPromptBox(true);
validation.setEmptyCellAllowed(false);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
sheet.addValidationData(validation);