EasyExcel的使用方法

一、导入依赖

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

二、编写实体类

@Data
public class DemoData {
    @ExcelProperty(value = "学生编号", index = 0)
    private Integer sno;
    @ExcelProperty(value = "学生姓名", index = 1)
    private String sname;
}

三、写入Excel

@Test
public void writeExcel() {
    //实现excel写操作
    // 写法1 JDK8+
    // since: 3.0.0-beta1
    String fileName = "F:\\online-education/student.xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("Sheet1").doWrite(getData());
}
private static List<DemoData> getData(){
    List<DemoData> list=new ArrayList<>();
    for (int i=0;i<10;i++){
        DemoData demoData = new DemoData();
        demoData.setSno(i);
        demoData.setSname("liming"+i);
        list.add(demoData);
     }
    return list;
}

四、写入Excel并以流的方式发送给前端并下载

1.编辑响应体参数

	/**
     * 导出
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(data);
    }
	
	/**
     * 流方式响应给前端
     *
     * @param fileName
     * @param response
     * @return
     * @throws Exception
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        response.setStatus(RESPONSE_SUCCESS_CODE);
        return response.getOutputStream();
    }

@PostMapping("exportExcel")
public void webWriteExcel(HttpServletResponse response) throws IOException {
         ExcelUtil.writeExcel(response, ptPartDetails, fileName, sheetName, PtPartDetail.class);
    }

五、读取Excel

编写监听类并重写所需方法

public class ExcelListener extends AnalysisEventListener<DemoData> {
    //一行一行的读取excel内容
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        System.out.println("数据："+demoData);
    }
	
    //读取表头内容
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头："+ headMap);
    }

    //读取完成之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

读取Excel文件

@Test
    public void readExcel(){
        String fileName = "F:\\online-education/student.xlsx";
        EasyExcel.read(fileName,DemoData.class,new ExcelListener()).sheet().doRead();
    }