Excel文件解析

一、概述：

         在应用程序的开发过程中，经常需要使用Excel文件来进行数据的导入或导出。所以，在通过Java语言实现此类需求的时候，往往会面临着Excel文件的解析（导入）或生成（导出）。在Java技术生态圈中，可以进行Excel文件处理的主流技术包括：Apache POI、JXL、Alibaba EasyExcel等。Apache POI基于DOM方式进行解析，将文件直接加载内存，所以速度较快，适合Excel文件数据量不大的应用场景。JXL只支持Excel 2003以下版本，所以不太常见。Alibaba EasyExcel采用逐行读取的解析模式，将每一行的解析结果以观察者的模式通知处理（AnalysisEventListener），所以比较适合数据体量较大的Excel文件解析。

二、2. Apache POI：

Apache POI是用Java编写的免费开源的跨平台的Java API，Apache POI提供给Java程序对Microsoft Office格式档案进行读写功能的API开源类库。它分别提供对不同格式文件的解析

（1）HSSF － 提供读写Microsoft Excel格式档案的功能。

（2）XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

（3）HWPF － 提供读写Microsoft Word格式档案的功能。

（4）HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

（5）HDGF － 提供读写Microsoft Visio格式档案的功能。

三、XSSF解析Excel文件：

   HSSF用于解析旧版本（*.xls）Excel文件，由于旧版本的Excel文件只能存在65535行数据，所以目前已经不常用。所以目前主要采用XSSF进行新版本（*.xlsx）Exce文件的解析。

1、Workbook（Excel文件）：

      Workbook接口代表一个Excel文件，用于创建或加载（解析）Excel文件。常见实现类是XSSFWorkbook。

（1）创建Excel文件

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

（2）加载（解析）Excel文件：

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

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

2、Sheet （工作簿）：通过Workbook来进行工作簿Sheet对象的获取或创建

（1） 创建工作簿

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

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

（2）获取工作簿：

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

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

（3）获取工作簿的数量：

int n = workbook.getNumberOfSheets();

4、Row（数据行）：通过Sheet来进行数据行Row对象的获取或创建

（1）创建数据行：

Row row = sheet.createRow(0);

（2）获取首行下标：

int first = sheet.getFirstRowNum();

（3）获取尾行下标：

int last = sheet.getLastRowNum();

（4）获取下标指定行：

Row row = sheet.getRow(0);

（5）遍历所有行：

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

（6）遍历指定区域行：

for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
System.out.println(row);
}

5、Cell（单元格）：通过Row来进行单元格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）设置单元格样式：

// 创建单元格样式
CellStyle headerCellStyle = workbook.createCellStyle();

//设置单元格的水平对齐类型, 此时水平居中
headerCellStyle.setAlignment(HorizontalAlignment.CENTER);

// 设置单元格的垂直对齐类型,此时垂直靠底边
headerCellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

// 创建并设置字体
Font font = workbook.createFont();
font.setBold(true);
font.setColor(Font.COLOR_RED);
headerCellStyle.setFont(font);

（7）设置单元格数据格式：

// 创建单元格样式
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()); // 保存当前日期时间至本单元格

（8）设置单元格对齐：

// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();

//设置单元格的水平对齐类型。 此时水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);

// 设置单元格的垂直对齐类型。 此时垂直靠底边
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

四、超大Excel文件读写

1、 使用POI写出：使用SXSSFWorkbook进行写入，通过设置SXXFWorkbook的构造参数，可以设置每次在内存中保持的行数，当达到这个值的时候，那么会把这些数据flush到磁盘上，这样就不会出现内存不够的情况。

try (Workbook workbook = new SXSSFWorkbook(100);
FileOutputStream fos = new FileOutputStream("c:\\\\test\\\\temp.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(new Date());
}

workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
}

2、使用EasyExcel：

（1）准备实体类：

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 + "]";
}
}

（2）、准备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")));
}

}

3、写入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;
}
}

4、读取100w条数据：

// 读取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();