POI、EasyExcel操作Excel

作者: WebGiser | 来源:发表于2022-01-10 21:02 被阅读0次

项目结构

image.png

pom.xml

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.geovis</groupId>
  <artifactId>excel-util</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>excel-util</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>

    <!-- easyexcel内部已经包含了poi依赖，不能同时引入 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.0.5</version>
    </dependency>
<!--

    &lt;!&ndash; poi-xsl 03版 &ndash;&gt;
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>5.1.0</version>
    </dependency>

    &lt;!&ndash; poi-xsl 07版 &ndash;&gt;
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>5.1.0</version>
    </dependency>
-->

    <!-- joda-time -->
    <dependency>
      <groupId>joda-time</groupId>
      <artifactId>joda-time</artifactId>
      <version>2.10.13</version>
    </dependency>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version>
      <scope>provided</scope>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.78</version>
    </dependency>
  </dependencies>
</project>

EasyExcel

EasyExcel 读取 Excel

DemoData2.java

package com.geovis.easyExcel;

import lombok.Data;

import java.util.Date;

@Data
public class DemoData2 {
    private String string;

    private Date date;

    private Double doubleData;

}

DemoDAO.java

package com.geovis.easyExcel;

import java.util.List;

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

DemoData2Listener.java

package com.geovis.easyExcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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


// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoData2Listener extends AnalysisEventListener<DemoData2> {

    private static final Logger LOGGER = LoggerFactory.getLogger(DemoData2Listener.class);
    // 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
    private static final int BATCH_COUNT = 5;
    List<DemoData2> list = new ArrayList<DemoData2>();
    // 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
    private DemoDAO demoDAO;

    public DemoData2Listener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    public DemoData2Listener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData2 data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        LOGGER.info("解析到一条数据:{}", 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("存储数据库成功！");
    }
}

EasyExcelRead.java

package com.geovis.easyExcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

public class EasyExcelRead {
    public static void main(String[] args) {

        String fileName = "D:/temp/easy.xlsx";
        EasyExcel.read(fileName, DemoData2.class, new DemoData2Listener()).sheet().doRead();

//        ExcelReader excelReader = EasyExcel.read(fileName, DemoData2.class, new DemoData2Listener()).build();
//        ReadSheet readSheet = EasyExcel.readSheet(0).build();
//        excelReader.read(readSheet);
//        excelReader.finish();
    }
}

EasyExcel 写入 Excel

DemoData.java

package com.geovis.easyExcel;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class DemoData {

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

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

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

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

EasyExcelWrite.java

package com.geovis.easyExcel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;

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

public class EasyExcelWrite {

    public static void main(String[] args) {

        // 创建示例数据
        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);
        }

        String fileName = "D:/temp/easy.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(list);

//        ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
//        excelWriter.write(list, writeSheet);
//        excelWriter.finish();
    }
}

POI

POI 03版读取 Excel

PoiExcelRead03.java

package com.geovis.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;

// poi读取03版excel
public class PoiExcelRead03 {

    static String path = "D:/temp/poi-03.xls";

    public static void main(String[] args) throws Exception {
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        fis.close();
    }
}

POI 03版读取大数据量的 Excel

PoiExcelWriteBigData03.java

package com.geovis.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;

// poi写入03版excel，最多只能处理65536行，否则会抛出异常
public class PoiExcelWriteBigData03 {

    static String path = "D:/temp";

    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("员工统计表");
        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 fos = new FileOutputStream(path + "/poi-bigdata-03.xls");
        workbook.write(fos);
        fos.close();
        System.out.println("文件生成完毕！");

        long end = System.currentTimeMillis();
        System.out.println((double)(end-start)/1000);
    }
}

POI 03版写入 Excel

PoiExcelWrite03.java

package com.geovis.poi;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;

// poi写入03版excel，最多只能处理65536行，否则会抛出异常
public class PoiExcelWrite03 {

    static String path = "D:/temp";

    public static void main(String[] args) throws Exception {
        // 1、创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2、创建一个工作表
        Sheet sheet = workbook.createSheet("员工统计表");
        // 3、创建一个行
        Row row1 = sheet.createRow(0);
        // 4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("名字");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("年龄");
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("生日");

        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("张三");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(20);
        Cell cell23 = row2.createCell(2);
        cell23.setCellValue(DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));

        // 第二行
        Row row3 = sheet.createRow(2);
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("李四");
        Cell cell32 = row3.createCell(1);
        cell32.setCellValue(30);
        Cell cell33 = row3.createCell(2);
        cell33.setCellValue(DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));

        // 生成文件
        FileOutputStream fos = new FileOutputStream(path + "/poi-03.xls");
        workbook.write(fos);
        fos.close();

        System.out.println("文件生成完毕！");
    }
}

POI 07版读取 Excel

PoiExcelRead07.java

package com.geovis.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;


// poi读取07版excel
public class PoiExcelRead07 {

    static String path = "D:/temp/poi-07.xlsx";

    public static void main(String[] args) throws Exception {
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        fis.close();
    }
}

POI 07版读取完整的 Excel

ReadExcel07.java

package com.geovis.poi;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileInputStream;
import java.util.Date;


// poi完整读取07版excel
public class ReadExcel07 {

    static String path = "D:/temp/test.xlsx";

    public static void main(String[] args) throws Exception {
        FileInputStream fis = new FileInputStream(path);
        Workbook workbook = new XSSFWorkbook(fis);

        // 读取title表头
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        int cellNum = rowTitle.getPhysicalNumberOfCells();
        for (int i = 0; i < cellNum; i++) {
            Cell cell = rowTitle.getCell(i);
            if(cell != null){
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue + " | ");
            }
        }
        System.out.println();

