Java springboot EasyExcel Excel自定义表头导出demo

愛～杦辷个訾

已于 2023-04-20 09:38:07 修改

阅读量2.8k

点赞数 1

于 2023-04-19 18:23:07 首次发布

本文链接：https://blog.csdn.net/weixin_43969830/article/details/130226660

版权

一、需求

现有一个业务，需要将列表数据导出excel，并返回给前端。表头有合并、拆分，结构比较复杂，使用EasyExcel将数据导出。这里采用了两种返回方式：导出到本地、直接返回给前端。

二、pom依赖

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

三、创建单行表头实体

package com.example.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Author:
 * @Description 表头 需要加@ExcelProperty注解¬
 * @Date: 下午5:25 2023/4/12
 */
@Data
public class ScoreData implements Serializable {

    @ExcelProperty(value = "一年级")
    private Double scoreOne;

    @ExcelProperty(value = "二年级")
    private Double scoreTwo;

    @ExcelProperty(value = "三年级")
    private Double scoreThree;

    @ExcelProperty(value = "四年级")
    private Double scoreFour;

    public ScoreData() {
    }

    public ScoreData(Double scoreOne, Double scoreTwo, Double scoreThree, Double scoreFour) {
        this.scoreOne = scoreOne;
        this.scoreTwo = scoreTwo;
        this.scoreThree = scoreThree;
        this.scoreFour = scoreFour;
    }
}

四、创建多行表头返回实体

package com.example.dto;

import lombok.Data;

import java.io.Serializable;

/**
 * @Author:
 * @Description 二级表头
 * @Date: 下午5:25 2023/4/12
 */
@Data
public class ScoreMergeData implements Serializable {


    /**
     * 序号
     */
    private Integer index;
    /**
     * 小学一年级
     */
    private Double scoreOne;
    /**
     * 小学二年级
     */
    private Double scoreTwo;
    /**
     * 小学三年级
     */
    private Double scoreThree;
    /**
     * 小学四年级
     */
    private Double scoreFour;
    /**
     * 初中一年级
     */
    private Double scoreOne1;
    /**
     * 初中二年级
     */
    private Double scoreTwo1;
    /**
     * 初中三年级
     */
    private Double scoreThree1;
    /**
     * 初中四年级
     */
    private Double scoreFour1;
    /**
     * 大学一年级
     */
    private Double scoreOne2;
    /**
     * 大学二年级
     */
    private Double scoreTwo2;
    /**
     * 大学三年级
     */
    private Double scoreThree2;
    /**
     * 大学四年级
     */
    private Double scoreFour2;

    public ScoreMergeData() {
    }

    public ScoreMergeData(Integer index, Double scoreOne, Double scoreTwo, Double scoreThree, Double scoreFour, Double scoreOne1, Double scoreTwo1, Double scoreThree1, Double scoreFour1, Double scoreOne2, Double scoreTwo2, Double scoreThree2, Double scoreFour2) {
        this.index = index;
        this.scoreOne = scoreOne;
        this.scoreTwo = scoreTwo;
        this.scoreThree = scoreThree;
        this.scoreFour = scoreFour;
        this.scoreOne1 = scoreOne1;
        this.scoreTwo1 = scoreTwo1;
        this.scoreThree1 = scoreThree1;
        this.scoreFour1 = scoreFour1;
        this.scoreOne2 = scoreOne2;
        this.scoreTwo2 = scoreTwo2;
        this.scoreThree2 = scoreThree2;
        this.scoreFour2 = scoreFour2;
    }
}

五、编写service

package com.example.service;

import com.alibaba.excel.EasyExcel;
import com.example.dto.ScoreData;
import com.example.dto.ScoreMergeData;
import lombok.extern.slf4j.Slf4j;
import org.apache.tomcat.util.http.fileupload.FileUtils;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * @Author:
 * @Description Excel导出
 * @Date: 下午5:30 2023/4/12
 */
@Slf4j
@Service
public class ExcelService {

