Easy Excel

ki1616 / 2023-05-19 / 原文

1.用处

可以用来生成各种Excel

2.入门案例

引入pom

　　　　 <!--easypoi-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.httpcomponents/httpcore -->
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpcore</artifactId>
            <version>4.4.15</version>
        </dependency>

工具类封装

public class EasyExcelUtils {
    public static void export(HttpServletResponse response, String name, String bottomName, Class<?> object, List<?> data) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), object).autoCloseStream(Boolean.FALSE).sheet(bottomName).doWrite(data);
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
}

调用

//LiveRankingDto 导出模版  data是数据传入是 List<LiveRankingDto> 格式的
EasyExcelUtils.export(response, "文件名字", "excel底部标题", LiveRankingDto.class, data);

LiveRankingDto

@Data
@ApiModel(value="直播场次排行榜")
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER)
//horizontalAlignment 水平居中   verticalAlignmen垂直居中
public class LiveRankingDto {

    @ApiModelProperty(value = "排名")
    @ExcelProperty("排名")
    @ColumnWidth(10) //设置宽度
    private Integer rank;

    @ApiModelProperty(value = "开课老师")
    @ExcelProperty("开课老师")
    @ColumnWidth(30)
    private String teacher;

    @ApiModelProperty(value = "所属学校")
    @ExcelProperty("所属学校")
    @ColumnWidth(40)
    private String school;

    @ApiModelProperty(value = "场次（单位：场）")
    @ExcelProperty("场次（单位：场）")
    @ColumnWidth(30)
    private Integer total;

}