🎈 1 參考文檔

填充Excel | Easy Excel官方文檔

EasyExcel-合并單元格 | 博客園-默念x

---

🔍2 個人需求

2.1 數據需求

• 第一部分
  • 粉色部分：表頭固定，直接使用模板；
  • 紅色部分：正常數據內容，和一般的Excel導出大同小異；
• 第二部分
  • 綠色部分：上面的表頭是動態的，并且這些字段沒有落表；
  • 橙色部分：左邊的實測值、總權重得分、總得分相當于固定的表頭；
  • 黃色部分：這部分是正常數據，但是橙色部分也需要被當作成數據。

2.2 單元格合并需求

• 紫色部分：表頭部分，類別相同的進行橫向和縱向合并；
• 綠色部分：根據相同類別，將表格數據進行橫向合并；
• 橙色部分：相同兩行實測值包括實測值所屬的數據進行縱向合并；
• 紅色部分：因為橙色部分加上綠色部分對應“實測值、實測值、總權重得分、總得分”一共四行，所以紅色部分是相同的四行產品數據，需要進行縱向合并。

合并后的樣子：

💡 3 解決方案

3.1 數據處理

使用EasyExcel利用模板填充的方式，以一個單元格為最小單位，把數據全部查出來，然后將數據處理成一行一行的形式進行填充，碰到相同的數據，就進行合并單元格。

3.2 數據字段沒落表

有一部分表頭數據的字段沒有落表，在實際數據庫中都屬于一個字段，例如下圖：光學、電學、聲學實際上都屬于category，而不是optics、electricity、acoustics。

可以使用map的進行對數據進行處理和存儲，處理后的樣子：

3.3 動態表頭

一般都是固定表頭，然后填充數據，相當于一維的。因為表頭是動態的，所以第二部分數據相當于二維的，需要將表頭和表格數據分別進行填充。

EasyExcel的填充方式是通過模板進行填充導出的，那我們可以導出兩次，第一次用/resources/template下的模板文件將Excel導出成流，接著以第一次導出的Excel流，作為第二次導出的模板，最后再導出需要的Excel表格。

模板：

第一次導出：

第二次導出：

以這種方法，不僅僅是導出兩次，還可以導出多次，以此處理更加復雜的表格。

3.4 合并單元格

參考官方的文章合并單元格和文章EasyExcel-合并單元格-默念x，然后根據需要自定義合并策略Strategy并且繼承于AbstractMergeStrategy，計算出需要合并單元格數量的列表，然后利用CellRangeAddress進行單元格合并。

CellRangeAddress cellRangeAddress = new CellRangeAddress(起始行，結尾行，起始列，結尾列)； sheet.addMergedRegionUnsafe(cellRangeAddress);

使用合并策略的方式：

ExcelWriter screenTemplateExcelWriter = EasyExcel.write(templateOut) // 導出最終臨時文件.withTemplate(templateFileName) // 使用的模板.registerWriteHandler(new XXXStrategy(需要的參數)) // 自定義單元格合并策略.build();

---

🚀4 第一次導出（部分代碼）

4.1 Excel 填充模板

總體樣貌，模板名稱為screenTemplate.xlsx，工作表名稱為sheet0。

拉長單元格，查看具體變量。

4.2 ScreenServiceImpl 業務實現層

