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:
Expand|Select|Wrap|Line Numbers
- 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)
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:
Expand|Select|Wrap|Line Numbers
- //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();
- }
- }
Expand|Select|Wrap|Line Numbers
- // 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);
- // }
-blazed