Add multiple sheets

Aspose.Cells Product Family
1

hi

how to add multiple sheets in loop

i am using this logic

public void addSheets(Workbook writeWorkbook ,Hashtable styleHashTable,Object eis_home,Object reportName)throws Exception
{
Workbook addworkBook1 = new Workbook();

FileInputStream fstream = new FileInputStream(eis_home+"/1008404_1589_2.csv");
addworkBook1.getOpenOptions().setEncoding(“UTF-8”);
addworkBook1.open(fstream,FileFormatType.CSV);
for(int index=1;index<3;index++)
{
Worksheet writeWorkSheetTwo=writeWorkbook.getWorksheets().addSheet();
//Copy the first sheet of the first book into second book.
writeWorkbook.getWorksheets().getSheet(index).copy(addworkBook1 .getWorksheets().getSheet(0));
Cells writeCells= writeWorkSheetTwo.getCells();
StyleFlag styleFlag = new StyleFlag();
styleFlag.setFontSize(true);
styleFlag.setNumberFormat(true);
int noOfCols=writeCells.getMaxDataColumn();
for(int i=0;i<=noOfCols;i++)
{
writeCells.getColumns().getColumn(i).applyStyle((Style)styleHashTable.get(String.valueOf(i)),styleFlag);
Style style=(Style)styleHashTable.get(String.valueOf(i));
}
writeWorkSheetTwo.setName(“Data”+index);
writeWorkSheetTwo.freezePanes(1,0,1,0);
}
}

i am getting outof memory error.
when i run in indivduals without loop (adding sheet)
then it’s running fine.

thanks & regards

kumar

2

Hi Kumar,


Please also attach your input file, that will help us to re-create the exception you are facing. Mean while we will look into your code.
Thanks for reporting
3

hi

we executed the program in different platforms

in eclipse it add 3 sheet.when i add 4 th sheet it will give out of memory error.
in oracle application server the loop will fail.

if i add the single sheet like


Worksheet w1=wk.getWorksheets().addSheet();
w1.getCells().copyCellRange(workbook.getWorksheets().getSheet(0).getCells(), 0, 0, 0, 0, workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow(), workbook.getWorksheets().getSheet(0).getCells().getMaxDataColumn());
w1.autoFitColumns();


Worksheet w2=wk.getWorksheets().addSheet();
w2.getCells().copyCellRange(workbook.getWorksheets().getSheet(0).getCells(), 0, 0, 0, 0, workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow(), workbook.getWorksheets().getSheet(0).getCells().getMaxDataColumn());
w2.autoFitColumns();


Worksheet w3=wk.getWorksheets().addSheet();
w3.getCells().copyCellRange(workbook.getWorksheets().getSheet(0).getCells(), 0, 0, 0, 0, workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow(), workbook.getWorksheets().getSheet(0).getCells().getMaxDataColumn());
w3.autoFitColumns();


Worksheet w1=wk.getWorksheets().addSheet();
w1.getCells().copyCellRange(workbook.getWorksheets().getSheet(0).getCells(), 0, 0, 0, 0, workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow(), workbook.getWorksheets().getSheet(0).getCells().getMaxDataColumn());
w1.autoFitColumns();


Worksheet w4=wk.getWorksheets().addSheet();
w4.getCells().copyCellRange(workbook.getWorksheets().getSheet(0).getCells(), 0, 0, 0, 0, workbook.getWorksheets().getSheet(0).getCells().getMaxDataRow(), workbook.getWorksheets().getSheet(0).getCells().getMaxDataColumn());
w4.autoFitColumns();

this work fine. if i add 5th sheet it will gives error.

*why loop will fails and how the above logic will executed.

“loop logic which is mentioned in main.zip is fialed in oracle application”.


1.we also executed the “lightcellsdataprovider” with excel 2003.
this will also fail if i add 4th sheet to workbook.

means excel2003 supports only 2,50,000 rows with 36 columns.


2.if we run excel2007 in eclipse it will work fine with “lightcellsdataprovider”. but takes more time.
the java heap size is 512 mb in eclipse.

3.if we run oracle application 11i the excel2007 the filesupport only 2,40,000.(copyCellRange)

we tested all the above cases with following options.
1.using file rename
2.using copyCellRange
3.using copyColumn
4.using copyRow

we get same thing.



please trace this file in both eclipse and oracle applications.
and give clear clarification on this.

thanks & regards
kumar

4

Hi,

Please see the following code. It adds 5 copies of source worksheet taken from source workbook into destination workbook.

Please see the source.xlsx used in this code and destination.xlsx generated by this code.

Java


String dirPath=“f:\downloads\”;


//Open the source workbook

Workbook sourceWorkbook=new Workbook();

sourceWorkbook.open(dirPath + “source.xlsx”);


//Get source sheet

Worksheet sourceSheet=sourceWorkbook.getWorksheets().getSheet(0);


//Create a new destination workbook and remove its

//only sheet

Workbook destWorkbook=new Workbook();

destWorkbook.getWorksheets().removeSheet(0);



//Now add 5 copies of source sheet into destination workbook

for(int i=0; i<5; i++)

{

Worksheet destSheet = destWorkbook.getWorksheets().addSheet();

destSheet.copy(sourceSheet);

}


//Write the destination workbook on disk

destWorkbook.save(dirPath + “destination.xlsx”, FileFormatType.XLSX);



5

hi

thanks for your response

try to test with 65,530 rows and 36 columns of data to add more than 4sheets to xls(2003 format).
please find the previous attached file. that file having 65,530 rows and 36 columns.

thanks & regards.
ranjith kumar


6

Hi,

Well, please attach your source file. I will test the code with it.

I can’t find your previously attached file.

7

hi please find the attached file

and test with different environments like windows,Linux
different servers like tomcat, ApacheServer(OracleApplications).

thanks & regards

Ranjith kumar

8

hi

any update on this ?


Thanks & Regards,

Ranjith

9

Hi,


Please test your scenario with our latest fix of Aspose.Cells JAVA v2.5.2.14 and share your feedback here.
Thanks
10
Hi,
For the memory issue, we did find that oc4j needs a bit more memory than tomcat to create one same excel file. But I am afraid this is caused by the difference of the application servers to manage memory and other resources and we cannot change it or make optimizations for special server. For the memory issue when creating large excel files, we will try to enhance our component, such as provide options for users to save data to temporary files first before creating the final resultant file. But it is a complicate task for us and I am afraid we cannot support it soon.
Thank you.