tracker issue : CF-3035824

Problem:

Optimise and improve the performance of the excelAddRows() function.

The excelAddRows() function (using a query) is significantly slower than the <cfexcel action="write" query="x" /> tag usage - On the order of 24 times slower.

The <cfexcel action="write" /> tag easily writes up to 50,000 rows / 200,000 cells in about 15 seconds. The excelAddRows() function, using the same query, takes 6 minutes.

Given that nearly every excel file produced will need to be structured in some way, and that high-volume reporting is a high-value business requirement, the excelAddRows() function should be optimised if possible to provide similiar performance to the <cfexcel action="write" /> tag.

Discussed with code examples at thread:
https://prerelease.adobe.com/project/forum/post.html?cap=87529bda13744b3db718e841890b9240&forid={267a8bea-8d25-435c-a533-6c830dd686ca}&topid={62f37c71-6d2b-4054-9d9d-034a500c790f}&tp=1
Method:

From the originating forum post:

Q: What was the first test I performed after my successful Centaur install?
A: I made cfexcel write a 50,000 row, 200,000 cell spreadsheet using a query:


<cfset queryToShow = createQuery() />
<cfexcel action="write" query="queryToShow" filename="#expandPath(’.’)#\test1.xls"
overwrite="true" sheet="1" />


The good news is this is very fast. On my home PC this takes about 15 seconds with a cached query, which I think is pretty neat. I tested this since my company does a lot of high-volume Excel reporting on this scale (well, with more columns and more data in columns).

Of course, most reports are not simple query dumps but include structured outputs of some kind, even if it is only a title and the details of the report filter that was used. So I went about testing the use of ExcelAddRows() to add a query, thinking that I could decorate an excelInfo object on the fly with such format:


<cfset test2xls = excelNew("report") />
<cfset excelAddRows(test2xls,queryToShow,5,1) />
<cfexcel action="write" name="test2xls" overwrite="true"
filename="#expandPath(’.’)#\test2.xls" />


Unfortunately, this takes about 24 times longer for a cached query with the same amount of data.

Can one of the developer’s comment? Can the use of excelAddRows() be optimised to the speed that a straight cfexcel action="write" is?

On a broader note, I was wondering how it is envisaged that we would use the new excel features to produce formatted reports of a semi-decent size?

Using cfexcel action="write" would I be correct in assuming that once written, we would need to re-read the XLS and then manipulate the spreadsheet using the excel functions to add normal formatting?

I’ll try to do more performance testing on this tomorrow.

cheers
Result:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3035824

External Customer Info:
External Company:  
External Customer Name: Ben Davies
External Customer Email: 447227C844C7765A992015A8
External Test Config: 08/16/2008

Attachments:

Comments: