java easyexcel 写入数据到excel中的多个sheet中

71 阅读 0 评论 47 点赞

我是靠谱客的博主笑点低火车，最近开发中收集的这篇文章主要介绍java easyexcel 写入数据到excel中的多个sheet中，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

controller方法：

@ApiOperation("下载导入结果")
    @RequestMapping(value = "/downloadUploadResut", method = RequestMethod.POST)
    public void batchDownload(HttpServletRequest request,
                              HttpServletResponse response,
                              @RequestBody  DownloadAssetResultDto  downloadAssetResultDto) throws Exception {

        if(downloadAssetResultDto==null || downloadAssetResultDto.getUploadId()==null){
            throw  new RRException("上传数据ID为空，请先上传数据");
        }

        request.setCharacterEncoding("UTF-8");
        String prefix=".xlsx";
        String fileName="uploadResult";
        String userAgent = request.getHeader("User-Agent");
        String     downLoadPath = URLDecoder.decode(fileName, "UTF-8");
        System.out.println(downLoadPath);
        try {

            response.setContentType("application/vnd.ms-excel");
            fileName = URLEncoder.encode(fileName, "ISO8859-1");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            response.setHeader("filename",fileName);
            Integer uploadId = downloadAssetResultDto.getUploadId();
            downloadAssetService.downloadUploadResut(response,uploadId);


        } catch (Exception e) {
            e.printStackTrace();
        }

    }

service业务类实现方法：

@Override
    public void downloadUploadResut(HttpServletResponse response,Integer  uploadId) {
        ExcelWriter excelWriter = null;
        try {
            CustomCellWriteHandler customCellWriteHandler = new CustomCellWriteHandler();
            excelWriter = EasyExcel.write(response.getOutputStream()).build();
           
            List<BizObjectModeExport> bizObjectUploadData =bizObjectUploadData(uploadId);
            //创建一个sheet
            WriteSheet writeSheet = EasyExcel.writerSheet( "sheet1").head(BizObjectModeExport.class).registerWriteHandler(customCellWriteHandler).build();
            excelWriter.write(bizObjectUploadData, writeSheet);


            
            List<LogicEntityModelExport> logicEntityUploadData = logicEntityUploadData(uploadId);
            //创建一个新的sheet
            writeSheet = EasyExcel.writerSheet("sheet2").head(LogicEntityModelExport.class).registerWriteHandler(customCellWriteHandler).build();
            excelWriter.write(logicEntityUploadData, writeSheet);

            
            List<AttributeModelExport> attributeUploadData = attributeUploadData(uploadId);
            writeSheet = EasyExcel.writerSheet("sheet3").head(AttributeModelExport.class).registerWriteHandler(customCellWriteHandler).build();
            excelWriter.write(attributeUploadData, writeSheet);


           
            List<TechMetadataModelExport> techMetadataUploadData = techMetadataUploadData(uploadId);
            writeSheet = EasyExcel.writerSheet("shee4").head(TechMetadataModelExport.class).registerWriteHandler(customCellWriteHandler).build();
            excelWriter.write(techMetadataUploadData, writeSheet);



          /*
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), BizObjectModeExport.class)
                    .registerWriteHandler(new CustomCellWriteHandler())
                    .autoCloseStream(Boolean.TRUE).sheet("sheet")
                    .doWrite(bizObjectUploadData);*/
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("flag", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            try {
                response.getWriter().println(JSON.toJSONString(map));
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }finally{
            if(excelWriter!=null){
                excelWriter.finish();
            }

        }
    }

CustomCellWriteHandler.java

package cn.getech.data.manager.service.impl;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * @ClassName CustomCellWriteHandler
 * @Description TODO
 * @Author Getech
 * @Date 2020/11/13 16:33
 */
public class CustomCellWriteHandler  extends AbstractColumnWidthStyleStrategy {

    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

其中一个excelmodel.java

package com.cwp.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class BizObjectModel {


   
    @ExcelProperty(value = "编码",order = 1)
    private String code;


   
    @ExcelProperty(value = "名称",order = 5)
    private String name;

   
}

引入alibaba easyexcel依赖片段：

 <!-- alibaba excel依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
            <exclusions>
                <exclusion>
                    <artifactId>cglib</artifactId>
                    <groupId>cglib</groupId>
                </exclusion>
            </exclusions>
        </dependency>