poi事件模式读取excel

admin管理员组

文章数量:1585679

引入jar包 

    </dependencies>
        <dependency>
         <groupId>org.apache.poi</groupId>
         <artifactId>poi-ooxml</artifactId>
         <version>3.15</version>
        </dependency>    
        <dependency>
            <groupId>com.syncthemall</groupId>
            <artifactId>boilerpipe</artifactId>
            <version>1.2.1</version>
        </dependency>
    </dependencies>

关键代码工具类

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.springframework.stereotype.Component;
import org.xml.sax.helpers.DefaultHandler;

import java.io.InputStream;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

@Component
public class ExcelUtil extends DefaultHandler {

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;

    private int sheetIndex = -1;
    //存储某一行的数据
    private List<String> rowlist = new ArrayList<String>();
    private int curRow = 0;
    private int curCol = 0;
    private String col = "";
    //存储某个sheet的数据
    private List<List<String>> list = new ArrayList<>();
    //存储整个excel的数据
    private List<List<List<String>>> data;


    public static void main(String[] args) throws Exception {
        new ExcelUtil().process("C:\\Users\\zhang\\Desktop\\1-50蔡晨浩等人案件要素 - 删空白行和列(1).xlsx");
    }


    /**
     * 读取第一个工作簿的入口方法
     *
     * @param path
     */
    public void readOneSheet(String path) throws Exception {
        OPCPackage pkg = OPCPackage.open(path);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        InputStream sheet = r.getSheet("rId1");

        InputSource sheetSource = new InputSource(sheet);
        parser.parse(sheetSource);

        sheet.close();
    }


    /**
     * 读取所有工作簿的入口方法
     *
     * @param path
     * @throws Exception
     */
    public List<List<List<String>>> process(String path) throws Exception {
        data = new ArrayList<>();
        OPCPackage pkg = OPCPackage.open(path);
        XSSFReader r = new XSSFReader(pkg);
        SharedStringsTable sst = r.getSharedStringsTable();

        XMLReader parser = fetchSheetParser(sst);

        Iterator<InputStream> sheets = r.getSheetsData();
        while (sheets.hasNext()) {
//            System.out.println("换了个sheet=========================================================");
            curRow = 0;
            sheetIndex++;
            InputStream sheet = sheets.next();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource);
            data.add(list);
            list = new ArrayList<>();
            sheet.close();
        }
        return data;
    }

    /**
     * 该方法自动被调用，每读一行调用一次，在方法中写自己的业务逻辑即可
     *
     * @param sheetIndex 工作簿序号
     * @param curRow     处理到第几行
     

本文标签： 模式事件poiExcel