If you are interested in the XML version, and you don't mind the warning message, and you don't need to support the really old versions of Excel, then go for it. You can do pretty much everything you would want to do, such as controlling cell size, frozen headers, setting page margins, etc. All you are doing is building up a string, so you can do this on the database server if you would prefer that. Building up display output isn't processor intensive though. You create it in the same way you create a regular HTML page, except that it is XML. If you need to support Mac users then make sure you test opening the file in Mac Excel since it seems to have a different XML parser than Windows Excel and a file that works on Windows might not work on a Mac.
Regarding POI, what you should be careful with is making copies of objects in memory versus reusing existing objects in memory. If part of your code is making copies and it is inside of a long loop, then you could see an exponential rise in memory usage due to the object you are copying over and over. POI has been around for a long time so there is a lot of information about it already on the Web. -Mike Chabot On Wed, Dec 3, 2008 at 8:53 AM, Craigsell <[EMAIL PROTECTED]> wrote: > Can you give me some insight into what to look for with string handling and > optimal POI code arrangement? We haven't done that much in this area. > > I am leaning heavily towards the XML solution. I use stored procedures in > Oracle to make queries. I'm pretty sure I can create the XML and zip it up > (to reduce size) inside the stored proc then send it back to the web server > as a blob for user delivery. This way I can get all the heavy lifting off > the web server. > > I did search the archives but what I found led me back to POI or xml since I > want cell formatting control I did find a thread which indicated COM > objects were slower than POI. > > I'll also take a look at using OOXML as suggested on Arehart -- hadn't > considered that. > > > ----- Original Message ----- > From: "Mike Chabot" <[EMAIL PROTECTED]> > To: "cf-talk" <cf-talk@houseoffusion.com> > Sent: Tuesday, December 02, 2008 10:24 PM > Subject: Re: Excel Workbook Creators > > >> Your POI code could be optimized for memory usage. POI has problems >> with memory leaks, but these problems are fixable with >> experimentation. String handling is a place to look for optimization. >> In my experience, a suboptimal arrangement of the POI code could be >> the difference between creating a file in less than a second and >> crashing the entire server. >> >> The XML format works well and I have used it successfully, but it >> isn't perfect. The main problem is that Excel 2007 throws up a warning >> dialog box when you try to open an Excel 2003 XML file saying that the >> content doesn't match the file extension. This is annoying and >> unacceptable to some people and I don't believe there is any fix while >> still retaining the Excel 2003 XML file format. The official fix >> involves a registry change on the computer of the person opening the >> file. This issue also applies for HTML opened in Excel. There is no >> denying that the XML format is substantially easier to work with than >> POI and doesn't have any RAM issues. There are other problems with >> that file format, but the Excel 2007 issue that is the most noticeable >> one. >> >> There are alternatives to POI that produce native Excel for you. >> Check out this page: >> http://www.carehart.org/cf411/ >> >> Also maybe check the latest FAQU. >> http://www.fusionauthority.com/quarterly/ >> >> If you haven't already done so, try searching the list archive to see >> if your specific question has already been answered. >> >> Good luck, >> Mike Chabot >> >> On Tue, Dec 2, 2008 at 10:49 PM, Barney Boisvert <[EMAIL PROTECTED]> >> wrote: >>> I do pretty much everything with the XML format anymore. Just send it >>> with an xls extension and you're set. It's a native workbook with full >>> control over everything, it's just not binary. Users will never know >>> the difference. >>> >>> cheers, >>> barneyb >>> >>> On 12/2/08, Craigsell <[EMAIL PROTECTED]> wrote: >>>> We currently have a CFMX7 application that uses Jakarta POI HSSF to >>>> create >>>> excel spreadsheets from a query. We initially chose POI because we >>>> wanted a >>>> higher degree of control over cell formatting (currency, date, etc) >>>> than >>>> what we could get in CFReport. The problem is that POI is eating >>>> memory. A >>>> two thousand row 24 column spreadsheet which saves out at 771kb >>>> takes over >>>> 120mb of memory during creation and is bringing our server down when >>>> we get >>>> multiple requests. >>>> >>>> We were looking for alternatives to POI. I found JExcel but I >>>> couldn't find >>>> any data to see if it was better on memory usage than POI. I >>>> suspect it >>>> won't be but.... I am also contemplating creating XML spreadsheets >>>> which I >>>> can send to the user zipped to compensate for the bigger size. I've >>>> done >>>> that before but I just would prefer the user to get an xls workbook >>>> rather >>>> than an xml one. >>>> >>>> Anyone have any experience with JExcel and how it compares to POI >>>> HSSF? Or >>>> another alternative I hadn't considered? >>>> >>>> Thanks! >> >> > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316199 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
