Java实现解析.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-ojavascriptoxml</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 namandroide = 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 = jstestSheetHandler.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);
            }elphpse 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);http://www.devze.com
    }

    @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;
}