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();