本文主要对Excel中数据的解析和生成进行总结
前言
在应用程序的开发过程中，我们经常要用到Excel进行数据的导入或导出。所以，在通过Java语言实现此类需求时，通常会对Excel文件进行解析或生成。
一、Excel文件解析
1，概述
在应用程序开发过程中，经常需要使用Excel文件来进行数据的导入或导出。所以，在通过Java语言来实现此类需求的时候，往往会面临着Excel文件解析（导入）或生成（导出）。
在Java技术生态圈中，可以进行Excel问价处理的主流技术包括：Apache POI，JXL，Alibaba EasyExcel等。
Apache POI基于DOM方式进行解析，将文件直接加载内存，所以速度较快，适合Excel文件数量不大的应用场景。JXL只支持Excel2003以下版本，所以不太常见。
Alibaba EasyExcel采用逐行读取的解析模式，将每一行的解析结果以观察者模式通知处理（AnalyEventListener），所以比较适合数据体量较大的Excel文件解析。
2， Apache POI
Apache POI使用Java编写的免费开源的跨平台的Java API。Apache POI提供给Java程序对Microso Office 格式档案进行读写功能的API开源类库
HSSF ：提供读写Microsoft Excel格式档案的功能。
XSSF ：提供读写Microsoft Excel 0OXML格式档案的功能。HWPF
HWPF : 提供读写Microsoft Word格式档案的功能。
HSLF : 提供读写Microsoft PowerPoint格式档案的功能。
HDGF : 提供读写Microsoft Visio格式档案的功能。
3， XSSF解析Excel文件
HSSF用于解析旧版本(*.xls) Excel文件， 由于旧版本的Excel文件只能存在65535行数据, 所以目前已经不常用。所以目前主要采用XSSF进行新版本(*.xlsx) Exce文件的解析。
导入下图中的架包：
Workbook (Excel文件)
Workbook接口代表一一个 Excel文件，用于创建或加载(解析)见实现类是XSSFWorkbook。
创建Excel文件：
// 输出流
FileOutputStream fos = new FileOutputStream("c:\\test\\temp.xlsx");
// Excel文件对象
Workbook workbook = new XSSFWorkbook();
// 通过输出流进行写入
workbook.write(fos);
// 关闭资源
fos.close();
workbook.close();try (Workbook workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream("c:\\test\\temp.xlsx")) {
workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
}
解析Excel文件
// 输入流
FileInputStream fis = new FileInputStream("c:\\test\\12345.xlsx");
// Excel文件对象
Workbook workbook = new XSSFWorkbook(fis);Sheet(工作簿)
通过Workbook来进行工作簿Sheet对象的获取或创建。
创建工作簿
// 按照默认名称创建工作簿
Sheet sheet1 = workbook.createSheet();
// 按照自定义名称创建工作簿
Sheet sheet2 = workbook.createSheet("自定义工作簿2");
获取工作簿
// 按照工作簿下标获取Sheet
Sheet sheet01 = workbook.getSheetAt(0);
// 按照工作簿名称获取Sheet
Sheet sheet02 = workbook.getSheet("Sheet0");获取工作簿数量
int n = workbook.getNumberOfSheets();Row (数据行)
通过Sheet 来进行数据行Row 对象的获取或创建
获取首行下标
int first = sheet.getFirstRowNum();
获取末尾行下标
int last = sheet.getLastRowNum();
根据下标获取指定行
Row row = sheet.getRow(0);
遍历所有行
for(Row row : sheet) {
System.out.println(row);
}
遍历指定区域行
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
System.out.println(row);
}
Cell (单元格)
通过Row 来进行单元格Cell 对象的获取或创建
创建单元格
Cell cell0 = row.createCell(0);
设置单元格值
cell0.setCellValue(UUID.randomUUID().toString());
遍历所有单元格
for(Cell cell : row) {
}
获取所有单元格类型
CellType type = cell.getCellType();
设置单元格样式
// 创建单元格样式
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()); // 保存当前日期时间至本单元格
设置单元格对齐
// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();
//设置单元格的水平对齐类型。 此时水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格的垂直对齐类型。 此时垂直靠底边
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
二、超大Excel文件读写
1，使用POI写入
package Myde;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
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;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Demo06{
public static void main(String[] args) {
//List<String> list= Arrays.asList("AA","BB","CC","DD","EE","FF");
//读取一个已存在的Excel文件
try (Workbook workbook=new SXSSFWorkbook(1000);
FileOutputStream out=new FileOutputStream("D:\\demo-data.xlsx")){
//在已经存在的Excel文件中，创建一个新的sheet
Sheet sheet=workbook.createSheet();
//获取格式编码值
DataFormat dataformat=workbook.createDataFormat();
short dateformatCode=dataformat.getFormat("yy年MM月dd日 HH:mm:ss");
short moneyformatCode=dataformat.getFormat("￥#,###");
//创建日期格式对象
CellStyle datecellstyle=workbook.createCellStyle();
datecellstyle.setDataFormat(dateformatCode);
//创建货币格式对象
CellStyle moneycellstyle=workbook.createCellStyle();
moneycellstyle.setDataFormat(moneyformatCode);;
//创建列头
Row headrow=sheet.createRow(0);
Cell headcell0=headrow.createCell(0);
headcell0.setCellValue("序号");
Cell headcell1=headrow.createCell(1);
headcell1.setCellValue("姓名");
Cell headcell2=headrow.createCell(2);
headcell2.setCellValue("日期");
Cell headcell3=headrow.createCell(3);
headcell3.setCellValue("红包金额");
long begin=System.currentTimeMillis();
for(int i=0;i<1000000;i++) {
//String name=list.get(i);
String name=i+"A";
//创建行
Row row=sheet.createRow(i+1);
//创建单元格
Cell cell0=row.createCell(0);//序号
cell0.setCellValue(String.valueOf(i+1));
Cell cell1=row.createCell(1);//姓名
cell1.setCellValue(name);
Cell cell2=row.createCell(2);//日期
cell2.setCellStyle(datecellstyle);
cell2.setCellValue(new Date());
Cell cell3=row.createCell(3);//金额
cell3.setCellStyle(moneycellstyle);
cell3.setCellValue(Math.random()*1000000);
}
//写入文件
workbook.write(out);
long end=System.currentTimeMillis();
System.out.println("耗时"+(end-begin)+"毫秒");
} catch (IOException e) {
e.printStackTrace();
}
}
}
可以看到使用SXSSDWorkbook的执行时间为：
2.使用EasyExcel
（1）下载alibaba easyexcel相关jar包
（2） 订单实体类Order
public class Order {
@ExcelProperty("订单编号")
private String orderId; // 订单编号
@ExcelProperty("支付金额")
@NumberFormat("￥#,###")
private Double payment; // 支付金额
@ExcelProperty(value = "创建日期",converter = LocalDateTimeConverter.class)
private LocalDateTime creationTime; // 创建时间
public Order() {
this.orderId = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddhhmmss"))
+ UUID.randomUUID().toString().substring(0, 5);
this.payment = Math.random() * 10000;
this.creationTime = LocalDateTime.now();
}
public String getOrderId() {
return orderId;
}
public void setOrderId(String orderId) {
this.orderId = orderId;
}
public Double getPayment() {
return payment;
}
public void setPayment(Double payment) {
this.payment = payment;
}
public LocalDateTime getCreationTime() {
return creationTime;
}
public void setCreationTime(LocalDateTime creationTime) {
this.creationTime = creationTime;
}
@Override
public String toString() {
return "Order [orderId=" + orderId + ", payment=" + payment + ", creationTime=" + creationTime + "]";
}
}（3）Converter转换类（兼容LocalDateTime日期时间类）
public class LocalDateTimeConverter implements Converter<LocalDateTime> {
@Override
public Class<LocalDateTime> supportJavaTypeKey() {
return LocalDateTime.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
}
@Override
public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
}
}（4）写入100w条数据
public class Demo {
public static void main(String[] args) {
// 写入100w
EasyExcel.write("c:\\test\\run\\easy.xlsx", Order.class)
.sheet("订单列表")
.doWrite(data());
}
// 创建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;
}
}（5）读取100w条数据
EasyExcel.read("c:\\test\\run\\easy.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);
}
@Override
public void doAfterAllAnalysed(AnalysisContext arg0) {
// 读取完毕
System.out.println("END");
}
}).sheet().doRead();EasyExcel的执行时间为
可以看到两种方法时间相差不大，但在运行时，CPU的利用率还是有一定差距。
POI:
EasyExcel：
从此可以得出，在读写超大Excel文件时，我推荐大家使用EasyExcel。
总结
- Excel文件解析 apache POI,可以解析world和Excel
- getRow(),获取行 lastRoeNumber() 最后一个，getCell()按下标获取当前行的单元格
- GetStringCellValue() -->文本 getNumberCellValue() -->数字
- Workbook.createSheet() 创建表格
- CellStyle单元格格式
- Excel文件对象 Workbook workbook=new XSSFEorkbook()
- 创建工作簿 Sheet sheet1=workbook.createSheet();
- 按照工作簿下标获取Sheet Sheet sheet1=workbook.getSheetAt(0);
- 遍历所有行for(Row row:sheet){System.out.println(row);}
326
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
