Aspose.cells: append workbook contents to a file in a loop

Aspose.Cells Product Family
1
Hi I am trying to save workbook to a file using FileOutputStream, I am able to write to the file if it is done in one shot. Since i need to implement pagination, I wish to fetch the data and append workbook contents to existing file on filesystem recurringly. I went through documentation but couldnt find where to look for this specific criteria exactly.

Below is the pseudo code that i am using.


file = new File(pathToFile+workbook.getFileName());
fos = new FileOutputStream(file, true);
if (!file.exists()) {
file.createNewFile();
}

Loop begin{
workbook = getSvc().exportData(searchParamDto);
workbook.save(fos, FileFormatType.XLSX);
fos.flush();
}

fos.close();

Can anybody please point me in the right direction. Any help in this regard is deeply appreciated.

Thanks.



2

Hi,


Thanks for providing us some details.

Although I am not entirely certain about your actual needs but the following topics would help you to accomplish your task a bit. So, you should see the documents/articles and refer to the example codes and then try your scenario/case accordingly:
http://www.aspose.com/docs/display/cellsjava/Save+Entire+Workbook+into+Text+or+CSV+Format
http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Workbooks+into+a+Single+Workbook
http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Worksheets+into+a+Single+Worksheet

Hope, this helps a bit.

Thank you.

3

Hi Amjad,

Thanks for the reply. I wish to perform below action.

1> initialize blank outputWorkBook; (filename: output.xlsx)
2> initialize and create workbook1 with data populated on sheet1. (10 rows)

3> copy workbook1, to outputWorkBook and write to file on disk.
so output.xlsx file on disk now has 10 rows in sheet1.

4> initialize and create workbook2 with data populated on sheet1. (10 rows)

final step
5> append workbook2, to outputWorkBook sheet1 and append to file on disk.
so the same output.xlsx file on disk now has 20 rows in sheet1.

http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Worksheets+into+a+Single+Worksheet

I went through the links provided by you and tried the same examples. But those examples only deals with below scenario.

step1: I have workbook1, workbook2. with data populated in sheet1 each having 10 rows.
step2: I will read workbook1, copy to outputWorkBook sheet1.(dont write to file on disk)
step3: I will read workbook2, append to outputWorkBook sheet1.
step3: now write the contents of outputWorkBook to file on disk, so all 20 rows in sheet1 will be written to file on disk in one go.

Hope the difference is clear. Why I wish to have first approach is because I dont wish to keep outputWorkBook in memory until i read all the workbook that i have and write all records to file on disk at one go. If there are 100,000 records in each workbook that i read, the outputWorkBook keeps growing on everyloop and this might consume more memory as I have less system memory to work with.

So is there any option to take first approach mentioned.

4

Hi,


Well, the documents/articles which we asked you to refer to would only give you hints and guidance, so you could write your own code to accomplish your custom needs and tasks. See the sample code below for your reference (The code below covers your task to certain extent as you got to write your own code by yourself using Aspose.Cells APIs):
e.g
Sample code:

String dataDir = “f:\files\”;

String [] books = {“Workbook1.xlsx”, “Workbook2.xlsx”}; //Both files have one worksheet with 10 rows each in it.

int cnt=1;
for (String book: books)
{

Workbook workbook = new Workbook(book);

Workbook destWorkbook = new Workbook();

Worksheet destSheet = destWorkbook.getWorksheets().get(0);

for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
Worksheet sourceSheet = workbook.getWorksheets().get(i);

Range sourceRange = sourceSheet.getCells().getMaxDisplayRange();

Range destRange = destSheet.getCells().createRange(sourceRange.getFirstRow(),
sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

destRange.copy(sourceRange);

destWorkbook.save(dataDir + “out”+ cnt++ + “.xlsx”);;
}

}
//Now we have out1.xlsx and out2.xlsx files.

//Final step: appending workbooks contents, etc.:
//Your code goes here, please refer to the document to combine data of the worksheets into one:
//http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Worksheets+into+a+Single+Worksheet/


Hope, this helps you and you can write your own code accordingly now.

Thank you.
5

Hi Amjad,

Thanks for the quick response, the steps you have mentioned is exactly what I had tried earlier before the previous comment.

To explain it better, here below is the example code outside the loop , which works fine on first write, the file checks out (refer comments in the code). If you go through the code, it is as per the documentation you had directed me to earlier.

On second write the file goes corrupt. May be I am doing something wrong or missed something. Can you please point me in right direction.

fileName=“output.xlsx”;
file = new File(pathToFile+fileName);
file.createNewFile();
fos = new FileOutputStream(file, true);


Workbook destWorkbook = new Workbook(pathToFile+fileName);
Worksheet destSheet = destWorkbook.getWorksheets().get(0);


Workbook srcWorkbook = new Workbook(pathToFile+“Report1.xlsx”);
Worksheet sourceSheet = srcWorkbook.getWorksheets().get(0);
Range sourceRange = sourceSheet.getCells().getMaxDisplayRange();

//as we need to start from row 0 as this is first write to the disk
int TotalRowCount=0;
Range destRange = destSheet.getCells().createRange(sourceRange.getFirstRow() + TotalRowCount,sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

destRange.copy(sourceRange);
destWorkbook.save(fos, FileFormatType.XLSX);

//adding a break here, may be breakpoint or System.exit(0), the output file is written to disk and checks out with 10 records.

srcWorkbook = new Workbook(pathToFile+“Report2.xlsx”);
sourceSheet = srcWorkbook.getWorksheets().get(0);
sourceRange = sourceSheet.getCells().getMaxDisplayRange();


//as there are 12 rows in first file. need to start from 13th row
TotalRowCount=13;
destRange = destSheet.getCells().createRange(sourceRange.getFirstRow() + TotalRowCount,sourceRange.getFirstColumn(), sourceRange.getRowCount(), sourceRange.getColumnCount());

destRange.copy(sourceRange);
destWorkbook.save(fos, FileFormatType.XLSX);
fos.flush();
fos.close();
//now the file (double the size in KB), complains that it is unreadable.


I am facing this problem only on appending to existing file on disk. If i keep workbook in memory and write the content of both input files in one go it works just fine.

6

Sorry, I re read your comment and saw that your response was that we can write to two output files and then combine them in the end. So writing to same existing file is not feasible…?


If so I will take the process of writing to different temporary output files and combining them later with the workbook.combine(workbook2) option.

Thanks.
7
Hi,

vick_4444:
Sorry, I re read your comment and saw that your response was that we can write to two output files and then combine them in the end. So writing to same existing file is not feasible...?

Yes, your understanding is correct. I think writing to same stream file (in MS Excel file format(s)) again might not be appropriate and won't work here, so you got to use/write to some temp files (if possible) and combine its data at the end. I will further check and look into it too. And, in case, if I find something useful, I will share it with you here.

Thank you.