poi模板动态导出，下拉框联动，公式设置

背景：有一个动态导出模板的需求，根据页面维护的数据比如模板名称，模板的sheet名称，列名称宽度高度等，导出excel文件。打破以往把excel上传到代码的resource中，或者文件服务中，再下载出来导入。这样的弊端显而易见不易维护，在做导入的时候必须技术去修改xlsx文件然后发版，非常不方便。至此我们想要动态去识别列以及列中的字段，做一个通用的导出和导入功能。当然导入我已经用设计模式做完了可以参考 通用导入下面就重点说一下导出模板的维护实现。

1. 根据模板编码和sheet编码查询配置信息
2. 生成数据源sheet比如行政区划，设置为隐藏
3. 生成业务sheet其中包含下拉框，以及下拉框联动比如省市区联动公式设置，联动效果展示

添加依赖

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.3</version></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-lang3</artifactId><version>3.4</version></dependency>

整体代码

@Slf4j
@RestController
public class CreateXlsxController {static Map<String,String> colMap;static {colMap = new HashMap<>();colMap.put("0","A");colMap.put("1","B");colMap.put("2","C");colMap.put("3","D");colMap.put("4","E");colMap.put("5","F");colMap.put("6","G");colMap.put("7","H");colMap.put("8","I");colMap.put("9","J");colMap.put("10","K");colMap.put("11","L");colMap.put("12","M");colMap.put("13","N");colMap.put("14","O");colMap.put("15","P");colMap.put("16","Q");colMap.put("17","R");colMap.put("18","S");colMap.put("19","T");colMap.put("20","U");colMap.put("21","V");colMap.put("22","W");colMap.put("23","X");colMap.put("24","Y");colMap.put("25","Z");}@PostMapping("/generate-excel")public ResponseEntity<byte[]> generateExcel(@RequestBody List<TemplateDO> data) throws IOException {Assert.isTrue(data.size()!=0, "data is empty");Map<String,String> proviceCityCountryMap = new HashMap<>();Workbook workbook = new XSSFWorkbook();int fillRow = 1000;//生成省市区的基础数据String hiddenSheetName = "area";createArea(workbook,hiddenSheetName);for (TemplateDO templateDO : data) {// Create a workbook and sheetfor (TemplateSheet templateSheet : templateDO.getSheetList()) {Sheet sheet = workbook.createSheet(templateSheet.getSheetName());//在第一行创建Row row1 = sheet.createRow(0);row1.setHeight((short) templateSheet.getTitleHeight());//标明是第一行Cell titleCell = row1.createCell(0);//创建了一个新的单元格样式对象CellStyle titleStyle = workbook.createCellStyle();// 设置文本左对齐titleStyle.setAlignment(HorizontalAlignment.LEFT);// 垂直居中titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);titleStyle.setWrapText(true);titleCell.setCellStyle(titleStyle);sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, templateSheet.getTitleWidth()));titleCell.setCellValue(templateSheet.getTitleContext());//下面生成列信息int rowNum = 1;Row row = sheet.createRow(rowNum);int colNum = 0;for (TemplateSheetCol sheetCol : templateSheet.getColumnList()) {sheet.setColumnWidth(colNum,sheetCol.getColumnWidth());Cell cell = row.createCell(colNum);//必输字段颜色展示+*号if(("Y").equalsIgnoreCase(sheetCol.getNullableFlag())){// 创建一个单元格样式并设置背景颜色CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);cell.setCellStyle(style);cell.setCellValue("*"+sheetCol.getColumnName());}else{cell.setCellValue(sheetCol.getColumnName());}if(StringUtils.isNotBlank(sheetCol.getChangeDataFlag()) && "Y".equalsIgnoreCase(sheetCol.getChangeDataFlag()) && StringUtils.isNotBlank(sheetCol.getValidateSet()) ){//配置的值集代码是省侧的设置下拉框if("province_code".equalsIgnoreCase(sheetCol.getValidateSet())){CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.RED.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);cell.setCellStyle(style);int rowIndex = cell.getRowIndex();int columnIndex = cell.getColumnIndex();proviceCityCountryMap.put("province_code",rowIndex+2+","+columnIndex);XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);StringBuffer listFormula = new StringBuffer();listFormula.append("=INDIRECT(").append(hiddenSheetName).append("!$A$1)");XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());//从第3行开始往下填充  第1行是说明  第2行是标题CellRangeAddressList regions = new CellRangeAddressList(2,fillRow, colNum, colNum);XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);data_validation_list.setSuppressDropDownArrow(true);data_validation_list.setShowErrorBox(true);sheet.addValidationData(data_validation_list);}else if("city_code".equalsIgnoreCase(sheetCol.getValidateSet())){CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);cell.setCellStyle(style);int rowIndex = cell.getRowIndex();int columnIndex = cell.getColumnIndex();proviceCityCountryMap.put("city_code",rowIndex+2+","+columnIndex);XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);Integer provinceRow = Integer.parseInt(proviceCityCountryMap.get("province_code").split(",")[0]);String provinceCol = proviceCityCountryMap.get("province_code").split(",")[1];for (int i = 0; i < fillRow; i++) {StringBuffer listFormula = new StringBuffer();listFormula.append("=INDIRECT(").append("$").append(colMap.get(provinceCol)).append("$").append(provinceRow).append(")");XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());CellRangeAddressList regions = new CellRangeAddressList(provinceRow-1,provinceRow-1, colNum, colNum);provinceRow++;XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);data_validation_list.setSuppressDropDownArrow(true);data_validation_list.setShowErrorBox(true);sheet.addValidationData(data_validation_list);}}else if("country_code".equalsIgnoreCase(sheetCol.getValidateSet())){CellStyle style = workbook.createCellStyle();style.setFillForegroundColor(IndexedColors.CORAL.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);cell.setCellStyle(style);int rowIndex = cell.getRowIndex();int columnIndex = cell.getColumnIndex();proviceCityCountryMap.put("country_code",rowIndex+","+columnIndex);XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);Integer cityRow = Integer.parseInt(proviceCityCountryMap.get("city_code").split(",")[0]);String cityCol = proviceCityCountryMap.get("city_code").split(",")[1];for (int i = 0; i < fillRow; i++) {StringBuffer listFormula = new StringBuffer();listFormula.append("=INDIRECT(").append("$").append(colMap.get(cityCol)).append("$").append(cityRow).append(")");XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());CellRangeAddressList regions = new CellRangeAddressList(cityRow-1,cityRow-1, colNum, colNum);cityRow++;XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);data_validation_list.setSuppressDropDownArrow(true);data_validation_list.setShowErrorBox(true);sheet.addValidationData(data_validation_list);}}else if("gender_code".equalsIgnoreCase(sheetCol.getValidateSet())){DataValidationHelper validationHelper = sheet.getDataValidationHelper();//根据validateSet去数据库查DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(new String[]{"M-男","W-女"});CellRangeAddressList addressList = new CellRangeAddressList(rowNum+1, 100000, colNum, colNum);DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);sheet.addValidationData(dataValidation);}}colNum++;}}}// Write the output to a byte arrayByteArrayOutputStream out = new ByteArrayOutputStream();workbook.write(out);workbook.close();// Set response headersHttpHeaders headers = new HttpHeaders();String headerValue = "attachment; filename="+data.get(0).getTemplateName()+".xlsx";headers.add(HttpHeaders.CONTENT_DISPOSITION, headerValue);headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);return ResponseEntity.ok().headers(headers).body(out.toByteArray());}public void createArea(Workbook workbook,String sheetName) throws IOException {// 创建工作簿和工作表String[] provinceArr = {"江苏省","安徽省"};//依次列出各省的市、各市的县String[] cityJiangSu = {"南京市","苏州市","盐城市"};String[] cityAnHui = {"合肥市","安庆市"};//将有子区域的父区域放到一个数组中String[] areaFatherNameArr ={"江苏省","安徽省"};Map<String,String[]> areaMap = new HashMap<>();areaMap.put("江苏省", cityJiangSu);areaMap.put("安徽省",cityAnHui);Map<String,List<String>> countryMap = new HashMap<String,List<String>>(){{put("南京市",Arrays.asList("南京区1","南京区2"));put("苏州市",Arrays.asList("苏州区1","苏州区2"));put("盐城市",Arrays.asList("盐城市区1","盐城市区2"));put("合肥市",Arrays.asList("合肥市区1","合肥市区2"));put("安庆市",Arrays.asList("安庆市区1","安庆市区2"));}};//创建一个专门用来存放地区信息的隐藏sheet页//因此也不能在现实页之前创建，否则无法隐藏。Sheet hideSheet = workbook.createSheet(sheetName);//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);//整个sheet页的colNum 持续递增int colNum = 0;int procinceRowNum = 0;// 设置第一行，存省的信息Row zeroRow = hideSheet.createRow(procinceRowNum++);String procinceTitle = "省列表";zeroRow.createCell(0).setCellValue(procinceTitle);for(int i = 0; i < provinceArr.length; i ++){Row row1 = hideSheet.createRow(procinceRowNum++);Cell cell = row1.createCell(colNum);cell.setCellValue(provinceArr[i]);}// 添加名称管理器String rangeProvice = getRange(colNum, procinceRowNum);Name nameProvice = workbook.createName();//key不可重复,将父区域名作为keynameProvice.setNameName(procinceTitle);String formulaProvice = sheetName +"!" + rangeProvice;nameProvice.setRefersToFormula(formulaProvice);colNum++;for (int i = 0; i < areaFatherNameArr.length; i++) {Cell cell = zeroRow.createCell(colNum);cell.setCellValue(areaFatherNameArr[i]);String[] cityArr = areaMap.get(areaFatherNameArr[i]);int rowNum =1;for (int j = 0; j < cityArr.length; j++) {Row tmpRow = hideSheet.getRow(rowNum);if(tmpRow == null){Row row = hideSheet.createRow(rowNum);Cell cell1 = row.createCell(colNum);cell1.setCellValue(cityArr[j]);}else {Cell cell1 = tmpRow.createCell(colNum);cell1.setCellValue(cityArr[j]);}rowNum++;}// 添加名称管理器String rangeCity = getRange(colNum, rowNum);Name nameCity = workbook.createName();//key不可重复,将父区域名作为keynameCity.setNameName(areaFatherNameArr[i]);String formula = sheetName +"!" + rangeCity;nameCity.setRefersToFormula(formula);colNum++;}for (Map.Entry<String, List<String>> entry : countryMap.entrySet()) {//市区String key = entry.getKey();Cell cell = zeroRow.createCell(colNum);cell.setCellValue(key);List<String> countryList = entry.getValue();int rowNum =1;for (int j = 0; j < countryList.size(); j++) {Row tmpRow = hideSheet.getRow(rowNum);if(tmpRow == null){Row row = hideSheet.createRow(rowNum);Cell cell1 = row.createCell(colNum);cell1.setCellValue(countryList.get(j));}else {Cell cell1 = tmpRow.createCell(colNum);cell1.setCellValue(countryList.get(j));}rowNum++;}// 添加名称管理器String rangeCountry = getRange(colNum, rowNum);Name nameCountry = workbook.createName();//key不可重复,将父区域名作为keynameCountry.setNameName(key);String formula = sheetName +"!" + rangeCountry;nameCountry.setRefersToFormula(formula);colNum++;}}/***  计算formula* @param colNum   代表A B C D E F G ......* @param rowNum* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1** */public String getRange(int colNum, int rowNum) {String abc = colMap.get(String.valueOf(colNum));//默认都是从第二行开始return "$" + abc + "$" + 2 + ":$" + abc + "$" + rowNum;}
}

入参模拟查询配置信息

[{"templateName": "people_base_info","sheetList": [{"sheetName": "sheet1","titleContext": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\njjjjjjjjjjjjjjjjjjjjjjjjjjj\n","titleWidth": 8,"titleHeight": 1000,"columnList": [{"columnName": "序号","columnCode": "number","columnWidth": 2000,"columnHeight": 20,"nullableFlag": "N"},{"columnName": "年龄","columnCode": "age","columnWidth": 5000,"columnHeight": 20,"nullableFlag": "N"},{"columnName": "性别","columnCode": "gender","columnWidth": 5000,"columnHeight": 20,"nullableFlag": "Y","changeDataFlag": "Y","validateSet": "gender_code"},{"columnName": "省份","columnCode": "province","columnWidth": 5000,"columnHeight": 20,"nullableFlag": "Y","changeDataFlag": "Y","validateSet": "province_code"},{"columnName": "城市","columnCode": "city","columnWidth": 5000,"columnHeight": 20,"nullableFlag": "Y","changeDataFlag": "Y","validateSet": "city_code"},{"columnName": "区县","columnCode": "country","columnWidth": 5000,"columnHeight": 20,"nullableFlag": "Y","changeDataFlag": "Y","validateSet": "country_code"}]}]}
]

下面拆解代码，重点代码展示

代码拆解

设置样式

//创建了一个新的单元格样式对象
CellStyle titleStyle = workbook.createCellStyle();
// 设置文本左对齐
titleStyle.setAlignment(HorizontalAlignment.LEFT);
// 垂直居中
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
titleStyle.setWrapText(true);
titleCell.setCellStyle(titleStyle);

合并单元格

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, templateSheet.getTitleWidth()));

设置颜色

// 创建一个单元格样式并设置背景颜色
CellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(style);
cell.setCellValue("*"+sheetCol.getColumnName());

设置INDIRECT公式

INDIRECT嵌套名称管理器可以生成下拉框
下拉框嵌套INDIRECT引用前面的下拉框
参考下面的xlsx文件，查看名称管理器，公式有效性的序列属性
链接: https://pan.baidu.com/s/1zz4q0B3jfeybsSB4MrOMdA?pwd=qt5u 提取码: qt5u 复制这段内容后打开百度网盘手机App，操作更方便哦

XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet);
StringBuffer listFormula = new StringBuffer();
listFormula.append("=INDIRECT(").append(hiddenSheetName).append("!$A$1)");
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(listFormula.toString());
//从第3行开始往下填充  第1行是说明  第2行是标题
CellRangeAddressList regions = new CellRangeAddressList(2,fillRow, colNum, colNum);
XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
sheet.addValidationData(data_validation_list);

隐藏sheet页

//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);

添加名称管理器，给下拉框用

// 添加名称管理器String rangeCity = getRange(colNum, rowNum);Name nameCity = workbook.createName();//key不可重复,将父区域名作为keynameCity.setNameName(areaFatherNameArr[i]);String formula = sheetName +"!" + rangeCity;nameCity.setRefersToFormula(formula);public String getRange(int colNum, int rowNum) {String abc = colMap.get(String.valueOf(colNum));//默认都是从第二行开始return "$" + abc + "$" + 2 + ":$" + abc + "$" + rowNum;
}

判断row是否为空，不为空再创建，否则会覆盖之前的

 Row tmpRow = hideSheet.getRow(rowNum);if(tmpRow == null){Row row = hideSheet.createRow(rowNum);Cell cell1 = row.createCell(colNum);cell1.setCellValue(countryList.get(j));}else {Cell cell1 = tmpRow.createCell(colNum);cell1.setCellValue(countryList.get(j));}