Excel解析与超大文件的读写

# 1. Excel解析

## 1.1 Workbook

常见实现类是XSSFWorkbook

创建Excel文件

```java
try (Workbook workbook = new XSSFWorkbook();
				FileOutputStream fos = new FileOutputStream("c:\\test\\temp.xlsx")) {
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
}
```



​	加载（解析）Excel文件

```java
// 输入流
FileInputStream fis = new FileInputStream("c:\\test\\1627356554991.xlsx");

// Excel文件对象
Workbook workbook = new XSSFWorkbook(fis);
```

## 1.2 工作簿

1. 创建工作簿

```java
// 按照默认名称创建工作簿
Sheet sheet1 = workbook.createSheet();

// 按照自定义名称创建工作簿
Sheet sheet2 = workbook.createSheet("自定义工作簿2");
```

2. 获取工作簿

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

// 按照工作簿名称获取Sheet
Sheet sheet02 = workbook.getSheet("Sheet0");
```

3. 获取工作簿的数量

```java
int n = workbook.getNumberOfSheets();
```

## 1.2 Row(数据行)

1. 创建数据行

```java
Row row = sheet.createRow(0);
```

2. 获取首行下标

```java
int first = sheet.getFirstRowNum();
```

3. 获取尾行下标

int row = sheet.getRow(0);

4. 遍历指定区域行

for(int i=1; i<=sheet.getLashRowNum(); i++) {

​	Row row = sheet.getRow(i);

​	System.out.println(row);

}



## 1.3 Cell(单元格)

1. 创建单元格

Cell cell0 = row.createCell(0);

2. 设置单元格值

cell0.setCellValue(UUID.randomUUID().toString());

3. 根据下标获取单元格

Cell cell = row.getCell(1);

4. 遍历所有单元格

for(Cell cell : row) {

}

5. 获取单元格的类型

CellType type = cell.getCellType();

6. 设置单元格样式

//创建单元格样式

DataFormat dataFormat = workbook.createDataFormat();

Short formatCode = dataFormat.getFormat("yyyy-MM-dd HH:mm:ss");

CellStyle cellStyle = workbook.createCellStyle();

cellStyle.setDataFormat(formatCode);

//为当前行创建单元格

Cell cell1 = row.createCell(1);

cell1.setCellStyle(cellStyle);

cell1.setCellValue(new Date());

7. 设置单元格对齐

//创建单元格样式

CellStyle cellStyle = workbook.createCellStyle();

cellStyle.setAlignment(HorizontalAlignment.CENTER);

cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

# 2 超大Excel文件读写

## 2.1 使用POI写入和使用EasyExcel写入的效率比较

- 使用EasyExcel写

```java
package com.my.test01;

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

import com.alibaba.excel.EasyExcel;
import com.my.entity.Order;


public class Easy_writer {
	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		// 写入100w
		EasyExcel.write("D:\\D-JAVA\\猿-文档\\easy100w.xlsx", Order.class).sheet("订单列表").doWrite(data());
		long end = System.currentTimeMillis();
		System.out.println(end - start);
	}

	// 创建100w条订单数据
	private static List<Order> data() {
		List<Order> list = new ArrayList<Order>();
		for (int i = 0; i < 1000000; i++) {
			list.add(new Order());
		}
		return list;
	}
}

```

执行结果：

![image-20220714210318073](C:\Users\李刚\AppData\Roaming\Typora\typora-user-images\image-20220714210318073.png)

- 使用EasyExcel读

```java
package com.my.test01;

import java.util.ArrayList;


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.my.entity.Order;

public class Test_read {
	public static void main(String[] args) {
		
		// 用于保存读取到结果
		List<Order> orderList = new ArrayList<Order>();

		// 读取
		long start = System.currentTimeMillis(); //开始时间
		//AnalysisEventListener抽象类，实现了ReadListener，创建匿名子类刚好可以用到抽象类中的三种方法
		EasyExcel.read("D:\\D-JAVA\\猿-文档\\easy100w.xlsx", Order.class, new AnalysisEventListener<Order>() {
			
			@Override
			public void invoke(Order order, AnalysisContext arg1) {
				// 每读取到一条订单数据
				orderList.add(order);
			}
			
			@Override
			public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
				//读取表格列头
				System.out.println(headMap);
				super.invokeHeadMap(headMap, context);
			}

			

			@Override
			public void doAfterAllAnalysed(AnalysisContext arg0) {
				//所有订单数据读取完毕
				System.out.println("GAME OVER!");

			}

		}).sheet().doRead();
		
		long end = System.currentTimeMillis();//结束时间
		//获得执行时间
		System.out.println(end-start);
		//遍历
//		for(Order order : orderList) {
//			System.out.println(order);
//		}
	}
}

```





- 使用POI写

```java
package com.my.easyExcel_poi;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.UUID;

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.apache.poi.xssf.streaming.SXSSFWorkbook;
public class PoiWriter {
	public static void main(String[] args) {
		try (Workbook workbook = new SXSSFWorkbook(100);
				FileOutputStream fos = new FileOutputStream("D:\\D-JAVA\\猿-文档\\easy100w.xlsx")) {
			Sheet sheet1 = workbook.createSheet();
			long start = System.currentTimeMillis();
			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(new Date());
			}
			
			workbook.write(fos);
			long end = System.currentTimeMillis();
			System.out.println(end - start);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}



```

执行结果

![image-20220714211303548](C:\Users\李刚\AppData\Roaming\Typora\typora-user-images\image-20220714211303548.png)

- 比较总结

1. 使用难度

- POI比较难，使用完必须手动关闭流
- easyExcel，简单只需要提供数据和模板，不需要关闭

2. 读写速度

easyExcel读写速度大于SXSSF

3. easyExcel的优势

从上面可以看出来，POI能做的easyExcel也能做，而且做得更加优秀（防止内存溢出、性能）、更加地方便使用。