Java解析.Xlsb文件

pom.xml

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.17</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.17</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.10</version></dependency>

讀取XLSB文件

package com.example.demo.utils;import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.binary.XSSFBSharedStringsTable; import org.apache.poi.xssf.binary.XSSFBSheetHandler; import org.apache.poi.xssf.binary.XSSFBStylesTable; import org.apache.poi.xssf.eventusermodel.XSSFBReader; import org.xml.sax.SAXException;import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.List; import java.util.Objects;/*** @author xinlan* @version 1.0* @date 2023/1/29 21:15*/ public class ExcelXlsbFileUtils {private static final String filePath = "D:\\Documents\\WeChat Files\\wxid_cnm3kxiloquj21\\FileStorage\\File\\2023-01\\CLARKSONS WAF VLCC POSITION UPDATED 05 JAN 2023.xlsb";public static void main(String[] args) {callXLToList(filePath);}static void callXLToList(String xlsbFileName){OPCPackage pkg;try {pkg = OPCPackage.open(xlsbFileName);XSSFBReader r = new XSSFBReader(pkg);XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();List<XLSB2Lists> workBookAsList = new ArrayList<>();int sheetNr = 1;XLSB2Lists testSheetHandler = new XLSB2Lists();while (it.hasNext()) {InputStream is = it.next();String name = it.getSheetName();XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is,xssfbStylesTable,it.getXSSFBSheetComments(),sst, testSheetHandler,new DataFormatter(),false);sheetHandler.parse();sheetNr++;// Add parsed sheet to workbook listworkBookAsList.add(testSheetHandler);}List<Book> list = testSheetHandler.list;System.out.println("========================");for (Book book : list) {if(!Objects.isNull(book)) {System.out.println("book.toString() = " + book.toString());}}} catch (InvalidFormatException e) {// TODO Please do your catch hiere.printStackTrace();} catch (IOException e) {// TODO Please do your catch hiere.printStackTrace();} catch (OpenXML4JException e) {// TODO Please do your catch hiere.printStackTrace();} catch (SAXException e) {// TODO Please do your catch hiere.printStackTrace();}} }

解析具體單元格

package com.example.demo.utils;import lombok.Data; import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler; import org.apache.poi.xssf.usermodel.XSSFComment;import java.util.ArrayList; import java.util.List;/*** @author xinlan* @version 1.0* @date 2023/1/29 21:42*/ @Data public class XLSB2Lists implements XSSFSheetXMLHandler.SheetContentsHandler {public List<Book> list = new ArrayList<>();private Book vo = null;/*** 開始解析某一行的時候，自動進行調用* 參數 ： 行索引*/public void startRow(int i) {if(i>=2) {vo = new Book();}}/*** 完成解析某一行的時候，自動進行調用* 參數：行索引* 目的：在解析完成某一行的時候，完成業務邏輯*/@Overridepublic void endRow(int i) {System.out.println("解析完成第"+i+"行數據："+vo);}/*** 開始行中每一個單元格到時候，自動調用的方法* cellname : 單元格名稱（A3,H23,B2）* cellvalue ：單元格數據**/@Overridepublic void cell(String cellname, String cellvalue, XSSFComment xssfComment) {if(vo != null) {cellname = cellname.substring(0,1);if("C".equals(cellname)) {vo.setETA(cellvalue);}else if("D".equals(cellname)) {vo.setVESSEL(cellvalue);}else if("E".equals(cellname)) {vo.setDWT(cellvalue);}else if("F".equals(cellname)) {vo.setBLT(cellvalue);}else if("G".equals(cellname)) {vo.setSCR(cellvalue);}else if("H".equals(cellname)) {vo.setDRAFT(cellvalue);}else if("I".equals(cellname)) {vo.setPOSITION(cellvalue);}else if("J".equals(cellname)) {vo.setOPEN(cellvalue);}else if("K".equals(cellname)) {vo.setOWNER(cellvalue);}else if("L".equals(cellname)) {vo.setCOMMENTS(cellvalue);}}list.add(vo);}@Overridepublic void headerFooter(String s, boolean b, String s1) {} }

實體類

package com.example.demo.utils;import lombok.Data;/*** @author xinlan* @version 1.0* @date 2023/1/29 22:19*/ @Data public class Book {private String ETA;private String VESSEL;private String DWT;private String BLT;private String SCR;private String DRAFT;private String POSITION;private String OPEN;private String OWNER;private String COMMENTS; }

結果

總結

以上是生活随笔為你收集整理的JAVA解析.xlsb文件的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。