冰凌汇编

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 34|回复: 0
收起左侧

[Java] Excel文件解析

[复制链接]
JackStar 发表于 2022-8-13 23:19:43
一、前言
java程序开发过程中经常需要使用Excel文件来进行数据的导入与导出,本次文章向大家介绍Excel文件解析或生成以及相关方法

二、Excel文件解析的主流技术:
Apache POI、JXL、Alibaba EasyExcel等。

三、技术特点:
Apache POI:基于DOM方式行进解析,将文件直接加载内存,速度快;

适合场景:较小数据量的Excel文件;

JXL:只支持Excel2003以下的版本(并不常用);

Alibaba EasyExcel:采用逐行读取的解析模式,将每一行的解析结果以观察者的模式通知处理

适合场景:较大数据量的Excel文件;

四、主流技术讲解
Apache POI:

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程式对Microsoft Office格式档案读和写的功能。对不同的格式文件提供不同解析:

HSSF:提供读写Microsoft Excel格式档案的功能(用于解析旧版本(*.xls)Excel文件)

注:旧版本Excel文件只能存在65535行数据,所以HSSF目前不常用

XSSF:提供读写Microsoft Excel OOXML格式档案的功能(用于解析新版本(*.xlsx)Excel文件)

HWLF:提供读写Microsoft Work格式档案的功能

HSLF:提供读写Microsoft PowerPoint格式档案的功能

HDGF:提供读写Microsoft Visio格式档案的功能

XSSF解析Excel文件:
使用XSSF解析Excel文件时必须依赖第三方jar包:
Excel文件解析 - JackStar_冰凌汇编

使用XSSF解析Excel文件时最重要的四个接口:Workbook(Excel文件)、Sheet(工作簿)、Row(数据行)、Cell(单元格);,并且提供了一系列静态方法

Workbook(Excel文件):
        Workbook接口代表一个Excel文件,用于创建或加载(解析)一个Excel文件,常见实现类有:XSSFWorkbook
创建Excel文件:
[Java] 纯文本查看 复制代码
        try (Workbook workbook = new XSSFWorkbook();
                FileOutputStream out = new FileOutputStream("D:\\texs\\"+System.currentTimeMillis()+".xlsx")) {
        //将Workbook对象中包含的数据,通过输出流,写入至Excel文件
        workbook.write(out);
    }catch (IOException e) {        
                e.printStackTrace();
        }

注:仅依靠Workbook类虽然可以创建一个Excel文件,但无法打开;

加载(解析)Excel文件:
[Java] 纯文本查看 复制代码
    try (FileInputStream in = new FileInputStream("D:\\琐碎\\1627356552686.xlsx");
                        Workbook workbook = new XSSFWorkbook(in)) {
        } catch (IOException e) {
                        e.printStackTrace();
        }

Sheet(工作簿):
通过Workbook来进行工作簿Sheet对象的创建与获取

获取工作簿:
[Java] 纯文本查看 复制代码
Sheet sheet=workbook.getSheet("sheet0");
//按照名称获取工作簿
 
Sheet sheet=workbook.getSheetAt(0);
//按照下标获取工作簿

创建工作簿:
[Java] 纯文本查看 复制代码
Sheet sheet=workbook.createSheet();
//创建默认名称工作簿
 
Sheet sheet=workbook.createSheet("数据表");
//创建名为“数据表”的工作簿

获取工作簿的数量:
[Java] 纯文本查看 复制代码
int num=workbook.getNumberOfSheets();

Row(数据行):
通过Sheet来进行数据行Row对象的获取与创建

创建数据行:
[Java] 纯文本查看 复制代码
Row row=sheet.createRow(0);
//按照下标创建行

获取首尾行下标:
[Java] 纯文本查看 复制代码
        int firstRowIndex=sheet.getFirstRowNum();
    //获取首行下标
 
        int lastRowIndex=sheet.getLastRowNum();
    //获取尾行下标

根据下标获取指定行:
[Java] 纯文本查看 复制代码
Row row=sheet.getRow(0);
//获取下标为零的数据行

遍历数据行:
[Java] 纯文本查看 复制代码
    for(Row row:sheet) {
                System.out.println(row);
        }

