gpt4 book ai didi

java - 大型电子表格的 Apache POI Java Excel 性能

转载 作者:搜寻专家 更新时间:2023-10-30 21:17:07 25 4
gpt4 key购买 nike

我有一个电子表格,我正在尝试使用 POI 阅读(我有 xls 和 xlsx 格式),但在这种情况下,问题出在 xls 文件上。我的电子表格大约有 10,000 行和 75 列,读取它可能需要几分钟时间(尽管 Excel 会在几秒钟内打开)。我正在使用基于事件的阅读,而不是将整个文件读入内存。我的代码的内容如下。现在有点乱,但它实际上只是一个很长的 switch 语句,大部分是从 POI 示例中复制的。

使用事件模型的 POI 性能是否典型地如此缓慢?我有什么办法可以加快速度吗?我认为几分钟对我的申请来说是 Not Acceptable 。

    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);

最佳答案

如果您使用 Apache POI 生成大型 excel 文件,请注意以下行:

sheet.autoSizeColumn((短) p);

因为这会降低性能。

关于java - 大型电子表格的 Apache POI Java Excel 性能,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5992536/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com