day21-Excel文件解析

1. 概述

2. Apache POI

3. XSSF解析Excel文件

3.1. 添加Jar包依赖

3.2. Workbook（Excel文件）

3.2.2. 加载（解析）Excel文件

3.3. Sheet （工作簿）

3.3.1. 创建工作簿

3.3.2. 获取工作簿

3.3.3. 获取工作簿的数量

3.4. Row（数据行）

3.4.1. 创建数据行

3.4.2. 获取首行下标

3.4.3. 获取尾行下标

3.4.4. 根据下标获取指定行

3.4.5. 遍历所有行

3.4.6. 遍历指定区域行

3.5. Cell（单元格）

3.5.1. 创建单元格

3.5.2. 设置单元格值

3.5.3. 根据下标获取单元格

3.5.4. 遍历所有单元格

3.5.5. 获取单元格的类型

3.5.6. 设置单元格样式

3.5.7设置单元格数据格式

3.5.8. 设置单元格对齐

4. 超大Excel文件读写

4.1. 使用POI写出

4.2. 使用EasyExcel

4.2.1. 准备工作

4.3. 写入100w条数据

4.4. 读取100w条数据

1. 概述

在应用程序的开发过程中，经常需要使用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开源类库。

它分别提供对不同格式文件的解析：

HSSF －提供读写Microsoft Excel格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML格式档案的功能。
HWPF －提供读写Microsoft Word格式档案的功能。
HSLF －提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读写Microsoft Visio格式档案的功能。

3. XSSF解析Excel文件

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

3.1. 添加Jar包依赖

3.2. Workbook（Excel文件）

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

// 输出流
FileOutputStream fos = new FileOutputStream("c:\\test\\temp.xlsx");// Excel文件对象
Workbook workbook = new XSSFWorkbook();// 通过输出流进行写入
workbook.write(fos);// 关闭资源
fos.close();
workbook.close();

3.2.2. 加载（解析）`Excel`文件

// 输入流
FileInputStream fis = new FileInputStream("c:\\test\\1627356554991.xlsx");// Excel文件对象
Workbook workbook = new XSSFWorkbook(fis);

3.3. Sheet （工作簿）

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

3.3.1. 创建工作簿

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

3.3.2. 获取工作簿

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

3.3.3. 获取工作簿的数量

int n = workbook.getNumberOfSheets();

3.4. Row（数据行）

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

3.4.1. 创建数据行

Row row = sheet.createRow(0);

3.4.2. 获取首行下标

int first = sheet.getFirstRowNum();

3.4.3. 获取尾行下标

int last = sheet.getLastRowNum();

3.4.4. 根据下标获取指定行

Row row = sheet.getRow(0);

3.4.5. 遍历所有行

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

3.4.6. 遍历指定区域行

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

3.5. Cell（单元格）

通过Row来进行单元格Cell对象的获取或创建

3.5.1. 创建单元格

Cell cell0 = row.createCell(0);

3.5.2. 设置单元格值

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

3.5.3. 根据下标获取单元格

Cell cell = row.getCell(1);

3.5.4. 遍历所有单元格

for(Cell cell : row) {}

3.5.5. 获取单元格的类型

CellType type = cell.getCellType();

3.5.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);

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

3.5.8. 设置单元格对齐

// 创建单元格样式
CellStyle cellStyle = workbook.createCellStyle();//设置单元格的水平对齐类型。 此时水平居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 设置单元格的垂直对齐类型。 此时垂直靠底边
cellStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);

4. 超大Excel文件读写

4.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();
}

4.2. 使用EasyExcel

4.2.1. 准备工作

下载相关alibabaeasyexcel相关jar包
准备实体类

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;}@Overridepublic String toString() {return "Order [orderId=" + orderId + ", payment=" + payment + ", creationTime=" + creationTime + "]";}
}

准备Converter转换类（兼容LocalDateTime日期时间类）

public class LocalDateTimeConverter implements Converter<LocalDateTime> {@Overridepublic Class<LocalDateTime> supportJavaTypeKey() {return LocalDateTime.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));}@Overridepublic CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) {return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));}}

4.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.4. 读取100w条数据

// 读取100w条数据
EasyExcel.read("c:\\test\\run\\easy.xlsx", Order.class,new AnalysisEventListener<Order>() {@Overridepublic void invoke(Order order, AnalysisContext arg1) {// 读取每条数据orderList.add(order);}@Overridepublic void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {// 读取到列头System.out.println(headMap);}@Overridepublic void doAfterAllAnalysed(AnalysisContext arg0) {// 读取完毕System.out.println("END");}
}).sheet().doRead();