    //自己修改路径
    private static final String filePath = "/Users/admin/work/2333/excel-demo/excelfiles/";

    /**
     * 最简单样式导出--写到本地
     */
    public static void exportExcelByDto() {
        String fileName = String.format("2023年各年级平均分%s.xlsx", getTimeStr());
        EasyExcel.write(filePath + fileName, ScoreData.class).sheet("各年级平均分").doWrite(getListData());
    }

    /**
     * 自定义表头且有合并导出
     */
    public static void exportExcelMerge() {
        //模拟数据（可以从数据库中查询）
        List<ScoreMergeData> list = Stream.of(new ScoreMergeData(1, 70.9, 80.5, 89.5, 96.7, 70.9, 80.5, 89.5, 96.7, 70.9, 80.5, 89.5, 96.7),
                new ScoreMergeData(2, 40.9, 40.5, 29.5, 76.7, 40.9, 40.5, 29.5, 76.7, 40.9, 40.5, 29.5, 76.7),
                new ScoreMergeData(3, 50.9, 30.5, 19.5, 56.7, 50.9, 30.5, 19.5, 56.7, 50.9, 30.5, 19.5, 56.7),
                new ScoreMergeData(4, 60.9, 70.5, 89.5, 106.7, 60.9, 70.5, 89.5, 106.7, 60.9, 70.5, 89.5, 106.7)).collect(Collectors.toList());
        String fileName = String.format("2023年各年级平均分合并%s.xlsx", getTimeStr());
        EasyExcel.write(filePath + fileName, ScoreMergeData.class).head(dealWithHeads()).sheet("日月湖商品及卡券收入台账").doWrite(list);
    }

    /**
     * 最简单样式导出--响应到浏览器
     * 先将文件写到本地，然后读取文件，返回给前端，最后删除本地文件
     *
     * @param response
     */
    public static void exportExcelByDtoClient(HttpServletResponse response) {
        try {
            String fileName = String.format("2023年各年级平均分%s.xlsx", getTimeStr());
            EasyExcel.write(filePath + fileName, ScoreData.class).sheet("各年级平均分").doWrite(getListData());
            //设置响应头
            setResponseParm(response, fileName);
            ServletOutputStream out = null;
            ByteArrayOutputStream baos = null;
            try {
                File file = new File(filePath + fileName);
                InputStream inStream = new FileInputStream(file);
                byte[] buffer = new byte[1024];
                int len;
                baos = new ByteArrayOutputStream();
                while ((len = inStream.read(buffer)) != -1) {
                    baos.write(buffer, 0, len);
                }
                out = response.getOutputStream();
                out.write(baos.toByteArray());
            } catch (Exception e) {
                log.error("本地文件导出异常{}", e);
                e.printStackTrace();
            } finally {
                baos.flush();
                baos.close();
                out.flush();
                out.close();
                try {
                    //删除本地文件
                    FileUtils.forceDelete(new File(filePath + fileName));
                } catch (IOException e) {
                    log.error("删除本地文件异常{}", e);
                    e.printStackTrace();
                }
            }
        } catch (IOException e) {
            log.error("文件下载异常{}", e);
            e.printStackTrace();
        }
    }

    /**
     * 最简单样式导出--响应到浏览器
     * 直接返回给前端
     *
     * @param response
     */
    public static void exportExcelByDtoClientNew(HttpServletResponse response) {
        try {
            String fileName = String.format("2023年各年级平均分%s.xlsx", getTimeStr());
            //设置响应头
            setResponseParm(response, fileName);
            EasyExcel.write(response.getOutputStream(), ScoreData.class).sheet("各年级平均分").doWrite(getListData());
        } catch (IOException e) {
            log.error("文件下载异常{}", e);
            e.printStackTrace();
        }
    }

