- POIUtils类
- Excel2007Reader类
- 参考
记录web前端导入excel2007大型xlsx文件时出现的OOM错误，excel2007的Sheet是xml文件，采用处理xml的Sax ContentHandler后解决该问题。
POIUtils类前端上传的excel xlsx文件
package com.bs.common.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.springframework.web.multipart.MultipartFile;
import com.bs.core.po.Dbat2002;
public class POIUtils {
public static List excel2Record(MultipartFile file) throws Exception {
List list = null;
try {
//创建workbook对象
Excel2007Reader ered = new Excel2007Reader();
InputStream is = file.getInputStream();
OPCPackage opk = OPCPackage.open(is);
list = ered.processOneSheet(opk);
} catch (IOException e) {
e.printStackTrace();
}
return list;
}
}
Excel2007Reader类
Dbat2002s存放读取出的数据，业务处理主要放在startElement和endElement这两个方法中。
package com.bs.common.utils;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
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.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
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.bs.core.po.Dbat2002;
public class Excel2007Reader
{
public List processOneSheet(OPCPackage pkg) throws Exception {
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// 获得第一个sheet
InputStream sheet2 = r.getSheet("rId1");
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
return SheetHandler.dbat2002s;
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = (ContentHandler) new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
private static class SheetHandler extends DefaultHandler {
//存放读取的excel数据
private static List dbat2002s = new ArrayList();
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private Dbat2002 db;
//用来单元格记录是第几列数据
private int k;
private SheetHandler(SharedStringsTable sst) {
this.db = new Dbat2002();
this.sst = sst;
this.k = 1;
}
//元素开始时的handler
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
if(name.equals("c")) {
System.out.print(attributes.getValue("r") + " - ");
// 获取单元格类型
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
lastContents = "";
}
//元素结束时的handler
public void endElement(String uri, String localName, String name)
throws SAXException {
if(nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getItemAt(idx).toString()).toString();
nextIsString = false;
}
// v => 单元格内容
if(name.equals("v")) {
switch (k) {
case 1:
db.setP01(lastContents);
break;
case 2:
db.setP02(lastContents);
break;
case 3:
//excel存储的日期时间为double类型，需要转为date类型
double date = Double.parseDouble(lastContents);
db.setP03(double2Date(date));
break;
case 4:
db.setP04(Double.parseDouble(lastContents));
break;
case 5:
db.setP05(Double.parseDouble(lastContents));
break;
case 6:
db.setP06(Double.parseDouble(lastContents));
break;
case 7:
db.setP07(Double.parseDouble(lastContents));
break;
case 8:
db.setP08(Double.parseDouble(lastContents));
break;
case 9:
db.setP09(Double.parseDouble(lastContents));
break;
case 10:
db.setP10(Double.parseDouble(lastContents));
break;
case 11:
db.setP11(Double.parseDouble(lastContents));
break;
case 12:
db.setP12(lastContents);
break;
case 13:
db.setP13(lastContents);
break;
case 14:
db.setP14(Double.parseDouble(lastContents));
break;
case 15:
db.setP15(Double.parseDouble(lastContents));
break;
case 16:
db.setP16(lastContents);
break;
case 17:
db.setP17(Double.parseDouble(lastContents));
break;
case 18:
db.setP18(lastContents);
break;
case 19:
db.setP19(lastContents);
break;
case 20:
db.setP20(lastContents);
break;
}
k++;
}
//判断单元格为行末的操作
if(localName.equals("row")) {
dbat2002s.add(db);
db = new Dbat2002();
k = 1;
}
}
//读取元素间内容时的handler
public void characters(char[] ch, int start, int length)
throws SAXException {
lastContents += new String(ch, start, length);
}
//double类型转日期类型
public String double2Date(Double d){
String t;
Calendar base = Calendar.getInstance();
SimpleDateFormat outFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
base.set(1899, 11, 30, 0, 0, 0);
base.add(Calendar.DATE, d.intValue());
base.add(Calendar.MILLISECOND,(int)((d % 1) * 24 * 60 * 60 * 1000));
t = outFormat.format(base.getTime());
return t;
}
}
}
参考
- double转date方法引用此处
- SAX ContentHandler 解析超大的XML内容
- POI实现大数据EXCLE导入导出，解决内存溢出问题
