EasyExcel与POI_easyexcel对应poi版本

EasyExcel与POI

执行流程与区别

tip:07版本的Excel最多有104万

先引入pom

 <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>

        </dependency>
        <!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.11.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

POI的写入

public void testWriter03() throws Exception {
        //创建接收路径
        String path="D:\\EasyExcel\\";
        //创建工作簿
        Workbook workbook=new XSSFWorkbook();
        //创建工作表
        Sheet sheet=workbook.createSheet("核算统计表");
//        创建第一行
        Row row=sheet.createRow(0);
        Cell cell=row.createCell(0);
        cell.setCellValue("今日新增");
        Cell cell1=row.createCell(1);
        cell1.setCellValue(666);
//        创建第二行
        Row row1=sheet.createRow(1);
        Cell cell2=row1.createCell(0);
        cell2.setCellValue("人数");
        Cell cell3=row1.createCell(1);
        cell3.setCellValue("sdw");
//        生成一张表
        FileOutputStream fileOutputStream=new FileOutputStream(path+"hello.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

测试生成百万数据的时间

  public void Test() throws Exception {
        //创建一个存放地址
        Date date=new Date();
        String  path="D:\\EasyExcel\\";
        //创建一个工作簿
        Workbook workbook=new SXSSFWorkbook();
        //创建工作表
        Sheet sheet=workbook.createSheet("填满表");
        Row row ;
        Cell cell = null;
        for(int i=0;i<1000000 ;i++){
            row= sheet.createRow(i);
            for (int j=0;j<10;j++){
                cell=row.createCell(j);

                cell.setCellValue(j);
            }
        }

        FileOutputStream fileOutputStream=new FileOutputStream(path+"Test.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
       Date date1=new Date();
       long from=date.getTime();
       long end=date1.getTime();
        System.out.println(end-from);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

电脑跑完11秒多

POI的读取

 public void Test() throws Exception {
        //路径地址
        String  path="D:\\EasyExcel\\";
        FileInputStream inputStream=new FileInputStream(path+"hello.xlsx");
        //创建工作簿
        Workbook workbook=new XSSFWorkbook(inputStream);
       Sheet sheet= workbook.getSheetAt(0);
       Row row=sheet.getRow(0);
        Cell cell=row.getCell(0);
        System.out.println(cell.getStringCellValue());
        inputStream.close();
    }
1
2
3
4
5
6
7
8
9
10
11
12

读取多个，需要对每行数据的类型进行识别

  public void Test() throws Exception {
        String path = "D:\\EasyExcel\\";
        FileInputStream inputStream = new FileInputStream(path + "hello.xlsx");
        //创建一个工作簿
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        //获取标题的内容
        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();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + " | ");
                }
            }
            System.out.println();
        }
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNumber = 1; rowNumber < rowCount; rowNumber++) {
            Row rowData = sheet.getRow(rowNumber);
            if (rowData!=null){
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNumber=0;cellNumber<cellCount;cellNumber++){
                    System.out.print("["+(rowNumber+1)+"-"+(cellNumber+1)+"]");
                    Cell cell=rowData.getCell(cellNumber);
                    if (cell!=null){
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case STRING:
                                System.out.print("字符串类型");
                                cellValue=cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.print("布尔类型");
                                cellValue=String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK:
                                System.out.print("空类型");
                                break;
                            case NUMERIC:
                                System.out.print("数字类型");
                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.print("日期");
                                 Date date= cell.getDateCellValue();
                                     cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else {
                                    System.out.print("普通数字");
                                   cellValue= cell.toString();
                                }
                                break;
                            case ERROR:
                                System.out.print("类型错误");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }

        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

计算公式的使用实例

public void Test2() throws Exception {
        String path = "D:\\EasyExcel\\";
        FileInputStream inputStream=new FileInputStream(path+"hello.xlsx");
        Workbook workbook=new XSSFWorkbook(inputStream);
        Sheet sheet=workbook.getSheetAt(0);
        Row row=sheet.getRow(4);
        Cell cell=row.getCell(0);
        //拿到计算公式
        FormulaEvaluator formulaEvaluator =new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
        //输出单元格的内容
        CellType cellType= cell.getCellType();
        switch (cellType){
            case FORMULA:
                String formula =cell.getCellFormula();
                System.out.println(formula);
                //计算
                CellValue cellValue=formulaEvaluator.evaluate(cell);
                String cel=cellValue.formatAsString();
                System.out.println(cel);
                break;
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

EasyExcel是一个基于Java简单的，省内存的读写Excel的开源项目，具体的可以官网上查看，找不到源码的话可以私信我

先引入pom依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>

1
2
3
4
5
6
7

写入

@Slf4j
public class Data {
    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        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;
    }
    /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName = "D:\\EasyExcel\\EasyTest.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class)
                .sheet("模板")
                .doWrite(() -> {
                    // 分页查询数据
                    return data();
                });

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

模板

public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}
1
2
3
4
5
6
7
8
9
10
11
12
13

读取

   @Test
    public void simpleRead() {
        // 写法1：JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String fileName = "D:\\EasyExcel\\EasyTest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取100条数据 然后返回过来 直接调用使用数据就行
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                log.info("读取到一条数据{}", JSON.toJSONString(demoData));
            }
        })).sheet().doRead();

1
2
3
4
5
6
7
8
9
10
11
12
13

固定套路

1.写入，固定类格式进行写入

2.读取，根据监听器 设置的规则进行读取