问题描述
我正在使用Java编写一个excel（.xls MS Excel 2003格式）文件。 excel输出文件可能包含大约200,000行，我打算分割多张表（64K行每张，由于excel限制）。
I am looking to write to an excel (.xls MS Excel 2003 format) file programatically using Java. The excel output files may contain ~200,000 rows which I plan to split over number of sheets (64k rows per sheet, due to the excel limit).
我已经尝试使用apache POI API，但由于API对象模型，它似乎是一个内存块。我被迫将内容中的工作簿对象添加单元格/表单，只有添加所有数据后，我可以将工作簿写入文件！以下是Apache使用API编写excel文件的示例：
I have tried using the apache POI APIs but it seems to be a memory hog due to the API object model. I am forced to add cells/sheets to the workbook object in memory and only once all data is added, I can write the workbook to a file! Here is a sample of how the apache recommends i write excel files using their API:
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
//Create a row and put some cells in it
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
显然，写入大约20k行（每列有大约10-20个列）给我可怕的java.lang.OutOfMemoryError：Java堆空间。
Clearly, writing ~20k rows(with some 10-20 columns in each row) gives me the dreaded "java.lang.OutOfMemoryError: Java heap space".
我尝试使用Xms和Xmx参数增加JVM初始堆叠和最大堆大小，如Xms512m和Xmx1024。仍然无法写入超过150k行的文件。
I have tried increasing JVM initial heapsize and max heap size using Xms and Xmx parameters as Xms512m and Xmx1024. Still cant write more than 150k rows to the file.
我正在寻找一种方法来流式传输到一个excel文件，而不是在写入内存之前构建内存中的整个文件这将有望节省大量的内存使用量。任何替代的API或解决方案将不胜感激，但我仅限于使用java。谢谢！ ：）
I am looking for a way to stream to an excel file instead of building the entire file in memory before writing it to disk which will hopefully save a lot of memory usage. Any alternative API or solutions would be appreciated, but I am restricted to usage of java. Thanks! :)
推荐答案
所有现有的Java API都会立即在RAM中构建整个文档。尝试编写符合新的xslx文件格式的XML文件。为了让您开始，我建议在Excel中以所需的形式构建一个小文件并保存。然后打开它并检查结构并更换所需的零件。
All existing Java APIs try to build the whole document in RAM at once. Try to write an XML file which conforms to the new xslx file format instead. To get you started, I suggest to build a small file in the desired form in Excel and save it. Then open it and examine the structure and replace the parts you want.
维基百科有一个。
这篇关于API使用java编写大量的excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！