遍历指定区域:
[Java] 纯文本查看 复制代码
    for(int i=1;i<sheet.getLastRowNum();i++) {
        Row row=sheet.getRow(i);
            System.out.println(row);
    }

Cell(单元格):
通过Row来进行单元格Cell对象的获取或创建
创建单元格:
[Java] 纯文本查看 复制代码
Cell cell=row.createCell(0);       //按下标索引来创建单元格

添加单元格值:
[Java] 纯文本查看 复制代码
       Cell cell0=row.createCell(0);                                    
       cell0.setCellValue(UUID.randomUUID().toString());
        //在当前行下标为0的单元格添加String类型的UUID值   
            Cell cell1=row.createCell(1);
                 cell1.setCellValue(Math.PI);
        //在当前行下标为1的单元格添加圆周率
                Cell cell2=row.createCell(2);
                cell2.setCellValue(LocalDateTime.now());
        //在当前行下标为2的单元格添加计算机当前时间

根据下标获取单元格:
[Java] 纯文本查看 复制代码
      Cell cell1=row.getCell(0);
      //获取当前行下标值为0的单元格
          Cell cell2=row.getCell(1);
      //获取当前行下标值为1的单元格

遍历所以单元格:
[Java] 纯文本查看 复制代码
   for(Cell cell : Row){  
        system.out.println(cell);
   }

获取单元格类型:
[Java] 纯文本查看 复制代码
CellType cellType=cell.getCellType();

设置单元格样式:
[Java] 纯文本查看 复制代码
   //获取格式编码值
        DataFormat dataFormat=workbook.createDataFormat();
    //日期格式编码值
        Short dateDataFormat=dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss SSS");
    //货币格式编码值
        Short moneyDataFormat=dataFormat.getFormat("¥#,###");
                        
        //创建日期格式对象
        CellStyle dateStyle=workbook.createCellStyle();
        dateStyle.setDataFormat(dateDataFormat);//设置格式编码值
        dateStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        dateStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);//垂直居低
 
    //创建货币格式对象
        CellStyle moneyStyle=workbook.createCellStyle();
        moneyStyle.setDataFormat(moneyDataFormat);//设置格式编码值
        moneyStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居低
    ....
 
    Cell cell2=row.createCell(2);//日期
        cell2.setCellStyle(dateStyle);//日期格式对象
        cell2.setCellValue(new Date());
                                
        Cell cell3=row.createCell(3);//红包金额
        cell3.setCellStyle(moneyStyle);//货币格式对象
        cell3.setCellValue((int)(Math.random()*100000));

超大Excel文件读写:
POI
:​​​​​SXSSFWorkbook类是专门处理大数据的类​,SXSSF是在XSSF的基础上提供了一种支持低内存占用的操作方式;对于大型Excel的创建不会产生内存溢出;

当我们在进行Excel文件操作时,在数据量过大的情况下就可以采用POI提供的SXSSFWorkbook类来避免内存溢出,在使用SXSSFWorkbook进行写入时,首先要设置SXSSFWorkbook的构造参数,这样可以设置每次在内存中保持的行数,当行数达到参数值时,这些在内存中临时保存的数据会被一次性flush(刷新)到磁盘上,这样就不会出现内存不够用的情况,从而达到防止内存溢出的目的;

对POI中的XSSFWorkbook,SXSSFWorkbook和Alibaba中EasyExcel对Excel文件操作的效率进行比较。
使用POI的XSSFWorkbook读取100w行数据:
此操作谨慎尝试!
[Java] 纯文本查看 复制代码
public class Demo06 {
        public static void main(String[] args) {
                String path="D:\\琐碎\\Excel\\100w行数据.xlsx";
                long begin=System.currentTimeMillis();
                try (Workbook workbook = new XSSFWorkbook(new FileInputStream(path))) {
                        Sheet sheet=workbook.getSheetAt(0);
                        //列头
                        Row headRow =sheet.getRow(0);
                        //遍历列头单元格
                        for(Cell headCell:headRow) {
                                System.out.print(headCell+"\t");
                        }
                        System.out.println();
                        //数据行
                        for(int i=1;i<sheet.getLastRowNum();i++) {
                                //获取当前行
                                Row row=sheet.getRow(i);
                                for (Cell cell:row) {
                                        CellType cellType=cell.getCellType();
                                        switch (cellType) {
                                        case STRING:
                                                System.out.print(cell.getStringCellValue()+"\t");
                                                break;
                                        case NUMERIC:
                                                System.out.print(cell.getNumericCellValue()+"\t");
                                                break;
                                        default:
                                                break;
 
                        
                                        }
                                }
                                System.out.println();
                        }
                        long end=System.currentTimeMillis();
                        System.out.println("程序共运行:"+(end-begin)/1000.0+"秒");
                } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }
}

