Easyexcel导出文件（多图片）（自用）

雅诗莫拉

已于 2023-05-12 14:39:42 修改

阅读量5k

点赞数 3

分类专栏： JAVA 文章标签： java spring boot servlet

于 2023-05-12 10:17:55 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_45564990/article/details/130636029

版权

JAVA 专栏收录该内容

2 篇文章

订阅专栏

成品展示：

依赖：

              <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.3.1</version>
              </dependency>

代码展示

  public void imageWrite(ApplyVo applyVo, HttpServletResponse response) throws IOException {
     //   String fileName = URLEncoder.encode("applyExcel.xlsx", "UTF-8").replaceAll("\\+", "%20");

        //业务数据处理
        applyVo.setSelectState(0);
        List<ApplyVo> webApply1 = applyDao.getWebApply(applyVo);

        //导出
        List<ApplyVo> demoDataList = new ArrayList<>();
        for (ApplyVo applyVo1 : webApply1) {
            List<String> path = new ArrayList<>();
            String specialData = applyVo1.getSpecial();

            if (!StringUtils.isEmpty(specialData)) {
                List<JSONObject> list = JSON.parseArray(specialData, JSONObject.class);

                for (JSONObject item : list) {
                    String url = item.getString("url");
                    String getpath = getpath(url);
                    path.add(new File(getpath).getAbsolutePath());

                }
            }
            
            ApplyVo applyVo2 = new ApplyVo(
                    applyVo1.getCode(),
                    applyVo1.getCreatedDate(),
                    applyVo1.getName(),
                    applyVo1.getPhone(),
                    applyVo1.getReleaseDate(),
                    path,
                    applyVo1.getCarrier(),
                    applyVo1.getCarrierPhone()
            );


            demoDataList.add(applyVo2);
        }


        genImageExcel(demoDataList, response);
    }

    //下载
    public String getpath(String path) throws IOException {
        URL url = new URL(path);
        URLConnection conn = url.openConnection();

        String fileName1 = "";
        try (InputStream in = conn.getInputStream()) {
            byte[] buffer = new byte[1024];
            int n;
            fileName1= path.substring(path.lastIndexOf('/') + 1); // 获取文件名
            String fileExtension = fileName1.substring(fileName1.lastIndexOf('.') + 1).toLowerCase(); // 获取文件扩展名
            fileName1 = fileName1.substring(0, fileName1.lastIndexOf('.')); // 去掉扩展名，只保留文件名部分
            fileName1 = fileName1 + "_" + System.currentTimeMillis() + "." + fileExtension; // 在文件名后添加时间戳
            fileName1="jpgs/"+fileName1;
            File file = new File(fileName1);
            try (OutputStream out = new FileOutputStream(file)) {
                while ((n = in.read(buffer)) != -1) {
                    out.write(buffer, 0, n);
                }
            }
        }

        return fileName1;
    }

    /**
     * 生成有图片的excel  只要传入List集合的数据就可 ，下面的内容整体复制后：除需更改对象类之外，  注意！！！放行物品-----这四个字是我的图片存在的列  需改成自己的 和对象类里的图片列对应 
     *
     * @param demoDataList 数据列表
     *
     */
    public static void genImageExcel(List<ApplyVo> demoDataList,  HttpServletResponse response) throws IOException {

        if (CollectionUtils.isEmpty(demoDataList)) {
            return;
        }

        //图片列最大图片数
        AtomicReference<Integer> maxImageSize = new AtomicReference<>(0);
        demoDataList.forEach(item -> {
            if (CollectionUtils.isNotEmpty(item.getPath()) && item.getPath().size() > maxImageSize.get()) {
                maxImageSize.set(item.getPath().size());
            }
        });

        //设置列长度所用类
        AutoColumnWidthStyleStrategy longWidth = new AutoColumnWidthStyleStrategy();

        demoDataList.forEach(item -> {
            WriteCellData<Void> writeCellData = new WriteCellData<>();
            if (CollectionUtils.isNotEmpty(item.getPath())) {
                //每张图片间距
                Integer splitWidth = 2;
                //每张图片的长度
                Integer imageWidth = 80;
                //图片列的最大长度
                Integer sumWidth = maxImageSize.get() * (imageWidth + splitWidth);

                List<ImageData> imageDataList = new ArrayList<>();
                List<String> imagePathList = item.getPath();
                for (int i = 1; i <= imagePathList.size(); i++) {
                    String path = imagePathList.get(i - 1);
                    Integer left = imageWidth * (i - 1) + i * splitWidth;
                    Integer right = sumWidth - imageWidth - left;
                    ImageData imageData = new ImageData();
                    try {
                        imageData.setImage(FileUtils.readFileToByteArray(new File(path)));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    imageData.setImageType(ImageData.ImageType.PICTURE_TYPE_PNG);
                    //距离单元格顶部距离
                    imageData.setTop(1);
                    //距离单元格底部距离
                    imageData.setBottom(1);
                    //距离单元格左边距离
                    imageData.setLeft(left);
                    //距离单元格右边距离
                    imageData.setRight(right);
                    imageData.setAnchorType(ClientAnchorData.AnchorType.DONT_MOVE_DO_RESIZE);
                    imageDataList.add(imageData);
                }
                writeCellData.setImageDataList(imageDataList);

                Map<String, Integer> zdyColumnWidth = new HashMap<>();
                //图片列名称，对应导出对象的列名称，图片列长度
                zdyColumnWidth.put("放行物品", sumWidth / 6);
                longWidth.setZdyColumnWidth(zdyColumnWidth);
            }
            // item.setSpeical(writeCellData);
            item.setSpecial1(writeCellData);
        });

        //写入数据
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        // 使用 EasyExcel 将数据写入流中
        EasyExcel.write(out, ApplyVo.class).registerWriteHandler(longWidth)
                .sheet().doWrite(demoDataList);
        // 设置响应内容
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        // 设置编码格式
        response.setCharacterEncoding("UTF-8");

        long timestamp = System.currentTimeMillis();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String strDate = sdf.format(new Date(timestamp));
        String excelName="applyExcel"+strDate+"（荐：微软excel）";

        //设置文件名
        String fileName1 = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        //设置响应头
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName1 + ".xlsx");

        // 获取输出流并写入数据
        ServletOutputStream servletOut = response.getOutputStream();
        servletOut.write(out.toByteArray());
        servletOut.flush();
        servletOut.close();
        //删除图片
        demoDataList.forEach(item->{
            List<String> path = item.getPath();
            path.forEach(pa->{
                File file = new File(pa);
                if (file.exists()) {
                    file.delete();
                }
            });

        });

    }

