EasyExcel 复杂模板导出excel得坑（多个）实现将多个数据转成多个excel并放入压缩包中。

最新推荐文章于 2025-03-18 20:02:11 发布

折纸猫@

最新推荐文章于 2025-03-18 20:02:11 发布

阅读量1.7k

点赞数 7

文章标签： excel

本文链接：https://blog.youkuaiyun.com/vensonxiao/article/details/135956484

版权

本文讲述了作者在实现将多个Excel记录转换为zip并导出的过程中遇到的问题，包括普通Excel导出、多个Excel文件压缩到zip、复杂模板填充等，分享了使用EasyExcel和ApachePOI库的具体实现和注意事项。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近有个需求是将多个记录转成excel然后集中到一个zip中并导出，在这样得背景下，我得研究路径是这样：

1.普通excel导出，write填写

2.多个excel集中到zip中并导出

3.模板导出excel-填充

4.复杂模板导出excel-填充

其中得经历以及坑如下

普通excel导出

最普通得导出类似于这种（例子为下载模板）通过write方法写入excel表格并填写。

public void excelTemplate(HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("test", "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ImportDTO.class).sheet("sheet1").doWrite(Lists.newArrayList());
    }

多个excel集中成zip导出

这一块得坑有：zip工具类，网上有很多利用zipUtil工具类来压缩的方法，我自己用的时候感觉不是很好用，没有深入，搜了别的方法，具体如下：

 public void exportExcel(HttpServletResponse response, Map<String, RecordDTO> map) throws IOException {
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
        try {
            for (Map.Entry<String, PdHandoverRecordDTO> entry : map.entrySet()) {
                String k = entry.getKey();
                
                //获取数据
                
                String Json = value.getSuccessionJson();
                
                List<PdRecordInfo> pdinfoList = getProdInfo(value.getSuccessionJson(), value.getHandoverTime().toString());//生产信息
                
              
                ClassPathResource classPathResource = new ClassPathResource("templates/交接班模板.xls");
                InputStream inputStream = classPathResource.getInputStream();
               
               
                //设置 自动换行
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                //设置水平，垂直居中
                WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
                contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);  //.registerWriteHandler(horizontalCellStyleStrategy)
                contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentWriteCellStyle);
                //构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错.registerWriteHandler(horizontalCellStyleStrategy)
                ExcelWriter excelWriter = EasyExcel.write(outputStream)
                                         .withTemplate(inputStream)
                                        
                                         .excelType(ExcelTypeEnum.XLS).build();
                //sheet
                WriteSheet writeSheet = EasyExcel.writerSheet().build();
                //构建excel表头信息
                // WriteTable writeTable0 = EasyExcel.writerTable(0).head(PdProductionRecordDTO.class).needHead(Boolean.TRUE).build();
                //自动换行
                //将表头和数据写入表格
                //excelWriter.write(value, writeSheet, writeTable0);
                //填充数据
             
                excelWriter.fill(new FillWrapper("data3",pdinfoList),writeSheet);
     

                //创建压缩文件
                ZipEntry zipEntry = new ZipEntry(k);
                zipOutputStream.putNextEntry(zipEntry);
                Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                //将excel对象以流的形式写入压缩流
                workbook.write(zipOutputStream);
            }
            zipOutputStream.flush();
        } catch (Exception e) {
            log.error("导XXX失败,原因" + e.getMessage());
            log.error(e.getMessage(), e);
            //抛出异常结束程序
            throw new BusinessException("数据导出接口异常");
        } finally {
            //关闭数据流，注意关闭的顺序
            zipOutputStream.close();
            outputStream.close();
        }
    }

代码有经过删减，完整版我会放到最后。总之这里主要是用了zipOutputStream,zipEntry来实现压缩

复杂模板导出excel-填充

这两点应该是坑最多的。正常情况下拿到数据-data后，直接excelWriter.fill()就好了，自己在模板中配置好相应位置得数据(map类型填{xx},List类型填{.xx}//xx为属性名）。但是既然是模板导出，肯定希望导出得样式是正常得，起码不会这里缺一块，那里又缺少一块边框等等。

而这其中，涉及得就有-自动换行，合并单元格，边框样式等

自动换行: fillConfig 和合并单元格中onceabsolutemergestrategy(进行一次单元格得合并得方法)在一起使用时会失效！！然后easyexcel版本3.0.5和2.1.6在填充和自动换行上有很大区别，网上搜到很多都是用2.1.6来举的例子。

合并单元格：11合并单元格网上有很多方法，基本上离不开CellRangeAddress这个类

CellRangeAddress：用于合并单元格，但是普通的使用得在excelWriter.write(),而不是fill得时候使用（目前我没看到可以直接用在fill上）

所以就得找合并策略，合并策略有很多，但是咱毕竟只是用一用而已，不会深入理解，有些发的合并策略没仔细看还不会用。我这也是找的别人得，然后稍微修改了一下，应该容易理解。边框也在有注释得地方写了。自行查看

package com.chem.mes.business.util;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

//列合并工具类
public class ExcelFillCellMergePrevColUtils implements CellWriteHandler {
    private static final String KEY ="%s-%s";
    //所有的合并信息都存在了这个map里面
    Map<String, Integer> mergeInfo = new HashMap<>();

    public ExcelFillCellMergePrevColUtils() {
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }


    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        //当前行
        int curRowIndex = cell.getRowIndex();
        //当前列
        int curColIndex = cell.getColumnIndex();

        //HSSFCellStyle  style = writeTableHolder.cr
        Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex));
        if(null != num){
            // 合并最后一行 ,列
            mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num);
        }
    }
    public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) {
        Sheet sheet = writeSheetHolder.getSheet();
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num);
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet);//设置边框
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        sheet.addMergedRegion(cellRangeAddress);
    }
    //num从第几列开始增加多少列,(6,2,7)代表的意思就是第6行的第2列至第2+7也就是9列开始合并
   // public void add (int curRowIndex,  int curColIndex , int num){
   //     mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
   // }

    //起始行，结束行，起始列，起始列加num列，  每一行的 curcolindex 到 curcolindex+num 进行合并。
    public void add(int curRowIndex,int endRowindex,int curColIndex,int num){
        while(curRowIndex <= endRowindex){
            mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num);
            curRowIndex++;
        }
    }
    //将0-4列，5-9列合并
    public void addall(int curRowIndex,int endRowindex,int curColIndex,int num){
        add(curRowIndex,endRowindex,curColIndex,num);
        add(curRowIndex,endRowindex,curColIndex+5,num);
    }

}

