Java开发中的POI与easyExcel对Excel文件的读与写

盛者无名

已于 2022-04-22 16:55:17 修改

阅读量645

点赞数

分类专栏： Java 编程文章标签： java

于 2022-04-22 16:54:04 首次发布

本文链接：https://blog.csdn.net/weixin_41489136/article/details/124349965

版权

编程同时被 2 个专栏收录

25 篇文章 0 订阅

订阅专栏

Java

18 篇文章 1 订阅

订阅专栏

本文详细介绍了Apache POI和阿里巴巴的EasyExcel两个Java库在处理Excel文件时的方法，包括写入和读取操作。通过示例代码展示了03版和07版Excel的创建、大数据量写入以及读取，同时对比了两者在处理大数据量时的性能差异。EasyExcel提供了更简单的API和内存优化，适合处理大量数据。

摘要由CSDN通过智能技术生成

Java POI与easyExcel

POI

Apache POI是用Java编写的免费开源跨平台的Java API,Apache POI提供API给Java对Microsoft Office格式档案都和写的功能.POI为Poor Obfuscation Implementation的首字母缩写,意为简洁版的模糊实现

easyExcel

easyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称.

excel中的对象:

工作簿
工作表
行
列

导入Maven poi依赖:

 <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>

导入Maven 时间插件依赖:

    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.1</version>
    </dependency>

excel分为03和07两种版本:

基本写入

03版本写入代码:

    public void testWrite03() throws Exception
    {
         String PATH="路径";
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet=workbook.createSheet("sheet名");
        //创建一行
        Row row1=sheet.createRow(0);
        //创建单元格
        Cell cell1= row1.createCell(0);
        cell1.setCellValue("值");
        Cell cell2=row1.createCell(1);
        cell2.setCellValue(666);

        //第二行
        Row row2=sheet.createRow(1);
        Cell cell21=row2.createCell(0);
        cell21.setCellValue("统计时间");
        Cell cell22=row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        //生成一张表
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("finish");
    }

07版本写入代码:

        public void testWrite07() throws Exception
        {
             String PATH="路径";
            //创建一个工作簿
            Workbook workbook = new XSSFWorkbook();
            //创建一个工作表
            Sheet sheet=workbook.createSheet("统计表");
            //创建一行
            Row row1=sheet.createRow(0);
            //创建单元格
            Cell cell1= row1.createCell(0);
            cell1.setCellValue("今日新增");
            Cell cell2=row1.createCell(1);
            cell2.setCellValue(666);

            //第二行
            Row row2=sheet.createRow(1);
            Cell cell21=row2.createCell(0);
            cell21.setCellValue("统计时间");
            Cell cell22=row2.createCell(1);
            cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

            //生成一张表
            FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("finish");
    }

二者的区别只有调用对象和生成文件拓展名有些区别,03对象为HSSFWorkbook(),拓展名为.xls,07对象为XSSFWorkbook,拓展名为.xlsx
03最大只能到65536行

数据量大写入

03大数据量写入:

    public void testWrite03BigData() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new HSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

07大数据量写入:

    public void testWrite07BigData() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

07版写入速度较慢,所以引入S方法(使用缓存)(SXSSF方法):

    public void testWrite07BigDataS() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new SXSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

读取

03:

public class ExcelReadTest {
    String PATH="路径";
    public void testRead03() throws Exception
    {
        FileInputStream inputStream=new FileInputStream(PATH+"testWrite03BigData.xls");
        //创建一个工作簿
        Workbook workbook=new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheet=workbook.getSheetAt(0);
        Row row=sheet.getRow(0);
        Cell cell=row.getCell(0);
        //读取值需要类型判断
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

07:

    public  void testRead07() throws Exception
    {
         String PATH="路径";
        FileInputStream inputStream=new FileInputStream(PATH+"文件名.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.getNumericCellValue());
        inputStream.close();
    }

判断表格内容类型:

03:

public void testCellType() throws Exception
    {
        String PATH="路径";
        FileInputStream fileInputStream=new FileInputStream(PATH+"文件名.xls");
        Workbook workbook=new HSSFWorkbook(fileInputStream);
        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)
                {
                    int cellType=cell.getCellType();
                    String cellValue=cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
        }
        int rowCount=sheet.getPhysicalNumberOfRows();
        for(int rowNum=1;rowNum<rowCount;rowNum++)
        {
            Row rowData=sheet.getRow(rowNum);
            if(rowData!=null){
                int cellCount=rowTitle.getPhysicalNumberOfCells();
                for(int cellNum=0;cellNum<cellCount;cellNum++)
                {
                    Cell cell=rowData.getCell(cellNum);
                    if(cell!=null)
                    {
                        int cellType=cell.getCellType();
                        String cellValue="";
                        switch (cellType)
                        {
                            case HSSFCell.CELL_TYPE_STRING://字符串
                                System.out.print("[String]");
                                cellValue=cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                System.out.print("[Blank]");
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN://布尔
                                System.out.print("[boolean]");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC://数字(日期、普通数字)
                                if(HSSFDateUtil.isCellDateFormatted(cell))
                                {
                                    System.out.print("[日期]");
                                    Date date=cell.getDateCellValue();
                                    cellValue=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;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        fileInputStream.close();
    }

easyExcel

导入依赖:

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.0.5</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.16</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.25</version>
    </dependency>

pojo:

@Data
public class Dd {
    @ExcelProperty("发货日期")
    private String fhrq;
    @ExcelProperty("合同号")
    private String hth;

}

写入类:

public class Easy {
    private List<Dd> data()
    {
        List<Dd> list=new ArrayList<Dd>();
        Dd data=new Dd();
        data.setFhrq("2022/3/1");
        data.setHth("CYZL-KJ-A-2021013-D003");
        list.add(data);
        return list;
    }
    public void simpleWrite()
    {
        String PATH="D:\\SourceCodes\\Temp\\poitest\\result\\";
        String fileName=PATH+"EasyTest.xlsx";
        EasyExcel.write(fileName,Dd.class).sheet("模板").doWrite(data());
    }
}

EasyExcel利用实体类直接写入.