java 操作 excel 2010

 前段时间，需要进行excel2010数据的解析。先是找组件，jxl不支持，然后时poi。poi倒是可以解析excel10（XSSF），但是数据量一大就会内存溢出（但是用的还是一个小破笔记本1000+行就会内存溢出）。这下傻眼了，没办法，只好到网上找资料了，发现没什么能用，最后在apache 官网上找到一个示例 XLSX2CSV（地址：http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java），对它进行了改写。

excel 从07开始其数据存储结构就发生了改变，现在是用多个xml进行存储的，可以直接用解压软件进行打开，就可以看到里面的组成（可以参考：http://blog.csdn.net/lupengcheng2025/article/details/5339998）。

好了，现在开始解析。

 

import java.io.IOException;
import java.io.InputStream;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
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;



/**
* 读取excel xlsx(07、10)
* @author dallas16
*
*/
public class ExcelAnalysisXLSX extends DefaultHandler {
/**
* excel样式
*/
private StylesTable stylesTable;
/**
* 好像
*/
private ReadOnlySharedStringsTable sharedStringsTable;
/**
* 单元格中的数据可能的数据类型
*
*/
enum xssfDataType {
BOOL,
ERROR,
FORMULA,
INLINESTR,
SSTINDEX,
NUMBER,
}
/**
* 存储某个cell的类型
*/
private xssfDataType nextDataType = xssfDataType.NUMBER;
/**
* 某个cell的值
*/
private String value = "";

private short formatIndex;
private String formatString;
private final DataFormatter formatter = new DataFormatter();

/**
* 存储某一行的数据
*/
private List<String> rowlist = new ArrayList<String>();
/**
* excel的sheet名称
*/
private String sheetName;
/**
* excel 的路径
*/
private String path;
/**
* 准备存放读取结果
*/
private List<List<String>> datas = new ArrayList<List<String>>();
/**
* 当前的数据时第几列
*/
private int thisColumn;
private int lastColumnNumber;

/**
* 匹配开头和结尾是否是数字
*/
private static Pattern p1 = Pattern.compile("^\\d.*\\d$");



/**
* 对单元格的数据进行处理，重写了DefaultHandler中characters方法（其实DefaultHandler中的所有方法都没有方法体），
* 这个方法在读取过程中会被自动调用
* @param ch
* @param start
* @param length
*/
@Override
public void characters(char[] ch, int start, int length)
throws SAXException {
value = value+new String(ch,start,length);
}

/**
* 在读取元素结束时的 处理，主要是判断是不是一个单元格结束，是不是一行结束，是的话进行相应的处理
* 是单元格则将数据（value）添加到rowlist的相应位置
* 是一行结束的话则将 rowlist添加到sheetVo中
* @param uri
* @param localName
* @param qName
*/
@Override
public void endElement(String uri, String localName, String qName)
throws SAXException {
if ("v".equals(qName)) {
endDeal();
} else if ("row".equals(qName)) {
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
this.dealData();
lastColumnNumber = -1;
}

}

/**
* 在读取元素开始时的 处理，
* @param uri
* @param localName
* @param qName
*/
@Override
public void startElement(String uri, String localName, String qName,
Attributes attributes) throws SAXException {
if ("inlineStr".equals(qName) || "v".equals(qName)) {
value = "";
}
else if ("c".equals(qName)) {
String r = attributes.getValue("r");
int firstDigit = -1;
int length = r.length();
for (int c = 0; c < length; ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
dealDataType(attributes);
}

}

/**
* 对指定的sheet进行处理
* @param styles
* @param strings
* @param sheetInputStream
* @throws IOException
* @throws ParserConfigurationException
* @throws SAXException
*/
public void processSheet(
StylesTable styles,
ReadOnlySharedStringsTable strings,
InputStream sheetInputStream)
throws IOException, ParserConfigurationException, SAXException {

InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
this.stylesTable = styles ;
this.sharedStringsTable = strings;
sheetParser.setContentHandler(this);
sheetParser.parse(sheetSource);
}

/**
*
* @param excelUtilBean.getPath() 需要读取的excel的路径
* @param excelUtilBean.getSheetName() 如果值不为空则按名称进行解析
* @param excelUtilBean.getSheetNumber() 如果值为空则按指定的顺序进行解析
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
@SuppressWarnings("unused")
public void process()
throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
OPCPackage pkg = OPCPackage.open(path);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
XSSFReader xssfReader = new XSSFReader(pkg);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
boolean flag = false;
while (iter.hasNext()) { // 做判断，看是否是自己需要解析的那一个sheet
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
if(iter.getSheetName().equals(sheetName)){
processSheet(styles, strings, stream);
flag = true;
break;
}
stream.close();
++index;
}
if(!flag ){
String errorInfo = "名为 ‘"+sheetName+"’ 的sheet不存在！";
throw new RuntimeException(errorInfo);
}
pkg = null;
strings = null;
xssfReader = null;
styles = null;
}

/**
* 结束元素时的处理，根据情况将数据添加到 rowlist中
*/
public void endDeal(){
String thisStr = null;
thisStr = dealData(value,thisStr); //对单元格的数据进行类型处理
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
for (int i = lastColumnNumber+1; i < thisColumn; ++i){
rowlist.add(" ");
}
rowlist.add(thisStr==null?"":thisStr);
if (thisColumn > -1){
lastColumnNumber = thisColumn;
}
}

/**
* 将数据添加到sheetVo中去，也是根据条件进行不同的处理
*/
public void dealData(){
if(rowlist != null && rowlist.size() != 0 ){
datas.add(rowlist);
rowlist = null;

}
if(rowlist == null){
rowlist = new ArrayList<String>();
}
}

/**
* 好像是计算当前是第几列
* @param name
* @return
*/
private int nameToColumn(String name) {
int column = -1;
int length = name.length();
for (int i = 0; i < length ; ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}

/**
* 对解析出来的数据进行类型处理
* @param value 单元格的值（这时候是一串数字）
* @param thisStr 一个空字符串
* @return
*/
@SuppressWarnings("deprecation")
public String dealData(String value,String thisStr){
switch (nextDataType) {//这几个的顺序不能随便交换，交换了很可能会导致数据错误
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA:
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());

thisStr = rtsi.toString() ;
rtsi = null;
break;
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = rtss.toString();
rtss = null;
}
catch (NumberFormatException ex) {
thisStr = value.toString();
}
break;
case NUMBER:
String n = value.toString();
if (this.formatString != null)
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex, this.formatString);
else
thisStr = n;
break;
default:
thisStr = " ";

break;
}

try {
Date date = new Date(thisStr);

} catch (Exception e) {

}

return thisStr;
}


