Java实现解析.xlsb文件的示例代码

目录

• Java解析.Xlsb文件
• pom.xml
• 读取XLSB文件
• 解析具体单元格
• 实体类
• 结果

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 list workBookAsList.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 hier e.printStackTrace(); } catch (IOException e) { // TODO Please do your catch hier e.printStackTrace(); } catch (OpenXML4JException e) { // TODO Please do your catch hier e.printStackTrace(); } catch (SAXException e) { // TODO Please do your catch hier e.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(); } } / * 完成解析某一行的时候，自动进行调用 * 参数：行索引 * 目的：在解析完成某一行的时候，完成业务逻辑 */ @Override public void endRow(int i) { System.out.println("解析完成第"+i+"行数据："+vo); } / * 开始行中每一个单元格到时候，自动调用的方法 * cellname : 单元格名称（A3,H23,B2） * cellvalue ：单元格数据 * */ @Override public 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); } @Override public 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文件的示例代码的详细内容，更多关于Java解析.xlsb文件的资料请关注本网站其它相关文章！

您可能感兴趣的文章:

• java解析Excel的方法(xls、xlsx两种格式)
• java简单解析xls文件的方法示例【读取和写入】