EasyExcel+Springboot实现Excel数据导出

1.概述

环境说明：
java1.8.0_131
springboot2.2.0.RELEASE
easyexcel2.2.6
fastjson1.2.70

2.maven依赖

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.0.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
    </dependencies>

3.需要导出实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

import java.util.Date;

/**
 * @author:Ding
 * @date:2021/11/29 14:27
 * @Desperation 导出对象实体类
 */
@ColumnWidth(20)//可以设置列宽
public class Person {
    //人员id
    @ExcelProperty(value = "人员id", index = 0)
    private Long id;
    //人员姓名
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    //创建时间
    @DateTimeFormat("yyyy年MM月dd日")
    @ExcelProperty(value = "创建时间", index = 2)
    private Date createTime;
    //是否删除，导出时忽略
    @ExcelIgnore
    private Boolean isDel;

    public Person(Long id, String name, Date createTime, Boolean isDel) {
        this.id = id;
        this.name = name;
        this.createTime = createTime;
        this.isDel = isDel;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Boolean getDel() {
        return isDel;
    }

    public void setDel(Boolean del) {
        isDel = del;
    }
}

4.导出excel方法

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.fastjson.JSON;
import org.springframework.stereotype.Component;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author:Ding
 * @date:2021/11/29 14:19
 * @Desperation
 */
@Component
public class ExcelUtils {
    /**
     * 导出excel
     *
     * @param objects 数据List
     * @param response 返回response对象
     * @param fileName 返回文件的名称
     * @param c 数据List的对象类
     */
    public <T> void excelExport(List<?> objects, HttpServletResponse response, String fileName, Class<T> c) throws IOException {
        if (objects.size() == 0)
            return;
        ServletOutputStream outputStream = null;
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx");
            outputStream = response.getOutputStream();
            ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(), c).excelType(ExcelTypeEnum.XLS);
            write.sheet(fileName).doWrite(objects);
            if (outputStream != null) {
                outputStream.close();
            }
        } catch (Exception e) {
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            map.put("data", null);
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
}

5.controller

@RestController
public class ExcelController {
    @Resource
    ExcelUtils excelUtils;

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        try {
            List<Person> people = new ArrayList<>();
            Person person1 = new Person(1L, "小白", new Date(), false);
            Person person2 = new Person(2L, "小黑", new Date(), true);
            people.add(person1);
            people.add(person2);
            excelUtils.excelExport(people, response, "人员", Person.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

6.测试

浏览器访问http://localhost:8080/export