easyExcel合并单元格导出

一、导入maven依赖

（很多旧项目自定义了一套Excel导出工具，poi版本可能不兼容，一般poi新旧版本不兼容分界线在3.17，选择3.17版本不会发生代码不兼容情况）

        <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version><exclusions><exclusion><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId></exclusion></exclusions></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version><classifier>sources</classifier></dependency><dependency><groupId>org.apache.commons</groupId><artifactId>commons-collections4</artifactId><version>4.1</version></dependency>

二、重写easyExcel-自定义写入处理器

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;
import java.util.List;/*** 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法* @author reshui* @date 2023/9/4**/
public class CustomMergeStrategy extends AbstractMergeStrategy {/*** 分组，每几行合并一次*/private List<Integer> exportFieldGroupCountList;/*** 目标合并列index*/private Integer targetColumnIndex;/*** 需要开始合并单元格的首行index*/private Integer rowIndex;/*** @param exportDataList exportDataList为待合并目标列的值* @param targetColumnIndex 需要合并的列* @return {@code  }* @author reshui* @date 2023/09/05*/public CustomMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) {this.exportFieldGroupCountList = getGroupCountList(exportDataList);this.targetColumnIndex = targetColumnIndex;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {if (null == rowIndex) {rowIndex = cell.getRowIndex();}// 仅从首行以及目标列的单元格开始合并，忽略其他if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) {mergeGroupColumn(sheet);}}private void mergeGroupColumn(Sheet sheet) {int rowCount = rowIndex;for (Integer count : exportFieldGroupCountList) {if (count == 1) {rowCount += count;continue;}// 合并单元格CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex);sheet.addMergedRegionUnsafe(cellRangeAddress);rowCount += count;}}/*** 该方法将目标列根据值是否相同连续可合并，存储可合并的行数* @param exportDataList* @return {@code List<Integer> }* @author reshui* @date 2023/09/05*/private List<Integer> getGroupCountList(List<String> exportDataList) {if (CollectionUtils.isEmpty(exportDataList)) {return new ArrayList<>();}List<Integer> groupCountList = new ArrayList<>();int count = 1;for (int i = 1; i < exportDataList.size(); i++) {if (exportDataList.get(i).equals(exportDataList.get(i - 1))) {count++;} else {groupCountList.add(count);count = 1;}}// 处理完最后一条后groupCountList.add(count);return groupCountList;}}

三、导出工具类封装

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;/*** 下载excel文件工具** @author reshui* @date 2023/09/05*/
public class DownloadExcelUtil {private static final Logger log = LoggerFactory.getLogger(DownloadExcelUtil.class);private final static String separatorChar = "-";public static <T> void downloadFile(HttpServletResponse response, Class<T> clazz, String data) throws IOException {String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");String fileName = timeStamp + "-log";downloadFile(response, clazz, data, fileName, "数据", null);}/*** @param response         响应请求* @param clazz            导出数据类型* @param data             数据源* @param customFileName   文件名* @param sheetName        页名* @param writeHandlerList 自定义写入处理器* @return {@code T }* @author reshui* @date 2023/09/05*/public static <T> T downloadFile(HttpServletResponse response, Class<T> clazz, String data, String customFileName, String sheetName, List<WriteHandler> writeHandlerList) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmantry {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easy-excel没有关系String fileName = URLEncoder.encode(customFileName, "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 这里需要设置不关闭流ExcelWriterSheetBuilder writerSheetBuilder = EasyExcel.write(response.getOutputStream(), clazz).autoCloseStream(Boolean.FALSE).sheet(sheetName);if (CollUtil.isNotEmpty(writeHandlerList)) {for (WriteHandler writeHandler : writeHandlerList) {writerSheetBuilder.registerWriteHandler(writeHandler);}}writerSheetBuilder.doWrite(JSONObject.parseArray(data, clazz));} catch (Exception e) {// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = new HashMap<>(2);map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());response.getWriter().println(JSON.toJSONString(map));}return null;}/*** 出把excel** @param timeStamp       时间戳* @param excelFileName   excel文件名字* @param headClassType   头类类型* @param resultExcelList 结果excel表* @param filePath        文件路径* @author reshui* @date 2023/02/15*/public static void outputExcelToLocal(String timeStamp, String excelFileName, Class headClassType, List resultExcelList, String filePath) {//文件时间戳timeStamp = Objects.nonNull(timeStamp) ? timeStamp : StrUtil.EMPTY;String partFileName = filePath + File.separator + excelFileName + separatorChar + timeStamp + "-log.xlsx";FileUtil.touch(partFileName);EasyExcel.write(partFileName, headClassType).sheet("源数据").doWrite(resultExcelList);}/*** 简单把excel** @param excelFileName   excel文件名字* @param headClassType   头类类型* @param resultExcelList 结果excel表* @param filePath        文件路径* @author reshui* @date 2023/02/15*/public static void easyOutputExcelToLocal(String excelFileName, Class headClassType, List resultExcelList, String filePath) {String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");outputExcelToLocal(timeStamp, excelFileName, headClassType, resultExcelList, filePath);}public static void main(String[] args) {String timeStamp = DateUtil.format(DateUtil.date(), "yyyyMMddHHmmss");String titleTmpDirPath = FileUtil.getTmpDirPath() + File.separator + "test" + File.separator + timeStamp + File.separator;try {System.out.println("日志存储地址-[" + titleTmpDirPath + "]");log.info("日志存储[" + titleTmpDirPath + "]");String partFileName = titleTmpDirPath + timeStamp + "-log.xlsx";FileUtil.touch(partFileName);EasyExcel.write(partFileName, String.class).sheet("源数据").doWrite(null);} catch (Exception e) {log.error("日志存储[" + titleTmpDirPath + "]" + "-error:", e);}}
}

四、运行

@RestController
@RequestMapping("/check")
public class TestController {@RequestMapping(value = "/run1",method = {RequestMethod.GET})public void run1(HttpServletResponse response) throws IOException {List<DemoData> demoDataList = data1();List<WriteHandler> customMergeStrategies = Arrays.asList(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getName).collect(Collectors.toList()), 1));DownloadExcelUtil.downloadFile(response,DemoData.class, JSONObject.toJSONString(demoDataList),"测试","页1", customMergeStrategies);}public static List<DemoData> data1(){List<DemoData> demoDataList = new ArrayList<>();DemoData demoData0 = new DemoData();demoData0.setId("0");demoData0.setName("hello0");demoDataList.add(demoData0);DemoData demoData1 = new DemoData();demoData1.setId("1");demoData1.setName("hello1");demoDataList.add(demoData1);DemoData demoData11 = new DemoData();demoData11.setId("1");demoData11.setName("hello1");demoDataList.add(demoData11);DemoData demoData2 = new DemoData();demoData2.setId("2");demoData2.setName("hello2");demoDataList.add(demoData2);return demoDataList;}
}