        // 读取数据内容
        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rowNum; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    if(cell != null){
                        CellType cellType = cell.getCellType();
                        String cellVal = "";
                        switch (cellType){
                            // 字符串
                            case STRING:
                                System.out.print("[字符串]");
                                cellVal = cell.getStringCellValue();
                                break;
                            // 布尔
                            case BOOLEAN:
                                System.out.print("[布尔]");
                                cellVal = String.valueOf(cell.getBooleanCellValue());
                                break;
                            // 数值
                            case NUMERIC:
                                System.out.print("[数值]");
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    // 日期
                                    System.out.print("[日期]");
                                    Date date = cell.getDateCellValue();
                                    cellVal = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                                }else{
                                    // 数字
                                    System.out.print("[数字]");
                                    cellVal = String.valueOf(cell.getNumericCellValue());
                                }
                                break;
                            // 空
                            case BLANK:
                                System.out.print("[空]");
                                break;
                            // 公式
                            case FORMULA:
                                System.out.print("[公式]");
                                XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                String formula = cell.getCellFormula();
                                System.out.println("公式为："+formula);
                                CellValue value = formulaEvaluator.evaluate(cell);
                                cellVal = value.formatAsString();
                                break;
                            // 错误
                            case ERROR:
                                System.out.print("[错误]");
                                break;
                        }
                        System.out.println(cellVal);
                    }
                }
            }
            System.out.println("********************************");
        }
        fis.close();
    }
}

POI 07版写入 Excel

PoiExcelWrite07.java

package com.geovis.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;


// poi写入07版excel，数据量大时，可能内存溢出
public class PoiExcelWrite07 {

    static String path = "D:/temp";

    public static void main(String[] args) throws Exception {
        // 1、创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2、创建一个工作表
        Sheet sheet = workbook.createSheet("员工统计表");
        // 3、创建一个行
        Row row1 = sheet.createRow(0);
        // 4、创建一个单元格
        Cell cell11 = row1.createCell(0);
        cell11.setCellValue("名字");
        Cell cell12 = row1.createCell(1);
        cell12.setCellValue("年龄");
        Cell cell13 = row1.createCell(2);
        cell13.setCellValue("生日");

        // 第二行
        Row row2 = sheet.createRow(1);
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue("张三");
        Cell cell22 = row2.createCell(1);
        cell22.setCellValue(20);
        Cell cell23 = row2.createCell(2);
        cell23.setCellValue(DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));

        // 第二行
        Row row3 = sheet.createRow(2);
        Cell cell31 = row3.createCell(0);
        cell31.setCellValue("李四");
        Cell cell32 = row3.createCell(1);
        cell32.setCellValue(30);
        Cell cell33 = row3.createCell(2);
        cell33.setCellValue(DateTime.now().toString("yyyy-MM-dd HH:mm:ss"));

        // 生成文件
        FileOutputStream fos = new FileOutputStream(path + "/poi-07.xlsx");
        workbook.write(fos);
        fos.close();

        System.out.println("文件生成完毕！");
    }
}

POI 07版写入大数据量的 Excel

PoiExcelWriteBigData07.java

package com.geovis.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;

// poi写入07版excel，耗时较长。数据量大时，可能内存溢出
public class PoiExcelWriteBigData07 {

    static String path = "D:/temp";

    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("员工统计表");
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i+","+j);
            }
        }
        // 生成文件
        FileOutputStream fos = new FileOutputStream(path + "/poi-bigdata-07.xlsx");
        workbook.write(fos);
        fos.close();
        System.out.println("文件生成完毕！");

        long end = System.currentTimeMillis();
        System.out.println((double)(end-start)/1000);
    }
}

POI 07版写入大数据量的 Excel（优化）

PoiExcelWriteBigDataS07.java

package com.geovis.poi;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;

// 使用 SXSSFWorkbook 代替 XSSFWorkbook，会加快速度
// SXSSFWorkbook 默认每次读取100万行数据到内存中
public class PoiExcelWriteBigDataS07 {

    static String path = "D:/temp";

    public static void main(String[] args) throws Exception {
        long start = System.currentTimeMillis();

        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet("员工统计表");
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(i+","+j);
            }
        }
        // 生成文件
        FileOutputStream fos = new FileOutputStream(path + "/poi-bigdatas-07.xlsx");
        workbook.write(fos);
        fos.close();
        // 清除临时文件
        ((SXSSFWorkbook)workbook).dispose();
        System.out.println("文件生成完毕！");

        long end = System.currentTimeMillis();
        System.out.println((double)(end-start)/1000);
    }
}

POI、EasyExcel操作Excel

项目结构

pom.xml

EasyExcel

EasyExcel 读取 Excel

EasyExcel 写入 Excel

POI

POI 03版读取 Excel

POI 03版读取大数据量的 Excel

POI 03版写入 Excel

POI 07版读取 Excel

POI 07版读取完整的 Excel

POI 07版写入 Excel

POI 07版写入大数据量的 Excel

POI 07版写入大数据量的 Excel（优化）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SpringBoot

POI、EasyExcel操作Excel

项目结构

pom.xml

EasyExcel

EasyExcel 读取 Excel

EasyExcel 写入 Excel

POI

POI 03版 读取 Excel

POI 03版 读取大数据量的 Excel

POI 03版 写入 Excel

POI 07版 读取 Excel

POI 07版读取完整的 Excel

POI 07版写入 Excel

POI 07版写入大数据量的 Excel

POI 07版写入大数据量的 Excel（优化）

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

SpringBoot

POI 03版读取 Excel

POI 03版读取大数据量的 Excel

POI 03版写入 Excel

POI 07版读取 Excel