java操作execl读取百万级数据报表

本次记录java操作poi

步骤分析
（1）设置POI的事件模式
根据Excel获取文件流
根据文件流创建OPCPackage
创建XSSFReader对象
（2）Sax解析
自定义Sheet处理器
创建Sax的XmlReader对象
设置Sheet的事件处理器
逐行读取

代码片

实体

public class PoiEntity {
    private String id;
    private String breast;
    private String adipocytes;
    private String negative;
    private String staining;
    private String supportive;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getBreast() {
        return breast;
    }

    public void setBreast(String breast) {
        this.breast = breast;
    }

    public String getAdipocytes() {
        return adipocytes;
    }

    public void setAdipocytes(String adipocytes) {
        this.adipocytes = adipocytes;
    }

    public String getNegative() {
        return negative;
    }

    public void setNegative(String negative) {
        this.negative = negative;
    }

    public String getStaining() {
        return staining;
    }

    public void setStaining(String staining) {
        this.staining = staining;
    }

    public String getSupportive() {
        return supportive;
    }

    public void setSupportive(String supportive) {
        this.supportive = supportive;
    }

    @Override
    public String toString() {
        return "PoiEntity{" +
                "id='" + id + '\'' +
                ", breast='" + breast + '\'' +
                ", adipocytes='" + adipocytes + '\'' +
                ", negative='" + negative + '\'' +
                ", staining='" + staining + '\'' +
                ", supportive='" + supportive + '\'' +
                '}';
    }
}

 自定义处理器
//自定义Sheet基于Sax的解析处理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    //封装实体对象
    private PoiEntity entity;
    /**
     * 解析行开始
     */
    @Override
    public void startRow(int rowNum) {
        if (rowNum >0 ) {
            entity = new PoiEntity();
       }
   }
     * 解析每一个单元格
     * 参数说明：cellReference：单元格名称，
     * formattedValue：单元的值
     * comment：批注
     */
    @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment) 
{
        if(entity != null) {
            switch (cellReference.substring(0, 1)) {
                case "A":
                    entity.setId(formattedValue);
                    break;
                case "B":
                    entity.setBreast(formattedValue);
                    break;
                case "C":
                    entity.setAdipocytes(formattedValue);
                    break;
                case "D":
                    entity.setNegative(formattedValue);
                    break;
                case "E":
                    entity.setStaining(formattedValue);
                    break;
                case "F":
                    entity.setSupportive(formattedValue);
                    break;
                default:
                    break;
           }
       }
   }
    /**
     * 解析行结束
     */
    public void endRow(int rowNum) {
        System.out.println(entity);
   }
    //处理头尾
    public void headerFooter(String text, boolean isHeader, String tagName) {
   }
}
    /**

解析器

/**
* 自定义Excel解析器
*/
public class ExcelParser {
    public void parse (String path) throws Exception {
        //1.根据Excel获取OPCPackage对象
         OPCPackage pkg = OPCPackage.open(文件路径, PackageAccess.READ);
        try {
            //2.创建XSSFReader对象
            XSSFReader reader = new XSSFReader(pkg);
            //3.获取SharedStringsTable对象
            SharedStringsTable sst = reader.getSharedStringsTable();
            //4.获取StylesTable对象
            StylesTable styles = reader.getStylesTable();
            //5.创建Sax的XmlReader对象
            XMLReader parser = XMLReaderFactory.createXMLReader();
            //6.设置处理器
            parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new
SheetHandler(), false));
            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) 
reader.getSheetsData();
            //7.逐行读取
            while (sheets.hasNext()) {
                InputStream sheetstream = sheets.next();
                InputSource sheetSource = new InputSource(sheetstream);
                try {
                    parser.parse(sheetSource);
               } finally {
                    sheetstream.close();
               }
           }
       } finally {
            pkg.close();
       }
   }
}