POI及EasyExcel

用于批量数据的导入和导出。POI和EasyExcel都是可以操作excel的第三方组件。

POI

依赖

 <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.0</version>
    </dependency>
    <!--07 xlsx-->
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/joda-time/joda-time -->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.13</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/junit/junit -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>

数据单行写入

package cn.zjq;

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;
import java.io.IOException;

public class ExcelWriteTest {
   private static String Path = "D:/CODE/poi-easyEXCEL/";
    public static void main(String[] args) throws IOException {
//        创建一个工作簿 03版本
        Workbook workbook = new HSSFWorkbook();//07 XSSWorkbook
//        创建一个工资表
        Sheet sheet = workbook.createSheet("biao1");
//        创建第一行
        Row row1 = sheet.createRow(0);
//        创建单元格
        Cell cell = row1.createCell(0);//(1,1)
        Cell cell1 = row1.createCell(1);//(1,2)
//        设置值
        cell.setCellValue("今日新增");
        cell1.setCellValue(666);
//        创建第二行
        Row row2 = sheet.createRow(1);
//        创建单元格
        Cell cell2 = row2.createCell(1);

        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell2.setCellValue(time);
//        生成表(IO)
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "表1.xls");//07 xlsx
       workbook.write(fileOutputStream);
       fileOutputStream.close();
        System.out.println("生成完毕");

    }
}

数据批量导入

HSSF最多只能处理65536行，多了会抛出异常。但是因为写入内存再读入磁盘因此速度较快。
XSSF速度较慢，消耗内存，可写入较大的文件。
03版

    @Test
    void testBigData() throws IOException {
        long begin = System.currentTimeMillis();
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        for (int rowNumber = 0; rowNumber <65536 ; rowNumber++) {
            HSSFRow row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                HSSFCell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "大数据03.xls");//07 xlsx
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("生成完毕");
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);


    }

行数超过65536就会报异常,但是速度较快。

@Test
    void testBigData07() throws IOException {
        long begin = System.currentTimeMillis();
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        for (int rowNumber = 0; rowNumber <655370 ; rowNumber++) {
            XSSFRow row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                XSSFCell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "大数据07.xls");//07 xlsx
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("生成完毕");
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);


    }

可以写超过65536但是速度很慢。
优化：SXSSF
过程中会产生临时文件，默认100条记录被保存再内存中，若超过100条则最前面的数据被写入临时文件。通过new SXSSFWorkbook(数量)来修改默认保存记录数。

@Test
    void testBigData07s() throws IOException {
        long begin = System.currentTimeMillis();
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet();
        for (int rowNumber = 0; rowNumber <65537 ; rowNumber++) {
            SXSSFRow row = sheet.createRow(rowNumber);
            for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
                SXSSFCell cell = row.createCell(cellNumber);
                cell.setCellValue(cellNumber);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(Path + "大数据07.xls");//07 xlsx
        workbook.write(fileOutputStream);
        fileOutputStream.close();
//        清除临时文件
        workbook.dispose();
        System.out.println("生成完毕");
        long end = System.currentTimeMillis();
        System.out.println((double)(end - begin)/1000);


    }

数据读取

读取时要特别注意单元格的数据类型，如果不一致会报错，可以先用typeof进行判断。

    @Test
    public void testRead03() throws IOException {
        FileInputStream inputStream = new FileInputStream(Path + "大数据03.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        HSSFRow row = sheet.getRow(9);
        HSSFCell cell = row.getCell(9);
        System.out.println(cell.getNumericCellValue());
        //读取数字类型
//System.out.println(cell.getStringCellValue()); 读取字符串类型

    }

读取不同类型

 @Test
    public void testCellType() throws IOException {
        FileInputStream inputStream = new FileInputStream(Path + "2020.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(1);
        //读取标题
        if(rowTitle!=null){
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i <cellCount ; i++) {
                Cell cell = rowTitle.getCell(i);
                System.out.print(cell.getStringCellValue()+"|");
            }
        }
//        读取表中的内容
        int rowCount = sheet.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum <rowCount ; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if(row!=null){
                int cellCount = row.getPhysicalNumberOfCells();
                for (int  cellNum= 0; cellNum <cellCount ; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if(cell!=null){
                        CellType cellType = cell.getCellType();
                        String cellValue = "";
                        switch (cellType){
                            case STRING:
                                cellValue+=cell.getStringCellValue();
                                break;
                            case NUMERIC://数字分为日期和普通数字
                                if(HSSFDateUtil.isCellDateFormatted(cell)) {
//                                判断是不是日期
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else {
//                                防止数字过长
                                    cellValue +=cell.getNumericCellValue();
                                }
                                break;
                            case BLANK:
                                break;
                            case ERROR:
                                System.out.println("数据类型错误");
                                break;
                        }
                        System.out.print(cellValue+"|");
                    }

                }

            }System.out.println();

        }
        inputStream.close();
    }

计算公式：

 @Test
    public void testFormula() throws IOException {
        FileInputStream inputStream = new FileInputStream(Path + "计算公式.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(3);
        Cell cell = row.getCell(0);

//        用于计算公式
        FormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        CellType type = cell.getCellType();
        switch (type){
            case FORMULA:
                String formula = cell.getCellFormula();
                System.out.println(formula);

                CellValue evaluate = evaluator.evaluate(cell);
                System.out.println(evaluate.formatAsString());


        }
    }

EasyExcel

根据官方文档操作https://www.yuque.com/easyexcel/doc/write

实体类

package cn.zjq;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

写入

package cn.zjq;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.junit.jupiter.api.Test;

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

public class EasyExcelTests {
    private static String Path = "D:/CODE/poi-easyEXCEL/";

    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;
    }

    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

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

读取

虚拟的DAO层

import java.util.List;

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

监听器

package cn.zjq.eexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

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

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;

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

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

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}

测试

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

    }