给excel加表格边框线数据会更加准确，则不需要多做校验判断cell类型
本文查询了很多网上的资料综合在一块的 仅供参考
如何查看excel转换成xml文件 直接将excel文件后缀改为zip格式查看解压文件中的sheet1.xml即可
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>xerces</groupId>
<artifactId>xerces</artifactId>
<version>2.4.0</version>
</dependency>
package com.aa.aa.ebs.biz.entity.mysql;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.junit.Test;
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;
/**
* Excel超大数据读取，抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析
* xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低 内存的耗费，特别使用于大数据量的文件。
* @version 2014-9-2
*/
public class ExcelUtil extends DefaultHandler {
private ExcelRowRead rowReader;
public void setRowReader(ExcelRowRead rowReader) {
this.rowReader = rowReader;
}
//共享字符串表
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;
//前一个单元格的xy
private String preXy = "";
//当前单元格的xy
private String currXy = "";
//是否跳过了单元格
private boolean isSkipCeil = false;
private boolean isTElement;
//两个不为空的单元格之间隔了多少个空的单元格
private int flag = 0;
/**
* 遍历工作簿中所有的电子表格
* @param filename
* @throws Exception
*/
public void process(String filename) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
}
/**
* 只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3
* @param filename
* @param sheetId
* @throws Exception
*/
public void process(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();
}
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;
}
//与判断空单元格有关
isSkipCeil = false;
String cellXy = attributes.getValue("r");
currXy = cellXy;
char pre=0;
char curr=0;
if(!"".equals(preXy)) {
pre = preXy.replaceAll("[a-zA-Z]","").trim().charAt(0);
curr = currXy.replaceAll("[a-zA-Z]","").trim().charAt(0);
flag=covertRowIdtoInt(currXy)-covertRowIdtoInt(preXy);
}else {
flag=covertRowIdtoInt(currXy);//第一列为空格
}
if(flag != 0 && flag != 1 && pre==curr) {//保证同一行单元格之间空号计算
isSkipCeil = true;
}
preXy = cellXy;
}
//当元素为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();
} catch (Exception e) {
}
}
if(name.equals("c")) {
if(lastContents.length()==0) {
rowlist.add(curCol, "");
curCol++;
}
}
//t元素也包含字符串
if(isTElement){
String value = lastContents.trim();
rowlist.add(curCol, value);
curCol++;
isTElement = false;
} else if ("v".equals(name)) {
String value = lastContents.trim();
value = value.equals("")?" ":value;
//当某个单元格的数据为空时，其后边连续的单元格也可能为空
if(isSkipCeil == true) {
for(int i = 0; i < (flag-1); i++) {
rowlist.add(curCol + i, "");
}
curCol += (flag-1);
}
rowlist.add(curCol, value);
curCol++;
}else {
//如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
if (name.equals("row")) {
try {
rowReader.getRows(sheetIndex,curRow,rowlist);
} catch (Exception e) {
e.printStackTrace();
}
rowlist.clear();
curRow++;
curCol = 0;
preXy="";
}
}
}
public void characters(char[] ch, int start, int length)
throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
//System.out.println("-----"+lastContents);
}
/**
* 获取行数据回调
* @param sheetIndex
* @param curRow
* @param rowList
*/
// public abstract void getRows(int sheetIndex, int curRow, List<String> rowList);
//
// /**
// * 测试方法
// */
// public static void main(String[] args) throws Exception {
// String file = "/Users/wangyanan/Desktop/test 3.xlsx";
// getData(file,"#");
// }
//
//
// public static ArrayList<String> getData(String file,String place) {
// ArrayList<String> result =new ArrayList<String>();
// ExcelUtil reader = new ExcelUtil() {
// public void getRows(int sheetIndex, int curRow, List<String> rowList) {
System.out.println("Sheet:" + sheetIndex + ", Row:" + curRow + ", Data:" + rowList);
// StringBuffer sb=new StringBuffer();
// if(rowList.size()>0) {
// for(String s:rowList) {
// sb.append(s).append(place);
// }
// result.add(sb.toString());
// System.out.println(sb.toString());
// }
// }
// };
// try {
// reader.process(file, 1);
// } catch (Exception e) {
// e.printStackTrace();
// }
// System.out.println(result.size());
// return result;
// }
/**
*
*
* @param excel03
* @param excel07
* @param fileName
* @throws Exception
*/
public static void readExcel(ExcelRowRead reader, String fileName) throws Exception {
ExcelUtil exceXlsx = new ExcelUtil();
exceXlsx.setRowReader(reader);
exceXlsx.process(fileName);
}
public static int covertRowIdtoInt(String rowId){
int firstDigit = -1;
for (int c = 0; c < rowId.length(); ++c) {
if (Character.isDigit(rowId.charAt(c))) {
firstDigit = c;
break;
}
}
//AB7-->AB
//AB是列号, 7是行号
String newRowId = rowId.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;
}
public static void main(String[] args) throws Exception {
ExcelRowRead rowReader = new ExcelRowRead();
readExcel(rowReader,"/Users/haha/Desktop/shougong.xlsx");
}
}
public class ExcelRowRead {
ArrayList<String> result = new ArrayList<String>();
public void getRows(int sheetIndex, int curRow, List<String> rowList) {
StringBuffer sb=new StringBuffer();
if(rowList.size()>0) {
for(String s:rowList) {
sb.append(s).append(",");
}
String[] arrays=sb.toString().split(",");
if(0!=arrays.length) {
result.add(sb.toString());
}
System.out.println(sb.toString());
}
}
public ArrayList<String> getArrayList() {
return result;
}
}
679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
