package cn.itcast.poi.test;
import cn.itcast.poi.entity.cn.itcast.poi.handler.SheetHandler;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.InputStream;
import java.util.Iterator;
/**
* 使用事件模型解析百万数据excel报表
*/
public class PoiTest06 {
public static void main(String[] args) throws Exception {
String path = "C:\Users\ThinkPad\Desktop\ihrm\day8\资源\百万数据报表\demo.xlsx";
//1.根据excel报表获取OPCPackage
OPCPackage opcPackage = OPCPackage.open(path, PackageAccess.READ);
//2.创建XSSFReader
XSSFReader reader = new XSSFReader(opcPackage);
//3.获取SharedStringTable对象
SharedStringsTable table = reader.getSharedStringsTable();
//4.获取styleTable对象
StylesTable stylesTable = reader.getStylesTable();
//5.创建Sax的xmlReader对象
XMLReader xmlReader = XMLReaderFactory.createXMLReader();
//6.注册事件处理器
XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(stylesTable,table,new SheetHandler(),false);
xmlReader.setContentHandler(xmlHandler);
//7.逐行读取
XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) reader.getSheetsData();
while (sheetIterator.hasNext()) {
InputStream stream = sheetIterator.next(); //每一个sheet的流数据
InputSource is = new InputSource(stream);
xmlReader.parse(is);
}
}
}
package cn.itcast.poi.entity.cn.itcast.poi.handler;
import cn.itcast.poi.entity.PoiEntity;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
/**
* 自定义的事件处理器
* 处理每一行数据读取
* 实现接口
*/
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
private PoiEntity entity;
/**
* 当开始解析某一行的时候触发
* i:行索引
*/
@Override
public void startRow(int i) {
//实例化对象
if(i>0) {
entity = new PoiEntity();
}
}
/**
* 当结束解析某一行的时候触发
* i:行索引
*/
@Override
public void endRow(int i) {
//使用对象进行业务操作
System.out.println(entity);
}
/**
* 对行中的每一个表格进行处理
* cellReference: 单元格名称
* value：数据
* xssfComment：批注
*/
@Override
public void cell(String cellReference, String value, XSSFComment xssfComment) {
//对对象属性赋值
if(entity != null) {
String pix = cellReference.substring(0,1);
switch (pix) {
case "A":
entity.setId(value);
break;
case "B":
entity.setBreast(value);
break;
case "C":
entity.setAdipocytes(value);
break;
case "D":
entity.setNegative(value);
break;
case "E":
entity.setStaining(value);
break;
case "F":
entity.setSupportive(value);
break;
default:
break;
}
}
}
}
package cn.itcast.poi.entity;
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 + ''' +
'}';
}
}
