Split ouptut to MULTIPLIE sheets in Excel.
I have some reports that have a requirement of being in Excel format. However, the returned datasets have the ability to be more than 65,000 records. Instead of creating multipe files, does anyone know of a way to format the data to automatically split this data to multiple sheets? ~| The annual ColdFusion User Conference is being held Sat 6/26 - Sun 6/27/04 8am-5pm in the Washington DC Area. http://www.houseoffusion.com/banners/view.cfm?bannerid=44 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183148 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
If you are using a fairly recent version of Excel, you can format your data as XML and Excel can read that in. Create a dummy file in Excel and export it as XML for the syntax. > I have some reports that have a requirement of being in Excel format. > However, the returned datasets have the ability to be more than 65,000 > records. Instead of creating multipe files, does anyone know of a way > to format the data to automatically split this data to multiple sheets? ~| Sams Teach Yourself Regular Expressions in 10 Minutes by Ben Forta http://www.houseoffusion.com/banners/view.cfm?bannerid=40 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183159 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
If you dont fear java, the jxl.jar (java excel api) is awesome. I have used it a couple of times, and I have used it with great success in cf http://www.andykhan.com/ On Tue, 02 Nov 2004 11:50:44 -0400, Jonathan Nickle <[EMAIL PROTECTED]> wrote: > I have some reports that have a requirement of being in Excel format. However, the > returned datasets have the ability to be more than 65,000 records. Instead of > creating multipe files, does anyone know of a way to format the data to > automatically split this data to multiple sheets? > > ~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://www.houseoffusion.com/banners/view.cfm?bannerid=36 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183170 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
Problem with the XML solution I just found was that the XML file that is created (named file.xls) is HUGE. 30,000 records (with only 12 columns of text) creates a 42MB file. When this file is then saved through Excel as an .xls workbook it is only 16MB. There is obviously some overhead in file size with the XML file. Not only is this huge, but Excel takes an extra long time to translate this file when opening (3 minutes on a very fast machine). In addition, when I tried to produce a file with 120,000 records (split into 3 sheets), it bombed out with the out of memory error. This makes sense after I saw what the file size is with ony 30,000 records. Arghhh!!! Next approach I am working on is to use the COM object and interface with Excel automatically... I will post the results if anyone replies to this. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183380 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
On Thu, 04 Nov 2004 12:59:51 -0400, Jonathan Nickle <[EMAIL PROTECTED]> wrote: > Problem with the XML solution I just found was that the XML file that is created > (named file.xls) is HUGE. 30,000 records (with only 12 columns of text) creates a > 42MB file. When this file is then saved through Excel as an .xls workbook it is > only 16MB. There is obviously some overhead in file size with the XML file. > > Not only is this huge, but Excel takes an extra long time to translate this file > when opening (3 minutes on a very fast machine). > > In addition, when I tried to produce a file with 120,000 records (split into 3 > sheets), it bombed out with the out of memory error. This makes sense after I saw > what the file size is with ony 30,000 records. Arghhh!!! > > Next approach I am working on is to use the COM object and interface with Excel > automatically... > Have you tried using the Apache POI project's stuff, which is a Java API to Microsoft file types? Check this blog out for Excel stuff. It's really slick: http://www.d-ross.org/index.cfm?objectid=9C65ED5A-508B-E116-6F4F7F38C6AE167C Regards, Dave. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183385 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
Dave, I assume from this that the site must be hosted on an Apache server?? Or is the Java in this case portable? ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183393 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
It's a separate collection of Java classes for doing the manipulation, totally independant of the Apache HTTP Server. There is a lot of java stuff that Apache deals with, from utility packages like POI or Commons, to the Tomcat JSP/Servlet container, to Struts (a J2EE Web app framework), the ant build tool (which is hella sweet), to templating engines like Turbine. There's a lot more that I've left out cheers, barneyb On Thu, 04 Nov 2004 14:04:02 -0400, Jonathan Nickle <[EMAIL PROTECTED]> wrote: > Dave, I assume from this that the site must be hosted on an Apache server?? Or is > the Java in this case portable? > > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ I currently have 0 GMail invites for the taking ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183394 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
On Thu, 04 Nov 2004 14:04:02 -0400, Jonathan Nickle <[EMAIL PROTECTED]> wrote: > Dave, I assume from this that the site must be hosted on an Apache server?? Or is > the Java in this case portable? > Following on what Barney said, the "Apache" name is just the name of the parent non-profit organization that supports various open-source initiatives, the HTTP server being one of them. The POI project is another on of these projects they support, completely independent of the web server. Regards, Dave. ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183396 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Split ouptut to MULTIPLIE sheets in Excel.
Thank you both for the replies... I think this may be the ticket for this problem. I have downloaded the files and am working on comping the java files required. Thanks again and I will post again if other issues come up. I sincerely appreciate your time and advise!! -Jon ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:183409 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
