So I have a program below which writes an excel file with multiple sheets based on inputs of sheet names, data, cell types, etc. It uses Apache POI, which is currently the only thing I found (correct me if I'm wrong about this) that can write excel 2007 files. The program works. I've tested it out on very small files (I will include the commented out main method that you can use to test this out yourself if you'd like, that is assuming you download apache poi and all it's required .jar files too, mostly bean stuff).
Now I'm trying to implement it, but obviously with a bit more data than when I tested it out. It runs out of memory it seems. Here's the stack trace below:
- Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
-
at org.apache.xmlbeans.impl.store.Xobj$ElementXobj.newNode(Xobj.java:2710)
-
at org.apache.xmlbeans.impl.store.Xobj.copyNode(Xobj.java:1099)
-
at org.apache.xmlbeans.impl.store.Xobj.copy_contents_from(Xobj.java:2319)
-
at org.apache.xmlbeans.impl.values.XmlObjectBase.setterHelper(XmlObjectBase.java:1907)
-
at org.apache.xmlbeans.impl.values.XmlObjectBase.set(XmlObjectBase.java:1954)
-
at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:1162)
-
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.setCArray(Unknown Source)
-
at org.apache.poi.xssf.usermodel.XSSFRow.onDocumentWrite(XSSFRow.java:392)
-
at org.apache.poi.xssf.usermodel.XSSFSheet.write(XSSFSheet.java:1736)
-
at org.apache.poi.xssf.usermodel.XSSFSheet.commit(XSSFSheet.java:1701)
-
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:171)
-
at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:173)
-
at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:199)
-
at Utils.Utilities.createExcelFile(Utilities.java:660)
-
at ReadAndSpitOutSpecificExcelData.makeExcelSheetWithAllTheControlGroupData(ReadAndSpitOutSpecificExcelData.java:400)
-
at ReadAndSpitOutSpecificExcelData.main(ReadAndSpitOutSpecificExcelData.java:150)
The error occurs (I'll point out where the error occurs when I post the code further below) at the line where I write "workbook.write(fileOut)" where fileOut is a FileOutputStream. This seems so odd to me. That implies that there's obviously enough memory for ALL of the java objects to store the excel file, but for some reason as it's writing to the hard disk it must grab a whole lot more memory.
Just to tell you, I have attempted to increase the java heap size up until 1 gig (by adding -Xms128m -Xmx1024m) but that still doesn't seem to do the trick. And the thing is, this file when it's finished will probably barely be 1 mb, maybe 2... So I just don't understand what's going on here.
Now, my solution to this problem unless someone can help me figure it out, will be to just write separate excel files and then combine the sheets manually. I assume that'll work, though I could be mistaken.
Anyway, here's the code below:
-
//The following method makes an excel file with a bunch of sheets, each sheet containing some data, sort of all like the
-
//method above but for multiple sheets
-
public static void createExcelFile(ArrayList<ArrayList<ArrayList>> allSheetsData, ArrayList<ArrayList<String>> allSheetsHeaders, int[][] allSheetsCellTypes, ArrayList<String> sheetNames,String filePath) throws IndexOutOfBoundsException {
-
if(allSheetsData.size() != allSheetsHeaders.size() && allSheetsHeaders.size() != allSheetsCellTypes.length && allSheetsCellTypes.length != sheetNames.size())
-
throw new IndexOutOfBoundsException("The number of sheets does not match the number of sheet names?");
-
-
ArrayList<ArrayList> data;
-
ArrayList<String> headers;
-
int[] cellTypes;
-
String sheetName;
-
-
File file = new File(filePath);
-
XSSFWorkbook workbook = new XSSFWorkbook();
-
XSSFSheet sheet;
-
XSSFRow currentRow = null;
-
XSSFCell currentCell = null;
-
-
try {
-
FileOutputStream fileOut = new FileOutputStream(file);
-
try {
-
for (int currentSheetNum = 0; currentSheetNum < allSheetsData.size(); currentSheetNum++) {
-
data = allSheetsData.get(currentSheetNum);
-
headers = allSheetsHeaders.get(currentSheetNum);
-
cellTypes = allSheetsCellTypes[currentSheetNum];
-
sheetName = sheetNames.get(currentSheetNum);
-
-
if(data.size() != headers.size() && headers.size() != cellTypes.length)
-
throw new IndexOutOfBoundsException("The number of data ArrayLists is not equal to the number of headers in sheet " + sheetName);
-
-
sheet = workbook.createSheet(sheetName);
-
-
currentRow = sheet.createRow(0);
-
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
-
currentCell = currentRow.createCell(cellNum, XSSFCell.CELL_TYPE_STRING);
-
currentCell.setCellValue(headers.get(cellNum));
-
}
-
-
for(int rowNum = 0; rowNum < getSizeOfLongestArrayList(data); rowNum++) {
-
currentRow = sheet.createRow(rowNum + 1);
-
-
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
-
if (rowNum < data.get(cellNum).size()) {
-
if(cellTypes[cellNum] == XSSFCell.CELL_TYPE_NUMERIC) {
-
currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
-
currentCell.setCellValue(((Double) data.get(cellNum).get(rowNum)).doubleValue());
-
} else if (cellTypes[cellNum] ==XSSFCell.CELL_TYPE_STRING) {
-
currentCell = currentRow.createCell(cellNum, cellTypes[cellNum]);
-
currentCell.setCellValue((String) data.get(cellNum).get(rowNum));
-
}
-
}
-
}
-
}
-
}
-
-
workbook.write(fileOut); //ERROR HAPPENS HERE!
-
} finally {
-
if (fileOut != null)
-
fileOut.close();
-
}
-
} catch (FileNotFoundException fnfex) {
-
fnfex.printStackTrace();
-
} catch (IOException ioex) {
-
ioex.printStackTrace();
-
}
-
}
-
And here's the main method I used to test it out:
-
// public static void main(String args[]) {
-
// ArrayList<ArrayList<ArrayList>> twoSheetsData = new ArrayList<ArrayList<ArrayList>>(2);
-
// ArrayList<ArrayList<String>> allSheetsHeaders = new ArrayList<ArrayList<String>>(2);
-
// int[][] allSheetsCellTypes = new int[2][];
-
// ArrayList<String> sheetNames = new ArrayList<String>(2);
-
// sheetNames.add("Sheet One");
-
// sheetNames.add("Sheet Two");
-
// String filePath = "C:/Documents and Settings/Asaf/Desktop/testExcelFile.xlsx";
-
//
-
// ArrayList<ArrayList> testListForSheet1 = new ArrayList<ArrayList>(3);
-
//
-
// ArrayList<String> stringList1ForSheet1 = new ArrayList<String>(4);
-
// stringList1ForSheet1.add("one");
-
// stringList1ForSheet1.add("two");
-
// stringList1ForSheet1.add("three");
-
// stringList1ForSheet1.add("four");
-
//
-
// ArrayList<Double> doubleListForSheet1 = new ArrayList<Double>(3);
-
// doubleListForSheet1.add(new Double(1));
-
// doubleListForSheet1.add(new Double(2));
-
// doubleListForSheet1.add(new Double(3));
-
//
-
// ArrayList<String> stringList2ForSheet1 = new ArrayList<String>(5);
-
// stringList2ForSheet1.add("second one");
-
// stringList2ForSheet1.add("second two");
-
// stringList2ForSheet1.add("second three");
-
// stringList2ForSheet1.add("second four");
-
// stringList2ForSheet1.add("second five");
-
//
-
// ArrayList<String> headerListForSheet1 = new ArrayList<String>(3);
-
// headerListForSheet1.add("First strings");
-
// headerListForSheet1.add("Doubles");
-
// headerListForSheet1.add("Second strings");
-
//
-
// testListForSheet1.add(stringList1ForSheet1);
-
// testListForSheet1.add(doubleListForSheet1);
-
// testListForSheet1.add(stringList2ForSheet1);
-
//
-
// int[] cellTypesForSheet1 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
-
//
-
// ArrayList<ArrayList> testListForSheet2 = new ArrayList<ArrayList>(3);
-
//
-
// ArrayList<String> stringList1ForSheet2 = new ArrayList<String>(4);
-
// stringList1ForSheet2.add("five");
-
// stringList1ForSheet2.add("six");
-
// stringList1ForSheet2.add("seven");
-
// stringList1ForSheet2.add("eight");
-
//
-
// ArrayList<Double> doubleListForSheet2 = new ArrayList<Double>(3);
-
// doubleListForSheet2.add(new Double(4));
-
// doubleListForSheet2.add(new Double(5));
-
// doubleListForSheet2.add(new Double(6));
-
//
-
// ArrayList<String> stringList2ForSheet2 = new ArrayList<String>(5);
-
// stringList2ForSheet2.add("second six");
-
// stringList2ForSheet2.add("second seven");
-
// stringList2ForSheet2.add("second eight");
-
// stringList2ForSheet2.add("second nine");
-
// stringList2ForSheet2.add("second ten");
-
//
-
// ArrayList<String> headerListForSheet2 = new ArrayList<String>(3);
-
// headerListForSheet2.add("First strings");
-
// headerListForSheet2.add("Doubles");
-
// headerListForSheet2.add("Second strings");
-
//
-
// testListForSheet2.add(stringList1ForSheet2);
-
// testListForSheet2.add(doubleListForSheet2);
-
// testListForSheet2.add(stringList2ForSheet2);
-
//
-
// int[] cellTypesForSheet2 = { XSSFCell.CELL_TYPE_STRING, XSSFCell.CELL_TYPE_NUMERIC, XSSFCell.CELL_TYPE_STRING };
-
//
-
// twoSheetsData.add(testListForSheet1);
-
// twoSheetsData.add(testListForSheet2);
-
//
-
// allSheetsHeaders.add(headerListForSheet1);
-
// allSheetsHeaders.add(headerListForSheet2);
-
//
-
// allSheetsCellTypes[0] = cellTypesForSheet1;
-
// allSheetsCellTypes[1] = cellTypesForSheet2;
-
//
-
// createExcelFile(twoSheetsData, allSheetsHeaders, allSheetsCellTypes, sheetNames, filePath);
-
// }
-
Anyway, if you can help me out with this and explain to me either what I'm doing wrong, or what is going on, I would appreciate it. Thank you very much for your time and help.
-blazed