利用java操作excel

Apache POI官网

阿里巴巴的EsayExcel GitHub地址
语雀

学习地址（b站狂神视频)
码云地址

一，首先创建一个maven工程，导入相应的依赖

    <dependencies>
        <!--xls(03版本)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx(07版本)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

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

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

1.1创建一个类ExcelWriteTest

package com.hjm;

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 org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

/**
 * @Author: yd
 * @Date: 2020/4/28 14:55
 * @Version 1.0
 */
public class ExcelWriteTest {
    private final static String PATH = "G:/JAVA程序/poi_easy_excel";
    /**
     * 测试03版本的excel
     */
    @Test
    public void testWrite() throws IOException {
        //创建工作簿03 07为XSSWorkbook
        Workbook workbook = new HSSFWorkbook();
        //创建工作表(将下面sheet1 更名为 统计表)
        Sheet sheet = workbook.createSheet("统计表");
        //创建行，默认0为第一行
        Row row1 = sheet.createRow(0);

        //创建单元格,构成第一行第一列的格子，也就是第一个格子
        Cell cell_1_1 = row1.createCell(0);
        //填入值
        cell_1_1.setCellValue("1_1");

        //创建单元格,构成第一行第二列的格子，也就是第二个格子
        Cell cell_1_2 = row1.createCell(1);
        //填入值
        cell_1_2.setCellValue("1_2");

        //创建第二行，默认0为第一行
        Row row2 = sheet.createRow(1);
        Cell cell_2_1 = row2.createCell(0);
        //填入值
        cell_2_1.setCellValue("2_1");

        Cell cell_2_2 = row2.createCell(1);
        //填入值
        String toString = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell_2_2.setCellValue(toString);

        //生成一张表(IO操作流)，07结尾是xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/03版本.xls");
        workbook.write(fileOutputStream);
        //关闭流
        fileOutputStream.close();
        System.out.println("生成完毕!");
    }
}

效果如图

2，数据导入

大文件写HSSF(Excel 03版本)
优点：写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
缺点：只能处理65535行，否则会抛出异常

Exception in thread "main" java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

2.1 03版本

    @Test
    public void bigDataTest() throws IOException {
        long start = System.currentTimeMillis();

        //创建表
        Workbook workbook = new HSSFWorkbook();
        //创建工作簿
        Sheet sheet = workbook.createSheet("统计表");
        for (int row = 0; row < 65535; row++) {
            Row sheetRow = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell sheetRowCell = sheetRow.createCell(cell);
                sheetRowCell.setCellValue(cell);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/bigDataTest_03版本.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("完成");
        long end = System.currentTimeMillis();
        System.out.println((double)(end-start) / 1000);
    }

2.2 07版本

大文件写XSSF(Excel 07版本)
缺点：写数据很慢，非常耗内存，容易造成内存溢出(如100万条数据)
优点：可以写很大的数据量，如20万条

    @Test
    public void bigDataTest07() throws IOException {
        long start = System.currentTimeMillis();
        //创建表
        Workbook workbook = new XSSFWorkbook();
        //创建工作簿
        Sheet sheet = workbook.createSheet("统计表");
        //已经改为65537次循环
        for (int row = 0; row < 65537; row++) {
            Row sheetRow = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell sheetRowCell = sheetRow.createCell(cell);
                sheetRowCell.setCellValue(cell);
            }
        }
        //后缀名也发生变化 .xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/bigDataTest_07版本.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("完成");
        long end = System.currentTimeMillis();
        System.out.println((double)(end-start) / 1000);
    }

SXSSF(07版本，增强)
速度更快

2.3 增强版的07版本，更快

