springboot使用EasyExcel导出Excel

源码

GitHub： https://github.com/291685399/springboot-learning/tree/master/springboot-easyexcel01

EasyExcel是什么？

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

EasyExcel和POI的对比

1、 相比于POI，EasyExcel简化了开发量，能够用更少的代码实现更多的功能
2、 相比于POI，EasyExcel使用简单
3、 相比于POI，EasyExcel能够使用更少的内存占用

使用EasyExcel导出或下载Excel

pom.xml：

<dependencies>
    <!-- springboot -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <scope>runtime</scope>
        <optional>true</optional>
    </dependency>

    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.0.1</version>
    </dependency>

    <!-- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <!-- lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>

    <!-- easyexcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>1.1.2-beat1</version>
    </dependency>
</dependencies>

UserController：

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 导出Excel
     */
    @RequestMapping(value = "/exportExcel")
    public void exportExcel() {
        ExcelWriter writer = null;
        OutputStream out = null;
        try {
            List userList = userService.findAll();
            String fileName = "用户信息表格";
            out = new FileOutputStream("C:/Users/EDZ/Desktop/" + fileName + ".xlsx");
            writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet = new Sheet(1, 0, User.class);
            sheet.setSheetName("用户信息");
            writer.write(userList, sheet);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (writer != null) {
                writer.finish();
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 下载Excel
     *
     * @param response
     */
    @RequestMapping(value = "/uploadExcel")
    public void uploadExcel(HttpServletResponse response) {
        ExcelWriter writer = null;
        OutputStream out = null;
        try {
            List userList = userService.findAll();
            out = response.getOutputStream();
            writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            String fileName = "用户信息表格";
            Sheet sheet = new Sheet(1, 0, User.class);
            sheet.setSheetName("用户信息");
            writer.write(userList, sheet);
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "ISO8859-1"));
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (writer != null) {
                writer.finish();
            }
            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

UserService：

public interface UserService {

    /**
     * 查询所有用户
     */
    public List<User> findAll();

}

UserServiceImpl：

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<User> findAll() {
        return userMapper.findAll();
    }

}

UserMapper：

@Mapper
public interface UserMapper {

    /**
     * 查询所有用户
     */
    public List<User> findAll();

}

UserMapper.xml：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.wyj.mapper.UserMapper">

    <select id="findAll" resultType="com.wyj.entity.po.User">
        select * from user
    </select>

</mapper>

User：

@Data
public class User extends BaseRowModel implements Serializable {

    //通过 @ExcelProperty 注解与 index 变量可以标注成员变量所映射的列
    @ExcelProperty(value = "序号", index = 0)
    private int id;
    @ExcelProperty(value = "姓名", index = 1)
    private String name;
    @ExcelProperty(value = "性别", index = 2)
    private String sex;
    @ExcelProperty(value = "年龄", index = 3)
    private int age;
    @ExcelProperty(value = "地址", index = 4)
    private String address;
    @ExcelProperty(value = "联系方式", index = 5)
    private String phone;

}

application.properties：

#tomcat port
server.port=8080
#datasource
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/springboot-easyexcel01?allowMultiQueries=true&useUnicode=true&useSSL=false&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=root
#mybatis
mybatis.mapper-locations=classpath*:mapper/*.xml
mybatis.type-aliases-package=com.wyj.entity