POI和EasyExcel

概述

1. 将用户信息导出为excel表格
2. 将excel表中的信息录入到网站数据库
   Apache POI和阿里巴巴easyExcel

---

POI

官网：https://poi.apache.org/
会比较麻烦

1. HSSF 03版excel xls
2. XSSF 07版excel xlsx
3. HWPF word
4. HSLF ppt
5. HDGF Visio

导入依赖

 <!--03版-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>

    <!--07版-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.1.2</version>
    </dependency>

    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.9</version>
    </dependency>

    <!--单元测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13</version>
    </dependency>
</dependencies>

---

EasyExcel

easyExceleasyExcel是阿里巴巴开源的一个excel，简单，节省内存
官网：https://github.com/alibaba/easyexcel
java解析excel，简单，节省内存
原因在于：解析excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析
内存问题：

• POI=100W数据先加载到内存，再写入到文件
• EasyExcel：一次写一行

注意：easyexcel里自带poi版本，cell.getCellType()会返回int类型，此时判断数据类型switch case时，case HSSFCell_TYPE_STRING等等

EasyExcel和POI在解析excel时的对比图

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

主要对象：1.工作薄 2.工作表 3.行 4.列

POI-Excel写

        String path = "D:\\tools\\poi\\";
        //1.创建工作薄
        Workbook workbook = new HSSFWorkbook();
        //2.创建工作表
        Sheet sheet = workbook.createSheet("观众统计表");
        //3.创建行
        Row row1 = sheet.createRow(0);
        //4.创建单元格(1,1)
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("今日新增观众");

        Cell cell2 = row1.createCell(1);
        cell2.setCellValue(666);

        //第二行(2,1)
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("统计时间");
        //(2,2)
        Cell cell22 = row2.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell22.setCellValue(time);

        //生成一张表(IO流)
        FileOutputStream os = new FileOutputStream(path + "观众统计表03.xls");
        workbook.write(os);
        //关闭流
        os.close();
        System.out.println("生成完毕");

07版换成 XSSFWorkbook对象，文件格式变为xslx
加速版：对07加速SXSSFWorkbook

测试

 @Test
    public void test03() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i + "," + j);
            }
        }
        FileOutputStream os = new FileOutputStream(path + "03测试.xls");
        workbook.write(os);
        os.close();
        long end = System.currentTimeMillis();
        System.out.println("03用时：" + (end - start) / 1000);//2秒
    }

    @Test
    public void test07() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i + "," + j);
            }
        }
        FileOutputStream os = new FileOutputStream(path + "07测试.xlsx");
        workbook.write(os);
        os.close();
        long end = System.currentTimeMillis();
        System.out.println("07用时：" + (end - start) / 1000);//16秒
    }

07比03慢，所以大文件写用SXSSFWorkbook

原因：过程中会产生临时文件，需要清理临时文件，默认有100条记录保存在内存中，如果超过，则最前面的数据呗写如临时文件中

自定义内存中数据数量：new SXSSFWorkbook(数量)；

测试07加速版

@Test
    public void test07s() throws Exception {
        long start = System.currentTimeMillis();
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i + "," + j);
            }
        }
        FileOutputStream os = new FileOutputStream(path + "07加速版测试.xlsx");
        workbook.write(os);
        os.close();
        //清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println("07加速版用时：" + (end - start) / 1000);//3秒
    }

注意：例如合并区域，注意等操作仍储存在内存中，广泛使用，需要大量内存。

POI-Excel读

03|07

String path = "D:\\tools\\poi\\";
    @Test
    public void testRead03() throws Exception {
        //1.获取文件流
        FileInputStream is = new FileInputStream(path + "观众统计表03.xls");
        //2.创建工作薄,放入文件流
        Workbook workbook = new HSSFWorkbook(is);
        //3.得到工作表
        Sheet sheet = workbook.getSheetAt(0);
        //4.获取行
        Row row = sheet.getRow(0);
        //5.获取列
        Cell cell = row.getCell(0);
        //6.获取数据
        String value = cell.getStringCellValue();//字符串类型
        /*cell.getBooleanCellValue();//布尔类型
        cell.getDateCellValue();//日期类型
        cell.getNumericCellValue();//数字类型*/
        System.out.println(value);//今日新增观众
        is.close();
    }

