我正在研究Apache poi并尝试在现有的excel文件中添加新工作表 .
我正在使用FileOutputStream将工作表添加到工作簿对象并将工作簿对象写入文件 .
FileOutputStream outputStream = null;
FileInputStream inputStream = null;
HSSFWorkbook workbook = null;
int nRows = 5000;
int nCols = 100;
try {
ArrayList<String> names = new ArrayList();
names.add("NewSheet1");
names.add("NewSheet2");
names.add("NewSheet3");
names.add("NewSheet4");
workbook = new HSSFWorkbook();
for (String name : names) {
HSSFSheet sheet = workbook.createSheet(name);
for (int rowCounter = 0; rowCounter < nRows; rowCounter++) {
HSSFRow row = sheet.createRow(rowCounter);
for (int colCounter = 0; colCounter < nCols; colCounter++) {
HSSFCell cell = row.createCell(colCounter);
cell.setCellValue("" + rowCounter + colCounter);
}
}
}
outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (outputStream != null) {
outputStream.flush();
outputStream.close();
outputStream = null;
}
if (workbook != null) {
workbook = null;
}
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
} catch (Exception e) {
e.getMessage();
}
}
但它正在抛出内存异常 .
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.util.Arrays.copyOf(Arrays.java:2245)
at java.util.Arrays.copyOf(Arrays.java:2219)
at java.util.ArrayList.grow(ArrayList.java:242)
at java.util.ArrayList.ensureExplicitCapacity(ArrayList.java:216)
at java.util.ArrayList.ensureCapacityInternal(ArrayList.java:208)
at java.util.ArrayList.add(ArrayList.java:440)
at org.apache.poi.hssf.usermodel.HSSFWorkbook$SheetRecordCollector.visitRecord(HSSFWorkbook.java:1200)
at org.apache.poi.hssf.record.aggregates.RecordAggregate$PositionTrackingVisitor.visitRecord(RecordAggregate.java:106)
at org.apache.poi.hssf.record.aggregates.RecordAggregate$PositionTrackingVisitor.visitRecord(RecordAggregate.java:106)
at org.apache.poi.hssf.record.aggregates.ValueRecordsAggregate.visitCellsForRow(ValueRecordsAggregate.java:254)
at org.apache.poi.hssf.record.aggregates.RowRecordsAggregate.visitContainedRecords(RowRecordsAggregate.java:269)
at org.apache.poi.hssf.model.Sheet.visitContainedRecords(Sheet.java:550)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1247)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1157)
at com.ExcelTrial.main(ExcelTrial.java:43)
所以,我尝试了其他方法 .
我正在尝试逐页编写excel文件,即通过在每个工作表的输入模式下打开来更新文件 .
已在我的磁盘中创建Excel文件 .
我只是按照以下步骤操作:
-
使用FileInputStream在输入中打开文件
-
使用此FileInputStream对象创建工作簿
-
创建工作表,编写数据
-
使用FileOutputStream写入文件
String excelFilePath = "C:\\TrialNew\\TrialNew.xls";
FileOutputStream outputStream = null;
FileInputStream inputStream = null;
HSSFWorkbook workbook = null;
int nRows = 5000;
int nCols = 100;
try {
ArrayList<String> names = new ArrayList();
names.add("NewSheet1");
names.add("NewSheet2");
names.add("NewSheet3");
names.add("NewSheet4");
for (String name : names) {
inputStream = new FileInputStream(new File(excelFilePath));
workbook = new HSSFWorkbook(inputStream);
HSSFSheet sheet = workbook.createSheet(name);
for (int rowCounter = 0; rowCounter < nRows; rowCounter++) {
HSSFRow row = sheet.createRow(rowCounter);
for (int colCounter = 0; colCounter < nCols; colCounter++) {
HSSFCell cell = row.createCell(colCounter);
cell.setCellValue("" + rowCounter + colCounter);
}
}
outputStream = new FileOutputStream(excelFilePath);
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
outputStream = null;
workbook = null;
inputStream.close();
inputStream = null;
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (outputStream != null) {
outputStream.flush();
outputStream.close();
outputStream = null;
}
if (workbook != null) {
workbook = null;
}
if (inputStream != null) {
inputStream.close();
inputStream = null;
}
} catch (Exception e) {
e.getMessage();
}
}
它仍然提供相同的例外 . 但我注意到的一件事是,当我尝试将新工作表(例如“Newsheet2”)添加到已经包含“NewSheet1”的文件时,它正在重写整个工作簿,而不是在最后编写新工作表 .
有没有办法在最后写一个新的表,而不是一次又一次地重写所有的表?这可能会解决我的问题 .
或者,如果有任何其他解决方案来避免此Java堆空间异常,建议是最受欢迎的 .
1 回答
虽然我不熟悉文件格式的细节,但我非常怀疑 . 可能有关于文件 Headers 部分中的工作表的信息,因此您不能只在文件末尾滑动新工作表并使其工作而不重写 Headers (因此也不是整个文件) .
如果您使用的是较新的Excel格式(Office 2007以上版本),则可以尝试使用
XSSFWorkBook
. 我知道它提供了更好的阅读性能,因此写作也可能更高效(即内存更少) .