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:
[code=java]Exception in thread "main" java.lang.OutOf MemoryError: Java heap space
at org.apache.xmlb eans.impl.store .Xobj$ElementXo bj.newNode(Xobj .java:2710)
at org.apache.xmlb eans.impl.store .Xobj.copyNode( Xobj.java:1099)
at org.apache.xmlb eans.impl.store .Xobj.copy_cont ents_from(Xobj. java:2319)
at org.apache.xmlb eans.impl.value s.XmlObjectBase .setterHelper(X mlObjectBase.ja va:1907)
at org.apache.xmlb eans.impl.value s.XmlObjectBase .set(XmlObjectB ase.java:1954)
at org.apache.xmlb eans.impl.value s.XmlComplexCon tentImpl.arrayS etterHelper(Xml ComplexContentI mpl.java:1162)
at org.openxmlform ats.schemas.spr eadsheetml.x200 6.main.impl.CTR owImpl.setCArra y(Unknown Source)
at org.apache.poi. xssf.usermodel. XSSFRow.onDocum entWrite(XSSFRo w.java:392)
at org.apache.poi. xssf.usermodel. XSSFSheet.write (XSSFSheet.java :1736)
at org.apache.poi. xssf.usermodel. XSSFSheet.commi t(XSSFSheet.jav a:1701)
at org.apache.poi. POIXMLDocumentP art.onSave(POIX MLDocumentPart. java:171)
at org.apache.poi. POIXMLDocumentP art.onSave(POIX MLDocumentPart. java:173)
at org.apache.poi. POIXMLDocument. write(POIXMLDoc ument.java:199)
at Utils.Utilities .createExcelFil e(Utilities.jav a:660)
at ReadAndSpitOutS pecificExcelDat a.makeExcelShee tWithAllTheCont rolGroupData(Re adAndSpitOutSpe cificExcelData. java:400)
at ReadAndSpitOutS pecificExcelDat a.main(ReadAndS pitOutSpecificE xcelData.java:1 50)[/code]
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 FileOutputStrea m. 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:
[code=java]
//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<Arra yList<ArrayList >> allSheetsData, ArrayList<Array List<String>> allSheetsHeader s, int[][] allSheetsCellTy pes, ArrayList<Strin g> sheetNames,Stri ng filePath) throws IndexOutOfBound sException {
if(allSheetsDat a.size() != allSheetsHeader s.size() && allSheetsHeader s.size() != allSheetsCellTy pes.length && allSheetsCellTy pes.length != sheetNames.size ())
throw new IndexOutOfBound sException("The number of sheets does not match the number of sheet names?");
ArrayList<Array List> data;
ArrayList<Strin g> headers;
int[] cellTypes;
String sheetName;
File file = new File(filePath);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
XSSFRow currentRow = null;
XSSFCell currentCell = null;
try {
FileOutputStrea m fileOut = new FileOutputStrea m(file);
try {
for (int currentSheetNum = 0; currentSheetNum < allSheetsData.s ize(); currentSheetNum ++) {
data = allSheetsData.g et(currentSheet Num);
headers = allSheetsHeader s.get(currentSh eetNum);
cellTypes = allSheetsCellTy pes[currentSheetNum];
sheetName = sheetNames.get( currentSheetNum );
if(data.size() != headers.size() && headers.size() != cellTypes.lengt h)
throw new IndexOutOfBound sException("The number of data ArrayLists is not equal to the number of headers in sheet " + sheetName);
sheet = workbook.create Sheet(sheetName );
currentRow = sheet.createRow (0);
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
currentCell = currentRow.crea teCell(cellNum, XSSFCell.CELL_T YPE_STRING);
currentCell.set CellValue(heade rs.get(cellNum) );
}
for(int rowNum = 0; rowNum < getSizeOfLonges tArrayList(data ); rowNum++) {
currentRow = sheet.createRow (rowNum + 1);
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
if (rowNum < data.get(cellNu m).size()) {
if(cellTypes[cellNum] == XSSFCell.CELL_T YPE_NUMERIC) {
currentCell = currentRow.crea teCell(cellNum, cellTypes[cellNum]);
currentCell.set CellValue(((Dou ble) data.get(cellNu m).get(rowNum)) .doubleValue()) ;
} else if (cellTypes[cellNum] ==XSSFCell.CELL _TYPE_STRING) {
currentCell = currentRow.crea teCell(cellNum, cellTypes[cellNum]);
currentCell.set CellValue((Stri ng) data.get(cellNu m).get(rowNum)) ;
}
}
}
}
}
workbook.write( fileOut); //ERROR HAPPENS HERE!
} finally {
if (fileOut != null)
fileOut.close() ;
}
} catch (FileNotFoundEx ception fnfex) {
fnfex.printStac kTrace();
} catch (IOException ioex) {
ioex.printStack Trace();
}
}
[/code]
And here's the main method I used to test it out:
[code=java]
// public static void main(String args[]) {
// ArrayList<Array List<ArrayList> > twoSheetsData = new ArrayList<Array List<ArrayList> >(2);
// ArrayList<Array List<String>> allSheetsHeader s = new ArrayList<Array List<String>>(2 );
// int[][] allSheetsCellTy pes = new int[2][];
// ArrayList<Strin g> sheetNames = new ArrayList<Strin g>(2);
// sheetNames.add( "Sheet One");
// sheetNames.add( "Sheet Two");
// String filePath = "C:/Documents and Settings/Asaf/Desktop/testExcelFile.x lsx";
//
// ArrayList<Array List> testListForShee t1 = new ArrayList<Array List>(3);
//
// ArrayList<Strin g> stringList1ForS heet1 = new ArrayList<Strin g>(4);
// stringList1ForS heet1.add("one" );
// stringList1ForS heet1.add("two" );
// stringList1ForS heet1.add("thre e");
// stringList1ForS heet1.add("four ");
//
// ArrayList<Doubl e> doubleListForSh eet1 = new ArrayList<Doubl e>(3);
// doubleListForSh eet1.add(new Double(1));
// doubleListForSh eet1.add(new Double(2));
// doubleListForSh eet1.add(new Double(3));
//
// ArrayList<Strin g> stringList2ForS heet1 = new ArrayList<Strin g>(5);
// stringList2ForS heet1.add("seco nd one");
// stringList2ForS heet1.add("seco nd two");
// stringList2ForS heet1.add("seco nd three");
// stringList2ForS heet1.add("seco nd four");
// stringList2ForS heet1.add("seco nd five");
//
// ArrayList<Strin g> headerListForSh eet1 = new ArrayList<Strin g>(3);
// headerListForSh eet1.add("First strings");
// headerListForSh eet1.add("Doubl es");
// headerListForSh eet1.add("Secon d strings");
//
// testListForShee t1.add(stringLi st1ForSheet1);
// testListForShee t1.add(doubleLi stForSheet1);
// testListForShee t1.add(stringLi st2ForSheet1);
//
// int[] cellTypesForShe et1 = { XSSFCell.CELL_T YPE_STRING, XSSFCell.CELL_T YPE_NUMERIC, XSSFCell.CELL_T YPE_STRING };
//
// ArrayList<Array List> testListForShee t2 = new ArrayList<Array List>(3);
//
// ArrayList<Strin g> stringList1ForS heet2 = new ArrayList<Strin g>(4);
// stringList1ForS heet2.add("five ");
// stringList1ForS heet2.add("six" );
// stringList1ForS heet2.add("seve n");
// stringList1ForS heet2.add("eigh t");
//
// ArrayList<Doubl e> doubleListForSh eet2 = new ArrayList<Doubl e>(3);
// doubleListForSh eet2.add(new Double(4));
// doubleListForSh eet2.add(new Double(5));
// doubleListForSh eet2.add(new Double(6));
//
// ArrayList<Strin g> stringList2ForS heet2 = new ArrayList<Strin g>(5);
// stringList2ForS heet2.add("seco nd six");
// stringList2ForS heet2.add("seco nd seven");
// stringList2ForS heet2.add("seco nd eight");
// stringList2ForS heet2.add("seco nd nine");
// stringList2ForS heet2.add("seco nd ten");
//
// ArrayList<Strin g> headerListForSh eet2 = new ArrayList<Strin g>(3);
// headerListForSh eet2.add("First strings");
// headerListForSh eet2.add("Doubl es");
// headerListForSh eet2.add("Secon d strings");
//
// testListForShee t2.add(stringLi st1ForSheet2);
// testListForShee t2.add(doubleLi stForSheet2);
// testListForShee t2.add(stringLi st2ForSheet2);
//
// int[] cellTypesForShe et2 = { XSSFCell.CELL_T YPE_STRING, XSSFCell.CELL_T YPE_NUMERIC, XSSFCell.CELL_T YPE_STRING };
//
// twoSheetsData.a dd(testListForS heet1);
// twoSheetsData.a dd(testListForS heet2);
//
// allSheetsHeader s.add(headerLis tForSheet1);
// allSheetsHeader s.add(headerLis tForSheet2);
//
// allSheetsCellTy pes[0] = cellTypesForShe et1;
// allSheetsCellTy pes[1] = cellTypesForShe et2;
//
// createExcelFile (twoSheetsData, allSheetsHeader s, allSheetsCellTy pes, sheetNames, filePath);
// }
[/code]
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
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:
[code=java]Exception in thread "main" java.lang.OutOf MemoryError: Java heap space
at org.apache.xmlb eans.impl.store .Xobj$ElementXo bj.newNode(Xobj .java:2710)
at org.apache.xmlb eans.impl.store .Xobj.copyNode( Xobj.java:1099)
at org.apache.xmlb eans.impl.store .Xobj.copy_cont ents_from(Xobj. java:2319)
at org.apache.xmlb eans.impl.value s.XmlObjectBase .setterHelper(X mlObjectBase.ja va:1907)
at org.apache.xmlb eans.impl.value s.XmlObjectBase .set(XmlObjectB ase.java:1954)
at org.apache.xmlb eans.impl.value s.XmlComplexCon tentImpl.arrayS etterHelper(Xml ComplexContentI mpl.java:1162)
at org.openxmlform ats.schemas.spr eadsheetml.x200 6.main.impl.CTR owImpl.setCArra y(Unknown Source)
at org.apache.poi. xssf.usermodel. XSSFRow.onDocum entWrite(XSSFRo w.java:392)
at org.apache.poi. xssf.usermodel. XSSFSheet.write (XSSFSheet.java :1736)
at org.apache.poi. xssf.usermodel. XSSFSheet.commi t(XSSFSheet.jav a:1701)
at org.apache.poi. POIXMLDocumentP art.onSave(POIX MLDocumentPart. java:171)
at org.apache.poi. POIXMLDocumentP art.onSave(POIX MLDocumentPart. java:173)
at org.apache.poi. POIXMLDocument. write(POIXMLDoc ument.java:199)
at Utils.Utilities .createExcelFil e(Utilities.jav a:660)
at ReadAndSpitOutS pecificExcelDat a.makeExcelShee tWithAllTheCont rolGroupData(Re adAndSpitOutSpe cificExcelData. java:400)
at ReadAndSpitOutS pecificExcelDat a.main(ReadAndS pitOutSpecificE xcelData.java:1 50)[/code]
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 FileOutputStrea m. 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:
[code=java]
//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<Arra yList<ArrayList >> allSheetsData, ArrayList<Array List<String>> allSheetsHeader s, int[][] allSheetsCellTy pes, ArrayList<Strin g> sheetNames,Stri ng filePath) throws IndexOutOfBound sException {
if(allSheetsDat a.size() != allSheetsHeader s.size() && allSheetsHeader s.size() != allSheetsCellTy pes.length && allSheetsCellTy pes.length != sheetNames.size ())
throw new IndexOutOfBound sException("The number of sheets does not match the number of sheet names?");
ArrayList<Array List> data;
ArrayList<Strin g> headers;
int[] cellTypes;
String sheetName;
File file = new File(filePath);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
XSSFRow currentRow = null;
XSSFCell currentCell = null;
try {
FileOutputStrea m fileOut = new FileOutputStrea m(file);
try {
for (int currentSheetNum = 0; currentSheetNum < allSheetsData.s ize(); currentSheetNum ++) {
data = allSheetsData.g et(currentSheet Num);
headers = allSheetsHeader s.get(currentSh eetNum);
cellTypes = allSheetsCellTy pes[currentSheetNum];
sheetName = sheetNames.get( currentSheetNum );
if(data.size() != headers.size() && headers.size() != cellTypes.lengt h)
throw new IndexOutOfBound sException("The number of data ArrayLists is not equal to the number of headers in sheet " + sheetName);
sheet = workbook.create Sheet(sheetName );
currentRow = sheet.createRow (0);
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
currentCell = currentRow.crea teCell(cellNum, XSSFCell.CELL_T YPE_STRING);
currentCell.set CellValue(heade rs.get(cellNum) );
}
for(int rowNum = 0; rowNum < getSizeOfLonges tArrayList(data ); rowNum++) {
currentRow = sheet.createRow (rowNum + 1);
for(int cellNum = 0; cellNum < headers.size(); cellNum++) {
if (rowNum < data.get(cellNu m).size()) {
if(cellTypes[cellNum] == XSSFCell.CELL_T YPE_NUMERIC) {
currentCell = currentRow.crea teCell(cellNum, cellTypes[cellNum]);
currentCell.set CellValue(((Dou ble) data.get(cellNu m).get(rowNum)) .doubleValue()) ;
} else if (cellTypes[cellNum] ==XSSFCell.CELL _TYPE_STRING) {
currentCell = currentRow.crea teCell(cellNum, cellTypes[cellNum]);
currentCell.set CellValue((Stri ng) data.get(cellNu m).get(rowNum)) ;
}
}
}
}
}
workbook.write( fileOut); //ERROR HAPPENS HERE!
} finally {
if (fileOut != null)
fileOut.close() ;
}
} catch (FileNotFoundEx ception fnfex) {
fnfex.printStac kTrace();
} catch (IOException ioex) {
ioex.printStack Trace();
}
}
[/code]
And here's the main method I used to test it out:
[code=java]
// public static void main(String args[]) {
// ArrayList<Array List<ArrayList> > twoSheetsData = new ArrayList<Array List<ArrayList> >(2);
// ArrayList<Array List<String>> allSheetsHeader s = new ArrayList<Array List<String>>(2 );
// int[][] allSheetsCellTy pes = new int[2][];
// ArrayList<Strin g> sheetNames = new ArrayList<Strin g>(2);
// sheetNames.add( "Sheet One");
// sheetNames.add( "Sheet Two");
// String filePath = "C:/Documents and Settings/Asaf/Desktop/testExcelFile.x lsx";
//
// ArrayList<Array List> testListForShee t1 = new ArrayList<Array List>(3);
//
// ArrayList<Strin g> stringList1ForS heet1 = new ArrayList<Strin g>(4);
// stringList1ForS heet1.add("one" );
// stringList1ForS heet1.add("two" );
// stringList1ForS heet1.add("thre e");
// stringList1ForS heet1.add("four ");
//
// ArrayList<Doubl e> doubleListForSh eet1 = new ArrayList<Doubl e>(3);
// doubleListForSh eet1.add(new Double(1));
// doubleListForSh eet1.add(new Double(2));
// doubleListForSh eet1.add(new Double(3));
//
// ArrayList<Strin g> stringList2ForS heet1 = new ArrayList<Strin g>(5);
// stringList2ForS heet1.add("seco nd one");
// stringList2ForS heet1.add("seco nd two");
// stringList2ForS heet1.add("seco nd three");
// stringList2ForS heet1.add("seco nd four");
// stringList2ForS heet1.add("seco nd five");
//
// ArrayList<Strin g> headerListForSh eet1 = new ArrayList<Strin g>(3);
// headerListForSh eet1.add("First strings");
// headerListForSh eet1.add("Doubl es");
// headerListForSh eet1.add("Secon d strings");
//
// testListForShee t1.add(stringLi st1ForSheet1);
// testListForShee t1.add(doubleLi stForSheet1);
// testListForShee t1.add(stringLi st2ForSheet1);
//
// int[] cellTypesForShe et1 = { XSSFCell.CELL_T YPE_STRING, XSSFCell.CELL_T YPE_NUMERIC, XSSFCell.CELL_T YPE_STRING };
//
// ArrayList<Array List> testListForShee t2 = new ArrayList<Array List>(3);
//
// ArrayList<Strin g> stringList1ForS heet2 = new ArrayList<Strin g>(4);
// stringList1ForS heet2.add("five ");
// stringList1ForS heet2.add("six" );
// stringList1ForS heet2.add("seve n");
// stringList1ForS heet2.add("eigh t");
//
// ArrayList<Doubl e> doubleListForSh eet2 = new ArrayList<Doubl e>(3);
// doubleListForSh eet2.add(new Double(4));
// doubleListForSh eet2.add(new Double(5));
// doubleListForSh eet2.add(new Double(6));
//
// ArrayList<Strin g> stringList2ForS heet2 = new ArrayList<Strin g>(5);
// stringList2ForS heet2.add("seco nd six");
// stringList2ForS heet2.add("seco nd seven");
// stringList2ForS heet2.add("seco nd eight");
// stringList2ForS heet2.add("seco nd nine");
// stringList2ForS heet2.add("seco nd ten");
//
// ArrayList<Strin g> headerListForSh eet2 = new ArrayList<Strin g>(3);
// headerListForSh eet2.add("First strings");
// headerListForSh eet2.add("Doubl es");
// headerListForSh eet2.add("Secon d strings");
//
// testListForShee t2.add(stringLi st1ForSheet2);
// testListForShee t2.add(doubleLi stForSheet2);
// testListForShee t2.add(stringLi st2ForSheet2);
//
// int[] cellTypesForShe et2 = { XSSFCell.CELL_T YPE_STRING, XSSFCell.CELL_T YPE_NUMERIC, XSSFCell.CELL_T YPE_STRING };
//
// twoSheetsData.a dd(testListForS heet1);
// twoSheetsData.a dd(testListForS heet2);
//
// allSheetsHeader s.add(headerLis tForSheet1);
// allSheetsHeader s.add(headerLis tForSheet2);
//
// allSheetsCellTy pes[0] = cellTypesForShe et1;
// allSheetsCellTy pes[1] = cellTypesForShe et2;
//
// createExcelFile (twoSheetsData, allSheetsHeader s, allSheetsCellTy pes, sheetNames, filePath);
// }
[/code]
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
Comment