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


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

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
EasyExcel 是一款使用简单、效率高的 Java Excel 操作工具。EasyExcel 提供了两种合并单元格的方法:自动合并和手动合并。 1. 自动合并 EasyExcel 在写入 Excel 文件时,会自动识别需要合并的单元格,并将其合并。这种方式适用于需要合并的单元格比较规律的情况,比如表头或者某些固定格式的表格。 2. 手动合并 手动合并单元格需要使用 `Sheet` 类中的 `merge` 方法,传入合并的起始行、起始列、结束行、结束列即可手动合并单元格。 以下是一个示例代码: ```java // 创建 ExcelWriter 对象 String filePath = "output.xlsx"; ExcelWriter excelWriter = new ExcelWriter(filePath); // 创建 Sheet 对象 Sheet sheet = new Sheet(1, 0); // 设置表头 List<List<String>> head = new ArrayList<>(); head.add(Arrays.asList("姓名", "年龄", "性别")); sheet.setHead(head); // 设置数据 List<List<Object>> data = new ArrayList<>(); data.add(Arrays.asList("张三", 20, "男")); data.add(Arrays.asList("李四", 25, "女")); data.add(Arrays.asList("王五", 30, "男")); sheet.setClazz(List.class); sheet.setAutoWidth(Boolean.TRUE); sheet.setDataList(data); // 手动合并单元格 sheet.merge(0, 0, 0, 2); // 将数据写入 Excel 文件 excelWriter.write1(Collections.singletonList(sheet), null); // 关闭 ExcelWriter excelWriter.finish(); ``` 在上面的示例代码中,我们首先创建了一个 ExcelWriter 对象,并指定了需要写入的 Excel 文件路径。接着,我们创建了一个 Sheet 对象,并设置了表头和数据。最后,我们使用 Sheet 对象的 merge 方法来手动合并单元格,将第一行的三个单元格合并为一个单元格。最终,我们将数据写入 Excel 文件并关闭 ExcelWriter 对象。 需要注意的是,手动合并单元格时,需要在写入数据之前进行合并。如果在写入数据之后再进行合并,EasyExcel 无法识别需要合并的单元格。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值