/**
* 处理数据类型
* @param attributes
*/
public void dealDataType(Attributes attributes){
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");

if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
/**
* 将数据添加到rowlist中
* @param str 单元格的数据
* @param num 列号
*/
public void addRowlist(String str , int num){

if(rowlist.size() >= num){
rowlist.add(num, str==null?"":str);
} else {
int size = rowlist.size();
int newNum = num+1;
for(int i = size; i < newNum; i++){
rowlist.add("");
}
rowlist.add(num, str==null?"":str);
}
}

public String getSheetName() {
return sheetName;
}

public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}

public String getPath() {
return path;
}

public void setPath(String path) {
this.path = path;
}

public List<List<String>> getDatas() {
return datas;
}

public void setDatas(List<List<String>> datas) {
this.datas = datas;
}

public static void main(String[] args) throws Exception {

ExcelAnalysisXLSX excel = new ExcelAnalysisXLSX();
excel.setPath("F:/b.xlsx");
excel.setSheetName("Sheet1");
excel.process();
List<List<String>> datas = excel.getDatas();
for(List<String> data : datas){
System.out.println(data);
}
}

}


其中需要的jar包：poi相关jar、xmlBean-2.3.0.jar、dom4j.jar。

这样就可以解析了，我测试过的最大的是13000条数据（我系统中的解析10w条数据时没有问题的，但是做了批量处理），更大的数据就需要做批量处理了，因为数据就算能解析出来，但是list中已经装不下这么多了(当然这和电脑配置有关系的)。然后还需要考虑解析的性能问题，其中的日期类型处理那里的try{}catch{}的性能消耗相当大。还有做批量处理的时候一定要控制操作次数。

评论

2 楼 fangtinghua 2012-11-16  

空格问题怎么处理？

1 楼 qiyang199132 2012-03-23  

不错 呵呵。