运行结果:
使用POI的SXSSFWorkbook写入100w行数据:
[Java] 纯文本查看 复制代码
 
public class Demo {
        public static void main(String[] args) {
                long startTime=System.currentTimeMillis();
                try (Workbook workbook = new SXSSFWorkbook(200);
                                FileOutputStream fos = new FileOutputStream("D:\\琐碎\\Excel\\100w行数据.xlsx")) {
                        Sheet sheet1 = workbook.createSheet();
 
                        for (int i = 0; i <= [url=tel:1000000]1000000[/url]; i++) {
                        Row row = sheet1.createRow(i);
                        Cell cell0 = row.createCell(0);
                        cell0.setCellValue(UUID.randomUUID().toString());
                
                        Cell cell1 = row.createCell(1);
                        cell1.setCellValue(LocalDateTime.now());
                        }
 
                        workbook.write(fos);
                        long endTime=System.currentTimeMillis();
                        System.out.println("使用SXSSFWorkbook写入100w行数据需:"+(endTime-startTime)/1000.0+"秒");
                } catch (IOException e) {
                                e.printStackTrace();
                }
        }
}

写入前CPU与内存的占用情况:
Excel文件解析 - JackStar_冰凌汇编

写入时CPU与内存的占用情况:
Excel文件解析 - JackStar_冰凌汇编
代码运行结果:
Excel文件解析 - JackStar_冰凌汇编
使用SXSSFWorkbook写入100w行数据需:12.645秒

Alibaba EasyExcel:
EasyExcel是一个基于Java的操作简单、节省内存的读写Excel的Alibaba开源项目。在尽可能节约内存的情况下支持读写超大数据的Excel。

同样,要使用Alibaba EasyExcel必须下载相关jar包,并且不可随意下载不同版本:
Excel文件解析 - JackStar_冰凌汇编

准备实体类:
实体订单类:

[Java] 纯文本查看 复制代码
 
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.UUID;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.NumberFormat;
 
public class Order {
        @ExcelProperty("订单编号")
        private String orderId;
        @ExcelProperty("支付金额")
        @NumberFormat("¥#,###")
        private double money;
        @ExcelProperty(value="创建日期",converter = LocalDateTimeConveter.class)
        private LocalDateTime createTime;
        public Order() {
                this.orderId=LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss"))+UUID.randomUUID().toString().substring(0,5);
                this.money=Math.random()*100000;
                this.createTime=LocalDateTime.now();
        }
        public String getOrderId() {
                return orderId;
        }
        public void setOrderId(String orderId) {
                this.orderId = orderId;
        }
        public double getMoney() {
                return money;
        }
        public void setMoney(double money) {
                this.money = money;
        }
        public LocalDateTime getCreateTime() {
                return createTime;
        }
        public void setCreateTime(LocalDateTime createTime) {
                this.createTime = createTime;
        }
        @Override
        public String toString() {
                return "Order [orderId=" + orderId + ", money=" + money + ", createTime=" + createTime + "]";
        }
        
}

Converter转换类:目的---兼容LocalDateTime日期时间类
[Java] 纯文本查看 复制代码
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
 
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
 
public class LocalDateTimeConveter implements Converter<LocalDateTime> {
 
