EasyExcel导出Excel文件

介绍

EasyExcel是阿里巴巴出品的Excel文件处理工具，功能十分强大，能省去我们很多的编码，下面来通过一个例子，看看如何使用EasyExcel导出Excel文件的

准备

我们从数据库读取User实体对象数据，然后使用EasyExcel写入到HttpServletResponse输出流中，在浏览器就可以下载导出的Excel文件了

POM

主要有EasyExcel依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://maven.apache.org/POM/4.0.0"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.1</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>excel</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>excel</name>
    <description>excel</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>

        <!--json-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.69</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

User实体

@ExcelProperty(value = “id”) 表示导出的Excel文件列名为id，其他同理

package com.example.excel.entity;

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

import java.math.BigDecimal;
import java.util.Date;

@Data
public class User {

    @ExcelProperty("id")
    private String id;

    @ExcelProperty("username")
    private String username;

    @ExcelProperty("password")
    private String password;

    @ExcelProperty("address")
    private String address;

    @ExcelProperty("email")
    private String email;

    @ExcelProperty("profile")
    private String profile;

    @ExcelProperty("birthday")
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    @ExcelProperty("registerDay")
    @DateTimeFormat("yyyy-MM-dd")
    private Date registerDay;

    @ExcelProperty("loginDay")
    @DateTimeFormat("yyyy-MM-dd")
    private Date loginDay;

    @ExcelProperty("status")
    private Integer status;

    @ExcelProperty("account")
    private BigDecimal account;

    @ExcelProperty("balance")
    private BigDecimal balance;

    @ExcelProperty("age")
    private Integer age;

    @ExcelProperty("sex")
    private Integer sex;

    @ExcelProperty("avatar")
    private String avatar;

    @ExcelProperty("level")
    private Integer level;

}

ExcelUtil

实现浏览器导出下载Excel文件，写入本地文件也类似，可参考：EasyExcel使用

package com.example.excel.util;

import com.alibaba.excel.EasyExcel;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;

/**
 * @description: ExcelUtil
 * @author: zj
 * @date: 2022-07-14 17:07
 */
public class ExcelUtil {

    public static void exportExcel(List<?> objects, Class clazz, String fileName,
                                   String sheetName, HttpServletResponse response) throws IOException {

        // 设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");

        // 设置防止中文名乱码
        String exportName = URLEncoder.encode(fileName, "utf-8");
        String sheetSubName = URLEncoder.encode(sheetName, "utf-8");

        // 文件下载方式(附件下载还是在当前浏览器打开)
        response.setHeader("Content-disposition", "attachment;filename=" +
                exportName + ".xlsx");

        // 向Excel中写入数据
        EasyExcel.write(response.getOutputStream(), clazz)
                .sheet(sheetSubName)
                .doWrite(objects);

    }

}

Controller

实现从MySQL读取数据，写入HttpServletResponse流中的主要逻辑

package com.example.excel.controller;

import com.example.excel.entity.User;
import com.example.excel.mapper.UserMapper;
import com.example.excel.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

/**
 * @description: Excel
 * @author: zj
 * @date: 2022-07-14 17:02
 */
@RestController
@Slf4j
public class ExcelController {

    @Resource
    private UserMapper userMapper;


    @GetMapping("/excel")
    public void excel(HttpServletResponse response) throws IOException {

        long st = System.currentTimeMillis();

        List<User> users = userMapper.selectSub(0, 10000);

        ExcelUtil.exportExcel(users, User.class, "user_info", "sheet_one", response);

        log.info("export excel success, cost {} s.", (System.currentTimeMillis() - st) / 1000);

    }

}

效果

浏览器输入接口地址下载Excel文件

浏览器输入localhost/excel

导出文件

APIFox

注意：返回的是文件流