POI和easyExcel

2020-03-24 阅读量

POI和easyExcel

目标

1、将数据库信息导出为excel表格（导出数据）
2、将excel表中的信息导入到数据库（导出数据）
操作excel比较流行的就是 Apache POI 和阿里巴巴easyExcel。

Apache POI

POI的主要功能是可以用Java操作Microsoft Office的相关文件，这里主要讲Excel 。

1.导入依赖

<!-- 03版本excel   ( .xls )-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>

<!-- 07版本excel   ( .xlsx )-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>

2.写表格

/*
使用表格对象要注意三种创建方式
    HSSF：Excel2003版，扩展名为.xls。一个sheet最大行数65536，最大列数256。 
          虽然处理行数有限，但是写入速度快。
    XSSF：Excel2007版开始，扩展名为.xlsx。一个sheet最大行数1048576，最大列数16384。
          写入速度慢，耗内存，但可处理较大数据量。
    SXSSF：是在XSSF基础上，POI3.8版本开始提供的支持低内存占用的操作方式，扩展名为.xlsx。
    
    Excel版本兼容性是向下兼容。
    
API:
    Workbook wordkbook =new HSSFWorkbook();  //创建一个Workbook对象
    wordkbook.createSheet("表名");      //创建工作表
    Row row1=sheet.createRow(0);       //创建行，0默认为第一行
    Cell cell = row1.createCell(0);    //创建单元格，第一行第0个位置。
    cell.setCellValue("值");           //可给单元格赋值
*/
@Test
public void testWrite() throws Exception {
    //1.创建03版工作簿 （XSSFWorkbook() 创建07版）
    Workbook workbook = new HSSFWorkbook();
    
    //2.创建一个工作表
    Sheet sheet = workbook.createSheet("统计表");
    
    //3.创建一行 (0代表第一行)
    Row row1 = sheet.createRow(0);

    //4.在第一行第一个位置创建个单元格 （即(1, 1) 位置）
    Cell cell = row1.createCell(0);

    //5.往单元格中填数据
    cell.setCellValue("用户名");
    //(1, 2)位置填数据
    row1.createCell(1).setCellValue("张三");

    //创建第二行
    Row row2 = sheet.createRow(1);
    row2.createCell(0).setCellValue("统计时间");
    row2.createCell(1).setCellValue("2020-03-23");

    //6.生成一张表（03版后缀 .xls）
    FileOutputStream fileOutputStream = new FileOutputStream("D:/03版.xls");
    workbook.write(fileOutputStream);

    //7.关闭流
    fileOutputStream.close();
}

结果：
图加载中...

3.读表格

@Test
public void testRead() throws Exception {
    //1.获取文件流
    FileInputStream fileInputStream = new FileInputStream("D:/03版.xls");
    
    //2.读取Excel文件，返回工作簿
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    
    //3.获取工作表
    Sheet sheet = workbook.getSheet("统计表");
    
    //4.获取第一行
    Row row1 = sheet.getRow(0);
    
    //5.获取第1行 第0个单元格
    Cell cell = row1.getCell(0);

    /*
        6.获取单元格的值（一定要进行类型的判断）
            getStringCellValue()  获取字符串
            getNumericCellValue() 获取数字类型
            getBooleanCellValue() 获取布尔类型
            等等。。。。。
    */
    System.out.println(cell.getStringCellValue());

    //7.关闭流
    fileInputStream.close();
}

4.读取不同类型的数据

目标表格如下：
图加载中...

