SpringBoot整合EasyExcel进行读和写

EasyExcel网址:easyexcel.opensource.alibaba.com/docs/curren…

1.引入pom文件

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.3.2</version>
</dependency>

2.excel的读操作

实体类:

@Data
@AllArgsConstructor
@NoArgsConstructor
public class SysUserExcelVO {


    @ContentStyle
    @ColumnWidth(30)
    @ExcelProperty(value = "用户名",index = 0)
    private String userName;

    @ColumnWidth(30)
    @ExcelProperty(value = "密码",index = 1)
    private String password;
}

    EasyExcel.read(file.getInputStream(), SysUserExcelVO.class, new ReadListener<SysUserExcelVO>() {
        /**
         * 单次缓存的数据量
         */
        public static final int BATCH_COUNT = 100;
        /**
         *临时存储
         */
        private List<SysUserExcelVO> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

        @Override
        public void invoke(SysUserExcelVO data, AnalysisContext context) {
            cachedDataList.add(data);
            if (cachedDataList.size() >= BATCH_COUNT) {
                saveData();
                // 存储完成清理 list
                cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            saveData();
        }

        /**
         * 加上存储数据库
         */
        private void saveData() {
            log.info("{}条数据，开始存储数据库！", cachedDataList.size());
            log.info("存储数据库成功！");
        }
    }).sheet().doRead();

3.excel的写操作

简单的写:

try {
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    //设置背景颜色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
    //设置头字体
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)13);
    headWriteFont.setBold(true);
    headWriteCellStyle.setWriteFont(headWriteFont);
    //设置头居中
    headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

    //内容策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //设置 水平居中
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

    HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);


    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName= URLEncoder.encode("用户数据","UTF-8");
    response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
    List<SysUser> sysUsers = this.getAllUser();
    List<SysUserExcelVO> sysUserExcelVOS=new ArrayList<>();
    for (SysUser sysUser:sysUsers){
        SysUserExcelVO sysUserExcelVO=new SysUserExcelVO();
        BeanUtils.copyProperties(sysUser,sysUserExcelVO);
        sysUserExcelVOS.add(sysUserExcelVO);
    }
    EasyExcel.write(response.getOutputStream(),SysUserExcelVO.class)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .sheet("用户数据").doWrite(sysUserExcelVOS);
    } catch (Exception e) {
    e.printStackTrace();
    // 重置response
    response.reset();
    response.setContentType("application/json");
    response.setCharacterEncoding("utf-8");
    Map<String, String> map = new HashMap<String, String>();
    map.put("status", "failure");
    map.put("message", "下载文件失败" + e.getMessage());
    response.getWriter().println(JSON.toJSONString(map));
    }

根据模板的填充

try {
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    String fileName= URLEncoder.encode("用户数据","UTF-8");
    response.setHeader("Content-disposition","attachment;filename="+fileName+".xlsx");
    List<SysUser> sysUsers = this.getAllUser();
    List<SysUserExcelVO> sysUserExcelVOS=new ArrayList<>();
    for (SysUser sysUser:sysUsers){
        SysUserExcelVO sysUserExcelVO=new SysUserExcelVO();
        BeanUtils.copyProperties(sysUser,sysUserExcelVO);
        sysUserExcelVOS.add(sysUserExcelVO);
    }
    InputStream templateFileName = this.getClass().getResourceAsStream("/excelTemplates/用户导出模板.xlsx");
    EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).sheet().doFill(sysUserExcelVOS);
} catch (Exception e) {
    e.printStackTrace();
    // 重置response
    response.reset();
    response.setContentType("application/json");
    response.setCharacterEncoding("utf-8");
    Map<String, String> map = new HashMap<String, String>();
    map.put("status", "failure");
    map.put("message", "下载文件失败" + e.getMessage());
    response.getWriter().println(JSON.toJSONString(map));
}

4.下载导入模板

try {
    InputStream in = this.getClass().getResourceAsStream("/excelTemplates/用户导入模板.xlsx");
    String fileName = "用户导入模板";
    //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
    response.setContentType("multipart/form-data");

    //2.设置文件头：最后一个参数是设置下载文件名
    response.setHeader("Content-Disposition", "attachment;filename="
            + new String(fileName.getBytes("utf8"), "ISO-8859-1") + ".xlsx"); //支持中文文件名

    //3.通过response获取OutputStream对象(out)
    OutputStream out = new BufferedOutputStream(response.getOutputStream());

    int b = 0;
    byte[] buffer = new byte[2048];
    while ((b = in.read(buffer)) != -1) {
        out.write(buffer, 0, b); //4.写到输出流(out)中
    }
    in.close();
    out.flush();
    out.close();
} catch (Exception e) {
    e.printStackTrace();
    // 重置response
    response.reset();
    response.setContentType("application/json");
    response.setCharacterEncoding("utf-8");
    Map<String, String> map = new HashMap<String, String>();
    map.put("status", "failure");
    map.put("message", "下载文件失败" + e.getMessage());
    response.getWriter().println(JSON.toJSONString(map));
}