需求

接了需求，下载excel模版，本来看着还是简单的，然后实现起来一把泪，首先是使用poi，我查了好久，才实现，然后是我用easyexcel又实现了一遍，用了一个周多才实现。

这是需求，第一行是一个多行文本，红色，第二行一个合并单元格题目，第三行是一个多单元格标题，第四行是一个例子，红色。

实现

@Operation(summary = "人员维护模版下载", description = "人员维护模版下载")@PostMapping("persondowntemplate")public void personDownTemplate(HttpServletResponse response) {ExcelWriter writer = ExcelUtil.getWriter(true);//主标题String note = "1.前四行数据，系统不读取，不需要删除\n" +"2.约束区县，请输入约束的部门或者区县或者地市\n" +"3.约束岗位族的值列表，请参考第二个Sheet2，多个岗位族，请使用英文逗号隔开\n" +"4.日期格式：yyyy-mm-dd\n" +"5.下拉数据的值，不允许输入下拉值以外的数据";// 创建总标题行List<String> totalHeader1 = Lists.newArrayList();totalHeader1.add(note);writer.writeHeadRow(totalHeader1); // 写入总标题行，使用默认样式short headerRowHeight = 80 * 20; // 设置行高为30磅Sheet sheet = writer.getSheet();//因为是多行所以要自己控制行高sheet.getRow(0).setHeight(headerRowHeight);// 创建样式，建立每一行的样式CellStyle cellStyle1 = createRedRightAlignedCellStyle(writer.getWorkbook());Row row1 = sheet.getRow(0);Cell cell = row1.getCell(0);cell.setCellStyle(cellStyle1);// 创建总标题行List<String> totalHeader = Lists.newArrayList();totalHeader.add("人员契约化管理导入");writer.writeHeadRow(totalHeader); // 写入总标题行// 创建样式CellStyle cellStyle2 = createRedRightAlignedCellStyle2(writer.getWorkbook());Row row2 = sheet.getRow(1);Cell cell1 = row2.getCell(0);cell1.setCellStyle(cellStyle2);List<String> header = Lists.newArrayList();header.add("组织名称");header.add("* 员工编号");header.add("加入本企业途径");header.add("是否启用约束区县");header.add("约束区县");header.add("约束区县开始日期");header.add("约束区县结束日期");header.add("是否启用约束岗位族");header.add("约束岗位族");header.add("约束岗位族开始日期");header.add("约束岗位族结束日期");int mergeRowIndex = 0; // 总标题所在行索引int mergeColumnStartIndex = 0; // 起始列索引int mergeColumnEndIndex = header.size() - 1; // 结束列索引//其实上面的这些索引没啥用，下面几行是合并某几行的单元格CellRangeAddress cellRangeAddress = new CellRangeAddress(mergeRowIndex, mergeRowIndex, mergeColumnStartIndex, mergeColumnEndIndex);sheet.addMergedRegion(cellRangeAddress);CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, mergeColumnStartIndex, mergeColumnEndIndex);sheet.addMergedRegion(cellRangeAddress1);writer.writeHeadRow(header); // 写入头部标题//建立新的sheetwriter.renameSheet(0, "人员维护");StyleSet styleSet = writer.getStyleSet();//实话说这一块是直接抄的别的，你说有用处吧，可能也有，你说没用吧，可能也没有for (int i = 0; i < 11; i++) {CellStyle cellStyle = writer.createColumnStyle(i);DataFormat format = writer.getWorkbook().createDataFormat();cellStyle.setDataFormat(format.getFormat("@"));writer.setStyleSet(styleSet);}//实话说这一块好像是也没生效for (int i = 0; i < header.size(); i++) {int headerLength = header.get(i).length(); // 获取列名长度int cellWidth = headerLength * 500; // 将字符长度转换为列宽单位// 如果内容比默认宽度要宽，则使用内容宽度；否则使用默认宽度int defaultWidth = 24 * 256; // 默认宽度int columnWidth = Math.max(cellWidth, defaultWidth);sheet.setColumnWidth(i, columnWidth); // 设置列宽}//这是列表下面的示例行List<Map<String, Object>> dataList = new ArrayList<>();Map<String, Object> dataRow = new HashMap <>();dataRow.put("组织名称", "请参考hr标准组织名称");dataRow.put("* 员工编号", "0000001");dataRow.put("加入本企业途径", "社会招聘");dataRow.put("是否启用约束区县", "是");dataRow.put("约束区县", "泰安市分公司管理层");dataRow.put("约束区县开始日期", "2024-04-11");dataRow.put("约束区县结束日期", "2024-06-11");dataRow.put("是否启用约束岗位族", "是");dataRow.put("约束岗位族", "基层组织建设与党员教育管理");dataRow.put("约束岗位族开始日期", "2024-04-11");dataRow.put("约束岗位族结束日期", "2024-06-11");dataList.add(dataRow);writer.write(dataList, false);for (int i = 0; i < header.size(); i++) {// 创建样式CellStyle cellStyle4 = createRedRightAlignedCellStyle3(writer.getWorkbook());Row row4 = sheet.getRow(3);Cell cell4 = row4.getCell(i);cell4.setCellStyle(cellStyle4);// 创建样式CellStyle cellStyle5 = createRedRightAlignedCellStyle5(writer.getWorkbook());Row row5 = sheet.getRow(2);Cell cell5 = row5.getCell(i);cell5.setCellStyle(cellStyle5);}//这个是用来写下拉的，不同的下拉的，setXSSFValidation(sheet);// 创建样式CellStyle cellStyle3 = createRedRightAlignedCellStyle2(writer.getWorkbook());Row row3 = sheet.getRow(2);Cell cell3 = row3.getCell(0);cell3.setCellStyle(cellStyle3);//创建新的sheetwriter.setSheet("Sheet2");List<String> header1 = Lists.newArrayList();header1.add("岗位族信息");writer.writeHeadRow(header1);List<Map<String,Object>> personnelContractManagementList = personnelContractManagementMapper.jobFamilyList();List<String> personnelContractManagementSheet = new ArrayList<>();for (int i = 0; i < personnelContractManagementList.size(); i++) {personnelContractManagementSheet.add(String.valueOf(personnelContractManagementList.get(i).get("ZHUANYE")));}writer.write(personnelContractManagementSheet, true);setSizeColumn(writer.getSheet());response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=人员维护导入模板.xlsx");ServletOutputStream out = null;try {out = response.getOutputStream();writer.flush(out, true);out.close();} catch (IOException e) {throw new RuntimeException(e);} finally {writer.close();}}

private static CellStyle createRedRightAlignedCellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);Font font = workbook.createFont();font.setColor(IndexedColors.RED.getIndex());cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.LEFT);return cellStyle;}private static CellStyle createRedRightAlignedCellStyle2(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);cellStyle.setAlignment(HorizontalAlignment.CENTER);return cellStyle;}private static CellStyle createRedRightAlignedCellStyle3(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);Font font = workbook.createFont();font.setColor(IndexedColors.RED.getIndex());cellStyle.setFont(font);cellStyle.setAlignment(HorizontalAlignment.CENTER);return cellStyle;}private static CellStyle createRedRightAlignedCellStyle5(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();cellStyle.setWrapText(true);cellStyle.setAlignment(HorizontalAlignment.CENTER);return cellStyle;}

 /**** @param sheet* @return*/private static Sheet setXSSFValidation(Sheet sheet){String onelist[] = {"是","否"};String joinEnterpriseWay[] = {"校园招聘","社会招聘","接收复转军人","成建制划转","劳务派遣"};Workbook workbook = sheet.getWorkbook();/*** 创建一个名称为hidden的sheet页，用于是否启用约束区县*/Sheet hidden = workbook.createSheet("hidden");Cell cell = null;for (int i = 0, length = onelist.length; i < length; i++){String name = onelist[i];Row roww = hidden.createRow(i);cell = roww.createCell(0);cell.setCellValue(name);}Name namedCell = workbook.createName();namedCell.setNameName("hidden");namedCell.setRefersToFormula("hidden!$A$1:$A$" + onelist.length);//将第二个sheet页设置为隐藏workbook.setSheetHidden(1, true);XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);//将名称为hidden的数据进行加载XSSFDataValidationConstraint constraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("hidden");//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(1, 1000, 3, 3);XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(constraint, regions);data_validation_list.setErrorStyle(XSSFDataValidation.ErrorStyle.STOP);data_validation_list.createErrorBox("提示", "不允许自己输入，请选择下拉框里的数据");sheet.addValidationData(data_validation_list);/*** 创建一个名称为hidden的sheet页，用于是否启用约束区县*/Sheet hidden1 = workbook.createSheet("hidden1");Cell cell1 = null;for (int i = 0, length = onelist.length; i < length; i++){String name1 = onelist[i];Row row1 = hidden1.createRow(i);cell1 = row1.createCell(0);cell1.setCellValue(name1);}Name namedCell1 = workbook.createName();namedCell1.setNameName("hidden1");namedCell1.setRefersToFormula("hidden1!$A$1:$A$" + onelist.length);//将第二个sheet页设置为隐藏workbook.setSheetHidden(2, true);//将名称为hidden的数据进行加载XSSFDataValidationConstraint constraint1 = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("hidden1");//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions1 = new CellRangeAddressList(1, 1000, 7, 7);XSSFDataValidation data_validation_list1 = (XSSFDataValidation) dvHelper.createValidation(constraint1, regions1);data_validation_list1.setErrorStyle(XSSFDataValidation.ErrorStyle.STOP);data_validation_list1.createErrorBox("提示", "不允许自己输入，请选择下拉框里的数据");sheet.addValidationData(data_validation_list1);Sheet hidden2 = workbook.createSheet("hidden2");Cell cell2 = null;for (int i = 0; i < joinEnterpriseWay.length; i++) {String name = joinEnterpriseWay[i];Row row = hidden2.createRow(i);cell2 = row.createCell(0);cell2.setCellValue(name);}Name namedCell2 = workbook.createName();namedCell2.setNameName("hidden2");namedCell2.setRefersToFormula("hidden2!$A$1:$A$" + joinEnterpriseWay.length);//将第二个sheet页设置为隐藏workbook.setSheetHidden(3, true);//将名称为hidden的数据进行加载XSSFDataValidationConstraint constraint2 = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint("hidden2");//设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions2 = new CellRangeAddressList(1, 1000, 2, 2);XSSFDataValidation data_validation_list2 = (XSSFDataValidation) dvHelper.createValidation(constraint2, regions2);data_validation_list2.setErrorStyle(XSSFDataValidation.ErrorStyle.STOP);data_validation_list2.createErrorBox("提示", "不允许自己输入，请选择下拉框里的数据");sheet.addValidationData(data_validation_list2);return sheet;}