/*
    读取整个表内容（需要注意类型转换）

    sheet.getPhysicalNumberOfRows();  //获取总行数
    sheet.getRow(0).getPhysicalNumberOfCells() //获取第0行单元格总数
*/
@Test
public void testCellType() throws Exception {
    FileInputStream fileInputStream = new FileInputStream("D:/用户.xls");
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheet("统计表");

    // 获取表第一行内容
    Row rowTitle = sheet.getRow(0);
    // 行不为空，读取第一行所有内容
    if(rowTitle != null){
        // 获取本行的单元格个数
        int cellCount = rowTitle.getPhysicalNumberOfCells();

        // 遍历并获取每个单元格的内容
        for(int cellNum = 0; cellNum < cellCount; cellNum++){
            Cell cell = rowTitle.getCell(cellNum);
            if (cell != null){
                System.out.print(cell.getStringCellValue() + "|");
            }
        }
    }

    // 获取表中的数据（第二行往后）
    int rowCount = sheet.getPhysicalNumberOfRows(); // 获取行数
    for(int rowNum = 1; rowNum < rowCount; rowNum++){
        Row rowData = sheet.getRow(rowNum); // 获得某行数据
        if(rowData != null){
            // 读取列
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for(int cellNum = 0; cellNum < cellCount; cellNum++){
                Cell cell = rowData.getCell(cellNum);
                // 匹配列的类型
                if(cell != null){
                    int  cellType = cell.getCellType();// 获取列类型
                    String cellValue = "";

                    switch (cellType){
                        case HSSFCell.CELL_TYPE_STRING: // String
                            cellValue = cell.getStringCellValue(); // 调用String方法
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN: // boolean
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK: // Null
                            break;
                        case HSSFCell.CELL_TYPE_NUMERIC: // 数字（分为日期和普通数字）
                            // 判断是时间还是数字
                            if(HSSFDateUtil.isCellDateFormatted(cell)){
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            }else{
                                // 如果不是日期格式，防止数字过长，转化为String类型
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case HSSFCell.CELL_TYPE_ERROR: // 错误
                            System.out.println("有错误");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    fileInputStream.close();
}

4.EasyExcel

官网：https://www.yuque.com/easyexcel/doc/easyexcel

1.导入依赖

<!--注意它里面自带poi依赖，如果重复导入会报ClassNotfound-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
</dependency>

2.写入测试

//实体类
@Data
class DemoData {

    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double doubleData;

    // 如果想忽略某个字段，不让其出现在表中，加此注解
    @ExcelIgnore
    private String ignore;

}


public class TestEasyWrite {
    // 写入数据到List
    public static List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>(); //加载实体类
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i); //往实体类中添加内容
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);  //把实体类放入list集合
        }
        return list;
    }

    
    //根据list，写入Excel 写法1
    @Test
    public void simpleWrite(){
        String fileName = "D:/测试.xlsx";
        /*
            这里，需要指定用哪个实体类去写。
            write(fileName, 格式类)
            sheet(要创建的工作表名)
            doWrite(要写入的数据)
            文件流会自动关闭
        */
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

    
    //写法2
    @Test
    public void simpleWrite2(){
        String fileName = "D:/测试.xlsx";
        // 这里，需要指定用哪个实体类去写
        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        excelWriter.write(data(), writeSheet);
        // 千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }
}

效果：
图加载中...

3.读取测试

1.实体类

@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

2.假设有持久化操作，写个Dao

/**
 * 假设这个是你的DAO存储。
 * 当然还要这个类让spring管理，如果不需要存储，也就不需要这个类。
 **/
public class DemoDAO {
    // 持久化操作
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert
        // 自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

3.监听器

// 有个很重要的点 DemoDataListener 不能被spring管理。
// 每次读取excel都要new一个, 然后里面用到spring可以构造方法传进去
public class DemoDataListener  extends AnalysisEventListener<DemoData> {
    //日志
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);

    //每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();

    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    //如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 读取数据会执行 invoke 方法
     * 这个每一条数据解析都会来调用
     * DemoData: 实体类
     * AnalysisContext：分析器
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data)); //打印获取到的数据
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData(); //持久化逻辑
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

4.测试

/**
 * 最简单的读
 * 重点的读取逻辑在 invoke() 方法里
 * 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器
 */
@Test
public void simpleRead() {
    String fileName = "D:/demo.xlsx";
    // 有个很重要的点 DemoDataListener 不能被spring管理，
    //要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 这里 需要指定用哪个实体类去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

4.Web中的读

//文件上传
@PostMapping("upload")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
    EasyExcel.read(file.getInputStream(), UploadData.class, 
                   new UploadDataListener(uploadDAO)).sheet().doRead();
    return "success";
}

5.总结

easyExcel的确比poi方便，但是它的读需要编写监听器。
建议大数据用easyExcel,因为大数据时poi对于内存消耗非常大。
由于apache poi和jxl，excelPOI都有一个严重的问题，就是非常消耗内存，特别处理数据量多时，速度慢并且时有异常发生，所以改用由阿里研发的easyExcel更可靠一些,它的官方建议对于1000行以内的采用原来poi的写法一次读写，但于1000行以上的数据，有用了一行行进行解析的方案，这样避免了内存的溢出。
asyExcel扩展功能很多，且Api式调用真的轻松很多。