@Service public class ScreenServiceImpl implements ScreenService {@Overridepublic void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {// HttpServletResponse消息頭參數設置String filename = "exportFile.xlsx";httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");httpServletResponse.addHeader("Pragma", "no-cache");httpServletResponse.addHeader("Cache-Control", "no-cache");// 通過ClassPathResource獲取/resources/template下的模板文件ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");// 這里用try-with-resourcetry (// 獲取模板文件InputStream screenParamTemplateFileName = classPathResource.getInputStream();OutputStream screenOut = httpServletResponse.getOutputStream();BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);) {// --------------------------------基本配置--------------------------------// 設置內容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 設置內容水平居中對齊contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 設置內容垂直居中對齊contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 自動換行contentWriteCellStyle.setWrapped(true);// 設置字體樣式和大小WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontHeightInPoints((short) 12);contentWriteFont.setFontName("微軟雅黑");contentWriteCellStyle.setWriteFont(contentWriteFont);// 配置橫向填充FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// sheet名稱WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();// --------------------------------基本配置--------------------------------// ---------------------模擬獲取第一部分的表格數據、表頭參數---------------------List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();// 構造5個產品數據for (int i = 1; i <= 5; i++) {// 每份數據乘以4，為了合并單元格做準備for (int j = 0; j < 4; j++) {ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();screenGatherDTO.setScreenSize(String.valueOf(i * 10));screenGatherDTO.setSupplier("廠商" + i);screenGatherDTO.setPartMode("型號" + i);screenGatherDTO.setResolution("1080P");screenGatherDTO.setRefreshRate("60Hz");screenGatherDTO.setPanel("IPS");screenGatherDTOList.add(screenGatherDTO);}}if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {for (int i = 0; i < screenGatherDTOList.size(); i++) {// 在屏規格末尾加上表格模板參數screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");}}// 填充第一個表頭的單元格ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();screenValueExcelDTO.setValue("產品測試");screenValueExcelDTOList.add(screenValueExcelDTO);// 在屏規格末尾加上表頭模板參數List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();for (int i = 0; i < 4; i++) {ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();switch (i) {case 0:screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");break;case 1:screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");break;case 2:screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");break;case 3:screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");break;default:break;}screenTableExcelDTOList.add(screenTableExcelDTO);}// ---------------------模擬獲取第一部分的表格數據、表頭參數---------------------// --------------------------------第一次導出--------------------------------ExcelWriter screenTemplateExcelWriter = EasyExcel.write(screenBos) // 導出臨時文件，使用的是BufferedOutputStream.withTemplate(screenParamTemplateFileName) // 使用的模板.registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5)) // 自定義單元格合并策略.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置內容策略，頭部為null.build();// 填充屏規格表格數據screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);// 填充第一個表頭的單元格screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);// 填充表頭模板參數screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);screenTemplateExcelWriter.finish();// --------------------------------第一次導出--------------------------------} catch (IOException e) {throw new RuntimeException(e);}} }

4.3 ScreenValueMergeStrategy 自定義合并單元格策略

public class ScreenValueMergeStrategy extends AbstractMergeStrategy {/*** 分組，每幾行合并一次*/private List<Integer> exportFieldGroupCountList;/*** 合并的目標開始列索引*/private Integer targetBeginColIndex;/*** 合并的目標結束列索引*/private Integer targetEndColIndex;/*** 需要開始合并單元格的首行索引*/private Integer firstRowIndex;public ScreenValueMergeStrategy() {}/*** @param exportDataList 待合并目標行的值* @param targetBeginColIndex 合并的目標開始列索引* @param targetEndColIndex 合并的目標結束列索引* @param firstRowIndex 需要開始合并單元格的首行索引*/public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {this.exportFieldGroupCountList = getGroupCountList(exportDataList);this.targetBeginColIndex = targetBeginColIndex;this.targetEndColIndex = targetEndColIndex;this.firstRowIndex = firstRowIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {int rowCount = this.firstRowIndex;for (Integer count : exportFieldGroupCountList) {if (count == 1) {rowCount += count;continue;}// 合并單元格CellRangeAddress cellRangeAddress;for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);sheet.addMergedRegionUnsafe(cellRangeAddress);}rowCount += count;}}}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的行數** @param exportDataList* @return*/private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());if (equals) {count++;} else {groupCountList.add(count);count = 1;}}// 處理完最后一條后groupCountList.add(count);return groupCountList;} }

4.4 拉長單元格并查看導出效果

未合并的效果。

合并后的效果。

---

5 第二次導出(完整代碼)：以第一次導出的excel流，作為第二次導出的模板

5.1 配置文件

5.1.1 pom.xml 依賴

主要用到EasyExcel、Hutool、Lombok。

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.0.5</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.8</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency> </dependencies>

5.1.2 Excel 模板文件

Excel模板文件路徑在：/resources/template/screenTemplate.xlsx，文件內容同4.1。

5.2 controller

5.2.1 ScreenController

import com.example.demo.service.ScreenService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException;/*** @author Cauli* @date 2022/12/1 9:40* @description 控制層*/ @RestController @RequestMapping("/screen") public class ScreenController {@Autowiredprivate ScreenService screenService;@GetMapping(value = "/export")public void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {screenService.export(httpServletRequest, httpServletResponse);} }

?5.3 service

5.3.1 ScreenServiceImpl

import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.enums.WriteDirectionEnum; import com.alibaba.excel.write.metadata.WriteSheet; import com.alibaba.excel.write.metadata.fill.FillConfig; import com.alibaba.excel.write.metadata.fill.FillWrapper; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import com.example.demo.dto.ScreenExcelDTO; import com.example.demo.dto.ScreenGatherDTO; import com.example.demo.dto.ScreenValueExcelDTO; import com.example.demo.service.ScreenService; import com.example.demo.strategy.ScreenScoreHeaderMergeStrategy; import com.example.demo.strategy.ScreenScoreValueHorizontalMergeStrategy; import com.example.demo.strategy.ScreenScoreValueMergeStrategy; import com.example.demo.strategy.ScreenValueMergeStrategy; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.collections4.MapUtils; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.springframework.core.io.ClassPathResource; import org.springframework.http.HttpHeaders; import org.springframework.stereotype.Service;import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map;/*** @author Cauli|* @date: 2022/12/1 9:47* @description: 業務實現層*/ @Service public class ScreenServiceImpl implements ScreenService {@Overridepublic void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse) {// HttpServletResponse消息頭參數設置this.setHttpServletResponse(httpServletResponse);// 通過ClassPathResource獲取/resources/template下的模板文件ClassPathResource classPathResource = new ClassPathResource("template/screenTemplate.xlsx");// 需要導出的臨時模板文件InputStream screenTemporaryTemplate = null;// 這里用try-with-resourcetry (// 獲取模板文件InputStream screenParamTemplateFileName = classPathResource.getInputStream();OutputStream screenOut = httpServletResponse.getOutputStream();BufferedOutputStream screenBos = new BufferedOutputStream(screenOut);ByteArrayOutputStream screenTemplateOut = new ByteArrayOutputStream();) {// --------------------------------基本配置--------------------------------// 設置內容的策略WriteCellStyle contentWriteCellStyle = this.getWriteCellStyle();// 配置橫向填充FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();// sheet名稱，注意要和Excel模板中的工作表名稱相同，不然無法導出數據WriteSheet writeSheet = EasyExcel.writerSheet("sheet0").build();// --------------------------------基本配置--------------------------------// ---------------------模擬獲取第一部分的表格數據、表頭參數---------------------// 構造第一部分產品數據List<ScreenGatherDTO> screenGatherDTOList = this.getScreenGatherDTOList();// 填充第一個表頭的單元格List<ScreenValueExcelDTO> screenValueExcelDTOList = this.getScreenValueExcelDTOList();// 在屏規格末尾加上表頭模板參數List<ScreenValueExcelDTO> screenTableExcelDTOList = this.getScreenTableExcelDTOList();// ---------------------模擬獲取第一部分的表格數據、表頭參數---------------------// --------------------------------第一次導出--------------------------------ExcelWriter screenTemplateExcelWriter = EasyExcel.write(screenBos) // 導出臨時文件，使用的是BufferedOutputStream // .write(screenTemplateOut) // 導出最終臨時文件，使用的是ByteArrayOutputStream.withTemplate(screenParamTemplateFileName) // 使用的模板.registerWriteHandler(new ScreenValueMergeStrategy(screenGatherDTOList, 1, 6, 5)) // 自定義單元格合并策略.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置內容策略，頭部為null.build();// 填充屏規格表格數據screenTemplateExcelWriter.fill(new FillWrapper("screenGatherDTOList", screenGatherDTOList), writeSheet);// 填充第一個表頭的單元格screenTemplateExcelWriter.fill(new FillWrapper("screenValueExcelDTOList", screenValueExcelDTOList), writeSheet);// 填充表頭模板參數screenTemplateExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenTableExcelDTOList), writeSheet);screenTemplateExcelWriter.finish();// excel導出成流byte[] bytes = screenTemplateOut.toByteArray();screenTemporaryTemplate = new ByteArrayInputStream(bytes);// --------------------------------第一次導出--------------------------------// --------------------------模擬獲取第二部分數據--------------------------// 模擬獲取第二部分的表頭數據List<ScreenExcelDTO> screenScoreExcelDTOList = this.getScoreExcelDTOList();// 模擬獲取第二部分的表格數據Map<String, List<ScreenValueExcelDTO>> screenScoreMap = this.getScreenScoreMap(screenGatherDTOList);// --------------------------模擬獲取第二部分的數據--------------------------// --------------------------------第二次導出--------------------------------// 測評模型表格數據開始索引int screenScoreCostFirstColumnIndex = 7;// 測評模型表格數據結束索引int screenScoreTargetEndColIndex = screenScoreCostFirstColumnIndex + screenScoreMap.get("screenValueTemplateParam0").size() - 1;// 導出最終文件ExcelWriter screenScoreExcelWriter = EasyExcel.write(screenBos) // 導出最終文件.withTemplate(screenTemporaryTemplate) // 以第一次導出的excel流，作為第二次導出的模板.registerWriteHandler(new ScreenScoreHeaderMergeStrategy(0, screenScoreExcelDTOList, 1, screenScoreCostFirstColumnIndex)) // 表頭（模塊）合并策略.registerWriteHandler(new ScreenScoreHeaderMergeStrategy(1, screenScoreExcelDTOList, 2, screenScoreCostFirstColumnIndex)) // 表頭合并策略.registerWriteHandler(new ScreenScoreValueMergeStrategy(screenGatherDTOList, screenScoreCostFirstColumnIndex, screenScoreTargetEndColIndex, 5)) // 表格數據合并策略.registerWriteHandler(new ScreenScoreValueHorizontalMergeStrategy(screenGatherDTOList, screenScoreExcelDTOList, screenScoreCostFirstColumnIndex)) // 表格數據橫向合并策略.registerWriteHandler(new HorizontalCellStyleStrategy(null, contentWriteCellStyle)) // 只配置內容策略，頭部為null.build();// 填充測評模型表頭數據screenScoreExcelWriter.fill(new FillWrapper("screenTableExcelDTOList", screenScoreExcelDTOList), fillConfig, writeSheet);// 填充測評模型表格數據screenScoreMap.forEach((k, v) -> screenScoreExcelWriter.fill(new FillWrapper(k, v), fillConfig, writeSheet));screenScoreExcelWriter.finish();// --------------------------------第二次導出--------------------------------} catch (IOException e) {throw new RuntimeException(e);} finally {// 防止出現異常，導致流關閉失敗if (screenTemporaryTemplate != null) {try {screenTemporaryTemplate.close();} catch (IOException e) {throw new RuntimeException(e);}}}}/*** 模擬獲取第二部分的表格數據** @param screenGatherDTOList* @return*/private Map<String, List<ScreenValueExcelDTO>> getScreenScoreMap(List<ScreenGatherDTO> screenGatherDTOList) {// 模擬這部分數據的字段沒有落表，采用Map存儲和處理數據Map<String, List<ScreenValueExcelDTO>> screenScoreMap = new LinkedHashMap<>();if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {for (int j = 0; j < screenGatherDTOList.size(); j++) {Map<String, String> map = new LinkedHashMap<>();if ((j + 1) % 4 == 0) {map.put("項目", "總得分");} else if ((j + 1) % 3 == 0) {map.put("項目", "總權重得分");} else {map.put("項目", "實測值");}for (int i = 1; i <= 1; i++) {map.put("光學測試子項" + i, "1");}for (int i = 1; i <= 2; i++) {map.put("電學測試子項" + i, "2");}for (int i = 1; i <= 3; i++) {map.put("聲學測試子項" + i, "3");}screenGatherDTOList.get(j).setScoreMap(map);}for (int i = 0; i < screenGatherDTOList.size(); i++) {List<ScreenValueExcelDTO> valueExcelDTOList = new ArrayList<>();Map<String, String> scoreMap = screenGatherDTOList.get(i).getScoreMap();if (MapUtils.isNotEmpty(scoreMap)) {scoreMap.forEach((k, v) -> {ScreenValueExcelDTO valueExcelDTO = new ScreenValueExcelDTO();valueExcelDTO.setValue(v);valueExcelDTOList.add(valueExcelDTO);});}// 填充表格數據screenScoreMap.put("screenValueTemplateParam" + i, valueExcelDTOList);}}return screenScoreMap;}/*** 模擬獲取第二部分的表頭數據** @return*/private List<ScreenExcelDTO> getScoreExcelDTOList() {List<ScreenExcelDTO> screenScoreExcelDTOList = new ArrayList<>();for (int i = 1; i <= 1; i++) {ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();screenExcelDTO.setModelName("產品測試");screenExcelDTO.setTestItemCategory("光學");screenExcelDTO.setTestItemName("光學");screenExcelDTO.setSubTestItemName("光學測試子項" + i);screenScoreExcelDTOList.add(screenExcelDTO);}for (int i = 1; i <= 2; i++) {ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();screenExcelDTO.setModelName("產品測試");screenExcelDTO.setTestItemCategory("電學");screenExcelDTO.setTestItemName("電學");screenExcelDTO.setSubTestItemName("電學測試子項" + i);screenScoreExcelDTOList.add(screenExcelDTO);}for (int i = 1; i <= 3; i++) {ScreenExcelDTO screenExcelDTO = new ScreenExcelDTO();screenExcelDTO.setModelName("產品測試");screenExcelDTO.setTestItemCategory("聲學");screenExcelDTO.setTestItemName("聲學");screenExcelDTO.setSubTestItemName("聲學測試子項" + i);screenScoreExcelDTOList.add(screenExcelDTO);}return screenScoreExcelDTOList;}/*** 在屏規格末尾加上表頭模板參數** @return*/private List<ScreenValueExcelDTO> getScreenTableExcelDTOList() {List<ScreenValueExcelDTO> screenTableExcelDTOList = new ArrayList<>();for (int i = 0; i < 4; i++) {ScreenValueExcelDTO screenTableExcelDTO = new ScreenValueExcelDTO();switch (i) {case 0:screenTableExcelDTO.setValue("{screenTableExcelDTOList.modelName}");break;case 1:screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemCategory}");break;case 2:screenTableExcelDTO.setValue("{screenTableExcelDTOList.testItemName}");break;case 3:screenTableExcelDTO.setValue("{screenTableExcelDTOList.subTestItemName}");break;default:break;}screenTableExcelDTOList.add(screenTableExcelDTO);}return screenTableExcelDTOList;}/*** 填充第一個表頭的單元格** @return*/private List<ScreenValueExcelDTO> getScreenValueExcelDTOList() {ScreenValueExcelDTO screenValueExcelDTO = new ScreenValueExcelDTO();List<ScreenValueExcelDTO> screenValueExcelDTOList = new ArrayList<>();screenValueExcelDTO.setValue("產品測試");screenValueExcelDTOList.add(screenValueExcelDTO);return screenValueExcelDTOList;}/*** 構造第一部分產品數據** @return*/private List<ScreenGatherDTO> getScreenGatherDTOList() {List<ScreenGatherDTO> screenGatherDTOList = new ArrayList<>();// 構造5個產品數據for (int i = 1; i <= 5; i++) {// 每份數據乘以4，為了合并單元格做準備for (int j = 0; j < 4; j++) {ScreenGatherDTO screenGatherDTO = new ScreenGatherDTO();screenGatherDTO.setScreenSize(String.valueOf(i * 10));screenGatherDTO.setSupplier("廠商" + i);screenGatherDTO.setPartMode("型號" + i);screenGatherDTO.setResolution("1080P");screenGatherDTO.setRefreshRate("60Hz");screenGatherDTO.setPanel("IPS");screenGatherDTOList.add(screenGatherDTO);}}if (CollectionUtils.isNotEmpty(screenGatherDTOList)) {for (int i = 0; i < screenGatherDTOList.size(); i++) {// 在屏規格末尾加上表格模板參數screenGatherDTOList.get(i).setValueTemplateParam("{screenValueTemplateParam" + i + ".value}");}}return screenGatherDTOList;}/*** 設置內容的策略** @return*/private WriteCellStyle getWriteCellStyle() {WriteCellStyle contentWriteCellStyle = new WriteCellStyle();// 設置內容水平居中對齊contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 設置內容垂直居中對齊contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 自動換行contentWriteCellStyle.setWrapped(true);// 設置字體樣式和大小WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontHeightInPoints((short) 12);contentWriteFont.setFontName("微軟雅黑");contentWriteCellStyle.setWriteFont(contentWriteFont);return contentWriteCellStyle;}/*** HttpServletResponse消息頭參數設置** @param httpServletResponse*/private void setHttpServletResponse(HttpServletResponse httpServletResponse) {String filename = "exportFile.xlsx";httpServletResponse.addHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + filename);httpServletResponse.setContentType("application/octet-stream;charset=UTF-8");httpServletResponse.addHeader("Pragma", "no-cache");httpServletResponse.addHeader("Cache-Control", "no-cache");} }

5.3.2 ScreenService

import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.IOException;/*** @author Cauli* @date 2022/12/1 9:47* @description 業務層*/ public interface ScreenService {/*** 導出** @param httpServletRequest* @param httpServletResponse*/void export(HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse); }

?5.4 strategy

5.4.1 ScreenValueMergeStrategy

import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.example.demo.dto.ScreenGatherDTO; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.util.CollectionUtils;import java.util.ArrayList; import java.util.List;/*** @author yicheng1.he* @date 2022/12/1 14:06* @description 第一部分表格數據合并策略*/ public class ScreenValueMergeStrategy extends AbstractMergeStrategy {/*** 分組，每幾行合并一次*/private List<Integer> exportFieldGroupCountList;/*** 合并的目標開始列索引*/private Integer targetBeginColIndex;/*** 合并的目標結束列索引*/private Integer targetEndColIndex;/*** 需要開始合并單元格的首行索引*/private Integer firstRowIndex;public ScreenValueMergeStrategy() {}/*** @param exportDataList 待合并目標行的值* @param targetBeginColIndex 合并的目標開始列索引* @param targetEndColIndex 合并的目標結束列索引* @param firstRowIndex 需要開始合并單元格的首行索引*/public ScreenValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {this.exportFieldGroupCountList = getGroupCountList(exportDataList);this.targetBeginColIndex = targetBeginColIndex;this.targetEndColIndex = targetEndColIndex;this.firstRowIndex = firstRowIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {int rowCount = this.firstRowIndex;for (Integer count : exportFieldGroupCountList) {if (count == 1) {rowCount += count;continue;}// 合并單元格CellRangeAddress cellRangeAddress;for (int i = 0; i < targetEndColIndex - targetBeginColIndex + 1; i++) {cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount + count - 2, i, i);sheet.addMergedRegionUnsafe(cellRangeAddress);}rowCount += count;}}}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的行數** @param exportDataList* @return*/private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());if (equals) {count++;} else {groupCountList.add(count);count = 1;}}// 處理完最后一條后groupCountList.add(count);return groupCountList;} }

5.4.2 ScreenScoreHeaderMergeStrategy

import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.example.demo.dto.ScreenExcelDTO; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.util.CollectionUtils;import java.util.ArrayList; import java.util.List;/*** @author Cauli* @date 2022/12/1 15:51* @description 第二部分表頭合并策略*/ public class ScreenScoreHeaderMergeStrategy extends AbstractMergeStrategy {/*** 分組，每幾列合并一次*/private List<Integer> exportFieldGroupCountList;/*** 目標合并行index*/private Integer targetRowIndex;/*** 需要開始合并單元格的首列index*/private Integer firstColumnIndex;/*** 0:表頭 1：分類 2：測試項*/private Integer mergeMark;/*** 額外隊尾數量*/private static final Integer TAILS_LENGTH = 2;/*** 表頭第零行索引*/private static final Integer HEADER_ROW_ZERO = 0;/*** 表頭第一行索引*/private static final Integer HEADER_ROW_ONE = 1;/*** 表頭第二行索引*/private static final Integer HEADER_ROW_TWO = 2;public ScreenScoreHeaderMergeStrategy() {}/*** @param mergeMark 0:表頭 1：分類 2：測試項* @param exportDataList 為待合并目標列的值* @param targetRowIndex 合并的目標行索引* @param firstColumnIndex 需要開始合并單元格的首列索引*/public ScreenScoreHeaderMergeStrategy(Integer mergeMark, List<ScreenExcelDTO> exportDataList, Integer targetRowIndex, Integer firstColumnIndex) {this.mergeMark = mergeMark;this.exportFieldGroupCountList = getGroupCountList(exportDataList);this.targetRowIndex = targetRowIndex;this.firstColumnIndex = firstColumnIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getColumnIndex() == this.firstColumnIndex && cell.getRowIndex() == targetRowIndex - 1) {int columnCount = this.firstColumnIndex;for (Integer count : exportFieldGroupCountList) {// 合并單元格CellRangeAddress cellRangeAddress = null;if (mergeMark == 0) {cellRangeAddress = new CellRangeAddress(targetRowIndex - 1, targetRowIndex - 1, columnCount - 1, columnCount + count - 1);} else if (mergeMark == 1) {if (count == 1) {cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount);} else {cellRangeAddress = new CellRangeAddress(HEADER_ROW_ONE, HEADER_ROW_TWO, columnCount, columnCount + count - 1);}}sheet.addMergedRegionUnsafe(cellRangeAddress);columnCount += count;}}}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的列數** @param exportDataList* @return*/private List<Integer> getGroupCountList(List<ScreenExcelDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = false;if (0 == mergeMark) {equals = exportDataList.get(i).getModelName().equals(exportDataList.get(i - 1).getModelName());} else {equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());}if (equals) {count++;} else {groupCountList.add(count);count = 1;}}// 處理完最后一條后groupCountList.add(count);return groupCountList;} }

4.5.3 ScreenScoreValueMergeStrategy

import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.example.demo.dto.ScreenGatherDTO; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.util.CollectionUtils;import java.util.ArrayList; import java.util.List;/*** @author yicheng1.he* @date 2022/12/1 14:06* @description 第二部分表格數據合并策略*/ public class ScreenScoreValueMergeStrategy extends AbstractMergeStrategy {/*** 分組，每幾行合并一次*/private List<Integer> exportFieldGroupCountList;/*** 合并的目標開始列索引*/private Integer targetBeginColIndex;/*** 合并的目標結束列索引*/private Integer targetEndColIndex;/*** 需要開始合并單元格的首行索引*/private Integer firstRowIndex;/*** 額外的長度為2*/private static final Integer ADDITIONAL_LENGTH = 2;public ScreenScoreValueMergeStrategy() {}/*** @param exportDataList 待合并目標行的值* @param targetBeginColIndex 合并的目標開始列* @param targetEndColIndex 合并的目標結束列索引* @param firstRowIndex 需要開始合并單元格的首行索引*/public ScreenScoreValueMergeStrategy(List<ScreenGatherDTO> exportDataList, Integer targetBeginColIndex, Integer targetEndColIndex, Integer firstRowIndex) {this.exportFieldGroupCountList = getGroupCountList(exportDataList);this.targetBeginColIndex = targetBeginColIndex;this.targetEndColIndex = targetEndColIndex;this.firstRowIndex = firstRowIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getRowIndex() == this.firstRowIndex && cell.getColumnIndex() >= targetBeginColIndex - 1 && cell.getColumnIndex() <= targetEndColIndex - 1) {int rowCount = this.firstRowIndex;for (Integer count : exportFieldGroupCountList) {if (count == 1) {rowCount += count;continue;}// 合并單元格CellRangeAddress cellRangeAddress;for (int i = targetBeginColIndex - 1; i <= targetEndColIndex - 1; i++) {cellRangeAddress = new CellRangeAddress(rowCount - 1, rowCount - 1 + count - 1, i, i);sheet.addMergedRegionUnsafe(cellRangeAddress);}rowCount += count;}}}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的行數** @param exportDataList* @return*/private List<Integer> getGroupCountList(List<ScreenGatherDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());if (equals) {count++;} else {groupCountList.add(count - ADDITIONAL_LENGTH);groupCountList.add(1);groupCountList.add(1);count = 1;}}// 處理完最后一條后groupCountList.add(count - ADDITIONAL_LENGTH);groupCountList.add(1);groupCountList.add(1);return groupCountList;} }

5.4.3 ScreenScoreValueHorizontalMergeStrategy

import com.alibaba.excel.metadata.Head; import com.alibaba.excel.write.merge.AbstractMergeStrategy; import com.example.demo.dto.ScreenExcelDTO; import com.example.demo.dto.ScreenGatherDTO; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.springframework.util.CollectionUtils;import java.util.ArrayList; import java.util.List;/*** @author yicheng1.he* @date 2022/12/1 15:51* @description 第二部分表頭橫向合并策略*/ public class ScreenScoreValueHorizontalMergeStrategy extends AbstractMergeStrategy {/*** 分組，每幾行合并一次*/private List<Integer> rowFieldGroupCountList;/*** 分組，每幾列合并一次*/private List<Integer> colFieldGroupCountList;/*** 目標合并列索引*/private Integer targetColIndex;/*** 需要合并的行索引*/private Integer targetRowIndex;/*** 需要開始合并單元格的首列索引*/private Integer firstColumnIndex;/*** 表格數據第一行索引*/private static final Integer FIRST_ROW_INDEX = 5;/*** 額外的長度為2*/private static final Integer ADDITIONAL_LENGTH = 2;public ScreenScoreValueHorizontalMergeStrategy() {}/*** @param screenGatherDTOList 為待合并目標行的值* @param screenScoreExcelDTOList 為待合并目標列的值* @param firstColumnIndex 需要開始合并單元格的首列索引*/public ScreenScoreValueHorizontalMergeStrategy(List<ScreenGatherDTO> screenGatherDTOList, List<ScreenExcelDTO> screenScoreExcelDTOList, Integer firstColumnIndex) {this.rowFieldGroupCountList = getRowGroupCountList(screenGatherDTOList);this.colFieldGroupCountList = getColGroupCountList(screenScoreExcelDTOList);this.firstColumnIndex = firstColumnIndex;this.targetRowIndex = FIRST_ROW_INDEX;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (cell.getColumnIndex() == this.firstColumnIndex) {int columnCount = this.firstColumnIndex;// 列合并for (int i = 0; i < colFieldGroupCountList.size(); i++) {Integer count = colFieldGroupCountList.get(i);if (count == 1) {columnCount += count;continue;}// 行合并int rowIndexCount = 0;for (int j = 0; j < rowFieldGroupCountList.size(); j++) {if (j == 0) {rowIndexCount += this.targetRowIndex + rowFieldGroupCountList.get(j);} else {rowIndexCount += rowFieldGroupCountList.get(j);}CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH - 1, rowIndexCount - ADDITIONAL_LENGTH - 1, columnCount, columnCount + count - 1);sheet.addMergedRegionUnsafe(cellRangeAddress);cellRangeAddress = new CellRangeAddress(rowIndexCount - ADDITIONAL_LENGTH, rowIndexCount - ADDITIONAL_LENGTH, columnCount, columnCount + count - 1);sheet.addMergedRegionUnsafe(cellRangeAddress);}columnCount += count;}}}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的列數* 防止每個產品行數不同、不一定是4行的情況，該方法計算出每個產品的行數** @param exportDataList* @return*/private List<Integer> getRowGroupCountList(List<ScreenGatherDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = exportDataList.get(i).getPartMode().equals(exportDataList.get(i - 1).getPartMode());if (equals) {count++;} else {groupCountList.add(count);count = 1;}}// 處理完最后一條后groupCountList.add(count);return groupCountList;}/*** 該方法將目標列根據值是否相同連續可合并，存儲可合并的列數** @param exportDataList* @return*/private List<Integer> getColGroupCountList(List<ScreenExcelDTO> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {boolean equals = exportDataList.get(i).getTestItemName().equals(exportDataList.get(i - 1).getTestItemName());if (equals) {count++;} else {groupCountList.add(count);count = 1;}}// 處理完最后一條后groupCountList.add(count);return groupCountList;} }

5.5 dto

5.5.1 ScreenExcelDTO

import lombok.Data; import lombok.ToString;import java.io.Serializable;/*** @author Cauli* @date 2022/12/1 9:48* @description excel模板參數返回數據模型*/ @Data @ToString public class ScreenExcelDTO implements Serializable {/*** 模塊名稱*/private String modelName;/*** 測試項名稱*/private String testItemName;/*** 測試子項名稱*/private String subTestItemName;/*** 分類名稱*/private String testItemCategory;/*** 測試項值*/private String testItemValue; }

5.5.2 ScreenGatherDTO

import lombok.Data; import lombok.ToString;import java.util.Map;/*** @author Cauli* @date 2022/12/1 15:10* @description 查詢返回數據模型*/ @Data @ToString public class ScreenGatherDTO {/*** 產品型號*/private String partMode;/*** 廠商*/private String supplier;/*** 屏幕尺寸*/private String screenSize;/*** 屏幕分辨率*/private String resolution;/*** 屏幕刷新率*/private String refreshRate;/*** 面板屬性*/private String panel;/*** 用于存儲得分模塊map*/private Map<String, String> scoreMap;/*** 表頭數據模板參數*/private String valueTemplateParam;/*** 表格數據模板參數*/private String headerTemplateParam; }

5.5.3 ScreenValueExcelDTO

import java.io.Serializable;/*** @author Cauli* @date: 2022/12/1 20:20* @description: excel值返回數據模型*/ public class ScreenValueExcelDTO implements Serializable {private String value;public String getValue() {return value;}public void setValue(String value) {this.value = value;}@Overridepublic String toString() {return "ScreenValueExcelDTO{" +"value='" + value + '\'' +'}';} }

5.6 項目目錄結構

---

📋6 最終導出效果

📫7 代碼倉庫

代碼量比較多，建議下載Demo進行查看。

EasyExcel-Export-Demo | Gitee

總結

以上是生活随笔為你收集整理的基于EasyExcel模板填充方式进行二次导出（动态表头、合并单元格问题处理）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。