Java解析.Xlsb文件
pom.xml
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<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文件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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(); } } } |
解析具体单元格
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
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) {
} } |
实体类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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; } |
结果
