Java操作Excel之POI、EasyExcel、Hutool

POI

Apache 公司发布的，可以使用java语言操作Microsoft Office文件的开源Api。

maven依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

简单示例

• 创建Excel

        //创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建sheet页
        HSSFSheet firstSheet = workbook.createSheet("sheet1");
        //创建第一行 通常第一行作为 数据表头
        HSSFRow row = firstSheet.createRow(0);
        //设置 第一行的列数据
        String [] titles = new String[]{"序号","姓名","性别"};
        for(int i=0; i<titles.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
        }
        //插入1000条测试数据
        for(int i=1; i<=1000; i++){
            HSSFRow rowData = firstSheet.createRow(i);
            HSSFCell cell0 = rowData.createCell(0);
            cell0.setCellValue(i);
            HSSFCell cell1 = rowData.createCell(1);
            cell1.setCellValue("测试人员"+i);
            HSSFCell cell2 = rowData.createCell(2);
            cell2.setCellValue(i%2==0?"男":"女");
        }
        //创建文档 写入数据
        String excelPath = "E://test.xlsx";
        try {
            FileOutputStream stream = new FileOutputStream(excelPath);
            workbook.write(stream);
            stream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

• 读取Excel

        //读取文档的 地址
        String excelPath = "E://test.xlsx";
        InputStream inputStream = null;
        try {
            //创建读取 的工作簿
            inputStream = new FileInputStream(excelPath);
            POIFSFileSystem fs = new POIFSFileSystem(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(fs);
            //获取要读取的sheet页
            HSSFSheet sheet0 = workbook.getSheetAt(0);
            //遍历sheet行 从第一行度取
            for(int rowNum = 1; rowNum <= sheet0.getLastRowNum() ; rowNum++){
                HSSFRow row = sheet0.getRow(rowNum);
                //打印第 rowNum 行的数据
                for(int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++){
                    System.out.print(row.getCell(cellNum)+"||");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

EasyExcel

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

maven依赖

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

简单示例

• 创建实体

@Data
public class People {
    @ExcelProperty("序号")
    private String number;
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("性别")
    private String sex;
}

• 创建Excel

        List<People> peoples = new ArrayList<>();
        for(int i=1;i<=1000;i++){
            People people = new People();
            people.setNumber(String.valueOf(i));
            people.setName("测试人员"+i);
            people.setSex(i%2==0?"男":"女");
            peoples.add(people);
        }
        String excelPath = "E://test.xlsx";
        /**
         * 1.创建Excel
         * 2.写入数据
         * 3.自动关闭流
         */
        EasyExcel.write(excelPath, People.class).sheet("sheet1").doWrite(peoples);

• 读取Excel
  1.创建监听类

public class PeopleListener extends AnalysisEventListener<People> {
    @Override
    public void invoke(People people, AnalysisContext analysisContext) {
        System.out.println(people.getNumber()+"||"+people.getName()+"||"+people.getSex());
    }

    /**
     * 所有数据解析完成了 调用
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据全部读取完成！");
    }
}

2.读取Excel

        String excelPath = "E://test.xlsx";
        /**
         * 1.指定路径
         * 2.创建监听类 编写处理逻辑
         * 3.自动关闭流
         */
        EasyExcel.read(excelPath, People.class, new PeopleListener()).sheet(0).doRead();

更多Api详见 https://www.yuque.com/easyexcel/doc/easyexcel

Hutool

Hutool是一个小而全的java工具类库，有很多实用的工具类封装，Excel相关操作就是其中的工具类。

maven依赖

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.0.7</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

简单示例

• 创建实体

@Data
public class People {
    private String number;
    private String name;
    private String sex;
}

• 创建Excel

        List<People> peoples = new ArrayList<>();
        for(int i=1;i<=1000;i++){
            People people = new People();
            people.setNumber(String.valueOf(i));
            people.setName("测试人员"+i);
            people.setSex(i%2==0?"男":"女");
            peoples.add(people);
        }
        //获取 Excel 写入的操作类
        ExcelWriter excelWriter = ExcelUtil.getWriter();
        //设置表头 与 实体类的 属性绑定
        excelWriter.addHeaderAlias("number","序号");
        excelWriter.addHeaderAlias("name","姓名");
        excelWriter.addHeaderAlias("sex","性别");
        //写入全部内容
        excelWriter.write(peoples,true);
        //创建文档 写入数据
        String excelPath = "E://test.xlsx";
        try {
            FileOutputStream stream = new FileOutputStream(excelPath);
            excelWriter.flush(stream,true);
            excelWriter.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

• 读取Excel

        String excelPath = "E://test.xlsx";
        ExcelReader reader = ExcelUtil.getReader(excelPath);
        //读取为Map列表，默认第一行为表头，Map中的key为表头值，value为标题对应的单元格值
        List<Map<String, Object>> peoples = reader.readAll();
        for (Map<String, Object> people:peoples){
            System.out.println(people.get("序号")+"||"+people.get("姓名")+"||"+people.get("性别"));
        }

小结

• 如果操作Excel复杂度高，建议使用POI，编程灵活。
• 如果操作Excel数据量大，对性能有一定要求的情况，建议使用EasyExcel。
• 如果操作Excel数据量小，而且追求编程效率，建议使用Hutool的ExcelUtil。