Alibaba EasyExcel的写(导出)

Alibaba EasyExcel的写(导出)

参考：https://www.yuque.com/easyexcel/doc/fill 官网

一、前言

工作中使用到EasyExcel后，通过查找相关资料，然后对这些资料的整理，加上自己的亲身实践，下面是我用例子说明EasyExcel的使用。

二、使用步骤

1.数据表

type表示该账号是否可用（启用：1，禁用：0）

2.表对应的实体类

@Data
public class User {


    @ExcelIgnore
    Integer id;

    @ExcelProperty(value = "用户名", index = 0)//第一列序号为0
    String name;

    @ExcelProperty(value = "密码", index = 1)
    String password;

    @ExcelProperty(value = "状态", index = 2)
    Integer type;
}

3.Controller层

@RestController
@RequestMapping("/user")
@Api(tags = "UserController", description = "用户管理")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/exportUserExcel")
    @ApiOperation("导出用户账号信息")
    public void exportUserExcel() {
        userService.exportUserExcel();
    }

其中使用到spring boot注解加swagger2注解，稍后使用swagger2进行测试

4.Service层(重点)

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Autowired
    private HttpServletResponse response;

    @Override
    public void exportUserExcel() {
        //查询用户数据
        List<User> users = userDao.selectAll();

        //导出Excel
        //设置两个头
        //响应内容格式
        response.setContentType("application/vnd.ms-excel");
        //设置前端下载文件名
        response.setHeader("Content-disposition", "attachment;filename=user.xlsx");

        //向前端写入文件流流
        try {
            EasyExcel.write(response.getOutputStream(), User.class).sheet("工作表1").doWrite(users);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

5.Dao层

public interface UserDao {

    @Select("select * from user")
    List<User> selectAll();
}

注意：dao接口需要被扫描到才能完成映射

6.输出结果

单元格数据比较多的话，可以在实体类中使用@HeadRowHeight(30)设置表头行高，@ColumnWidth(15) 设置列宽

7.加入转换器

场景：状态列输出结果为"启用"或者"禁用"字样

EasyExcel 支持我们自定义转换器，将 excel 的内容转换为我们程序需要的信息，这里新建 TypeConverter，用来转换状态信息

public class TypeConverter implements Converter<Integer> {
    public static final String DISABLE = "禁用";
    public static final String ENABLE = "启用";

    @Override
    public Class supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (integer == 1) {
            return new CellData(ENABLE);
        } else if (integer == 0) {
            return new CellData(DISABLE);
        } else {
            return new CellData("");
        }
    }
}

在实体类对应属性上加上转换器

@Data
public class User {


    @ExcelIgnore
    Integer id;

    @ExcelProperty(value = "用户名", index = 0)//第一列序号为0
    String name;

    @ExcelProperty(value = "密码", index = 1)
    String password;

    @ExcelProperty(value = "状态", index = 2,converter = TypeConverter.class)
    Integer type;
}

数据表

输出结果

8.最后附上依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--MySQL数据库驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.16</version>
        </dependency>
        <!--MyBatis Plus 依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>
        <!--Swagger-UI API文档生产工具-->
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>
        <!--解决Swagger 2.9.2版本NumberFormatException-->
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-models</artifactId>
            <version>1.6.0</version>
        </dependency>
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.6.0</version>
        </dependency>

        <!--excel文件处理-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <dependency>
            <groupId>net.sf.json-lib</groupId>
            <artifactId>json-lib</artifactId>
            <version>2.4</version>
            <classifier>jdk15</classifier>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.73</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>
    </dependencies>

三、结尾

好了EasyExceld导出总结到此结束，虽然不是很深入，但是希望能够在日常工作中给大家带来帮助！如发现问题，欢迎留言。