SpringBoot整合EasyExcel

有时需要以excel文件的形式下载数据库中的数据。

1.引入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>

2. 建立实体类字段与Excel表中列的映射关系

使用@ExcelProperty注解，value表示表头名称，index表示excel列号（0为第1列）

public class UserExcelModel extends BaseRowModel {

    /**
     * value: 表头名称
     * index: 列的号, 0表示第一列
     */
    @ExcelProperty(value = "用户id", index = 0)
    private int id;

    @ExcelProperty(value = "用户名",index = 1)
    private String userName;

    @ExcelProperty(value = "用户密码",index = 2)
    private String password;

    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        UserExcelModel that = (UserExcelModel) o;
        return id == that.id &&
                userName.equals(that.userName) &&
                password.equals(that.password);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, userName, password);
    }
}

3. Dao

@Repository
public interface UserDao {
    List<UserExcelModel> getAllUsersByExcel();
}

4. Service

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    public List<UserExcelModel> getAllUsersByExcel() {
        return userDao.getAllUsersByExcel();
    }
}

5. Controller

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/exportUserInfo")
    public void exportDeviceInfo(HttpServletResponse response) throws IOException {
        response.setContentType("multipart/form-data");
        response.setHeader("content-type", "application/octet-stream");
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode("用户信息表.xlsx", "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        ExcelUtils.writeWithTemplate(userService.getAllUsersByExcel(), outputStream);
    }
}

6. mapper文件

<resultMap id="UserExcelResultMap"  type="demo.srammy.springbootwitheasyexcel.excel.UserExcelModel">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="user_name" jdbcType="VARCHAR" property="userName" />
        <result column="password" jdbcType="VARCHAR" property="password" />
    </resultMap>

    <select id="getAllUsersByExcel" resultMap="UserExcelResultMap">
          select id,user_name,password
           from t_user
          order by id asc
    </select>

7. 写入excel文件工具类

public class ExcelUtils {
    private static Sheet initSheet;
    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("sheet");
        //设置自适应宽度
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 生成excle
     * @param filePath 绝对路径, 如：/home/Downloads/aaa.xlsx
     * @param data 数据源
     */
    public static void writeWithTemplate(String filePath, List<? extends BaseRowModel> data){
        writeWithTemplateAndSheet(filePath,data,null);
    }

    /**
     * 生成excle
     * @param filePath 绝对路径, 如：/home/Downloads/aaa.xlsx
     * @param data 数据源
     * @param sheet excle页面样式
     */
    public static void writeWithTemplateAndSheet(String filePath, List<? extends BaseRowModel> data, Sheet sheet){
        if(CollectionUtils.isEmpty(data)){
            return;
        }

        sheet = (sheet != null) ? sheet : initSheet;
        sheet.setClazz(data.get(0).getClass());

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write(data,sheet);
        } catch (FileNotFoundException e) {
            System.out.println("找不到文件或文件路径错误, 文件：{}" + filePath);
        }finally {
            try {
                if(writer != null){
                    writer.finish();
                }

                if(outputStream != null){
                    outputStream.close();
                }
            } catch (IOException e) {
                System.out.println("excel文件导出失败, 失败原因：{}" + e);
            }
        }

    }

    /*********************匿名内部类开始，可以提取出去******************************/

    public static class MultipleSheelPropety{

        private List<? extends BaseRowModel> data;

        private Sheet sheet;

        public List<? extends BaseRowModel> getData() {
            return data;
        }

        public Sheet getSheet() {
            return sheet;
        }

        public void setData(List<? extends BaseRowModel> data) {
            this.data = data;
        }

        public void setSheet(Sheet sheet) {
            this.sheet = sheet;
        }
    }

    /**
     * 解析监听器，
     * 每解析一行会回调invoke()方法。
     * 整个excel解析结束会执行doAfterAllAnalysed()方法
     */
    public static class ExcelListener extends AnalysisEventListener {

        private List<Object> datas = new ArrayList<>();

        public List<Object> getDatas() {
            return datas;
        }

        public void setDatas(List<Object> datas) {
            this.datas = datas;
        }

        /**
         * 逐行解析
         * object : 当前行的数据
         */
        @Override
        public void invoke(Object object, AnalysisContext context) {
            //当前行
            // context.getCurrentRowNum()
            if (object != null) {
                datas.add(object);
            }
        }


        /**
         * 解析完所有数据后会调用该方法
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            //解析结束销毁不用的资源
        }

    }

    /************************匿名内部类结束，可以提取出去***************************/
}

8. 使用

在浏览器地址栏直接输入http://ip:host/exportUserInfo，即可以以excel文件的形式下载数据库中数据。