easyExcel合并单元格导出-Toy模板网

这篇具有很好参考价值的文章主要介绍了easyExcel合并单元格导出。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

效果图

easyExcel合并单元格导出,SpringBoot,spring boot,java,easyExcel

一、导入maven依赖

（很多旧项目自定义了一套Excel导出工具，poi版本可能不兼容，一般poi新旧版本不兼容分界线在3.17，选择3.17版本不会发生代码不兼容情况）文章来源地址https://www.toymoban.com/news/detail-705255.html

        <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;
    }


    @Override
    protected 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 会导致各种问题，请直接用浏览器或者用postman
        try {
            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) {
            // 重置response
            response.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;
    }
}

到了这里，关于easyExcel合并单元格导出的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！