至于为什么说搜到的一些合并单元格策略有问题，主要是我这是复杂模板填充，要填好几个list。位置也不同，所以得对每个不同得list进行区分，具体做法就是，将模板里得{.xxx}变为{data1.xxx}

然后填充语句变为：

excelWriter.fill(new FillWrapper("data2",atteList),fillConfig,writeSheet);
                excelWriter.fill(new FillWrapper("data1",succList),fillConfig,writeSheet);//fillConfig

类似这样，就能区分，其中fillConfig是自动换行相关策略。

有些问题做的时候记得很清楚，到写的时候就想不起来了，很烦。

言而总之，最后总算是完成了，具体代码如下，有其他问题可以评论，我看到了会回复，

具体代码如下：部分代码被我xxx掉了，别在意

public void exportExcel(HttpServletResponse response, Map<String, PdRecordDTO> map) throws IOException {
        ServletOutputStream outputStream = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(outputStream);
      
        
        
        //设置 自动换行
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        //设置水平，垂直居中
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);  //.registerWriteHandler(horizontalCellStyleStrategy)
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(null, contentWriteCellStyle);
        //创建模板
        ClassPathResource classPathResource = new ClassPathResource("templates/交接班模板.xls");
        try {
            for (Map.Entry<String, PdRecordDTO> entry : map.entrySet()) {
                String k = entry.getKey();
                PdRecordDTO value = entry.getValue();
                //合并单元格策略
                ExcelFillCellMergePrevColUtils excelFillCellMergePrevColUtils = new ExcelFillCellMergePrevColUtils();
                //获取数据
                try {
                    if (!isValid(value)) {//判断数据是否合法,完整
                        //throw new BusinessException("传入记录不合法");
                        continue;
                    }
                }catch (BusinessException e){//更离谱的不合法报错直接跳过
                    continue;
                }
                
                List<SuccessorExport> succList = getSuccessorData(xxx);//接班人
                List<AttendantExport> atteList = getAttendtantData(xxx);//交班人
                List<EquipExport> bequip = getEquipData(xxx);//设备
                List<PdRecordInfo> pdinfoList = getProdInfo(xxx);//生产信息
                HandoverMessage hdm = getMessage(xxx);//安全信息

                InputStream inputStream = classPathResource.getInputStream();
                int succsize = succList.size();
                int attsize = atteList.size();
                //添加要合并的行列
                excelFillCellMergePrevColUtils.addall(  5,5+attsize-1, 0, 4);//模板对应位置
                excelFillCellMergePrevColUtils.addall(  22+succsize-1,22+succsize+attsize-2, 0, 4);
                //构建一个excel对象,这里注意type要是xls不能是xlsx,否则下面的写入后流会关闭,导致报错.registerWriteHandler(horizontalCellStyleStrategy)
                ByteArrayOutputStream tempStream = new ByteArrayOutputStream();//用不同得流对应不同得excel导出流
                ExcelWriter excelWriter = EasyExcel.write(tempStream)
                                         .withTemplate(inputStream)
                                         .registerWriteHandler(horizontalCellStyleStrategy)
                                         .registerWriteHandler(excelFillCellMergePrevColUtils)
                                         .excelType(ExcelTypeEnum.XLS).build();
                //sheet
                WriteSheet writeSheet = EasyExcel.writerSheet().build();
                //构建excel表头信息
                // WriteTable writeTable0 = EasyExcel.writerTable(0).head(PdRecordDTO.class).needHead(Boolean.TRUE).build();
                //自动换行
                //将表头和数据写入表格
                //excelWriter.write(value, writeSheet, writeTable0);
                //填充数据
                excelWriter.fill(new FillWrapper("data2",atteList),fillConfig,writeSheet);
                excelWriter.fill(new FillWrapper("data1",succList),fillConfig,writeSheet);//fillConfig
                excelWriter.fill(new FillWrapper("data4",bequip),fillConfig,writeSheet);
                excelWriter.fill(new FillWrapper("data3",pdinfoList),writeSheet);
                excelWriter.fill(hdm,writeSheet);
                excelWriter.fill(value, writeSheet);

                //创建压缩文件
                ZipEntry zipEntry = new ZipEntry(k);
                zipOutputStream.putNextEntry(zipEntry);
                Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
                //将excel对象以流的形式写入压缩流
                workbook.write(tempStream);
                tempStream.writeTo(zipOutputStream);
                excelWriter.finish();
                workbook.close();
                tempStream.close();
            }
            zipOutputStream.flush();
        } catch (Exception e) {
            log.error("导XXX失败,原因" + e.getMessage());
            log.error(e.getMessage(), e);
            //抛出异常结束程序
            throw new BusinessException("数据导出接口异常");
        } finally {
            //关闭数据流，注意关闭的顺序
            zipOutputStream.close();
            outputStream.close();
        }
    }