这几天做excel导入数据.刚开始使用jxl导入03版的,因为数据量超过65536,03版装不下,所以就用poi来支持07版的.
直接网上copy了个代码过去,搞了2条数据测试下成功了.
结果在导入正式数据的时候,才20M的excel文件就内存溢出了.网上搜了下,这情况很常见,需要使用sax方式来解析才可以.
但是在使用中,历经磨难,所以记录下来,以增强记忆.
还有好几个问题没解决,也一起记录下来,看哪位帮忙解决.
代码总共三段. 1.解析.2.抽象接口.3.业务类
1.解析类,继承DefaultHandler
[java] view plain copy print?在CODE上查看代码片派生到我的代码片
/**
* 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析
* xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低
* 内存的耗费，特别使用于大数据量的文件。
*
*/
public class Excel2007Reader 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;
// 当前遍历的Excel单元格列索引
protected int thisColumnIndex = -1;
private String defaultStr = "";
private boolean listIsNull = true;
protected int total = 0; //总行数
private int totalCol = 0;//总列数
private String sheetName;
private IRowReader rowReader;
public void setRowReader(IRowReader rowReader){
this.rowReader = rowReader;
}
/**
* 根据sheetid 解析sheet
* @param stream
* @param sheetId
* @throws Exception
*/
public void processOneSheetByIndex(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# 查找sheet
InputStream sheet2 = r.getSheet("rId"+sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 根据sheet名称,解析sheet
* @param stream
* @param name
* @throws Exception
*/
public void process(InputStream stream) throws Exception {
OPCPackage pkg = OPCPackage.open(stream);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) r.getSheetsData();
while (iter.hasNext()) {
InputStream ist = iter.next();
sheetName = iter.getSheetName();
InputSource sheetSource = new InputSource(ist);
parser.parse(sheetSource);
ist.close();
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException {
XMLReader parser = XMLReaderFactory .createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if ("c".equals(name)) {
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
if ("s".equals(cellType)) {
nextIsString = true;
} else {
nextIsString = false;
}
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumnIndex = nameToColumn(r.substring(0, firstDigit)); ;
} else if (name.equals("row")) {
// 设置行号
if(listIsNull){
totalCol = getColumns(attributes.getValue("spans"));
listIsNull = false;
}
}else if (name.equals("dimension")){
//获得总计录数
String d = attributes.getValue("ref");
total = getNumber(d.substring(d.indexOf(":")+1,d.length()));
}
// 置空
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();
} catch (Exception e) {
// e.printStackTrace();
}
}
if (“v”.equals(name)) {
paddingNullCell();
String value = lastContents.trim();
value = value.equals(“”)?” “:value;
rowlist.add(curCol, value);
curCol++;
}else {
//如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
if (name.equals("row")) {
//对最后的空列做填充
for(int i=rowlist.size();i<totalCol;i++){
rowlist.add(i,defaultStr);
}
// optRows(sheetIndex,curRow,rowlist,total);
try {
rowReader.getRows(sheetIndex,curRow,rowlist,total,sheetName);
} catch (UIException e) {
throw new SAXException(e.getMessage());
}
rowlist.clear();
curRow++;
curCol = 0;
thisColumnIndex = 0;
}
}
}
private static int getNumber(String column) {
String c = column.toUpperCase().replaceAll("[A-Z]", "");
return Integer.parseInt(c);
}
public void characters(char[] ch, int start, int length)
throws SAXException {
//得到单元格内容的值
lastContents += new String(ch, start, length);
}
private static int getColumns(String spans) {
String number = spans.substring(spans.lastIndexOf(':') + 1,
spans.length());
return Integer.parseInt(number);
}
/**
* 空的单元个填充
*/
private void paddingNullCell() {
int index = curCol;
if(thisColumnIndex > index){
for(int i = index; i < thisColumnIndex; i++){
rowlist.add(curCol, "");
curCol++;
}
}
}
/**
* 从列名转换为列索引
* @param name
* @return
*/
private static int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
@SuppressWarnings("unused")
private void optRows(int sheetIndex,int curRow, List<String> rowlist, int total2) {
for (int i = 0; i < rowlist.size(); i++) {
System.out.print("'" + rowlist.get(i) + "',");
}
System.out.println("");
}
public static void main(String[] args) throws Exception {
long begintime,endtime;
Excel2007Reader howto = new Excel2007Reader();
howto.processOneSheetByIndex("E:/2012.xlsx",1);
}
}
2.接口
[java] view plain copy print?在CODE上查看代码片派生到我的代码片
public interface IRowReader {
public void getRows(int sheetIndex,int curRow, List rowlist, int total, String sheetName) throws UIException;
}
3.业务类
[java] view plain copy print?在CODE上查看代码片派生到我的代码片
public class ContractTempManagerImpl implements IRowReader {
//判断是07版则执行07版解析
public void importContract(UploadFile uploadFile) throws Exception{
String fileName = uploadFile.getFile().getOriginalFilename();
num=1;
long begintime,endtime;
//执行插入,1000条提交一次
begintime = new Date().getTime();
String type = fileName.substring(fileName.lastIndexOf('.')+1, fileName.length());
if(StringUtil.isNotBlank(type)&&type.equals("xlsx")){
Excel2007Reader rea = new Excel2007Reader();
rea.setRowReader(this);
rea.process(uploadFile.getFile().getInputStream());
}else{
//03版处理
}
endtime = new Date().getTime();
System.out.println("插入所需时间为 : " + (endtime - begintime)/1000 + "秒!");
}
/**
* 实现接口
* @param sheetIndex
* @param curRow 当前行
* @param rowlist 行结果
* @param total 总行数()
* @param sheetName sheet名称
* @throws UIException
*/
public void getRows(int sheetIndex, int curRow, List<String> rowlist,int total,String sheetName) throws UIException {
if(sheetName.equals("合同信息")){
this.saveContractTemp(sheetIndex,curRow,rowlist,total,sheetName);
}else if(sheetName.equals("合同对方")){
this.saveContractProviderTemp(sheetIndex,curRow,rowlist,total,sheetName);
}else{
throw new UIException("sheet名称有误,请核对!");
}
}
}
代码说明
1.解析里面的,大部分都是网上copy的,很多都不太懂.主要是要继承DefaultHandler, 和重写startElement,endElement.
然后在 endElement中调用业务类的操作.
这里说下我遇到的问题和解决方法及没有解决的:
1. 多个模版,每个模版多个sheet,解析类怎么公用.
解决: 在解析类中用了个接口的属性rowReader,endElement()调用rowReader.getRows(),然后业务类实现这个接口,就解决了多个模版复用代码的问题.
然后在getRows()中将sheet名称作为参数传递,则在同个业务类中根据sheet不同执行不同操作也解决.
实践中才更能理解面向接口编程的意义.
2.根据sheet名称解析指定sheet
解决: 网上没找到根据sheet名称解析的,都是根据id和全部解析.所以就用了全部解析,然后拿到sheet名称.
3.获取sheet的总行数.
解决:因为是用的批处理,业务类的list每2000条提交一次,最后一批可能不到2000,所以需要总行数用来判断是否最后一批,
这个很简单.代码中dimension类型,getNumber()方法.(这个方法没看太懂,不过无所谓了.)
4.空值的处理
解决:如果excel中某列为空,那么解析时候是读不到的,因为xml中根本就没有这列.(将excel后缀名修改为.zip,即可看xml文件)
网上copy了个方法paddingNullCell(). 应该是根据当前列号和上个列号做比较,来填充.
5.最后一列空值的处理
解决:上面说网上copy的方法解决了中间的空列,但是最后一列空的话却没有解决,所以我就自己写了个.
在startElement()中,第一行的时候,获得总列数(第一行是标题,以标题列数位置)
在endElement()中,行结束前,先看list的size是否是总列数,如果不是则补充
6.数字 3995.992, 读取到为 3995.9920000000002
未解决: 这个在编辑栏看到也是3995.992,并且设置很多小数位后,还是3995.9920000000000 ,没有出现2
在修改为zip查看xml文件中,xml显示为3995.9920000000002
很莫名其妙,设置格式为文本,并且双击然后让单元格前面出现文本标识那样,则xml中正常.
网上找了很多,也没找到解决办法. 例如截取,正则什么的, 都不适用.
最后只有用最笨的办法,手动一列一列的 分列为文本格式.大哭
扫一扫
4839
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
