Excel文件解析

一、前言

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包：

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

Workbook(Excel文件)：

Workbook接口代表一个Excel文件，用于创建或加载(解析)一个Excel文件，常见实现类有：XSSFWorkbook

创建Excel文件：

	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文件：

    try (FileInputStream in = new FileInputStream("D:\琐碎\1627356552686.xlsx");
			Workbook workbook = new XSSFWorkbook(in)) {
	} catch (IOException e) {
			e.printStackTrace();
	}

Sheet(工作簿）：

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

获取工作簿：

Sheet sheet=workbook.getSheet("sheet0");
//按照名称获取工作簿

Sheet sheet=workbook.getSheetAt(0);
//按照下标获取工作簿

创建工作簿：

Sheet sheet=workbook.createSheet();
//创建默认名称工作簿

Sheet sheet=workbook.createSheet("数据表");
//创建名为“数据表”的工作簿

获取工作簿的数量：

int num=workbook.getNumberOfSheets();

Row(数据行)：

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

创建数据行：

Row row=sheet.createRow(0);
//按照下标创建行

获取首尾行下标：

	int firstRowIndex=sheet.getFirstRowNum();
    //获取首行下标

	int lastRowIndex=sheet.getLastRowNum();
    //获取尾行下标

根据下标获取指定行：

Row row=sheet.getRow(0);
//获取下标为零的数据行

遍历数据行：

    for(Row row:sheet) {
		System.out.println(row);
	}

遍历指定区域：

    for(int i=1;i 
Cell(单元格）： 
通过Row来进行单元格Cell对象的获取或创建 
创建单元格： 
    Cell cell=row.createCell(0);
    //按下标索引来创建单元格 
添加单元格值： 
       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的单元格添加计算机当前时间 
根据下标获取单元格： 
      Cell cell1=row.getCell(0);
      //获取当前行下标值为0的单元格
	  Cell cell2=row.getCell(1);
      //获取当前行下标值为1的单元格
	  
遍历所以单元格: 
   for(Cell cell : Row){  
        system.out.println(cell);
   } 
获取单元格类型： 
   CellType cellType=cell.getCellType(); 
设置单元格样式： 
   //获取格式编码值
	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行数据：（此操作谨慎尝试！） 
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 
运行结果： 
使用POI的SXSSFWorkbook写入100w行数据： 
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 <= 1000000; 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与内存的占用情况： 
 
写入时CPU与内存的占用情况： 
 
 
代码运行结果： 
  
 
使用SXSSFWorkbook写入100w行数据需：12.645秒 
 
Alibaba EasyExcel： 
EasyExcel是一个基于Java的操作简单、节省内存的读写Excel的Alibaba开源项目。在尽可能节约内存的情况下支持读写超大数据的Excel。 
同样，要使用Alibaba EasyExcel必须下载相关jar包，并且不可随意下载不同版本： 
 
准备实体类： 
实体订单类： 
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日期时间类 
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 {

	@Override
	public CellData 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条数据： 
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 data() {
        List list = new ArrayList();
        for (int i = 0; i < 1000000; i++) {
            list.add(new Order());
        }
        return list;
    }
} 
写入时CPU与内存的占用情况：  
 
 运行结果： 
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条数据： 
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 list=new LinkedList();
		EasyExcel.read("D:\琐碎\Excel\100weasy.xlsx", Order.class, new AnalysisEventListener() {
			@Override
			public void invokeHeadMap(Map 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与内存的占用情况：  
 代码运行结果： 
....
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的操作更为简单，因此，对于不同的需求进行选择即可

Excel文件解析

.Net相关栏目本月热门文章