package com.example.travel.pojo.vo;


import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;

import com.alibaba.excel.metadata.data.WriteCellData;
import com.baomidou.mybatisplus.annotation.IdType;

import com.baomidou.mybatisplus.annotation.TableId;
import com.example.travel.pojo.excel.IsExceptionConverter;
import lombok.*;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(40)
@ColumnWidth(25)   //列宽
public class ApplyVo implements Serializable {
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelIgnore
    private Integer id;
    /**
     * 发起单编号*
     */
    @ExcelProperty("申请单编号")
    private String code;

    /**
     * 创建日期*
     */
    @ExcelProperty("申请时间")
    private Date createdDate;

    /**
     * 姓名*
     */
    @ExcelProperty("申请人")
    private String name;
    /**
     * 人员编号*
     */
    @ExcelIgnore
    private String personCode;
    /**
     * 公司id*
     */
    @ExcelIgnore
    private Integer enterpriseId;
    /**
     * 公司名称*
     */
    @ExcelIgnore
    private String enterpriseName;
    /**
     * 发起人公司地址*
     */
    @ExcelIgnore
    private String address;
    /**
     * 手机号*
     */
    @ExcelProperty("申请人手机号")
    private String phone;
    /**
     * 放行日期*
     */
    @ExcelProperty("放行日期")
    private Date releaseDate;

    /**
     * 物品简介*
     */
    @ExcelProperty(value = "放行物品")
    private WriteCellData<Void> special1;
    @ExcelIgnore
    private List<String> path;

    @ExcelIgnore
    private String articleIntroduction;

    /**
     * 备注信息*
     */
    @ExcelIgnore
    private String remark;

    /**
     * 携货人*
     */
    @ExcelProperty("携货人")
    private String carrier;
    /**
     * 携货人手机号*
     */
    @ExcelProperty("携货人手机号")
    private String carrierPhone;
    /**
     * 当前状态 处理完成：0
     *       ， 处理中：1
     *         已取消：2
     *         已拒绝：3
     *          通过：4
     *       拒绝通行：5
     */
    @ExcelProperty(value = "状态",converter = IsExceptionConverter.class)
    private Integer state;
    /**
     * 更新日期*
     */
    @ExcelIgnore
    private Date updatedDate;
    /**
     * 项目id*
     */
    @ExcelIgnore
    private Integer projectId;
    /**
     * 项目名称*
     */
    @ExcelIgnore
    private String projectName;
    /**
     * 工作流id*
     */
    @ExcelIgnore
    private String flowProcessInstanceId;


    /**
     * 搜索框*
     */
    @ExcelIgnore
    private String search;
    //开始时间
    @ExcelIgnore
    private Date stateTime;
    //结束时间
    @ExcelIgnore
    private Date endTime;
    //组织id集合
    @ExcelIgnore
    private List<Integer>  projectIds;
    //页数
    @ExcelIgnore
    private Integer pageNo;
    //条数
    @ExcelIgnore
    private Integer pageSize;
    //总数
    @ExcelIgnore
    private Integer total;
    //数据集合
    @ExcelIgnore
    private List<ApplyVo> applyVos;
    // 0为最新创建  1为最近更新
    @ExcelIgnore
    private Integer appConditions;
    //总数
    @ExcelIgnore
    private Integer selectState;
    /*
       数量
        */
    @ExcelIgnore
    private Integer number;
    /*
  放行物品
   */
    @ExcelIgnore
    private String photo;
    /*
    特殊物品
     */
    @ExcelIgnore
    private String special;
    @ExcelIgnore
    private List<Integer> states;



    public ApplyVo(String code, Date createdDate, String name, String phone, Date releaseDate,List<String> path, String carrier, String carrierPhone ) {
        this.code = code;
        this.createdDate = createdDate;
        this.name = name;
        this.phone = phone;
        this.releaseDate = releaseDate;
        this.carrier = carrier;
        this.carrierPhone = carrierPhone;
        this.path = path;
    }
    public ApplyVo(){}
    public ApplyVo(List<String> path) {
        this.path = path;
    }
}