A couple of pre-sales questions

Aspose.Cells Product Family
1

Hi,

We are taking a look as Aspose.Cells and have a couple of questions. Sales directed me to post these here.

Memory
We
need to be able to output very large files - potentially 100’s of
thousands of rows X 100’s of columns (.xslx only). I’ve found some
forum posts on your site showing code that gets a SaveOptions object,
and then sets a LightCellsDataProvider. But looking at the JavaDoc, I
cannot find that interface. Can you point me towards demo code that
shows Aspose.Cells’ efficient use of memory when writing a large Excel
file?

My recent test with 7.0.1 shows that I use importObjectArray to create a little over 1M rows (with 12 columns) before I get a OOM exception, topping out at about 800MB. Ideally, I’d like to know if there’s a streaming interface with a negligible effect on runtime memory.

Templates
We
want to produce Excel template files, and then drop data from the
application onto a data sheet in that template, and
have presentation sheets reference that data for charts and so on. Can
Aspose.Cells write into a data sheet this way? Will it preserve
formatting and other settings (dynamic named ranges, etc)?

PDFs, CSVs
I’m glad to see that I can also produce PDF and CSV files as well…what’s the memory picture for these options?

Thanks in advance!

- David

2

Hi,


1) Well, in recent ported versions e.g v7.0.0 and v7.0.1, we excluded the LightCells APIs, we actually matched the Java version with .NET version (that does not have LightCells APIs). We will make them available again soon. By the way when you do create/write over IM rows, you need certain amount of memory for creating this huge file. I think you should extend memory for your JVM accordingly. For such memory issue, you might need lot of memory for these bigger processes. Make sure that you got sufficient memory allocated for the process (in the JVM) when you do the big task.
e.g
You may try some command line too. Following is the command line you may use on the Windows XP/7 command prompt to extend the JVM memory accordingly to complete the task while running the program:
e.g

java -Xms1024m -Xmx1024m MyTestProgram


2) Yes, Aspose.Cells for Java can import/export data from/to number of data sources, see the documents:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/importing-data-to-worksheets.html
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/exporting-data-from-worksheets.html
Also, you may directly put data to the cells of a worksheet manually, see the document:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/adding-data-to-cells.html

Also, the formatting and other objects/settings would be preserved if you want to add/update data / objects into a formatted worksheet of an existing Excel file. Moreover, you may format data into the cells, see the documents:
http://www.aspose.com/documentation/java-components/aspose.cells-for-java/working-with-data-formatting.html

3) We recently enhanced performance regarding PDF rendering and other enhancements, it should be efficient process when you convert to PDF or CSV formats. If you have any issue, let us know with sample code and template files.

Thank you.

3

Since the original post, I was able to create an example with 90,000 rows. When saving to PDF, memory consumption grows to 880MB (64-bit Windows 7), and flat-lines. It appears to be fighting for memory, and doesn’t write anything to the PDF file.

Please advise with a technique for generating large PDF files.

4

With the LightCells API, what can expect for memory consumption? We try to stay within 768MB, including the GUI and runtime data for the application. So I need to find a way to fit Excel and PDF output into a reasonable amount of memory, maybe 400MB, regardless of data output size.

If I were to produce a CSV and import that into a Worksheet, would you expect that to perform better?

My test to produce a PDF file from 90,000 rows fails, consuming all memory (up to 900+MB). Attached you’ll find source code and a template file for this test.

- David

5
<!–[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}

<![endif]–>

Hi,


Thanks for the sample project.

The LightCells API is mainly for saving cells data directly to resultant Excel file without building the complete cells model in the memory. Currently (using latest fix V7.0.1.1 (attached)) we only support LightCells API for saving XLSX files. For saving PDF, one possible solution may be that we save some intermediate data into temporary file system before creating the final Pdf file. But we need to make further investigation for this feature and we are afraid we cannot finish it very soon.

Thank you.

6

Hi Amjad,

Thanks for the attachment. I’ll give it a try.

I am less concerned about a PDF containing many data rows…that is not a real world case. What we are going to want to export as PDF is going to be the presentation sheets where we have charts of rolled up summary data over the many rows. So I think we are likely in good shape there. My tests yesterday proved that to be the case.

- David

7

Since I need to be able to call calculateFormulas() (since I need to output PDF’s of several sheets), the LightCells API is not going to work for me.

8

Hi,

Please let us know your results with the normal Apis (not lightcells). Please use the latest version:
Aspose.Cells for Java v7.0.1.1

In case your are getting performance problem, then please create a sample project and post here, we will try to optimize it.