        @Override
        public CellData<String> convertToExcelData(LocalDateTime arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)
                        throws Exception {
                // TODO Auto-generated method stub
                return new CellData<>(arg0.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        }
        @Override
        public LocalDateTime convertToJavaData(CellData arg0, ExcelContentProperty arg1, GlobalConfiguration arg2)
                        throws Exception {
                // TODO Auto-generated method stub
                return LocalDateTime.parse(arg0.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        }
 
        @Override
        public CellDataTypeEnum supportExcelTypeKey() {
                // TODO Auto-generated method stub
                return CellDataTypeEnum.STRING;
        }
 
        @Override
        public Class supportJavaTypeKey() {
                // TODO Auto-generated method stub
                return LocalDateTime.class;
        }
 
 
 
}

使用EasyExcel写入100w条数据:
[Java] 纯文本查看 复制代码
import java.util.ArrayList;
import java.util.List;
 
import com.alibaba.excel.EasyExcel;
import com.jaiojaio.entity.Order;
 
public class Demo01 {
        public static void main(String[] args) {
        // 写入100w
                long startTime=System.currentTimeMillis();
        EasyExcel.write("D:\\琐碎\\Excel\\100weasy.xlsx", Order.class)
                 .sheet("订单列表")
                 .doWrite(data());
        long endTime=System.currentTimeMillis();
        System.out.println("使用EasyExcel写入100w条数据需:"+(endTime-startTime)/1000.0+"秒");
    }
    
    // 创建100w条订单数据
    private static List<Order> data() {
        List<Order> list = new ArrayList<Order>();
        for (int i = 0; i < [url=tel:1000000]1000000[/url]; i++) {
            list.add(new Order());
        }
        return list;
    }
}

写入时CPU与内存的占用情况:
Excel文件解析 - JackStar_冰凌汇编
运行结果:
Excel文件解析 - JackStar_冰凌汇编
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
使用EasyExcel写入100w条数据需:15.19秒

使用EasyExcel读取100w条数据:
[Java] 纯文本查看 复制代码
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
 
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.jaiojaio.entity.Order;
 
public class Demo02 {
        public static void main(String[] args) {
                long begin=System.currentTimeMillis();
                List<Order> list=new LinkedList<Order>();
                EasyExcel.read("D:\\琐碎\\Excel\\100weasy.xlsx", Order.class, new AnalysisEventListener<Order>() {
                        @Override
                        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                                // TODO Auto-generated method stub
                                System.out.println(headMap);
                                super.invokeHeadMap(headMap, context);
                        }
 
                        @Override
                        public void invoke(Order arg0, AnalysisContext arg1) {
                                // TODO Auto-generated method stub
                                list.add(arg0);
                        }
                        @Override
                        public void doAfterAllAnalysed(AnalysisContext arg0) {
                                // TODO Auto-generated method stub
                                System.out.println("game over");
                        }
                }).sheet().doRead();
                for(Order order:list) {
                        System.out.println(order);
                }
                long end=System.currentTimeMillis();
                System.out.println("程序需运行:"+(end-begin)/1000.0+"秒");
        }
}

读取时CPU与内存的占用情况:
Excel文件解析 - JackStar_冰凌汇编
代码运行结果:
[Java] 纯文本查看 复制代码
....
Order [orderId=20220717 11:41:37e9797, money=86139.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:377f2bc, money=52157.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:37afbb1, money=2815.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:37db079, money=27125.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:37944f7, money=92808.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:37862af, money=90109.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:378d5fe, money=41899.0, createTime=2022-07-17T11:41:37]
Order [orderId=20220717 11:41:3743530, money=4537.0, createTime=2022-07-17T11:41:37]
程序需运行:23.418秒

综上所属,在操作超大数据量的Excel文件时,不应使用操作普通文件的XSSFWork进行操作,应使用SXSSFWork或EasyExcel,并且EasyExcel的操作更为简单,因此,对于不同的需求进行选择即可
冰凌汇编免责声明
以上内容均来自网友转发或原创,如存在侵权请发送到站方邮件9003554@qq.com处理。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|小黑屋|站点统计|Archiver|小黑屋|RSS|冰凌汇编 ( 滇ICP备2022002049号 滇公网安备 53032102000029号)|网站地图

GMT+8, 2022-9-25 05:47 , Processed in 0.131874 second(s), 8 queries , Redis On.

冰凌汇编 - 建立于2021年12月20日

Powered by Discuz! © 2001-2022 Comsenz Inc.

快速回复 返回顶部 返回列表