Java使用POI實(shí)現(xiàn)Excel文件的創(chuàng)建與處理
1、背景
需求中有需要用戶自定義Excel表格表頭,然后生成Excel文件,使用EasyExcel更適合生成固定表頭的Excel文檔,所以此處采用POI原生方式進(jìn)行開發(fā)。文檔如下:
2、創(chuàng)建表格
主要的代碼邏輯如下,非主要方法可以在完整代碼中找到。
2.1 定義表頭對(duì)象
根據(jù)需求,表頭需要制定2級(jí)表頭,我們先定義一個(gè)Excel表頭對(duì)象。
@Data @NoArgsConstructor @AllArgsConstructor public class ExcelModelDto { /*** 名稱 */ private String fieldName; /*** 提示語(yǔ) */ private String comment; /*** 類型 */ private Integer type; /*** 背景色 */ private short backgroundColor; /*** 子標(biāo)題 */ private List<Child> children; @Data @NoArgsConstructor @AllArgsConstructor public static class Child { /*** 字段編碼 */ private String fieldCode; /*** 字段名稱 */ private String fieldName; /*** 提示語(yǔ) */ private String comment; /*** 類型 */ private Integer type; /*** 下拉框選項(xiàng) */ private String[] items; } }
2.2 Excel生成器
創(chuàng)建一個(gè)Excel文件生成對(duì)象,包含多個(gè)屬性,其中包括:文件路徑、文件名稱、是否需要下拉框、文件后綴名、最大文本行數(shù)等。
@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; /** * 擴(kuò)展字段sheet頁(yè)行數(shù)記錄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 創(chuàng)建模板
/** * 生成模板 * * @throws IOException 異常 */ public void createTemplate() throws IOException { this.doCreateSheet(Paths.get(file), sheetName, data); } /** * 向Excel文件新增一個(gè)新的工作表,并處理表頭。 * * @param pathForFile 新工作表將要保存的文件路徑。 * @throws IOException 如果讀寫文件時(shí)發(fā)生異常。 */ 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內(nèi)容單元格,默認(rèn)都是有二級(jí)標(biāo)題 this.dealExcelContentCell(workbook, sheet, data); // 將inputStream轉(zhuǎn)換為outputStream,并重新寫入文件 try (OutputStream outputStream = Files.newOutputStream(pathForFile)) { workbook.write(outputStream); } finally { long endTime = System.currentTimeMillis(); log.info("創(chuàng)建Excel模板文件共耗時(shí):{}秒。", (endTime - startTime) / 1000); } }
2.4 處理Excel表頭
/** * 處理 Excel 表頭數(shù)據(jù),包括第一行和第二行的標(biāo)題單元格樣式設(shè)置、數(shù)據(jù)填充和合并單元格。 * * @param workbook 工作簿對(duì)象 * @param sheet 主表的工作表對(duì)象 * @param data 表頭數(shù)據(jù) */ private void dealExcelHeadingCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) { // 創(chuàng)建第一行和第二行表頭數(shù)據(jù),并設(shè)置行高 Row row1 = this.getRow(sheet, 0); Row row2 = this.getRow(sheet, 1); row1.setHeightInPoints(20); row2.setHeightInPoints(20); // 已經(jīng)存在的列號(hào) int lastCellNum = this.getLastCellNum(sheet, 1); int currentCellNum = lastCellNum; int startCellNum = lastCellNum; int endCellNum; for (ExcelModelDto excelModelDto : data) { // 一級(jí)標(biāo)題名稱 String firstTitleName = excelModelDto.getFieldName(); // 一級(jí)標(biāo)題單元格樣式 CellStyle firstTitleCellStyle = this.buildFirstTitleCellStyle(workbook, excelModelDto); // 二級(jí)標(biāo)題的單元格樣式 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); // 處理完后列號(hào)加一 currentCellNum++; } endCellNum = currentCellNum - 1; // POI 版本升級(jí)后,合并單元格需要大于一個(gè)單元格 if (startCellNum != endCellNum) { CellRangeAddress region = new CellRangeAddress(0, 0, startCellNum, endCellNum); sheet.addMergedRegion(region); } startCellNum = endCellNum + 1; } }
2.5 處理Excel內(nèi)容單元格樣式
/** * 格式化內(nèi)容單元格。 * * @param sheet 工作表對(duì)象。 * @param workbook 工作簿對(duì)象。 */ private void dealExcelContentCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) { // 獲取統(tǒng)一的單元格樣式,不用每個(gè)單元格獲取一個(gè)對(duì)象,防止對(duì)象過(guò)多 CellStyle childCellStyle = this.getContentCellStyle(workbook); // 只格式化內(nèi)容單元格,且有上限 int maxContentRowNumber = MAX_CONTENT_ROW_NUMBER; // 跳過(guò)表頭,從文本行開始 for (int rowNumber = 2; rowNumber < maxContentRowNumber; rowNumber++) { Row row = sheet.createRow(rowNumber); // 列號(hào)從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); } // 每處理完一個(gè)單元格,列號(hào)加1 cellNumber++; } } } }
2.6 處理單個(gè)表頭
在處理表頭過(guò)程中,如果items 不為空,則說(shuō)明此列需要下拉框,數(shù)組為供用戶選擇的下拉內(nèi)容,防止下拉框內(nèi)容過(guò)大,所以將下拉內(nèi)容單獨(dú)生成到一個(gè)隱藏的sheet頁(yè)中,并且使用表達(dá)式來(lái)表達(dá)下拉框內(nèi)容,設(shè)定到單元格中。
/** * 處理Excel表格的標(biāo)題單元格。 * * @param workbook 工作簿對(duì)象 * @param sheet 工作表對(duì)象 * @param child ExcelModelDto.Child 對(duì)象,包含字段名、注釋和下拉框選項(xiàng)等信息 * @param firstTitleName 一級(jí)標(biāo)題名稱 * @param firstTitleCellStyle 一級(jí)標(biāo)題單元格樣式 * @param secondTitleCellStyle 二級(jí)標(biāo)題單元格樣式 * @param index 當(dāng)前處理的列索引 */ 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(); // 一級(jí)表頭 Cell cell1 = row1.createCell(index); cell1.setCellValue(firstTitleName); cell1.setCellStyle(firstTitleCellStyle); // 二級(jí)表頭,標(biāo)題如果以* 號(hào)結(jié)尾,則* 置為紅色 Cell cell2 = row2.createCell(index); RichTextString textString = this.parseCellValue(workbook, Font.COLOR_NORMAL, true, secondFieldName); cell2.setCellValue(textString); cell2.setCellStyle(secondTitleCellStyle); // 設(shè)置下拉框 if (items != null && items.length > 0 && needItems) { this.appendItems(workbook, sheet, secondTitleCellStyle, secondFieldName, items, index); } // 設(shè)置表頭備注 if (!org.apache.commons.lang.StringUtils.isEmpty(comment)) { this.setComment(sheet, cell2, comment); } // 根據(jù)字段長(zhǎng)度自動(dòng)調(diào)整列的寬度 sheet.setColumnWidth(index, 100 * 50); } /** * 在指定的工作簿和工作表中追加枚舉類型的項(xiàng),并設(shè)置公式引用。 * * @param workbook 工作簿對(duì)象 * @param sheet 工作表對(duì)象 * @param childCellStyle 子單元格樣式 * @param secondTitleName 第二級(jí)標(biāo)題名稱 * @param items 枚舉類型的項(xiàng)數(shù)組 * @param index 當(dāng)前項(xiàng)在總體中的索引位置 */ private void appendItems(Workbook workbook, Sheet sheet, CellStyle childCellStyle, String secondTitleName, String[] items, int index) { // 如果有序列單元格,則創(chuàng)建一個(gè)sheet頁(yè),來(lái)保存所有的枚舉類型,同時(shí)隱藏該sheet頁(yè) Sheet itemsSheet = this.getSheetByName(workbook, ITEM_SHEET_NAME, true); // 追加sheet的時(shí)候,需要看隱藏sheet的列已經(jīng)到哪一列了,避免追加時(shí)將原有隱藏列覆蓋掉 int existItemCell = this.getLastCellNum(itemsSheet, 0); // 將枚舉數(shù)組寫入到獨(dú)立的sheet頁(yè)中,同時(shí)設(shè)置表頭格式 String formula = this.writeItems(itemsSheet, childCellStyle, secondTitleName, existItemCell, items); // 設(shè)置公式到模板的sheet頁(yè)中,格式化后的最終公式為 // =itemSheet!$B$1:$B$88 // 表明該單元格引用的是 itemSheet sheet頁(yè)中 B1~B88的數(shù)據(jù) formula = String.format("=%s!%s", ITEM_SHEET_NAME, formula); this.setItems(sheet, formula, FIRST_ROW, LAST_ROW, index, index); }
3、追加sheet
有些需要在已有的Excel文檔中追加新的sheet表格內(nèi)容,效果如下:
/** * 在指定的 Excel 文件中添加一個(gè)新的工作表,并填充數(shù)據(jù)。 * * @param sheetName 新工作表的名稱 * @param data 要填充的數(shù)據(jù)列表 * @throws IOException 如果在操作文件時(shí)發(fā)生了 I/O 錯(cuò)誤 */ public void appendSheet(String sheetName, List<ExcelModelDto> data) throws IOException { long startTime = System.currentTimeMillis(); // 路徑不存在則創(chuàng)建,保證路徑是存在的 Path pathForLocalPath = Paths.get(localPath); boolean existPath = Files.exists(pathForLocalPath); if (!existPath) { Files.createDirectories(pathForLocalPath); } // 如果文件不存在,則走創(chuàng)建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模板文件共耗時(shí):{}秒。", (endTime - startTime) / 1000); } catch (Exception e) { log.error("追加Excel模板文件失??!", e); throw new BizException(e); } } /** * 向Excel文件追加一個(gè)新的工作表,并處理表頭。 * * @param inputStream Excel文件的輸入流。 * @param pathForFile 新工作表將要保存的文件路徑。 * @throws IOException 如果讀寫文件時(shí)發(fā)生異常。 */ 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內(nèi)容單元格,默認(rèn)都是有二級(jí)標(biāo)題 this.dealExcelContentCell(workbook, sheet, data); // 將inputStream轉(zhuǎn)換為outputStream,并重新寫入文件 try (OutputStream outputStream = Files.newOutputStream(pathForFile)) { IOUtils.copy(inputStream, outputStream); workbook.write(outputStream); } }
4、靜態(tài)工具
每次使用都需要new一個(gè)對(duì)象來(lái)創(chuàng)建Excel文件,所以創(chuàng)建一個(gè)靜態(tài)工具類,來(lái)通過(guò)靜態(tài)方法實(shí)現(xiàn)文檔的創(chuàng)建與追加。
public class ExcelGeneratorExecutors { /** * 創(chuàng)建 Excel 模板文件。 * * @param localPath 本地路徑 * @param fileName 文件名 * @param sheetName 工作表名稱 * @param data 數(shù)據(jù)列表 * @throws IOException 如果創(chuàng)建模板文件失敗 */ 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文件中追加一個(gè)新的工作表,并填充數(shù)據(jù)。 * * @param localPath Excel文件的本地路徑。 * @param fileName Excel文件的名稱。 * @param sheetName 新增工作表的名稱。 * @param data 填充到新增工作表的數(shù)據(jù)。 * @throws IOException 如果在追加工作表或填充數(shù)據(jù)時(shí)發(fā)生I/O錯(cuò)誤。 */ 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、單元測(cè)試
@Test public 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 = "測(cè)試"; 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(); } } @Test public 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 = "測(cè)試"; 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("冰箱", "洗衣機(jī)", "空調(diào)"); child1.setItems(list1.toArray(new String[0])); ExcelModelDto.Child child2 = new ExcelModelDto.Child(); child2.setComment("數(shù)量1"); child2.setFieldCode("qty"); child2.setFieldName("數(shù)量1"); List<String> list2 = Lists.newArrayList("1", "2", "3"); child2.setItems(list2.toArray(new String[0])); ExcelModelDto.Child child3 = new ExcelModelDto.Child(); child3.setComment("文本內(nèi)容"); child3.setFieldCode("textValue"); child3.setFieldName("文本內(nèi)容"); 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("商家運(yùn)單號(hào)"); excelModelDto0.setComment("商家運(yùn)單號(hào)"); excelModelDto0.setType((int) IndexedColors.TURQUOISE1.getIndex()); excelModelDto0.setBackgroundColor(IndexedColors.TURQUOISE1.getIndex()); ExcelModelDto.Child child0 = new ExcelModelDto.Child(); child0.setComment("關(guān)聯(lián)第一個(gè)sheet頁(yè)的商家運(yùn)單號(hào)"); child0.setFieldCode("orderNo"); child0.setFieldName("商家運(yùn)單號(hào)*"); List<ExcelModelDto.Child> children0 = new ArrayList<>(); children0.add(child0); excelModelDto0.setChildren(children0); ExcelModelDto excelModelDto = new ExcelModelDto(); excelModelDto.setFieldName("購(gòu)買電器"); 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("冰箱", "洗衣機(jī)", "空調(diào)"); child1.setItems(list1.toArray(new String[0])); ExcelModelDto.Child child2 = new ExcelModelDto.Child(); child2.setComment("數(shù)量"); child2.setFieldCode("qty"); child2.setFieldName("數(shù)量"); //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; /** * 擴(kuò)展字段sheet頁(yè)行數(shù)記錄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); } /** * 創(chuàng)建對(duì)象時(shí),將ExcelModel中的字段按順序排好,保存到List中 * * @param data 入?yún)? * @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 文件中添加一個(gè)新的工作表,并填充數(shù)據(jù)。 * * @param sheetName 新工作表的名稱 * @param data 要填充的數(shù)據(jù)列表 * @throws IOException 如果在操作文件時(shí)發(fā)生了 I/O 錯(cuò)誤 */ public void appendSheet(String sheetName, List<ExcelModelDto> data) throws IOException { long startTime = System.currentTimeMillis(); // 路徑不存在則創(chuàng)建,保證路徑是存在的 Path pathForLocalPath = Paths.get(localPath); boolean existPath = Files.exists(pathForLocalPath); if (!existPath) { Files.createDirectories(pathForLocalPath); } // 如果文件不存在,則走創(chuàng)建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模板文件共耗時(shí):{}秒。", (endTime - startTime) / 1000); } catch (Exception e) { log.error("追加Excel模板文件失??!", e); throw new BizException(e); } } /** * 向Excel文件新增一個(gè)新的工作表,并處理表頭。 * * @param pathForFile 新工作表將要保存的文件路徑。 * @throws IOException 如果讀寫文件時(shí)發(fā)生異常。 */ 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內(nèi)容單元格,默認(rèn)都是有二級(jí)標(biāo)題 this.dealExcelContentCell(workbook, sheet, data); // 將inputStream轉(zhuǎn)換為outputStream,并重新寫入文件 try (OutputStream outputStream = Files.newOutputStream(pathForFile)) { workbook.write(outputStream); } finally { long endTime = System.currentTimeMillis(); log.info("創(chuàng)建Excel模板文件共耗時(shí):{}秒。", (endTime - startTime) / 1000); } } /** * 向Excel文件追加一個(gè)新的工作表,并處理表頭。 * * @param inputStream Excel文件的輸入流。 * @param pathForFile 新工作表將要保存的文件路徑。 * @throws IOException 如果讀寫文件時(shí)發(fā)生異常。 */ 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內(nèi)容單元格,默認(rèn)都是有二級(jí)標(biāo)題 this.dealExcelContentCell(workbook, sheet, data); // 將inputStream轉(zhuǎn)換為outputStream,并重新寫入文件 try (OutputStream outputStream = Files.newOutputStream(pathForFile)) { IOUtils.copy(inputStream, outputStream); workbook.write(outputStream); } } /** * 處理 Excel 表頭數(shù)據(jù),包括第一行和第二行的標(biāo)題單元格樣式設(shè)置、數(shù)據(jù)填充和合并單元格。 * * @param workbook 工作簿對(duì)象 * @param sheet 主表的工作表對(duì)象 * @param data 表頭數(shù)據(jù) */ private void dealExcelHeadingCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) { // 創(chuàng)建第一行和第二行表頭數(shù)據(jù),并設(shè)置行高 Row row1 = this.getRow(sheet, 0); Row row2 = this.getRow(sheet, 1); row1.setHeightInPoints(20); row2.setHeightInPoints(20); // 已經(jīng)存在的列號(hào) int lastCellNum = this.getLastCellNum(sheet, 1); int currentCellNum = lastCellNum; int startCellNum = lastCellNum; int endCellNum; for (ExcelModelDto excelModelDto : data) { // 一級(jí)標(biāo)題名稱 String firstTitleName = excelModelDto.getFieldName(); // 一級(jí)標(biāo)題單元格樣式 CellStyle firstTitleCellStyle = this.buildFirstTitleCellStyle(workbook, excelModelDto); // 二級(jí)標(biāo)題的單元格樣式 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); // 處理完后列號(hào)加一 currentCellNum++; } endCellNum = currentCellNum - 1; // POI 版本升級(jí)后,合并單元格需要大于一個(gè)單元格 if (startCellNum != endCellNum) { CellRangeAddress region = new CellRangeAddress(0, 0, startCellNum, endCellNum); sheet.addMergedRegion(region); } startCellNum = endCellNum + 1; } } /** * 格式化內(nèi)容單元格。 * * @param sheet 工作表對(duì)象。 * @param workbook 工作簿對(duì)象。 */ private void dealExcelContentCell(Workbook workbook, Sheet sheet, List<ExcelModelDto> data) { // 獲取統(tǒng)一的單元格樣式,不用每個(gè)單元格獲取一個(gè)對(duì)象,防止對(duì)象過(guò)多 CellStyle childCellStyle = this.getContentCellStyle(workbook); // 只格式化內(nèi)容單元格,且有上限 int maxContentRowNumber = MAX_CONTENT_ROW_NUMBER; // 跳過(guò)表頭,從文本行開始 for (int rowNumber = 2; rowNumber < maxContentRowNumber; rowNumber++) { Row row = sheet.createRow(rowNumber); // 列號(hào)從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); } // 每處理完一個(gè)單元格,列號(hào)加1 cellNumber++; } } } } /** * 處理Excel表格的標(biāo)題單元格。 * * @param workbook 工作簿對(duì)象 * @param sheet 工作表對(duì)象 * @param child ExcelModelDto.Child 對(duì)象,包含字段名、注釋和下拉框選項(xiàng)等信息 * @param firstTitleName 一級(jí)標(biāo)題名稱 * @param firstTitleCellStyle 一級(jí)標(biāo)題單元格樣式 * @param secondTitleCellStyle 二級(jí)標(biāo)題單元格樣式 * @param index 當(dāng)前處理的列索引 */ 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(); // 一級(jí)表頭 Cell cell1 = row1.createCell(index); cell1.setCellValue(firstTitleName); cell1.setCellStyle(firstTitleCellStyle); // 二級(jí)表頭,標(biāo)題如果以* 號(hào)結(jié)尾,則* 置為紅色 Cell cell2 = row2.createCell(index); RichTextString textString = this.parseCellValue(workbook, Font.COLOR_NORMAL, true, secondFieldName); cell2.setCellValue(textString); cell2.setCellStyle(secondTitleCellStyle); // 設(shè)置下拉框 if (items != null && items.length > 0 && needItems) { this.appendItems(workbook, sheet, secondTitleCellStyle, secondFieldName, items, index); } // 設(shè)置表頭備注 if (!org.apache.commons.lang.StringUtils.isEmpty(comment)) { this.setComment(sheet, cell2, comment); } // 根據(jù)字段長(zhǎng)度自動(dòng)調(diào)整列的寬度 sheet.setColumnWidth(index, 100 * 50); } /** * 設(shè)置單元格下拉框 * 下拉框引用單獨(dú)一個(gè)sheet頁(yè)中的數(shù)據(jù) * * @param sheet sheet * @param formula 公式 * @param firstRow 起始行 * @param lastRow 結(jié)束行 * @param firstCol 起始列 * @param lastCol 結(jié)束列 */ 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); } /** * 設(shè)置單元格備注信息 * * @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(); // 設(shè)置提示框大小,默認(rèn)根據(jù) 提示信息的大小來(lái)確認(rèn)提示框高度 /// 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) // 前四個(gè)參數(shù)是坐標(biāo)點(diǎn),后四個(gè)參數(shù)是編輯和顯示批注時(shí)的大小. 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+"); // 以上參數(shù)不設(shè)置時(shí)會(huì)有默認(rèn)值,當(dāng)一個(gè)被重復(fù)設(shè)置批注時(shí)會(huì)報(bào)錯(cuò) // Multiple cell comments in one cell are not allowed // 故在設(shè)置批注前檢查錨點(diǎn)位置有無(wú)批注,有的話移除 if (cell.getCellComment() != null) { cell.removeCellComment(); } cell.setCellComment(comment); } /** * 獲取單元格樣式對(duì)象 * * @param workbook 工作簿 * @param backGroundColor 背景色 * @return 返回樣式對(duì)象 */ 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); // 設(shè)置邊框及顏色 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中寫入 序列內(nèi)容 * * @param sheet sheet * @param cellStyle 單元格格式,表頭格式 * @param itemsType 序列類型 * @param col 列號(hào) * @param items 序列數(shù)組 * @return 返回坐標(biāo) */ protected String writeItems(Sheet sheet, CellStyle cellStyle, String itemsType, int col, String[] items) { // 第一行為表頭數(shù)據(jù) Row row = sheet.getRow(0); if (row == null) { row = sheet.createRow(0); } Cell cell = row.createCell(col); // 獲取單元格列所對(duì)應(yīng)的字母,即 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]); } // 格式化后的公式坐標(biāo)為 $B$1:$B$88 return 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(); // 如果內(nèi)容是以 * 號(hào)結(jié)尾的,則將 * 號(hào)置為紅色,默認(rèn)黑色 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; } /** * 獲取指定行在給定工作表中的最后一個(gè)單元格的索引。 * * @param sheet 工作表對(duì)象 * @param rowNum 行號(hào)(從0開始計(jì)數(shù)) * @return 最后一個(gè)單元格的索引,若行不存在則返回0 */ private int getLastCellNum(Sheet sheet, int rowNum) { int existCell = 0; // 指定sheet頁(yè)不為空,則獲取已經(jīng)有多少列 Row row = sheet.getRow(rowNum); if (Objects.nonNull(row)) { existCell = row.getLastCellNum(); // 如果不存在返回的是-1,業(yè)務(wù)上從0開始計(jì)算 if (existCell < 0) { existCell = 0; } } return existCell; } /** * 獲取或創(chuàng)建指定名稱的工作表并將其隱藏。 * * @param workbook 工作簿對(duì)象 * @return 指定名稱的工作表對(duì)象 */ private Sheet getSheetByName(Workbook workbook, String sheetName, boolean hide) { Sheet itemsSheet = workbook.getSheet(sheetName); // 指定sheet頁(yè)為空則創(chuàng)建 if (Objects.isNull(itemsSheet)) { itemsSheet = workbook.createSheet(sheetName); int sheetIndex = workbook.getSheetIndex(sheetName); workbook.setSheetHidden(sheetIndex, hide); } return itemsSheet; } /** * 根據(jù)行號(hào)獲取或創(chuàng)建指定Sheet中的Row對(duì)象。 * * @param sheet 要操作的Sheet對(duì)象。 * @param rowNum 需要獲取或創(chuàng)建的行號(hào)。 * @return 指定行號(hào)的Row對(duì)象。 */ private Row getRow(Sheet sheet, int rowNum) { Row row = sheet.getRow(rowNum); if (Objects.isNull(row)) { row = sheet.createRow(rowNum); } return row; } /** * 構(gòu)建第一行標(biāo)題單元格樣式。 * * @param workbook 工作簿對(duì)象。 * @param excelModelDto Excel模型數(shù)據(jù)傳輸對(duì)象。 * @return 第一行標(biāo)題單元格樣式。 */ private CellStyle buildFirstTitleCellStyle(Workbook workbook, ExcelModelDto excelModelDto) { // 根據(jù)字段類型來(lái)獲取背景色 short backGroundColor = excelModelDto.getBackgroundColor(); CellStyle cellStyle = this.getCellStyle(workbook, backGroundColor); Font font = this.getFontStyle(workbook, Font.COLOR_NORMAL, true); cellStyle.setFont(font); return cellStyle; } /** * 在指定的工作簿和工作表中追加枚舉類型的項(xiàng),并設(shè)置公式引用。 * * @param workbook 工作簿對(duì)象 * @param sheet 工作表對(duì)象 * @param childCellStyle 子單元格樣式 * @param secondTitleName 第二級(jí)標(biāo)題名稱 * @param items 枚舉類型的項(xiàng)數(shù)組 * @param index 當(dāng)前項(xiàng)在總體中的索引位置 */ private void appendItems(Workbook workbook, Sheet sheet, CellStyle childCellStyle, String secondTitleName, String[] items, int index) { // 如果有序列單元格,則創(chuàng)建一個(gè)sheet頁(yè),來(lái)保存所有的枚舉類型,同時(shí)隱藏該sheet頁(yè) Sheet itemsSheet = this.getSheetByName(workbook, ITEM_SHEET_NAME, true); // 追加sheet的時(shí)候,需要看隱藏sheet的列已經(jīng)到哪一列了,避免追加時(shí)將原有隱藏列覆蓋掉 int existItemCell = this.getLastCellNum(itemsSheet, 0); // 將枚舉數(shù)組寫入到獨(dú)立的sheet頁(yè)中,同時(shí)設(shè)置表頭格式 String formula = this.writeItems(itemsSheet, childCellStyle, secondTitleName, existItemCell, items); // 設(shè)置公式到模板的sheet頁(yè)中,格式化后的最終公式為 // =itemSheet!$B$1:$B$88 // 表明該單元格引用的是 itemSheet sheet頁(yè)中 B1~B88的數(shù)據(jù) formula = String.format("=%s!%s", ITEM_SHEET_NAME, formula); this.setItems(sheet, formula, FIRST_ROW, LAST_ROW, index, index); } /** * 獲取單元格樣式對(duì)象 * * @param workbook 工作簿 * @return 返回樣式對(duì)象 */ 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); // 設(shè)置單元格格式為文本格式 DataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("@")); return cellStyle; } }
到此這篇關(guān)于Java使用POI實(shí)現(xiàn)Excel文件的創(chuàng)建與處理的文章就介紹到這了,更多相關(guān)Java POI創(chuàng)建Excel內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Spring Boot打jar包后配置文件的外部?jī)?yōu)化配置方法
這篇文章主要介紹了Spring Boot打jar包后配置文件的外部?jī)?yōu)化配置方法,需要的朋友可以參考下2018-02-02springboot項(xiàng)目使用SchedulingConfigurer實(shí)現(xiàn)多個(gè)定時(shí)任務(wù)的案例代碼
這篇文章主要介紹了springboot項(xiàng)目使用SchedulingConfigurer實(shí)現(xiàn)多個(gè)定時(shí)任務(wù),本文通過(guò)實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-01-01利用javadoc注釋自動(dòng)生成Swagger注解
由于現(xiàn)在controller方法上面沒(méi)有swagger注解,只能拿到接口url地址,無(wú)法獲得接口功能描述,所以本文為大家介紹一下如何利用javadoc注釋自動(dòng)生成Swagger注解,感興趣的可以了解下2023-08-08intellij idea如何將web項(xiàng)目打成war包的實(shí)現(xiàn)
這篇文章主要介紹了intellij idea如何將web項(xiàng)目打成war包的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2020-07-07Spring中@RequestParam、@RequestBody和@PathVariable的用法詳解
這篇文章主要介紹了Spring中@RequestParam、@RequestBody和@PathVariable的用法詳解,后端使用集合來(lái)接受參數(shù),靈活性較好,如果url中沒(méi)有對(duì)參數(shù)賦key值,后端在接收時(shí),會(huì)根據(jù)參數(shù)值的類型附,賦一個(gè)初始key,需要的朋友可以參考下2024-01-01Java圖片處理 (文字水印、圖片水印、縮放、補(bǔ)白)代碼實(shí)例
這篇文章主要介紹了Java圖片處理 (文字水印、圖片水印、縮放、補(bǔ)白)代碼實(shí)例,本文直接給出實(shí)現(xiàn)代碼,需要的朋友可以參考下2015-06-06IntelliJ IDEA基于SpringBoot如何搭建SSM開發(fā)環(huán)境的步驟詳解
這篇文章主要介紹了IntelliJ IDEA基于SpringBoot如何搭建SSM開發(fā)環(huán)境,本文分步驟通過(guò)圖文并茂的形式給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-10-10Springboot?內(nèi)部服務(wù)調(diào)用方式
這篇文章主要介紹了Springboot?內(nèi)部服務(wù)調(diào)用方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2022-03-03深入了解Java中Synchronized關(guān)鍵字的實(shí)現(xiàn)原理
synchronized是JVM的內(nèi)置鎖,基于Monitor機(jī)制實(shí)現(xiàn),每一個(gè)對(duì)象都有一個(gè)與之關(guān)聯(lián)的監(jiān)視器?(Monitor),這個(gè)監(jiān)視器充當(dāng)了一種互斥鎖的角色,本文就詳細(xì)聊一聊Synchronized關(guān)鍵字的實(shí)現(xiàn)原理,需要的朋友可以參考下2023-06-06