问题描述
我有A S preadsheet我试图用POI读（我都XLS和XLSX格式），但在这种情况下，问题出在xls文件。我的小号preadsheet拥有约10,000行和75列，并在阅读它可能需要几分钟时间（尽管Excel中在几秒钟内打开）。我使用的是基于事件的阅读，而不是读整个文件到内存中。我的code的肉如下。这是一个有点杂乱现在，但它是被大多来自POI的例子复制的实际上只是一个长的switch语句。
这是典型的使用事件模型来这么慢POI的表现？有什么我加快这是做什么？我想，几分钟将是我的应用程序不能接受的。
POIFSFileSystem POIFS =新POIFSFileSystem（FIS）;
InputStream的嚣= poifs.createDocumentInputStream（练习册）;
尝试
{
HSSFRequest REQ =新HSSFRequest（）;
听众=新FormatTrackingHSSFListener（新HSSFListener（）{
@覆盖
公共无效processRecord（录音REC）
{
thisString = NULL;
INT SID = rec.getSid（）;
开关（SID）
{
案例SSTRecord.sid：
strTable =（SSTRecord）REC;
打破;
案例LabelSSTRecord.sid：
LabelSSTRecord labelSstRec =（LabelSSTRecord）REC;
thisString = strTable.getString（labelSstRec
.getSSTIndex（））的getString（）;
行= labelSstRec.getRow（）;
COL = labelSstRec.getColumn（）;
打破;
案例RKRecord.sid：
RKRecord RRK =（RKRecord）REC;
thisString =;
行= rrk.getRow（）;
COL = rrk.getColumn（）;
打破;
案例LabelRecord.sid：
LabelRecord l录制=（LabelRecord）REC;
thisString = lrec.getValue（）;
行= lrec.getRow（）;
COL = lrec.getColumn（）;
打破;
案例BlankRecord.sid：
BlankRecord blrec =（BlankRecord）REC;
thisString =;
行= blrec.getRow（）;
COL = blrec.getColumn（）;
打破;
案例BoolErrRecord.sid：
BoolErrRecord贝雷克=（BoolErrRecord）REC;
行= berec.getRow（）;
COL = berec.getColumn（）;
字节errVal = berec.getErrorValue（）;
thisString = errVal == 0？ Boolean.toString（贝雷克
.getBooleanValue（））：ErrorConstants
.getText（errVal）;
打破;
案例FormulaRecord.sid：
FormulaRecord FREC =（FormulaRecord）REC;
开关（frec.getCachedResultType（））
{
案例Cell.CELL_TYPE_NUMERIC：
双NUM = frec.getValue（）;
如果（Double.isNaN（NUM））
{
//公式的结果是一个字符串
//这是存储在下一个记录
outputNextStringRecord = TRUE;
}
其他
{
thisString = formatNumericValue（FREC，NUM）;
}
打破;
案例Cell.CELL_TYPE_BOOLEAN：
thisString = Boolean.toString（FREC
.getCachedBooleanValue（））;
打破;
案例Cell.CELL_TYPE_ERROR：
thisString = HSSFErrorConstants
.getText（frec.getCachedErrorValue（））;
打破;
案例Cell.CELL_TYPE_STRING：
outputNextStringRecord = TRUE;
打破;
}
行= frec.getRow（）;
COL = frec.getColumn（）;
打破;
案例StringRecord.sid：
如果（outputNextStringRecord）
{
//字符串为公式
StringRecord SREC =（StringRecord）REC;
thisString = srec.getString（）;
outputNextStringRecord = FALSE;
}
打破;
案例NumberRecord.sid：
NumberRecord numRec =（NumberRecord）REC;
行= numRec.getRow（）;
COL = numRec.getColumn（）;
thisString = formatNumericValue（numRec，numRec
.getValue（））;
打破;
案例NoteRecord.sid：
NoteRecord noteRec =（NoteRecord）REC;
行= noteRec.getRow（）;
COL = noteRec.getColumn（）;
thisString =;
打破;
案例EOFRecord.sid：
inSheet = FALSE;
}
如果（thisString！= NULL）
{
//做一些与单元格的值
}
}
}）;
req.addListenerForAllRecords（监听）;
HSSFEventFactory厂=新HSSFEventFactory（）;
factory.processEvents（REQ，DIN）;
我也做了一些处理与成千上万的大型Excel文件，在我看来POI是非常快的。加载一个Excel文件也tooks约1分钟，在Excel中本身。所以我想确认问题所在了POI code的
I have a spreadsheet I'm trying to read with POI (I have both xls and xlsx formats), but in this case, the problem is with the xls file. My spreadsheet has about 10,000 rows and 75 columns, and reading it in can take several minutes (though Excel opens in a few seconds). I'm using the event based reading, rather than reading the whole file into memory. The meat of my code is below. It's a bit messy right now, but it's really just a long switch statement that was mostly copied from the POI examples.
Is it typical for POI performance using the event model to be so slow? Is there anything I an do to speed this up? I think several minutes will be unacceptable for my application.
POIFSFileSystem poifs = new POIFSFileSystem(fis);
InputStream din = poifs.createDocumentInputStream("Workbook");
try
{
HSSFRequest req = new HSSFRequest();
listener = new FormatTrackingHSSFListener(new HSSFListener() {
@Override
public void processRecord(Record rec)
{
thisString = null;
int sid = rec.getSid();
switch (sid)
{
case SSTRecord.sid:
strTable = (SSTRecord) rec;
break;
case LabelSSTRecord.sid:
LabelSSTRecord labelSstRec = (LabelSSTRecord) rec;
thisString = strTable.getString(labelSstRec
.getSSTIndex()).getString();
row = labelSstRec.getRow();
col = labelSstRec.getColumn();
break;
case RKRecord.sid:
RKRecord rrk = (RKRecord) rec;
thisString = "";
row = rrk.getRow();
col = rrk.getColumn();
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) rec;
thisString = lrec.getValue();
row = lrec.getRow();
col = lrec.getColumn();
break;
case BlankRecord.sid:
BlankRecord blrec = (BlankRecord) rec;
thisString = "";
row = blrec.getRow();
col = blrec.getColumn();
break;
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) rec;
row = berec.getRow();
col = berec.getColumn();
byte errVal = berec.getErrorValue();
thisString = errVal == 0 ? Boolean.toString(berec
.getBooleanValue()) : ErrorConstants
.getText(errVal);
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord) rec;
switch (frec.getCachedResultType())
{
case Cell.CELL_TYPE_NUMERIC:
double num = frec.getValue();
if (Double.isNaN(num))
{
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true;
}
else
{
thisString = formatNumericValue(frec, num);
}
break;
case Cell.CELL_TYPE_BOOLEAN:
thisString = Boolean.toString(frec
.getCachedBooleanValue());
break;
case Cell.CELL_TYPE_ERROR:
thisString = HSSFErrorConstants
.getText(frec.getCachedErrorValue());
break;
case Cell.CELL_TYPE_STRING:
outputNextStringRecord = true;
break;
}
row = frec.getRow();
col = frec.getColumn();
break;
case StringRecord.sid:
if (outputNextStringRecord)
{
// String for formula
StringRecord srec = (StringRecord) rec;
thisString = srec.getString();
outputNextStringRecord = false;
}
break;
case NumberRecord.sid:
NumberRecord numRec = (NumberRecord) rec;
row = numRec.getRow();
col = numRec.getColumn();
thisString = formatNumericValue(numRec, numRec
.getValue());
break;
case NoteRecord.sid:
NoteRecord noteRec = (NoteRecord) rec;
row = noteRec.getRow();
col = noteRec.getColumn();
thisString = "";
break;
case EOFRecord.sid:
inSheet = false;
}
if (thisString != null)
{
// do something with the cell value
}
}
});
req.addListenerForAllRecords(listener);
HSSFEventFactory factory = new HSSFEventFactory();
factory.processEvents(req, din);
I did also some processing with thousands of large excel files and in my opinion POI is very fast. Loading that excel files tooks also about 1 minute in Excel itself. So i would confirm that the problem lies out of POI code
这篇关于Apache的POI的Java Excel中表现为大S preadsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！