问题描述
我期待写一个Excel（.xls的MS Excel 2003中格式）文件编程方式使用Java。 Excel的输出文件可能包含这我打算拆分成张数（每片64K行，由于Excel的限制）〜200,000行。
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".
我曾尝试增加JVM初始堆大小和最大堆大小使用在XMS，XMX参数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或解决方案将是AP preciated，但我限制到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.
维基百科有对整体格式一个好文章。
Wikipedia has a good article about the overall format.
这篇关于API使用java写巨大的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！