Aspose.Cells for Java 1.6 performance

Aspose.Cells Product Family
1

Hello, our target max data set would give us 200 columns, 200 rows for each of 20 sheets.

Every other column has complicated conditional formating rules.

We need to use Workbook Designer and Workbook to do our processing.

We have bumped our JVM up to 768 MB and still need more memory.

Are there any recommendations to reduce memory usage and to improve performance?

A small test of just 800 columns over 4 sheets resulted in a 25+MB file and about 5-10 minutes run time.



Thanks in advanced

2

Hi,

Could you post your template file and you codes?

3

Ok, working… on this, this will take a little while because some of the code is confidential and we need to sanitize it.



Is there a FAQ or a Wiki regarding performance tuning tips & techniques?

Is it faster to insert an object array vs going cell by cell?

Is there an autocalc or refresh that should be turned off before adding data?

Is there a way of treating empty strings as an empty cell?

thx.


4

Hi,

1.

2.It is same .

3.There is not an autocalc or refresh when adding data.

4.It is an empty cell when the cell value is null.

Is the problem about performance only caused by smart marker designer?

If yes,could you post the smart marker designer file first?

5

Hi,

Please try this fix.Performance is impoved and we are still working on it.

You can get the internal Workbook object by WorbookDesigner.getWorkbook method.

6

Thanks. Not sure where the performance issues are, could be that our
template is bloated with conditional formatting. We are cleaning it up
before posting.



We don’t think the issue is directly with smart markers, we use smart markers for about 600 cells per sheet.

However we do have to switch back and forth between Designer and
Workbook objects on a per worksheet basis, and the only way to do that
is through a filestream.



We use the workbook object to import up to 200,000 cells per worksheet.


Here is our code fragment:

FileInputStream fis = new FileInputStream(outputFileName);

WorkbookDesigner wd = new WorkbookDesigner();

wd.open(fis);

fis.close();

…

wd.setDataSource(“xyz”+sheet, q);

…

wd.process();



FileOutputStream fos = new FileOutputStream(outputFileName);

wd.save(fos);

wd = null;

fos.close();

…

Object[][][]
reportBody = new Object [numRequiredWorksheets] [totalRows] [100];

fis = new FileInputStream(outputFileName);

Workbook wb = new Workbook();

wb.open(fis);

fis.close();



for (int sheet=0; sheet<numRequiredWorksheets; sheet++) {

Worksheet ws = wb.getSheet(sheet);

int numVisibleColumns = cList.size();

Cells cells = ws.getCells();

cells.importObjectArray(reportBody[sheet], 7, 0);

}



fos = new FileOutputStream(outputFileName);

wb.save(fos);

fos.close();

7

Ok, just saw the patch right after I composed my last posting… will try in the morning.

8

Hi,

Please use "wb.save(outputFileName)" to replace the following codes :

fos = new FileOutputStream(outputFileName);
wb.save(fos);
fos.close();

It will reduce the run time and the size of consuming memory.

9

After using the latest patch, we were able to access the Workbook from the WorkbookDesigner object. However, the smart markers only seem to work on the first two worksheets of our workbook now. Is this a bug?

Thanks in advance

10

Hi,

It seems strange. Do the smarts in other worksheets still exist after processing?

11

Yes they do. Also, the other worksheets are all created using the WorkSheet.copy method. The smart markers are added to the copied worksheet programatically.

When WorkBookDesigner.process is called, only the smart markers on the static worksheets (from the original XLS file) are replaced. The other smart markers on the copied sheets still exist.

12

Hi,

It is caused by worksheet.copy. We have fixed this bug.

Please try this fix.

13

The smart markers on the sheets created by Worksheet.copy are able to display data, but now every worksheet displays the same data.

For example:

Worksheet 0:

- static (predefined in an XLS file)

- has a smart marker: Marker0

Worksheet 1:

- created via Worksheet.copy from worksheet 0

- has a smart marker: Marker1

----------------------------------------------------------------------

WorkbookDesigner.setDataSource("Marker0", array0);

WorkkbookDesigher.setDataSource("Marker1", array1);

results in BOTH Worksheet 0 AND Worksheet1 displaying the contents of array0.

14

Hi,

Please try this fix.

15

Thank you. It works correctly now.

16

Hi,

Does the problem about the performance still exist?

17

Problem Solved.

The advice about the streams proved very useful. Does this mean that it
is better to save the final output to a file, re-open the file and
stream it back to the browser? We’re re-testing.



The interfaces to let us go from Designer to Workbook eliminated a lot of I/O.



Overall, performance is much improved. Thank you!