1.需求：需要导出的EXCEL示例：
2.依赖：
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.5</version></dependency>
3.工具类：
package com.minex.web.device.utils;import com.minex.web.device.entity.vo.ReadRecordsVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;@Component
public class ExcelExporter {private static final String DEFAULT_SHEET_NAME = "Sheet1";public void export(HttpServletResponse response, Map<String, List<ReadRecordsVO>> sheetDataMap,String fileName) throws IOException {try (Workbook workbook = new XSSFWorkbook()) {// 创建并写入多个Sheet页
// Map<String, List<ReadRecordsVO>> sheetDataMap = splitDataIntoSheets(data);for (String sheetName : sheetDataMap.keySet()) {Sheet sheet = createSheet(workbook, sheetName);writeDataToSheet(sheet, sheetDataMap.get(sheetName));}// 设置响应头以触发浏览器下载String utf8EncodedFilename = URLEncoder.encode(fileName, StandardCharsets.UTF_8);response.setHeader("Content-Disposition","attachment; filename*=utf-8''" + utf8EncodedFilename+".xlsx");// 响应类型,编码response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setCharacterEncoding("utf-8");// 将工作簿写入响应输出流try (OutputStream os = response.getOutputStream()) {workbook.write(os);}}}private Sheet createSheet(Workbook workbook, String sheetName) {return workbook.createSheet(sheetName);}private void writeDataToSheet(Sheet sheet, List<ReadRecordsVO> data) {// 获取所有列信息和它们对应的父级表头Map<String, String> headerToParentHeaderMap = getHeadersWithParent(data);// 写入表头Row headerRow = sheet.createRow(0);Row subHeaderRow = sheet.createRow(1);int columnIndex = 0;Map<String, Integer> startColumnIndexMap = new HashMap<>();Map<String, Integer> endColumnIndexMap = new HashMap<>();for (String header : headerToParentHeaderMap.keySet()) {String parentHeader = headerToParentHeaderMap.get(header);// 记录每个父级表头的起始列索引startColumnIndexMap.putIfAbsent(parentHeader, columnIndex);Cell cell = subHeaderRow.createCell(columnIndex++);cell.setCellValue(header);// 更新每个父级表头的最后一列索引endColumnIndexMap.put(parentHeader, columnIndex - 1);}// 合并第一行相同父级表头的单元格mergeCells(sheet, startColumnIndexMap, endColumnIndexMap);// 写入父级表头（第一行）columnIndex = 0;for (String parentHeader : startColumnIndexMap.keySet()) {Cell cell = headerRow.createCell(startColumnIndexMap.get(parentHeader));cell.setCellValue(parentHeader);}// 写入数据行writeDataRow(sheet, data, headerToParentHeaderMap.keySet(), 2);// 自动调整列宽autoSizeColumns(sheet, headerToParentHeaderMap.size());}private void writeDataRow(Sheet sheet, List<ReadRecordsVO> data, Set<String> allHeaders, int rowIndex) {for (ReadRecordsVO record : data) {Row row = sheet.createRow(rowIndex++);int columnIndex = 0;for (String header : allHeaders) {Cell cell = row.createCell(columnIndex++);record.getRecordItems().stream().filter(item -> item.getHeader().equals(header)).findFirst().ifPresentOrElse(item -> cell.setCellValue(item.getValue()),() -> cell.setCellValue(""));}}}private void autoSizeColumns(Sheet sheet, int columnCount) {for (int i = 0; i < columnCount; i++) {sheet.autoSizeColumn(i);}}private Map<String, String> getHeadersWithParent(List<ReadRecordsVO> data) {Map<String, String> headerToParentHeaderMap = new LinkedHashMap<>();for (ReadRecordsVO record : data) {for (ReadRecordsVO.RecordItem item : record.getRecordItems()) {headerToParentHeaderMap.putIfAbsent(item.getHeader(), item.getParentHeader());}}return headerToParentHeaderMap;}private void mergeCells(Sheet sheet, Map<String, Integer> startColumnIndexMap, Map<String, Integer> endColumnIndexMap) {Set<String> mergedParents = new HashSet<>();for (String parentHeader : startColumnIndexMap.keySet()) {if (!mergedParents.contains(parentHeader)) {int startColumnIndex = startColumnIndexMap.get(parentHeader);int endColumnIndex = endColumnIndexMap.getOrDefault(parentHeader, startColumnIndex);if (startColumnIndex != endColumnIndex) {sheet.addMergedRegion(new CellRangeAddress(0, 0, startColumnIndex, endColumnIndex));}mergedParents.add(parentHeader);}}}private Map<String, List<ReadRecordsVO>> splitDataIntoSheets(List<ReadRecordsVO> data) {// 根据业务逻辑分割数据到不同的Sheet页中// 这里简单地将所有数据放入一个名为DEFAULT_SHEET_NAME的Sheet页中Map<String, List<ReadRecordsVO>> result = new HashMap<>();result.put(DEFAULT_SHEET_NAME, data);return result;}
}
4.controller调用工具类导出
@PostMapping("/export")public void export(@RequestBody SubstationMeterReadRecordListRO query,HttpServletResponse response,@ApiIgnore CurrentUser user) throws IOException {List<ReadRecordsVO> dataList = substationMeterReadRecordService.readRecords(query, user);if (dataList.isEmpty()) {throw ExceptionFactory.warnBizException("没有数据可导出");}Map<String, List<ReadRecordsVO>> listMap = dataList.stream().collect(Collectors.groupingBy(ReadRecordsVO::getRecordDate));excelExporter.export(response, listMap, dataList.get(0).getSubstationName() + "抄表记录");}
5.数据类
package com.minex.web.device.entity.vo;import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;import java.util.Date;
import java.util.List;@Data
public class ReadRecordsVO {private String substationName;private String recordDate;private List<RecordItem> recordItems;@Data@AllArgsConstructor@NoArgsConstructorpublic static class RecordItem {private Integer order = 0;private String parentHeader;private String header;private String value;}
}
6.运行效果
