需求

接了需求，下載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;}