@Test
    public void bigDataTest07_super() throws IOException {
        long start = System.currentTimeMillis();
        /* 创建表 */
        Workbook workbook = new SXSSFWorkbook();
        //创建工作簿
        Sheet sheet = workbook.createSheet("统计表");
        for (int row = 0; row < 65537; row++) {
            Row sheetRow = sheet.createRow(row);
            for (int cell = 0; cell < 10; cell++) {
                Cell sheetRowCell = sheetRow.createCell(cell);
                sheetRowCell.setCellValue(cell);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/bigDataTest_07版本_增强版.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
         //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        System.out.println("完成");
        long end = System.currentTimeMillis();
        System.out.println((double)(end-start) / 1000);
    }

同样的数据量速度明显更快了

2,从Excel读取数据

2.1 03版本获取

public class ExcelReadTest {
    private final static String PATH = "G:/JAVA程序/poi_easy_excel";
    @Test
    public void Read_03() throws IOException {
        //获取文件
        FileInputStream fileInputStream = new FileInputStream(PATH+"/bigDataTest_03版本.xls");
        //创建表
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //获取工作簿,第1个表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row sheetRow = sheet.getRow(0);
        //获取第一列
        Cell sheetCell = sheetRow.getCell(0);
        //获取数字类型
		//System.out.println(sheetCell.getNumericCellValue());
		//获取字符串类型
        System.out.println(sheetCell.getStringCellValue());
        fileInputStream.close();
    }
}

2.2 07版本获取

    @Test
    public void Read_07() throws IOException {
        //获取文件
        FileInputStream fileInputStream = new FileInputStream(PATH+"/bigDataTest_07版本.xlsx");
        //创建表
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //获取工作簿,第1个表
        Sheet sheet = workbook.getSheetAt(0);
        //获取第一行
        Row sheetRow = sheet.getRow(0);
        //获取第一列
        Cell sheetCell = sheetRow.getCell(1);
        System.out.println(sheetCell.getNumericCellValue());
        //System.out.println(sheetCell.getStringCellValue());
        fileInputStream.close();
    }

3，同一张表读取不同的数据类型

    @Test
    public void Read_differentType() throws IOException {
        //获取文件
        FileInputStream fileInputStream = new FileInputStream(PATH+"/商品明细.xls");
        //创建表
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheetAt = workbook.getSheetAt(0);
        //获得第一行
        Row titleRow = sheetAt.getRow(0);
        //获取标题
        if(titleRow != null){
            //获得第一行总列数
            int cells = titleRow.getPhysicalNumberOfCells();
            for (int cellNumber = 0; cellNumber < cells; cellNumber++) {
                Cell cell = titleRow.getCell(cellNumber);
                if(cell != null){
                    int cellType = cell.getCellType();
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+" ");
                }
            }
            System.out.println();
        }
        //获取表中行数
        int rows = sheetAt.getPhysicalNumberOfRows();
        for (int rowCount = 1; rowCount < rows; rowCount++) {
            //获取行
            Row row = sheetAt.getRow(rowCount);
            if(row != null){
                //获取列
                int cells = row.getPhysicalNumberOfCells();
                String cellValue = "";
                for (int cellNum = 0; cellNum < cells; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    if(cell != null){
                        int cellType = cell.getCellType();
                        System.out.print("[" + (rowCount+1) + "-" + (cellNum+1) +"]");
                        //这种方法也能打印，暂时没有遇到问题
                        //System.out.print(cell.toString()+" ");
                        
                        switch (cellType){
                            //字符串
                            case HSSFCell.CELL_TYPE_STRING:
                                System.out.print("[String]");
                                cellValue = cell.getStringCellValue();
                                break;
                            //布尔类型
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                System.out.print("[boolean]");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            //空
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.print("[布尔类型]");
                                break;
                            //数字或者日期
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("[numeric]");
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.print("[日期]");
                                    Date date = cell.getDateCellValue();
                                    String toString = new DateTime(date).toString("yyyy-MM-dd");
                                }else{
                                    System.out.print("[转换为字符串形式输出]");
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                System.out.print("[数据类型错误]");
                                break;
                            default:break;
                        }
                        System.out.println(cellValue);
                    }
                }
                System.out.println();
            }
        }
        fileInputStream.close();
    }

二，EasyExcel

导入依赖，因为easyexcel里面自带POI所以把之前的导入的依赖注释掉

完整pom.xml

<dependencies>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.68</version>
        </dependency>
        <!--&lt;!&ndash;xls(03版本)&ndash;&gt;-->
        <!--<dependency>-->
            <!--<groupId>org.apache.poi</groupId>-->
            <!--<artifactId>poi</artifactId>-->
            <!--<version>3.9</version>-->
        <!--</dependency>-->

        <!--&lt;!&ndash;xlsx(07版本)&ndash;&gt;-->
        <!--<dependency>-->
            <!--<groupId>org.apache.poi</groupId>-->
            <!--<artifactId>poi-ooxml</artifactId>-->
            <!--<version>3.9</version>-->
        <!--</dependency>-->

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

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

2.1写操作

package com.hjm.easy;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.junit.Test;

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

/**
 * @Author: yd
 * @Date: 2020/4/28 19:43
 * @Version 1.0
 */
public class test {
    private final static String PATH = "G:/JAVA程序/poi_easy_excel";
    private List<DemoData> data() {
        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;
    }
    //根据list写入Excel
    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     */
    @Test
    public void simpleWrite() {
        // 写法1
        String fileName = PATH+"/EasyExcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
}

2.2读操作

在官网导入三个类<=====这是个链接

测试代码

    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */
    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = PATH+"/EasyExcel.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }
}

结果

最后：一定要读文档！！！

语雀 阿里巴巴EasyExcel文档