Re: Q re new CF9 Spreadsheet* functions
Den - Thanks for the info. I'd never heard of JExcelAPI but I've used Jasper on some previous Java-based contracts and, yes, it's great. Problem is that this client is on shared hosting (CrystalTech) so my ability to install 3rd-party libraries is essentially zero. Unless someone knows of a pure CF way to manipulate Excel files. :-) With the PDF idea already working, what I'd really prefer at this point is a better way to control the contents of those. The only way to do so on a stock CF9 install is through HTML wrapped in cfdocument (please anyone correct me if wrong) and that means using CSS which isn't well covered in the bundled HTML-to-PDF libraries. :-( Cheers, db ~| 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:344128 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
The Excel approach seemed like a winner but in the end, I had to ditch it. In the end, I hit what appears to be a well-known problem and a known limitation for cfspreadsheet. The current design of the spreadsheet functionality makes it hard (impossible?) to optimally share cell formats across cells and that makes Excel hit an internal limit. Excel then decides on its own which cells will not receive any formatting. I am setting the cell formats for a few hundred cells in the worksheet although there are only 4 or 5 truly unique formats. That works fine until I then create many similar sheets within the same workbook. When you open the XLS in Excel, you get an error message re Too many different cell formats. (see http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=82627 for Adobe's bug report and http://support.microsoft.com/kb/213904 for background info) My theory then is that every time you call spreadsheetFormatRow or spreadsheetFormatCell, the underlying library CF uses does not optimize how the format is applied. I experimented but couldn't see a way through the CF API to force it to be smarter. In the end, I had to back off to making a PDF which, with CF's PDF library CSS limitations meant no rotated text. In the meantime, a few notes to help those in the future: - when setting a format struct's rotation value, use a string for negative values, i.e. format.rotation = '-60' or you'll get an exception. - I could not get formats to work at all unless I set a cell value, even if to an empty string. This leads to another problem: - Setting cell values is SLOW. I looped through a grid of 20x16 cells, setting them to the empty string. That added generation time of 8 seconds. - Setting column widths and row heights was unreliable for me unless I did that after all cell formats were set. - It's unclear what the value is that is passed to spreadsheetSetRowHeight/ColumnWidth. The doc says it's in points. When I set it to X and then look at the resulting spreadsheet in Excel, Excel tells me a different value than X. db ~| 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:344063 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
There's also JExcelAPI for generating Excel files. Way nicer API than POI, and I've got a custom tag somewhere around here for using it, if you're interested. Really though, I'd recommend using JasperReports. It's an *awesome* reporting engine, and has a *kick ass* GUI report editor (that is cross platform, BTW). You can export in PDF, Excel, PPT, and about 10 other formats. Really just excellent stuff, and way, *way* better than the sorta hit-and-miss style editing required for many other solutions (like HTML = PDF). Just thought I'd toss those out there. Using JasperReports is great for offloading processing as well (if you're going to be generating a bunch of stuff). :Den -- Every existing thing is born without reason, prolongs itself out of weakness, and dies by chance. Jean-Paul Sartre On Fri, Apr 29, 2011 at 12:24 PM, Dave Burns wrote: The Excel approach seemed like a winner but in the end, I had to ditch it. In the end, I hit what appears to be a well-known problem and a known limitation for cfspreadsheet. The current design of the spreadsheet functionality makes it hard (impossible?) to optimally share cell formats across cells and that makes Excel hit an internal limit. Excel then decides on its own which cells will not receive any formatting. I am setting the cell formats for a few hundred cells in the worksheet although there are only 4 or 5 truly unique formats. That works fine until I then create many similar sheets within the same workbook. When you open the XLS in Excel, you get an error message re Too many different cell formats. (see http://cfbugs.adobe.com/cfbugreport/flexbugui/cfbugtracker/main.html#bugId=82627 for Adobe's bug report and http://support.microsoft.com/kb/213904 for background info) My theory then is that every time you call spreadsheetFormatRow or spreadsheetFormatCell, the underlying library CF uses does not optimize how the format is applied. I experimented but couldn't see a way through the CF API to force it to be smarter. In the end, I had to back off to making a PDF which, with CF's PDF library CSS limitations meant no rotated text. In the meantime, a few notes to help those in the future: - when setting a format struct's rotation value, use a string for negative values, i.e. format.rotation = '-60' or you'll get an exception. - I could not get formats to work at all unless I set a cell value, even if to an empty string. This leads to another problem: - Setting cell values is SLOW. I looped through a grid of 20x16 cells, setting them to the empty string. That added generation time of 8 seconds. - Setting column widths and row heights was unreliable for me unless I did that after all cell formats were set. - It's unclear what the value is that is passed to spreadsheetSetRowHeight/ColumnWidth. The doc says it's in points. When I set it to X and then look at the resulting spreadsheet in Excel, Excel tells me a different value than X. db ~| 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:344068 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
their requirements preclude me from going the old way of generating HTML and setting the MIME type to Excel). Are there additional requirements? Because the A4 page size and rotated text could probably be achieved with xml or the old html/mso schemas. ~| 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:344024 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
Leigh - Curious what you mean by 'acheived with xml'. What would I feed the XML into? I have made progress experimenting with the spreadsheet functions and for the most part, things work well including text rotation. I wish there were a way to set the default page size in Page Setup but I can leave that up to the operator if I have to. The main issue so far has been that things are a bit underdocumented and required some experimentation to get them to work but nothing insurmountable. When I'm done, I'll post some info back here in case it helps people searching in the future. ~| 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:344031 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
I meant you could use Excel's ability to interpret html/xml to do both of things (and more) if you preferred to stick with old approach. http://cfsilence.com/blog/client/index.cfm/2006/7/19/Creating-Multiple-Tabs-in-Excel-with-Coldfusion http://cfsilence.com/blog/client/index.cfm/2007/1/11/Excel-XML--DateFormat-UDF http://www.bennadel.com/blog/461-Creating-Microsoft-Excel-Documents-With-ColdFusion-And-XML.htm ~| 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:344033 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Q re new CF9 Spreadsheet* functions
A customer is asking me to generate an Excel spreadsheet and I've never used the new functions in CF9 (their requirements preclude me from going the old way of generating HTML and setting the MIME type to Excel). Two q's after I've done some research both with Google and Adobe's docs: - how does one set the paper size that I'd usually set in Excel's File - Page Setup dialog? It needs to be set to A4. I don't see this in the API. - the doc requires formatting some cells with rotated text. Has anyone already tried this and run into issues re compatibility, functionality, etc? ~| 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:344016 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Q re new CF9 Spreadsheet* functions
Alternatively, if someone knows how to do rotated text when generating a PDF, I'd love to learn how. ~| 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:344017 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