    /**
     * 封装表头
     */
    public static List<List<String>> dealWithHeads() {
        List<List<String>> listParent = new ArrayList<>();
        //第一行 名字重复则自动合并
        listParent.add(Arrays.asList("序号", "小学", "小学", "小学", "小学", "初中", "初中", "初中", "初中", "大学", "大学", "大学", "大学"));
        //第二行
        listParent.add(Arrays.asList("序号", "一年级", "二年级", "三年级", "四年级", "一年级", "二年级", "三年级", "四年级", "一年级", "二年级", "三年级", "四年级"));
        return xzHeadFields(listParent);
    }

    /**
     * 处理表头数据
     *
     * @param headsStr
     * @return
     */
    public static List<List<String>> xzHeadFields(List<List<String>> headsStr) {
        List<List<String>> res = new ArrayList<>();
        for (List<String> str : headsStr) {
            for (int j = 0; j < str.size(); j++) {
                if (res.size() > j) {
                    // 向第j个集合中添加值
                    res.get(j).add(str.get(j));
                } else {
                    // 将集合分割为单个List<String>
                    res.add(new ArrayList<>(Collections.singletonList(str.get(j))));
                }
            }
        }
        return res;
    }

    /**
     * 设置响应头
     *
     * @param response
     * @param fileName
     */
    public static void setResponseParm(HttpServletResponse response, String fileName) {
        try {
            // 设置编码
            response.setCharacterEncoding("UTF-8");
            //设置响应头类型
            response.setContentType("application/application/vnd.ms-excel");
            //设置文件名
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        } catch (UnsupportedEncodingException e) {
            log.error("设置响应头异常{}", e);
            e.printStackTrace();
        }
    }


    /**
     * 当前时间字符串 用于文件名称
     *
     * @return 返回格式：20230418111613
     */
    public static String getTimeStr() {
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        return format.format(new Date());
    }

    /**
     * 模拟数据（可以从数据库中查询）
     *
     * @return
     */
    public static List<ScoreData> getListData() {
        return Stream.of(new ScoreData(70.9, 80.5, 89.5, 96.7),
                new ScoreData(40.9, 40.5, 29.5, 76.7),
                new ScoreData(50.9, 30.5, 19.5, 56.7),
                new ScoreData(60.9, 70.5, 89.5, 106.7)).collect(Collectors.toList());
    }

    /**
     * 本地测试
     *
     * @param args
     */
    public static void main(String[] args) {
        //最简单样式
        exportExcelByDto();
        //表头合并
        exportExcelMerge();

    }

}

六、编写controller

package com.example.api;

import com.example.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;

/**
 * @Author:
 * @Description
 * @Date: 下午2:00 2023/4/18
 */
@RestController
@RequestMapping("/excel")
public class ExcelApi {

    @Autowired
    private ExcelService excelService;

    /**
     * 直接输出到前端
     *
     * @param response
     */
    @GetMapping("/export/one")
    public void downloadOne(HttpServletResponse response) {
        excelService.exportExcelByDtoClientNew(response);
    }

    /**
     * 先将文件写到本地，然后读取文件，返回给前端，最后删除本地文件
     *
     * @param response
     */
    @GetMapping("/export")
    public void download(HttpServletResponse response) {
        excelService.exportExcelByDtoClient(response);
    }

}

七、测试

1、直接执行ExcelService中的main方法，将数据保存到本地
2、启动springboot项目，浏览器分别访问http://localhost:8080/excel/export和http://localhost:8080/excel/export/one将文件下载下来
结果如下图：
在这里插入图片描述

八、仓库地址

1、Gitee地址：https://gitee.com/Dev9925/excel-demo
2、GitHub地址：

愛～杦辷个訾

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Java springboot EasyExcel Excel自定义表头导出demo

现有一个业务，需要将列表数据导出excel，并返回给前端。表头有合并、拆分，结构比较复杂，使用EasyExcel将数据导出。这里采用了两种返回方式：导出到本地、直接返回给前端。1、直接执行ExcelService中的main方法，将数据保存到本地。2、启动springboot项目，浏览器分别访问。
复制链接

扫一扫