EasyExcel使用笔记

简介

EasyExcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

GitHub地址：https://github.com/alibaba/easyexcel

使用

注意: excel文件用的个人考勤表，放在项目根路径

boot项目导入pom依赖

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

创建简单的模型类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;


@Data
public class ExcelPropertyIndexModel extends BaseRowModel {

    @ExcelProperty(value = "日期", index = 0)
    private String dateJuly;
    @ExcelProperty(value = "上班时间", index = 1)
    private String onDuty;
    @ExcelProperty(value = "下班时间", index = 2)
    private String offDuty;
    @ExcelProperty(value = "加班时长", index = 3)
    private String overtime;
    @ExcelProperty(value = "备注", index = 6)
    private String last;
    
}

创建监听器（解析）

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;


@Slf4j
@Component
public class ExcelListener extends AnalysisEventListener {

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

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

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

    @Override
    public void invoke(Object object, AnalysisContext context) {
        List<Object> stringList = (List<Object>) object;
        datas.add(stringList);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //解析结束销毁不用的资源
        // datas.clear();
    }
}

创建工具类（读取，导出）

@Slf4j
public class ExcelUtils {

    /**
     * 解析excel文件内容
     *
     * @param fileName
     * @return
     */
    public static List<List<Object>> readExcel(String fileName) {

        File file = new File(fileName);
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        // 解析每行结果在listener中处理
        ExcelListener listener = new ExcelListener();
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
        excelReader.read();
        List<List<Object>> datas = listener.getDatas();
        return datas;
    }



    /**
     * 导出方法,生成excle
     *
     * @param filePath 绝对路径,
     * @param data     数据源
     * @param sheet    excle页面样式
     */
    public static void writeSimpleBySheet(String filePath, List<List<Object>> data, Sheet sheet) {

        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = new FileOutputStream(filePath);
            writer = EasyExcelFactory.getWriter(outputStream);
            writer.write1(data, sheet);
        } catch (FileNotFoundException e) {
            log.error("找不到文件或文件路径错误, 文件：{}", filePath);
        } finally {
            try {
                if (writer != null) {
                    writer.finish();
                }
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }
    }
    
} 

创建Controller类查看实现效果

@RestController
@RequestMapping("/excel")
@Slf4j
public class ExcelController {

    /**
     * 导入（解析）EXCEL文件
     * @return
     */
    @GetMapping("/writerExcel")
    @ResponseBody
    public List<List<Object>> writerExcel() {
        List<List<Object>> lists = ExcelUtils.readExcel("withHead.xls");
        if (lists != null) {
            log.info("表数据："+lists);
        } else {
           log.info("空异常！");
        }
        return lists;
    }


    /**
     * 导出EXCEL文件
     * @param filePath 文件绝对路径
     */
    @PostMapping(value = "/exportExcel")
    public void exportExcel(@ApiParam(name="filePath",value="文件路径",required=true) @RequestParam String filePath){
        //木有数据库数据源，用xls的解析数据当作数据源
        List<List<Object>> lists = ExcelUtils.readExcel("withHead.xls");
        Sheet sheet1 = new Sheet(1, 0, ExcelPropertyIndexModel.class);
        ExcelUtils.writeSimpleBySheet(filePath,lists,sheet1);
    }
}