Writing huge data to excel

[hofar...@houseoffusion.com]

Hi,

I am working on exporting data to excel with multiple sheets. The sheets
are dynamic and may range from 2 to more than 5. What is the best way to
handle this?

1) Use cfspreadsheet or APACHE POI? Do these support dynamic creation of
sheets? I have tried with cfspreadsheet but only the last sheet is
displayed in excel
2) Use AJAX? If yes, is there an example on how to achieve this?

Thanks.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358781

[hofar...@houseoffusion.com]

You really ought to be looking outside of CF for transfers of large
datasets, especially into Excel. Have you tried using SSIS (SQL Server
Integration Services) for this? It's really not that difficult to do and
it'll handle millions of rows easily...

--
Jeff

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358782

[hofar...@houseoffusion.com]

I’ve actually become quite fond of CF's Spreadsheet functions, though I prefer to use the script versions as I find them more flexible when dealing with large datasets, formatting content for reports and working with multiple sheets:

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec1fb12-7fd6.html

You can create your workbook and then use SpreadsheetWrite() to write each worksheet to the workbook. I think you’ll find that having a dynamic number of sheets in the workbook will be easier using script syntax.

HTH,
Jon

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358783

[hofar...@houseoffusion.com]

Thank you. I am not using SpreadSheetWrite function to write to a file. I
am storing all the sheets in a variable and using cfheader and cfcontent to
prompt the user to save or download. Do you think this is a preferred
option?


On Tue, Jul 1, 2014 at 2:17 PM, Jon Clausen <jon_c...@silowebworks.com>
wrote:

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358784

[hofar...@houseoffusion.com]

That’s really your call. The way you’re doing it is the “old-school” method and used to be the standard for spreadsheet delivery. With CF9+, I find working directly with the spreadsheet object allows me to avoid jumping through a bunch of hoops to sanitize the cell input - especially when dealing with internationalized data or formatted text that is meant to be exported, used for bulk edits and then re-imported.

I can also add header row formatting and formulas to calculate subtotals and totals. If you’re dealing only with numerics, the “old school” method is faster and as reliable (and would be even faster still if you used the database handle it directly (Jeff G. suggested SSIS for MSSQL and PostgreSQL and MySQL also have similar functionality).

-J

Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:358785