POI創建Excel文件

文章目錄

1、背景
2、創建表格
- 2.1 定義表頭對象
- 2.2 Excel生成器
- 2.3 創建模板
- 2.4 處理Excel表頭
- 2.5 處理Excel內容單元格樣式
- 2.6 處理單個表頭
3、追加sheet
4、靜態工具
5、單元測試
6、完整代碼示例

1、背景

需求中有需要用戶自定義Excel表格表頭，然后生成Excel文件，使用EasyExcel更適合生成固定表頭的Excel文檔，所以此處采用POI原生方式進行開發。文檔如下：
在這里插入圖片描述

2、創建表格

主要的代碼邏輯如下，非主要方法可以在完整代碼中找到。

2.1 定義表頭對象

根據需求，表頭需要制定2級表頭，我們先定義一個Excel表頭對象。

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelModelDto {/*** 名稱 */private String fieldName;/*** 提示語 */private String comment;/*** 類型 */private Integer type;/*** 背景色 */private short backgroundColor;/*** 子標題 */private List<Child> children;@Data@NoArgsConstructor@AllArgsConstructorpublic static class Child {/*** 字段編碼 */private String fieldCode;/*** 字段名稱 */private String fieldName;/*** 提示語 */private String comment;/*** 類型 */private Integer type;/*** 下拉框選項 */private String[] items;}}

2.2 Excel生成器

創建一個Excel文件生成對象，包含多個屬性，其中包括：文件路徑、文件名稱、是否需要下拉框、文件后綴名、最大文本行數等。

@Slf4j
public class ExcelGenerator {private final String localPath;private final String sheetName;private final String fileName;private final String file;private final Boolean needItems;private final List<ExcelModelDto> data;/*** 字段編碼集合，從data中解析 */private final List<String> fieldCodeList;public static final Integer FIRST_ROW = 2;public static final Integer LAST_ROW = 65535;public static final String FILE_SUFFIX = ".xlsx";public static final String PATH_SUFFIX = "/";public static final String ITEM_SHEET_NAME = "itemSheet";public static final String END_FLAG = "*";public static final Integer MAX_CONTENT_ROW_NUMBER = 1002;/*** 擴展字段sheet頁行數記錄key值*/public static final String EXTEND_PAGE_ROW_NUMBER_KEY = "extend";public ExcelGenerator(String localPath, String fileName, String sheetName, List<ExcelModelDto> data) {this(localPath, fileName, sheetName, true, data);}public ExcelGenerator(String localPath, String fileName, String sheetName, Boolean needItems, List<ExcelModelDto> data) {this.localPath = localPath;this.fileName = fileName;this.sheetName = sheetName;this.file = localPath + fileName;this.needItems = needItems;this.data = data;fieldCodeList = this.parseField(data);}
}

2.3 創建模板

