https://blog.51cto.com/u_12302929/3289969
https://www.jianshu.com/p/30d21dba7cb4
package com.sjky.platform.myapp.importData.utils;
import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener; import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFEventFactory; import org.apache.poi.hssf.eventusermodel.HSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFRequest; import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener; import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord; import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord; import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.BOFRecord; import org.apache.poi.hssf.record.BlankRecord; import org.apache.poi.hssf.record.BoolErrRecord; import org.apache.poi.hssf.record.BoundSheetRecord; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.LabelRecord; import org.apache.poi.hssf.record.LabelSSTRecord; import org.apache.poi.hssf.record.NumberRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.SSTRecord; import org.apache.poi.hssf.record.StringRecord; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import com.sjky.platform.myapp.importData.service.impl.importDataServiceimpl;
public class Excel2003Reader implements HSSFListener{ private int minColumns = -1; private POIFSFileSystem fs; private int lastRowNumber; private int lastColumnNumber; /** Should we output the formula, or the value it has? / private boolean outputFormulaValues = true; /* For parsing Formulas / private SheetRecordCollectingListener workbookBuildingListener; //excel2003工作薄 private HSSFWorkbook stubWorkbook; // Records we pick up as we process private SSTRecord sstRecord; private FormatTrackingHSSFListener formatListener; //表索引 private int sheetIndex = -1; private BoundSheetRecord[] orderedBSRs; @SuppressWarnings("unchecked") private ArrayList boundSheetRecords = new ArrayList(); // For handling formulas with string results private int nextRow; private int nextColumn; private boolean outputNextStringRecord; //当前行 private int curRow = 0; //存储行记录的容器 private List rowlist = new ArrayList();; @SuppressWarnings( "unused") private String sheetName; /* * 遍历excel下所有的sheet * @throws IOException / public void process(String fileName) throws IOException { this.fs = new POIFSFileSystem(new FileInputStream(fileName)); MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener( this); formatListener = new FormatTrackingHSSFListener(listener); HSSFEventFactory factory = new HSSFEventFactory(); HSSFRequest request = new HSSFRequest(); if (outputFormulaValues) { request.addListenerForAllRecords(formatListener); } else { workbookBuildingListener = new SheetRecordCollectingListener( formatListener); request.addListenerForAllRecords(workbookBuildingListener); } factory.processWorkbookEvents(request, fs); } /* * HSSFListener 监听方法,处理 Record */ @SuppressWarnings("unchecked") public void processRecord(Record record) { int thisRow = -1; int thisColumn = -1; String thisStr = null; String value = null; switch (record.getSid()) { case BoundSheetRecord.sid: boundSheetRecords.add(record); break; case BOFRecord.sid: BOFRecord br = (BOFRecord) record; if (br.getType() == BOFRecord.TYPE_WORKSHEET) { // 如果有需要,则建立子工作薄 if (workbookBuildingListener != null && stubWorkbook == null) { stubWorkbook = workbookBuildingListener .getStubHSSFWorkbook(); } sheetIndex++; if (orderedBSRs == null) { orderedBSRs = BoundSheetRecord .orderByBofPosition(boundSheetRecords); } sheetName = orderedBSRs[sheetIndex].getSheetname(); } break; case SSTRecord.sid: sstRecord = (SSTRecord) record; break; case BlankRecord.sid: BlankRecord brec = (BlankRecord) record; thisRow = brec.getRow(); thisColumn = brec.getColumn(); thisStr = ""; rowlist.add(thisColumn, thisStr); break; case BoolErrRecord.sid: //单元格为布尔类型 BoolErrRecord berec = (BoolErrRecord) record; thisRow = berec.getRow(); thisColumn = berec.getColumn(); thisStr = berec.getBooleanValue()+""; rowlist.add(thisColumn, thisStr); break; case FormulaRecord.sid: //单元格为公式类型 FormulaRecord frec = (FormulaRecord) record; thisRow = frec.getRow(); thisColumn = frec.getColumn(); if (outputFormulaValues) { if (Double.isNaN(frec.getValue())) { // Formula result is a string // This is stored in the next record outputNextStringRecord = true; nextRow = frec.getRow(); nextColumn = frec.getColumn(); } else { thisStr = formatListener.formatNumberDateCell(frec); } } else { thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"'; } rowlist.add(thisColumn,thisStr); break; case StringRecord.sid://单元格中公式的字符串 if (outputNextStringRecord) { // String for formula StringRecord srec = (StringRecord) record; thisStr = srec.getString(); thisRow = nextRow; thisColumn = nextColumn; outputNextStringRecord = false; } break; case LabelRecord.sid: LabelRecord lrec = (LabelRecord) record; curRow = thisRow = lrec.getRow(); thisColumn = lrec.getColumn(); value = lrec.getValue().trim(); value = value.equals("")?" ":value; this.rowlist.add(thisColumn, value); break; case LabelSSTRecord.sid: //单元格为字符串类型 LabelSSTRecord lsrec = (LabelSSTRecord) record; curRow = thisRow = lsrec.getRow(); thisColumn = lsrec.getColumn(); if (sstRecord == null) { rowlist.add(thisColumn, " "); } else { value = sstRecord .getString(lsrec.getSSTIndex()).toString().trim(); value = value.equals("")?" ":value; rowlist.add(thisColumn,value); } break; case NumberRecord.sid: //单元格为数字类型 NumberRecord numrec = (NumberRecord) record; curRow = thisRow = numrec.getRow(); thisColumn = numrec.getColumn(); value = formatListener.formatNumberDateCell(numrec).trim(); value = value.equals("")?" ":value; // 向容器加入列值 rowlist.add(thisColumn, value); break; default: break; } // 遇到新行的操作 if (thisRow != -1 && thisRow != lastRowNumber) { lastColumnNumber = -1; } // 空值的操作 if (record instanceof MissingCellDummyRecord) { MissingCellDummyRecord mc = (MissingCellDummyRecord) record; curRow = thisRow = mc.getRow(); thisColumn = mc.getColumn(); rowlist.add(thisColumn," "); } // 更新行和列的值 if (thisRow > -1) lastRowNumber = thisRow; if (thisColumn > -1) lastColumnNumber = thisColumn; // 行结束时的操作 if (record instanceof LastCellOfRowDummyRecord) { if (minColumns > 0) { // 列值重新置空 if (lastColumnNumber == -1) { lastColumnNumber = 0; } } lastColumnNumber = -1; // 每行结束时, 调用getRows() 方法 importDataServiceimpl importMbrServiceimpl = new importDataServiceimpl(); try { //importMbrServiceimpl.getRows(sheetIndex,curRow, rowlist); } catch (Exception e) { e.printStackTrace(); } // 清空容器 rowlist.clear(); } } }
package com.sjky.platform.myapp.importData.utils;
import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.springframework.web.multipart.MultipartFile; 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.DefaultHandler; import org.xml.sax.helpers.XMLReaderFactory;
import com.sjky.platform.myapp.importData.service.impl.importDataServiceimpl;
/**
*/ public class Excel2007Reader extends DefaultHandler { //共享字符串表 private SharedStringsTable sst; //上一次的内容 private String lastContents; //判断是否是String private boolean nextIsString; //记录行数 private int sheetIndex = -1; //每行结果集 private List rowlist = new ArrayList(); //整个Excel的集合 private List<List> excelList = new ArrayList<List>(); //判断是否是空单元格 private boolean cellNull; //当前行 private int curRow = 0; //当前列 private int curCol = 0; //有效数据矩形区域,A1:Y2 private String dimension; //根据dimension得出每行的数据长度 private int longest; //上个有内容的单元格id,判断空单元格 private String lastCellid; //处理单行数据的类 private importDataServiceimpl importService; private boolean isTElement; public void setImpotService(importDataServiceimpl importService){
this.importService = importService;
}
/**只遍历一个电子表格,其中sheetId为要遍历的sheet索引,从1开始,1-3
* @param filename
* @param sheetId
* @throws Exception
*/
public void processOneSheet(String filename,int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 遍历工作簿中所有的电子表格
* @param multipartFile
* @throws Exception
*/
public void process(MultipartFile multipartFile) throws Exception {
//或得文件流 InputStream in = multipartFile.getInputStream(); //获取实例对象 OPCPackage pkg = OPCPackage.open(in); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator sheets = r.getSheetsData(); while (sheets.hasNext()) { curRow = 0; sheetIndex++; InputStream sheet = sheets.next(); //查看转换的xml原始文件,方便理解后面解析时的处理, // 注意:如果打开注释,下面parse()就读不到流的内容了 // this.streamOut(in); InputSource sheetSource = new InputSource(sheet); //据说当执行这个方法时 自动触发 startElement(开始的元素) endElement(结束的元素) parser.parse(sheetSource); sheet.close(); } } //读取流,查看文件内容 public static void streamOut(InputStream in) throws Exception{ byte[] buf = new byte[1024]; int len; while ((len=in.read(buf))!=-1){ System.out.write(buf,0,len); } }
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
/*
name Excel转xml后的开始标签 这个方法 可以把参数都打出来看看
/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if (name.equals("dimension")){
dimension = attributes.getValue("ref");
longest = covertRowIdtoInt(dimension.substring(dimension.indexOf(":")+1) );
}
// c => 表示是不是单元格 row=>:开始处理某一行 isTextTag(name):单元格值
if ("c".equals(name)) {
//当前单元格的位置
String cellId = attributes.getValue("r");
//空单元判断,添加空字符到list
if (lastCellid!=null)
{
int gap = covertRowIdtoInt(cellId)-covertRowIdtoInt(lastCellid);
for(int i=0;i<gap-1;i++)
{
rowlist.add(curCol, "");
curCol++;
}
}else{
//第一个单元格可能不是在第一列
if (!"A1".equals(cellId))
{
for(int i=0;i<covertRowIdtoInt(cellId)-1;i++)
{
rowlist.add(curCol, "");
curCol++;
}
}
}
lastCellid = cellId;
// 如果下一个元素是 SST 的索引,则将nextIsString标记为true
//判断单元格的值是SST 的索引,不能直接characters方法取值
if (attributes.getValue("t")!=null && attributes.getValue("t").equals("s"))
{
nextIsString = true;
cellNull=false;
}else{
nextIsString = false;
cellNull=true;
}
}
//当元素为t时
if("t".equals(name)){
isTElement = true;
} else {
isTElement = false;
}
// 置空
lastContents = "";
}
public void endElement(String uri, String localName, String name)
throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString().trim();
} catch (Exception e) {
}
}
//t元素也包含字符串
if(isTElement){
String value = lastContents;
value = value.equals("")?" ":value;
rowlist.add(curCol, value);
curCol++;
isTElement = false;
// v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
} else if ("v".equals(name)) {
String value = lastContents;
value = value.equals("")?" ":value;
cellNull=false;
rowlist.add(curCol, value);
curCol++;
}
else if("c".equals(name) && cellNull == true){
rowlist.add(curCol, "");
curCol++;
cellNull = false;
}else {
//如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
if (name.equals("row")) {
//大于0的判断主要是筛除表头信息 这里具体看你想要的信息在哪一行开始
if(curRow>0){
//判断最后一个单元格是否在最后,补齐列数
if(covertRowIdtoInt(lastCellid)<longest){
for(int i=0;i<longest- covertRowIdtoInt(lastCellid);i++)
{
rowlist.add(curCol, "");
curCol++;
}
}
//将此行放入一个大集合
excelList.add(rowlist);
}
rowlist=new ArrayList();
curRow++;
curCol = 0;
}else if(name.equals("worksheet")){
//结束标签为worksheet说明工作簿sheet读取完成
//调用要处理的方法
importService.getRows(excelList);
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到单元格内容的值
lastContents += new String(ch, start, length);
}
/*
* 列号转数字 AB7-->28 第28列
* @param cellId
* @return
*/
public static int covertRowIdtoInt(String cellId){
int firstDigit = -1;
for (int c = 0; c < cellId.length(); ++c) {
if (Character.isDigit(cellId.charAt(c))) {
firstDigit = c;
break;
}
}
//AB7-->AB
//AB是列号, 7是行号
String newRowId = cellId.substring(0,firstDigit);
int num = 0;
int result = 0;
int length = newRowId.length();
for(int i = 0; i < length; i++) {
//先取最低位,B
char ch = newRowId.charAt(length - i - 1);
//B表示的十进制2,ascii码相减,以A的ascii码为基准,A表示1,B表示2
num = (int)(ch - 'A' + 1) ;
//列号转换相当于26进制数转10进制
num *= Math.pow(26, i);
result += num;
}
return result;
}
}
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。