07版只需改变对象即可

读取不同数据类型

@Test//测试读取不同数据类型
    public void testReadCellType() throws Exception {
        FileInputStream is = new FileInputStream(path + "观众统计表03.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);
        //1.获取标题内容
        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) {
                    CellType cellType = cell.getCellType();
                }
            }
        }
        //获取表中内容
        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);
                    String cellValue = "";
                    //匹配列的数据类型
                    if (cell != null) {
                        //列的类型，低版本返回int类型
                        CellType cellType = cell.getCellType();
                        switch (cellType) {
                        //低版本依赖，case HSSFCell_TYPE_STRING(废弃)，
                            case STRING:
                                System.out.println("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.println("[BOOLEAN]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK:
                                System.out.println("[BLANK]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case NUMERIC://数字（日期，普通数字）
                                System.out.println("[NUMERIC]");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    System.out.println("日期");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {
                                    System.out.println("普通数字转为字符串"); 
                                    HSSFDataFormatter hssfDataFormatter = new HSSFDataFormatter(); 
                                    cellValue = hssfDataFormatter.formatCellValue(cell); 
                                    //直接toString
                                    //cellValue = String.valueOf(cell.getNumericCellValue());
                                    //弃用
                                    /* cell.setCellType(CellType.STRING); 
                                    cellValue = cell.toString();*/
                                }
                                break;
                            case ERROR:
                                System.out.println("[数据类型错误]");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        is.close();
    }

注意：类型转换问题

计算公式

@Test//公式计算
    public void testFormula() throws Exception {
        //文件流
        FileInputStream is = new FileInputStream(path + "观众统计表03.xls");
        //创建工作薄
        Workbook workbook = new HSSFWorkbook(is);
        //获得工作表
        Sheet sheet = workbook.getSheetAt(0);
        //获得行
        Row row = sheet.getRow(4);//第五行是公式算出
       //获得列
        Cell cell = row.getCell(0);
        //拿到计算公式
        FormulaEvaluator fe = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        //输入单元格内容
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA://公式类型
                String formula = cell.getCellFormula();
                System.out.println("公式：" + formula);
                 //计算
                CellValue evaluate = fe.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
                break;
        }
    }

---

EasyExcel操作

快速， 简单避免OOM：OutOfMemory (内存溢出)的java处理excel工具

导入依赖

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

注意：里面已经包含了poi相关，防止依赖冲突，自己项目删除poi相关依赖

写

测试类

    String path = "D:\\tools\\poi\\";
    private List<DemoData> getData() {
        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);
        }
        return list;
    }

    @Test
    public void simpleWrite() {
        String fileName = path + "easyTest.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        //write(fileName,格式类)
        //sheet(工作表名)
        //doWrite(数据)
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(getData());
    }

读

对象

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

监听器

// 有个很重要的点 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>();
    //也可以是一个service
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    //读取数据会执行invoke方法，invoke(数据实体类，分析上下文)
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
     //所有数据解析完成会调用此方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
     //存入数据库
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

持久层

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

测试代码

    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        //sheet()里可以写sheet索引，sheet名字
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

多行头

@Test
    //多行头
    public void complexHeaderRead() {
        String fileName = path + "03测试.xls";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet
        EasyExcel.read(fileName, com.jpy.easy.read.DemoData.class, new DemoDataListener()).sheet().headRowNumber(1).doRead();//1就是从第2行开始到最后
    }

其他需求请参考

官方文档：https://www.yuque.com/easyexcel/doc/easyexcel

1. invokeHeadMap方法，获取表头监听器要重写此方法

固定套路：

1. 写入，固定类格式进行
2. 读取，根据监听器设置的规则进行读取