1、Excel2003,文件后缀名.xls。
1)创建Excel文档
使用poi中的 HSSF相关方法。实现接口HSSFListener中的方法processRecord,处理record。
HSSF生成excel文件:
public class CreateExcel {
public static String outputFile="E:\\test.xls";
public void createHSSF(){
//创建一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//创建一个sheet,带有excel名字的创建方式
//HSSFSheet sheet = workbook.createSheet("test.xls");
HSSFSheet sheet = workbook.createSheet();
//创建sheet中的一条记录,从0开始索引,
HSSFRow row = sheet.createRow((short)0);
//创建一条记录的第一个单元格,索引从0开始
HSSFCell cell = row.createCell(0);
//定义单元格的数据类型:
//数值类型:CELL_TYPE_NUMERIC = 0
//字符串类型:CELL_TYPE_STRING = 1
//公式计算后的结果:CELL_TYPE_FORMULA = 2
//空单元格:CELL_TYPE_BLANK = 3
//布尔类型:CELL_TYPE_BOOLEAN = 4
//错误类型:CELL_TYPE_ERROR = 5
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("Item1:");
//保存excel文件
try {
FileOutputStream fout = new FileOutputStream(outputFile);
workbook.write(fout);
fout.flush();
fout.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
这里可以设置单元格格式,颜色,字体等。
2)读取Excel文档数据
public class ReadExcel {
public static String filePath;
public ReadExcel(String filePath){
this.filePath = filePath;
}
public void readHSSF(){
try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(this.filePath));
//表的缺省名Sheet1,第一张表缺省索引0
//HSSFSheet sheet = workbook.getSheet("Sheet1");
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFRow row = sheet.getRow(0);
HSSFCell cell = row.getCell(0);
System.out.println("左上端单元格:类型/"+cell.getCellType()+",数据/"+cell.getStringCellValue());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
3)ExcelExtractor可以获取整个excel文件的内容,表示为字符串类型。
其他类型文件,word,visio,powerpint文件也有这种方式获取整个文件的内容,可以从jar中查找。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
以上是简单的创建,读写数据,即poi中的usermodel(用户模型)。
usermodel主要有org.apache.poi.hssf.usermodel和org.apache.poi.hssf.eventusermodel包实现。
usermodel包把Excel文件映射成我们熟悉的结构,诸如Workbook、Sheet、Row、Cell等,它把整个结构以一组对象的形式保存在内存之中。
如果读取大批量的数据,则需要使用 它的eventusermodel(事件-用户模型)模型,效率高:
eventusermodel的API只提供读取文件的功能,也就是说不能用这个API来修改文件。eventusermodel要求用户熟悉文件格式的底层结构,它的操作风格类似于XML的SAX API和AWT的事件模型,要掌握窍门才能用好。
读取文件
应用程序首先要注册期望处理的数据,eventusermodel将在遇到匹配的数据结构时回调应用程序注册的方法。使用eventusermodel最大的困难在于你必须熟悉Excel工作簿的内部结构。
在HSSF中,低层次的二进制结构称为记录(Record)。记录有不同的类型,每一种类型由org.apache.poi.hssf.record包中的一个Java类描述。例如,BOFRecord记录表示Workbook或Sheet区域的开始,RowRecord表示有一个行存在并保存其样式信息。
所有具有CellValueRecordInterface接口的记录表示Excel的单元格,包括NumericRecord、LabelSSTRecord和FormulaRecord(还有其他一些,其中部分已被弃置不用,部分用于优化处理,但一般而言,HSSF可以转换它们)。
1)使用HSSFEventFactory处理excel ,HSSFRequest作为参数,携带一些信息,fs是excel文件源信息。
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
factory.processWorkbookEvents(request, fs);
2)在HSSFEventFactory的processWorkbookEvents方法中,将文件源的根目录转换成inpuitstream
processWorkbookEvents(req, fs.getRoot());
InputStream in = dir.createDocumentInputStream("Workbook");
processEvents(req, in);
short userCode = 0;
RecordFactoryInputStream recordStream = new RecordFactoryInputStream(in, false);
for (;;)
{
Record r = recordStream.nextRecord();
if (r == null) { break; }
userCode = req.processRecord(r);//这是继承HSSFListener后实现的方法,内部逻辑由开发人员自己实现,不是直接调用HSSFListener的方法,通过HSSFRequest间接调用
if (userCode != 0) { break; }
}
return userCode;
HSSFRequest中的 processRecord 方法如下:
protected short processRecord(Record rec)
throws HSSFUserException
{
Object obj = this._records.get(Short.valueOf(rec.getSid()));
short userCode = 0;
if (obj != null)
{
List listeners = (List)obj;
for (int k = 0; k < listeners.size(); k++)
{
Object listenObj = listeners.get(k);
if ((listenObj instanceof AbortableHSSFListener))
{
AbortableHSSFListener listener = (AbortableHSSFListener)listenObj;
userCode = listener.abortableProcessRecord(rec);
if (userCode != 0) { break; }
}else{
HSSFListener listener = (HSSFListener)listenObj;
listener.processRecord(rec);
}
}
}
return userCode;
}
在processRecord 方法中,每次传入一个单元格的数据,用record记录,然后根据record中的不同类型,找到以更的开始和结束,这中间的数据作为一行。对于这一样的数据做何种操作,再有一个抽象方法决定,由具体操作数据的人员实现逻辑。
/**
* 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) {
// Create sub workbook if required
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener
.getStubHSSFWorkbook();
}
// Works by ordering the BSRs by the location of
// their BOFRecords, and then knowing that we
// process BOFRecords in byte offset order
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 = "";
break;
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
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()) + '"';
}
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;
// System.out.println(rowlist.size()+"/"+thisColumn+"/"+value);
// if("-9202074443".equals(value)){
// System.out.println("ddd");
// }
if(rowlist.size()==thisColumn)rowlist.add(thisColumn,value);
else {
rowlist.add(rowlist.size(),"");
rowlist.add(thisColumn,value);
}
// System.out.println(count++);
}
break;
case NoteRecord.sid:
NoteRecord nrec = (NoteRecord) record;
thisRow = nrec.getRow();
thisColumn = nrec.getColumn();
// TODO: Find object to match nrec.getShapeId()
thisStr = '"' + "(TODO)" + '"';
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
value = formatListener.formatNumberDateCell(numrec).trim();
value = value.equals("")?" ":value;
// Format
// System.out.println(rowlist.size()+"/"+thisColumn+"/"+value);
rowlist.add(thisColumn, value);
break;
case RKRecord.sid:
RKRecord rkrec = (RKRecord) record;
thisRow = rkrec.getRow();
thisColumn = rkrec.getColumn();
thisStr = '"' + "(TODO)" + '"';
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 (thisStr != null) {
if (thisColumn > 0) {
output.print(',');
}
output.print(thisStr);
}
// 更新行和列的值
if (thisRow > -1)
lastRowNumber = thisRow;
if (thisColumn > -1)
lastColumnNumber = thisColumn;
// 行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) {
if (minColumns > 0) {
// 列值重新置空
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
}
// 行结束时, 调用 optRows() 方法
lastColumnNumber = -1;
// optRows(sheetIndex,curRow, rowlist);
count++;
System.out.println("count="+count);
try {
optRows(count,rowlist);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// if(count!=1){
// if(rowlist.size()<83){
// String str1="";
// String str2="";
// String str3="";
// if(rowlist.size()>81){
// str1=rowlist.get(81);
// if(rowlist.size()>82)str2=rowlist.get(81);
// }
// ConnectDB.insert(str1,str2,str3);
// }else {
// System.out.println(rowlist.get(23));
// String[] sets=null;
// if(rowlist.get(23)!="") {
// sets = rowlist.get(23).split("/");
// ConnectDB.insertWebSet(sets[2]);
// }
//
System.out.println(rowlist.get(81)+"/"+rowlist.get(82)+"/"+rowlist.get(83));
ConnectDB.insert(rowlist.get(81),rowlist.get(82),rowlist.get(83));
// }
rowlist.clear();
// }
rowlist.clear();
}
}
2、在Excel2003文件中,每一个sheet页最多只能有65536行数据,所以多于这个数字就不能使用Excel2003,则选择Excel2007或以上版本。使用poi-ooxml的jar包中的eventusermodel模型 解决这个问题。
excel2007内存存储结构实际上是一个压缩后的XML文件包,采用SAX解析XML,其中引用了SharedStringsTable类,该类将读取XML中所有字串信息的索引到内存中,
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
然后parser开始解析
InputStream sheet2 = r.getSheet("rId"+sheetId);
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
遍历过程中,重写了DefaultHandler的两个接口startElement和endElement,分别辨识当前单元格是不是开始单元格和结束单元格。
/**
* 取得标签的开始元素
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if (name.equals("c")) {
// 如果下一个元素是 SST 的索引,则将nextIsString标记为true
// t 属性存在表示该单元格有值
String cellType = attributes.getValue("t");
// r 属性 该单元格在excel中的位置,如:A1
String cellCol = attributes.getValue("r");
//判断空单元格,在rowlist中添加""
//取得当前单元格 r属性中的字母 如:A1,则返回A
char[] colNames=getColName(cellCol);
if(colNames[1]==' '){
//列中字母个数为单个,例如A1,B1....
if(lastIndex<cellCol.charAt(0)-1){
//len 为前后两次取得的单元格之间的空单元格个数
int len=cellCol.charAt(0)-lastIndex-1;
//每个空单元格,对应在rowlist中添加一个""
for(int i=0;i<len;i++){
rowlist.add(curCol,"");
curCol++;
}
}
lastIndex=cellCol.charAt(0);
zFlag=true;
} else if(colNames[1]!=' '){
//列中字母个数为2个,例如AA1,AB1....
//A-Z之间的某个单元格到AA之间有空单元格,需在之间添加空项目,即在rowlist中添加""
//所在位置中有一个字母时,类似A1,B1,的位置(计算的相对位置)
int lenOne=0;
//所在位置中有一个字母时,类似A1,B1,距离AA1的位置(计算的相对位置)
int len1=0;
if(zFlag==true){
len1=lastIndex-65;
}else {
//len1,len2为上一次取得单元格和当前取得单元格之间的相对位置(计算的相对位置)
len1=(lastIndex2[0]-64)*26+lastIndex2[1]-65+lenOne;
}
//当前单元格(有2个字母时)的位置 (计算的相对位置)
int len2=(cellCol.charAt(0)-64)*26+cellCol.charAt(1)-65;
// 如果前一次取得单元格不是当前单元格的前一个位置,则在rowlist中添加相应个数的""
if(len1<len2-1){
//len 为前后两次取得的单元格之间的空单元格个数
int len=len2-len1-1;
//每个空单元格,对应在rowlist中添加一个""
for(int i=0;i<len;i++){
rowlist.add(curCol,"");
curCol++;
}
}
// lastIndex2 置为当前单元格
lastIndex2[0]=cellCol.charAt(0);
lastIndex2[1]=cellCol.charAt(1);
zFlag=false;
} else {}
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
cellNull = true;
} else if(cellType==null){
cellNull = true;
}else {
nextIsString = false;
cellNull = 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) {
}
}
// v => 单元格的值,如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
if (name.equals("v")||"t".equals(name)) {
String value = lastContents.trim();
value = value.equals("")?" ":value;
rowlist.add(curCol, value);
curCol++;
cellNull = false;
} else if("c".equals(name) && cellNull == true) {
rowlist.add(curCol, "");
curCol++;
cellNull = false;
} else if("c".equals(name)){
}else {
//如果标签名称为 row ,这说明已到行尾,调用 optRows() 方法
if (name.equals("row")) {
lastIndex='A'-1;
lastIndex2[0]='A';
lastIndex2[1]='@';
countRow++;
try {
optRows(countRow, rowlist) ;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// if(countRow!=1) ConnectDB.insertWebSet(rowlist.get(23));
// //记录条数
// System.out.println(countRow+":"+rowlist.get(23));
// //清空当前数据
rowlist.clear();
curRow++;
curCol = 0;
}
}
}