/*** 生成模板** @throws IOException 異常*/public void createTemplate() throws IOException {this.doCreateSheet(Paths.get(file), sheetName, data);}/*** 向Excel文件新增一個新的工作表，并處理表頭。** @param pathForFile 新工作表將要保存的文件路徑。* @throws IOException 如果讀寫文件時發生異常。*/private void doCreateSheet(Path pathForFile, String sheetName, List<ExcelModelDto> data)throws IOException {long startTime = System.currentTimeMillis();Workbook workbook = new XSSFWorkbook();Sheet sheet = this.getSheetByName(workbook, sheetName, false);// 處理Excel表頭this.dealExcelHeadingCell(workbook, sheet, data);// 處理Excel內容單元格，默認都是有二級標題this.dealExcelContentCell(workbook, sheet, data);// 將inputStream轉換為outputStream，并重新寫入文件try (OutputStream outputStream = Files.newOutputStream(pathForFile)) {workbook.write(outputStream);} finally {long endTime = System.currentTimeMillis();log.info("創建Excel模板文件共耗時：{}秒。", (endTime - startTime) / 1000);}}

2.4 處理Excel表頭

/*** 處理 Excel 表頭數據，包括第一行和第二行的標題單元格樣式設置、數據填充和合并單元格。** @param workbook 工作簿對象* @param sheet    主表的工作表對象* @param data     表頭數據*/private void dealExcelHeadingCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) {// 創建第一行和第二行表頭數據，并設置行高Row row1 = this.getRow(sheet, 0);Row row2 = this.getRow(sheet, 1);row1.setHeightInPoints(20);row2.setHeightInPoints(20);// 已經存在的列號int lastCellNum = this.getLastCellNum(sheet, 1);int currentCellNum = lastCellNum;int startCellNum = lastCellNum;int endCellNum;for (ExcelModelDto excelModelDto : data) {// 一級標題名稱String firstTitleName = excelModelDto.getFieldName();// 一級標題單元格樣式CellStyle firstTitleCellStyle = this.buildFirstTitleCellStyle(workbook, excelModelDto);// 二級標題的單元格樣式CellStyle secondTitleCellStyle = this.getCellStyle(workbook, IndexedColors.WHITE.getIndex());List<ExcelModelDto.Child> children = excelModelDto.getChildren();if (children == null || children.size() == 0) {continue;}for (ExcelModelDto.Child child : children) {// 處理表頭單元格this.dealTitleCell(workbook, sheet, child, firstTitleName, firstTitleCellStyle, secondTitleCellStyle, currentCellNum);// 處理完后列號加一currentCellNum++;}endCellNum = currentCellNum - 1;// POI 版本升級后，合并單元格需要大于一個單元格if (startCellNum != endCellNum) {CellRangeAddress region = new CellRangeAddress(0, 0, startCellNum, endCellNum);sheet.addMergedRegion(region);}startCellNum = endCellNum + 1;}}

2.5 處理Excel內容單元格樣式

/*** 格式化內容單元格。** @param sheet    工作表對象。* @param workbook 工作簿對象。*/private void dealExcelContentCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) {// 獲取統一的單元格樣式，不用每個單元格獲取一個對象，防止對象過多CellStyle childCellStyle = this.getContentCellStyle(workbook);// 只格式化內容單元格，且有上限int maxContentRowNumber = MAX_CONTENT_ROW_NUMBER;// 跳過表頭，從文本行開始for (int rowNumber = 2; rowNumber < maxContentRowNumber; rowNumber++) {Row row = sheet.createRow(rowNumber);// 列號從0開始int cellNumber = 0;for (ExcelModelDto excelModelDto : data) {List<ExcelModelDto.Child> children = excelModelDto.getChildren();for (ExcelModelDto.Child child : children) {String[] items = child.getItems();if (Objects.isNull(items) || items.length == 0) {Cell cell = row.createCell(cellNumber);cell.setCellStyle(childCellStyle);}// 每處理完一個單元格，列號加1cellNumber++;}}}}

2.6 處理單個表頭

在處理表頭過程中，如果items 不為空，則說明此列需要下拉框，數組為供用戶選擇的下拉內容，防止下拉框內容過大，所以將下拉內容單獨生成到一個隱藏的sheet頁中，并且使用表達式來表達下拉框內容，設定到單元格中。

/*** 處理Excel表格的標題單元格。** @param workbook             工作簿對象* @param sheet                工作表對象* @param child                ExcelModelDto.Child 對象，包含字段名、注釋和下拉框選項等信息* @param firstTitleName       一級標題名稱* @param firstTitleCellStyle  一級標題單元格樣式* @param secondTitleCellStyle 二級標題單元格樣式* @param index                當前處理的列索引*/private void dealTitleCell(Workbook workbook, Sheet sheet,ExcelModelDto.Child child, String firstTitleName,CellStyle firstTitleCellStyle, CellStyle secondTitleCellStyle,int index) {Row row1 = this.getRow(sheet, 0);Row row2 = this.getRow(sheet, 1);String secondFieldName = child.getFieldName();String comment = child.getComment();String[] items = child.getItems();// 一級表頭Cell cell1 = row1.createCell(index);cell1.setCellValue(firstTitleName);cell1.setCellStyle(firstTitleCellStyle);// 二級表頭，標題如果以* 號結尾，則* 置為紅色Cell cell2 = row2.createCell(index);RichTextString textString = this.parseCellValue(workbook, Font.COLOR_NORMAL, true, secondFieldName);cell2.setCellValue(textString);cell2.setCellStyle(secondTitleCellStyle);// 設置下拉框if (items != null && items.length > 0 && needItems) {this.appendItems(workbook, sheet, secondTitleCellStyle, secondFieldName, items, index);}// 設置表頭備注if (!org.apache.commons.lang.StringUtils.isEmpty(comment)) {this.setComment(sheet, cell2, comment);}// 根據字段長度自動調整列的寬度sheet.setColumnWidth(index, 100 * 50);}/*** 在指定的工作簿和工作表中追加枚舉類型的項，并設置公式引用。** @param workbook        工作簿對象* @param sheet           工作表對象* @param childCellStyle  子單元格樣式* @param secondTitleName 第二級標題名稱* @param items           枚舉類型的項數組* @param index           當前項在總體中的索引位置*/private void appendItems(Workbook workbook, Sheet sheet, CellStyle childCellStyle, String secondTitleName, String[] items, int index) {// 如果有序列單元格，則創建一個sheet頁，來保存所有的枚舉類型，同時隱藏該sheet頁Sheet itemsSheet = this.getSheetByName(workbook, ITEM_SHEET_NAME, true);// 追加sheet的時候，需要看隱藏sheet的列已經到哪一列了，避免追加時將原有隱藏列覆蓋掉int existItemCell = this.getLastCellNum(itemsSheet, 0);// 將枚舉數組寫入到獨立的sheet頁中，同時設置表頭格式String formula = this.writeItems(itemsSheet, childCellStyle, secondTitleName, existItemCell, items);// 設置公式到模板的sheet頁中，格式化后的最終公式為// =itemSheet!$B$1:$B$88// 表明該單元格引用的是 itemSheet sheet頁中 B1~B88的數據formula = String.format("=%s!%s", ITEM_SHEET_NAME, formula);this.setItems(sheet, formula, FIRST_ROW, LAST_ROW, index, index);}

3、追加sheet

有些需要在已有的Excel文檔中追加新的sheet表格內容，效果如下：

在這里插入圖片描述

/*** 在指定的 Excel 文件中添加一個新的工作表，并填充數據。** @param sheetName 新工作表的名稱* @param data      要填充的數據列表* @throws IOException 如果在操作文件時發生了 I/O 錯誤*/public void appendSheet(String sheetName, List<ExcelModelDto> data) throws IOException {long startTime = System.currentTimeMillis();// 路徑不存在則創建，保證路徑是存在的Path pathForLocalPath = Paths.get(localPath);boolean existPath = Files.exists(pathForLocalPath);if (!existPath) {Files.createDirectories(pathForLocalPath);}// 如果文件不存在，則走創建sheet邏輯Path pathForFile = Paths.get(file);if (!Files.exists(pathForFile)) {this.doCreateSheet(pathForFile, sheetName, data);return;}// 如果文件存在則走追加sheet邏輯try (InputStream inputStream = Files.newInputStream(pathForFile)) {this.doAppendSheet(inputStream, pathForFile, sheetName, data);long endTime = System.currentTimeMillis();log.info("追加Excel模板文件共耗時：{}秒。", (endTime - startTime) / 1000);} catch (Exception e) {log.error("追加Excel模板文件失敗！", e);throw new BizException(e);}}/*** 向Excel文件追加一個新的工作表，并處理表頭。** @param inputStream Excel文件的輸入流。* @param pathForFile 新工作表將要保存的文件路徑。* @throws IOException 如果讀寫文件時發生異常。*/private void doAppendSheet(InputStream inputStream, Path pathForFile, String sheetName, List<ExcelModelDto> data)throws IOException {Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = this.getSheetByName(workbook, sheetName, false);// 處理Excel表頭this.dealExcelHeadingCell(workbook, sheet, data);// 處理Excel內容單元格，默認都是有二級標題this.dealExcelContentCell(workbook, sheet, data);// 將inputStream轉換為outputStream，并重新寫入文件try (OutputStream outputStream = Files.newOutputStream(pathForFile)) {IOUtils.copy(inputStream, outputStream);workbook.write(outputStream);}}

4、靜態工具

每次使用都需要new一個對象來創建Excel文件，所以創建一個靜態工具類，來通過靜態方法實現文檔的創建與追加。

public class ExcelGeneratorExecutors {/*** 創建 Excel 模板文件。** @param localPath 本地路徑* @param fileName  文件名* @param sheetName 工作表名稱* @param data      數據列表* @throws IOException 如果創建模板文件失敗*/public static void createTemplate(String localPath, String fileName, String sheetName, List<ExcelModelDto> data) throws IOException {ExcelGenerator excelGenerator = new ExcelGenerator(localPath, fileName, sheetName, data);excelGenerator.createTemplate();}/*** 在指定路徑的Excel文件中追加一個新的工作表，并填充數據。** @param localPath Excel文件的本地路徑。* @param fileName  Excel文件的名稱。* @param sheetName 新增工作表的名稱。* @param data      填充到新增工作表的數據。* @throws IOException 如果在追加工作表或填充數據時發生I/O錯誤。*/public static void appendSheet(String localPath, String fileName, String sheetName, List<ExcelModelDto> data) throws IOException {ExcelGenerator excelGenerator = new ExcelGenerator(localPath, fileName, sheetName, data);excelGenerator.appendSheet(sheetName, data);}}

5、單元測試

@Testpublic void testGenerate() {String localPath = "D:\\mytmp\\template\\";String dateTime = DateUtils.format(new Date(), DateUtils.DATE_FORMAT_COMMENT_2);String fileName = String.format("生成模板-%s.xlsx", dateTime);String sheetName = "測試";List<ExcelModelDto> data = this.buildExcelModelDtoList();ExcelGenerator excelGenerator = new ExcelGenerator(localPath, fileName, sheetName, data);try {excelGenerator.createTemplate();List<ExcelModelDto> data2 = this.buildExcelModelDtoList2();excelGenerator.appendSheet("自定義sheet", data);excelGenerator.appendSheet("自定義sheet2", data2);excelGenerator.appendSheet("自定義sheet3", data2);log.info("模板文件生成，名稱為：{}", fileName);} catch (IOException e) {e.printStackTrace();}}@Testpublic void testGenerate2() {String localPath = "D:\\mytmp\\template\\";String dateTime = DateUtils.format(new Date(), DateUtils.DATE_FORMAT_COMMENT_2);String fileName = String.format("生成模板-%s.xlsx", dateTime);String sheetName = "測試";List<ExcelModelDto> data = this.buildExcelModelDtoList();try {ExcelGeneratorExecutors.createTemplate(localPath, fileName, sheetName, data);ExcelGeneratorExecutors.appendSheet(localPath, fileName, sheetName, data);ExcelGeneratorExecutors.appendSheet(localPath, fileName, "自定義sheet3", data);log.info("模板文件生成，名稱為：{}", fileName);} catch (IOException e) {e.printStackTrace();}}public List<ExcelModelDto> buildExcelModelDtoList() {List<ExcelModelDto> data = new ArrayList<>();ExcelModelDto excelModelDto = new ExcelModelDto();excelModelDto.setFieldName("電器");excelModelDto.setComment("song");excelModelDto.setType(2);excelModelDto.setBackgroundColor((short) 2);List<ExcelModelDto.Child> children = new ArrayList<>();ExcelModelDto.Child child1 = new ExcelModelDto.Child();child1.setComment("類目1");child1.setFieldCode("category");child1.setFieldName("類目1");List<String> list1 = Lists.newArrayList("冰箱", "洗衣機", "空調");child1.setItems(list1.toArray(new String[0]));ExcelModelDto.Child child2 = new ExcelModelDto.Child();child2.setComment("數量1");child2.setFieldCode("qty");child2.setFieldName("數量1");List<String> list2 = Lists.newArrayList("1", "2", "3");child2.setItems(list2.toArray(new String[0]));ExcelModelDto.Child child3 = new ExcelModelDto.Child();child3.setComment("文本內容");child3.setFieldCode("textValue");child3.setFieldName("文本內容");children.add(child1);children.add(child2);children.add(child3);excelModelDto.setChildren(children);data.add(excelModelDto);return data;}public List<ExcelModelDto> buildExcelModelDtoList2() {List<ExcelModelDto> data = new ArrayList<>();ExcelModelDto excelModelDto0 = new ExcelModelDto();excelModelDto0.setFieldName("商家運單號");excelModelDto0.setComment("商家運單號");excelModelDto0.setType((int) IndexedColors.TURQUOISE1.getIndex());excelModelDto0.setBackgroundColor(IndexedColors.TURQUOISE1.getIndex());ExcelModelDto.Child child0 = new ExcelModelDto.Child();child0.setComment("關聯第一個sheet頁的商家運單號");child0.setFieldCode("orderNo");child0.setFieldName("商家運單號*");List<ExcelModelDto.Child> children0 = new ArrayList<>();children0.add(child0);excelModelDto0.setChildren(children0);ExcelModelDto excelModelDto = new ExcelModelDto();excelModelDto.setFieldName("購買電器");excelModelDto.setComment("song");excelModelDto.setType((int) IndexedColors.TURQUOISE1.getIndex());excelModelDto.setBackgroundColor(IndexedColors.TURQUOISE1.getIndex());ExcelModelDto.Child child1 = new ExcelModelDto.Child();child1.setComment("類目");child1.setFieldCode("category");child1.setFieldName("類目");List<String> list1 = Lists.newArrayList("冰箱", "洗衣機", "空調");child1.setItems(list1.toArray(new String[0]));ExcelModelDto.Child child2 = new ExcelModelDto.Child();child2.setComment("數量");child2.setFieldCode("qty");child2.setFieldName("數量");//List<String> list2 = Lists.newArrayList("1", "2", "3");//child2.setItems(list2.toArray(new String[0]));List<ExcelModelDto.Child> children = new ArrayList<>();children.add(child1);children.add(child2);excelModelDto.setChildren(children);data.add(excelModelDto0);data.add(excelModelDto);return data;}

6、完整代碼示例

@Slf4j
public class ExcelGenerator {private final String localPath;private final String sheetName;private final String fileName;private final String file;private final Boolean needItems;private final List<ExcelModelDto> data;/*** 字段編碼集合，從data中解析 */private final List<String> fieldCodeList;public static final Integer FIRST_ROW = 2;public static final Integer LAST_ROW = 65535;public static final String FILE_SUFFIX = ".xlsx";public static final String PATH_SUFFIX = "/";public static final String ITEM_SHEET_NAME = "itemSheet";public static final String END_FLAG = "*";public static final Integer MAX_CONTENT_ROW_NUMBER = 1002;/*** 擴展字段sheet頁行數記錄key值*/public static final String EXTEND_PAGE_ROW_NUMBER_KEY = "extend";public ExcelGenerator(String localPath, String fileName, String sheetName, List<ExcelModelDto> data) {this(localPath, fileName, sheetName, true, data);}public ExcelGenerator(String localPath, String fileName, String sheetName, Boolean needItems, List<ExcelModelDto> data) {this.localPath = localPath;this.fileName = fileName;this.sheetName = sheetName;this.file = localPath + fileName;this.needItems = needItems;this.data = data;fieldCodeList = this.parseField(data);}/*** 創建對象時，將ExcelModel中的字段按順序排好，保存到List中** @param data 入參* @return 返回值*/public List<String> parseField(List<ExcelModelDto> data) {List<String> fieldCodeList = new ArrayList<>();for (ExcelModelDto modelDto : data) {List<ExcelModelDto.Child> children = modelDto.getChildren();for (ExcelModelDto.Child child : children) {String fieldCode = child.getFieldCode();fieldCodeList.add(fieldCode);}}return fieldCodeList;}/*** 生成模板** @throws IOException 異常*/public void createTemplate() throws IOException {this.doCreateSheet(Paths.get(file), sheetName, data);}/*** 在指定的 Excel 文件中添加一個新的工作表，并填充數據。** @param sheetName 新工作表的名稱* @param data      要填充的數據列表* @throws IOException 如果在操作文件時發生了 I/O 錯誤*/public void appendSheet(String sheetName, List<ExcelModelDto> data) throws IOException {long startTime = System.currentTimeMillis();// 路徑不存在則創建，保證路徑是存在的Path pathForLocalPath = Paths.get(localPath);boolean existPath = Files.exists(pathForLocalPath);if (!existPath) {Files.createDirectories(pathForLocalPath);}// 如果文件不存在，則走創建sheet邏輯Path pathForFile = Paths.get(file);if (!Files.exists(pathForFile)) {this.doCreateSheet(pathForFile, sheetName, data);return;}// 如果文件存在則走追加sheet邏輯try (InputStream inputStream = Files.newInputStream(pathForFile)) {this.doAppendSheet(inputStream, pathForFile, sheetName, data);long endTime = System.currentTimeMillis();log.info("追加Excel模板文件共耗時：{}秒。", (endTime - startTime) / 1000);} catch (Exception e) {log.error("追加Excel模板文件失敗！", e);throw new BizException(e);}}/*** 向Excel文件新增一個新的工作表，并處理表頭。** @param pathForFile 新工作表將要保存的文件路徑。* @throws IOException 如果讀寫文件時發生異常。*/private void doCreateSheet(Path pathForFile, String sheetName, List<ExcelModelDto> data)throws IOException {long startTime = System.currentTimeMillis();Workbook workbook = new XSSFWorkbook();Sheet sheet = this.getSheetByName(workbook, sheetName, false);// 處理Excel表頭this.dealExcelHeadingCell(workbook, sheet, data);// 處理Excel內容單元格，默認都是有二級標題this.dealExcelContentCell(workbook, sheet, data);// 將inputStream轉換為outputStream，并重新寫入文件try (OutputStream outputStream = Files.newOutputStream(pathForFile)) {workbook.write(outputStream);} finally {long endTime = System.currentTimeMillis();log.info("創建Excel模板文件共耗時：{}秒。", (endTime - startTime) / 1000);}}/*** 向Excel文件追加一個新的工作表，并處理表頭。** @param inputStream Excel文件的輸入流。* @param pathForFile 新工作表將要保存的文件路徑。* @throws IOException 如果讀寫文件時發生異常。*/private void doAppendSheet(InputStream inputStream, Path pathForFile, String sheetName, List<ExcelModelDto> data)throws IOException {Workbook workbook = new XSSFWorkbook(inputStream);Sheet sheet = this.getSheetByName(workbook, sheetName, false);// 處理Excel表頭this.dealExcelHeadingCell(workbook, sheet, data);// 處理Excel內容單元格，默認都是有二級標題this.dealExcelContentCell(workbook, sheet, data);// 將inputStream轉換為outputStream，并重新寫入文件try (OutputStream outputStream = Files.newOutputStream(pathForFile)) {IOUtils.copy(inputStream, outputStream);workbook.write(outputStream);}}/*** 處理 Excel 表頭數據，包括第一行和第二行的標題單元格樣式設置、數據填充和合并單元格。** @param workbook 工作簿對象* @param sheet    主表的工作表對象* @param data     表頭數據*/private void dealExcelHeadingCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) {// 創建第一行和第二行表頭數據，并設置行高Row row1 = this.getRow(sheet, 0);Row row2 = this.getRow(sheet, 1);row1.setHeightInPoints(20);row2.setHeightInPoints(20);// 已經存在的列號int lastCellNum = this.getLastCellNum(sheet, 1);int currentCellNum = lastCellNum;int startCellNum = lastCellNum;int endCellNum;for (ExcelModelDto excelModelDto : data) {// 一級標題名稱String firstTitleName = excelModelDto.getFieldName();// 一級標題單元格樣式CellStyle firstTitleCellStyle = this.buildFirstTitleCellStyle(workbook, excelModelDto);// 二級標題的單元格樣式CellStyle secondTitleCellStyle = this.getCellStyle(workbook, IndexedColors.WHITE.getIndex());List<ExcelModelDto.Child> children = excelModelDto.getChildren();if (children == null || children.size() == 0) {continue;}for (ExcelModelDto.Child child : children) {// 處理表頭單元格this.dealTitleCell(workbook, sheet, child, firstTitleName, firstTitleCellStyle, secondTitleCellStyle, currentCellNum);// 處理完后列號加一currentCellNum++;}endCellNum = currentCellNum - 1;// POI 版本升級后，合并單元格需要大于一個單元格if (startCellNum != endCellNum) {CellRangeAddress region = new CellRangeAddress(0, 0, startCellNum, endCellNum);sheet.addMergedRegion(region);}startCellNum = endCellNum + 1;}}/*** 格式化內容單元格。** @param sheet    工作表對象。* @param workbook 工作簿對象。*/private void dealExcelContentCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) {// 獲取統一的單元格樣式，不用每個單元格獲取一個對象，防止對象過多CellStyle childCellStyle = this.getContentCellStyle(workbook);// 只格式化內容單元格，且有上限int maxContentRowNumber = MAX_CONTENT_ROW_NUMBER;// 跳過表頭，從文本行開始for (int rowNumber = 2; rowNumber < maxContentRowNumber; rowNumber++) {Row row = sheet.createRow(rowNumber);// 列號從0開始int cellNumber = 0;for (ExcelModelDto excelModelDto : data) {List<ExcelModelDto.Child> children = excelModelDto.getChildren();for (ExcelModelDto.Child child : children) {String[] items = child.getItems();if (Objects.isNull(items) || items.length == 0) {Cell cell = row.createCell(cellNumber);cell.setCellStyle(childCellStyle);}// 每處理完一個單元格，列號加1cellNumber++;}}}}/*** 處理Excel表格的標題單元格。** @param workbook             工作簿對象* @param sheet                工作表對象* @param child                ExcelModelDto.Child 對象，包含字段名、注釋和下拉框選項等信息* @param firstTitleName       一級標題名稱* @param firstTitleCellStyle  一級標題單元格樣式* @param secondTitleCellStyle 二級標題單元格樣式* @param index                當前處理的列索引*/private void dealTitleCell(Workbook workbook, Sheet sheet,ExcelModelDto.Child child, String firstTitleName,CellStyle firstTitleCellStyle, CellStyle secondTitleCellStyle,int index) {Row row1 = this.getRow(sheet, 0);Row row2 = this.getRow(sheet, 1);String secondFieldName = child.getFieldName();String comment = child.getComment();String[] items = child.getItems();// 一級表頭Cell cell1 = row1.createCell(index);cell1.setCellValue(firstTitleName);cell1.setCellStyle(firstTitleCellStyle);// 二級表頭，標題如果以* 號結尾，則* 置為紅色Cell cell2 = row2.createCell(index);RichTextString textString = this.parseCellValue(workbook, Font.COLOR_NORMAL, true, secondFieldName);cell2.setCellValue(textString);cell2.setCellStyle(secondTitleCellStyle);// 設置下拉框if (items != null && items.length > 0 && needItems) {this.appendItems(workbook, sheet, secondTitleCellStyle, secondFieldName, items, index);}// 設置表頭備注if (!org.apache.commons.lang.StringUtils.isEmpty(comment)) {this.setComment(sheet, cell2, comment);}// 根據字段長度自動調整列的寬度sheet.setColumnWidth(index, 100 * 50);}/*** 設置單元格下拉框* 下拉框引用單獨一個sheet頁中的數據** @param sheet    sheet* @param formula  公式* @param firstRow 起始行* @param lastRow  結束行* @param firstCol 起始列* @param lastCol  結束列*/public void setItems(Sheet sheet, String formula, int firstRow, int lastRow, int firstCol, int lastCol) {CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);DataValidationHelper helper = sheet.getDataValidationHelper();DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);DataValidation validation = helper.createValidation(constraint, addressList);validation.setShowErrorBox(true);sheet.addValidationData(validation);}/*** 設置單元格備注信息** @param sheet      sheet* @param cell       單元格* @param textString 提示信息*/public void setComment(Sheet sheet, Cell cell, String textString) {Drawing<?> drawing = sheet.createDrawingPatriarch();CreationHelper factory = sheet.getWorkbook().getCreationHelper();// 設置提示框大小，默認根據 提示信息的大小來確認提示框高度/// ClientAnchor anchor = factory.createClientAnchor();textString = StringUtils.defaultIfBlank(textString, "");int length = textString.length();int row2 = length / 25 + 6;// (int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)// 前四個參數是坐標點,后四個參數是編輯和顯示批注時的大小.ClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, row2);Comment comment = drawing.createCellComment(anchor);RichTextString str = factory.createRichTextString(textString);comment.setString(str);comment.setAuthor("Auto+");// 以上參數不設置時會有默認值，當一個被重復設置批注時會報錯// Multiple cell comments in one cell are not allowed// 故在設置批注前檢查錨點位置有無批注，有的話移除if (cell.getCellComment() != null) {cell.removeCellComment();}cell.setCellComment(comment);}/*** 獲取單元格樣式對象** @param workbook        工作簿* @param backGroundColor 背景色* @return 返回樣式對象*/public CellStyle getCellStyle(Workbook workbook, short backGroundColor) {CellStyle cellStyle = workbook.createCellStyle();CreationHelper createHelper = workbook.getCreationHelper();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));// IndexedColors.YELLOW.getIndex()cellStyle.setFillForegroundColor(backGroundColor);cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 水平居中cellStyle.setAlignment(HorizontalAlignment.CENTER);// 垂直居中cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 設置邊框及顏色cellStyle.setBorderTop(BorderStyle.DOUBLE);cellStyle.setBorderBottom(BorderStyle.DOUBLE);cellStyle.setBorderLeft(BorderStyle.DOUBLE);cellStyle.setBorderRight(BorderStyle.DOUBLE);cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());return cellStyle;}/*** 向sheet中寫入 序列內容** @param sheet     sheet* @param cellStyle 單元格格式，表頭格式* @param itemsType 序列類型* @param col       列號* @param items     序列數組* @return 返回坐標*/protected String writeItems(Sheet sheet, CellStyle cellStyle, String itemsType, int col, String[] items) {// 第一行為表頭數據Row row = sheet.getRow(0);if (row == null) {row = sheet.createRow(0);}Cell cell = row.createCell(col);// 獲取單元格列所對應的字母，即 0=A，1=B ...String columnLetter = CellReference.convertNumToColString(col);cell.setCellValue(itemsType);cell.setCellStyle(cellStyle);int length = items.length;for (int i = 0; i < length; i++) {Row itemRow = sheet.getRow(i + 1);if (itemRow == null) {itemRow = sheet.createRow(i + 1);}Cell itemRowCell = itemRow.createCell(col);itemRowCell.setCellValue(items[i]);}// 格式化后的公式坐標為  $B$1:$B$88return String.format("$%s$%s:$%s$%s", columnLetter, 2, columnLetter, items.length + 1);}/*** 格式化單元格字體樣式** @param workbook  工作簿* @param fontColor 字體顏色* @param isBold    是否加粗* @param value     單元格值*/public RichTextString parseCellValue(Workbook workbook, short fontColor, boolean isBold, String value) {value = StringUtils.defaultIfBlank(value, "");XSSFRichTextString textString = new XSSFRichTextString(value);Font font1 = getFontStyle(workbook, fontColor, isBold);if (StringUtils.isNotBlank(value)) {int length = value.length();// 如果內容是以 * 號結尾的，則將 * 號置為紅色，默認黑色if (value.endsWith(END_FLAG)) {int point = length - 1;textString.applyFont(0, point, font1);Font font2 = getFontStyle(workbook, Font.COLOR_RED, isBold);textString.applyFont(point, length, font2);} else {textString.applyFont(0, length, font1);}}return textString;}/*** 獲取字體樣式** @param workbook  工作簿* @param fontColor 字體顏色* @param isBold    是否加粗* @return 返回值*/public Font getFontStyle(Workbook workbook, short fontColor, boolean isBold) {Font font = workbook.createFont();font.setColor(fontColor);if (isBold) {font.setBold(true);}font.setFontName("宋體");// 字體大小font.setFontHeightInPoints((short) 10);return font;}/*** 獲取指定行在給定工作表中的最后一個單元格的索引。** @param sheet  工作表對象* @param rowNum 行號（從0開始計數）* @return 最后一個單元格的索引，若行不存在則返回0*/private int getLastCellNum(Sheet sheet, int rowNum) {int existCell = 0;// 指定sheet頁不為空，則獲取已經有多少列Row row = sheet.getRow(rowNum);if (Objects.nonNull(row)) {existCell = row.getLastCellNum();// 如果不存在返回的是-1，業務上從0開始計算if (existCell < 0) {existCell = 0;}}return existCell;}/*** 獲取或創建指定名稱的工作表并將其隱藏。** @param workbook 工作簿對象* @return 指定名稱的工作表對象*/private Sheet getSheetByName(Workbook workbook, String sheetName, boolean hide) {Sheet itemsSheet = workbook.getSheet(sheetName);// 指定sheet頁為空則創建if (Objects.isNull(itemsSheet)) {itemsSheet = workbook.createSheet(sheetName);int sheetIndex = workbook.getSheetIndex(sheetName);workbook.setSheetHidden(sheetIndex, hide);}return itemsSheet;}/*** 根據行號獲取或創建指定Sheet中的Row對象。** @param sheet  要操作的Sheet對象。* @param rowNum 需要獲取或創建的行號。* @return 指定行號的Row對象。*/private Row getRow(Sheet sheet, int rowNum) {Row row = sheet.getRow(rowNum);if (Objects.isNull(row)) {row = sheet.createRow(rowNum);}return row;}/*** 構建第一行標題單元格樣式。** @param workbook      工作簿對象。* @param excelModelDto Excel模型數據傳輸對象。* @return 第一行標題單元格樣式。*/private CellStyle buildFirstTitleCellStyle(Workbook workbook, ExcelModelDto excelModelDto) {// 根據字段類型來獲取背景色short backGroundColor = excelModelDto.getBackgroundColor();CellStyle cellStyle = this.getCellStyle(workbook, backGroundColor);Font font = this.getFontStyle(workbook, Font.COLOR_NORMAL, true);cellStyle.setFont(font);return cellStyle;}/*** 在指定的工作簿和工作表中追加枚舉類型的項，并設置公式引用。** @param workbook        工作簿對象* @param sheet           工作表對象* @param childCellStyle  子單元格樣式* @param secondTitleName 第二級標題名稱* @param items           枚舉類型的項數組* @param index           當前項在總體中的索引位置*/private void appendItems(Workbook workbook, Sheet sheet, CellStyle childCellStyle, String secondTitleName, String[] items, int index) {// 如果有序列單元格，則創建一個sheet頁，來保存所有的枚舉類型，同時隱藏該sheet頁Sheet itemsSheet = this.getSheetByName(workbook, ITEM_SHEET_NAME, true);// 追加sheet的時候，需要看隱藏sheet的列已經到哪一列了，避免追加時將原有隱藏列覆蓋掉int existItemCell = this.getLastCellNum(itemsSheet, 0);// 將枚舉數組寫入到獨立的sheet頁中，同時設置表頭格式String formula = this.writeItems(itemsSheet, childCellStyle, secondTitleName, existItemCell, items);// 設置公式到模板的sheet頁中，格式化后的最終公式為// =itemSheet!$B$1:$B$88// 表明該單元格引用的是 itemSheet sheet頁中 B1~B88的數據formula = String.format("=%s!%s", ITEM_SHEET_NAME, formula);this.setItems(sheet, formula, FIRST_ROW, LAST_ROW, index, index);}/*** 獲取單元格樣式對象** @param workbook 工作簿* @return 返回樣式對象*/public CellStyle getContentCellStyle(Workbook workbook) {CellStyle cellStyle = workbook.createCellStyle();CreationHelper createHelper = workbook.getCreationHelper();cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));// 背景色為純色cellStyle.setFillPattern(FillPatternType.NO_FILL);// 設置單元格格式為文本格式DataFormat format = workbook.createDataFormat();cellStyle.setDataFormat(format.getFormat("@"));return cellStyle;}}