Lots of you have been asking for a print output to Excel (this has come up in discussions at user groups, in e-mail threads, and in the comments to my blog entry regarding RTF files). I’ve been giving this one some thought, and honestly, I don’t believe that Excel makes sense as a “print” format. I am not saying that I don’t see a need for programmatic Excel spreadsheet generation, I just don’t know how a spreadsheet would map to a printable output.
As I see it (and as I’ve been demonstrating for the past three weeks), there are two distinct needs when it comes to printing from within ColdFusion apps:
1) Printing web pages: essentially compensating for the pathetic printing support in most browsers, allowing for content (text, images, alignment, tables, etc.) to be printed so that pages and breaks and wrapping all work properly. Being able to create PDF, FlashPaper, or RTF versions of web pages makes lots of sense. The intent is to generate something that is printable, and those all are. But this type of printing makes absolutely no sense as an Excel spreadsheet, there is no way to map freeform content to Excel rows and cells in any way that would actually be useful or relevant.
2) Reporting: data driven template based reports that display data in a structured format, supporting bands, running calculations, charting, and more. Supporting the same print formats makes sense too. Unlike the printing scenario described above, the type of data presented in reports is a bit more structured and so it may indeed be a fit for Excel spreadsheets. But, I still don’t quite understand how it could all work. Spreadsheets are 2 dimensional data views (essentially data grids, with a possible 3rd dimension if tabs are used), and reports really are not grids at all. After all, if you were to indent a total in your report, what spreadsheet cell would you want it in? And if you used a sum function would you expect the value or an Excel sum function to be embedded? And if you used nested bands with headers and footers, what would those map to? What would rows line up to? There is way too much potential ambiguity here, and the reality of it would likely be that the generated spreadsheet would never be exactly what you intended. To generate accurate spreadsheet content you would need to either a) explicitly put content in specific cells (using APIs for explicit cell manipulation), or b) be able to generate a grid type view with enough control so that it may be intelligently mapped to cells. And honestly, both seem like a lot of trouble for not much upside. Especially as you can already use other techniques to generate Excel content, including generating CSV content, and generating HTML tables which Excel can process properly (mapping HTML table cells to Excel spreadsheet cells, once you have done the work of determining exactly what to place in each cell, Excel can handle the importing quite nicely).
I may be way off base here, but it seems that the use case for Excel is very different from that of PDF or even RTF. The latter are great as print formats, I buy that. But Excel is not a print format at all, it is a tool used for data analysis, and that may result in charting and reporting, but not necessarily so. Or, put differently, data is not sent to Excel merely to be able to print it, that would make little sense. Data is sent to Excel to be manipulated, to perform calculations, to generate charts, and more. This may ultimately be printed, but the primary objective is the data manipulation that Excel offers, not the fact that it can print.
As such, I think that if ColdFusion were to generate Excel content then the interface to it should not be printing or reporting at all. It sounds like users want an “export” feature, a way to take a database query and export it to some other useful format, like Excel (or even XML). I can see a tag like this:
Or maybe functions like:#QueryToCSV(myQuery)#
#QueryToXML(myQuery)#
Both interfaces make sense (and have pros and cons), and both would allow you to simply generate Excel content (perhaps with column names as the top row, and then data in specific cells). Maybe we could even allow additional functionality to better manipulate specific cells (to set values, add formatting or functions, and more), we’d need to think that one through in more detail.
The more I think of it, the more I agree that Excel spreadsheet generation makes a lot of sense, and the more I feel that the use case and usage patterns require a slightly different way of thinking.
Your thoughts?
Pondering Output to Excel
25 responses to “Pondering Output to Excel”
-
and, if you want to add formatting/features specific to excel, you can use something like the apache project’s POI (hssf) library. I’ve had good results using it from CFMX.
-
I absolutely agree with you here. Unfortunately, I confused generating Excel files and using Excel files for print output.
The ability to generate/save "stuff" as Excel in an easy way would be great. -
Ben, I don’t like my users doing things out of what I build them, but they do. My clients like spreadsheets full of data that they can use outside of the systems that we build for them. Some import it into other systems, some use it for reports, some just like having it.
I don’t like it, but that’s what they want. They also want mailing labels, name tags, etc. Which is perfect for RTF.
PDF is nice for everything else. -
I agree that Export to Excel is geared towards data analysis rather than presentation. Ideally I’d like to be able to export the data into a range (named or defined) on a specific sheet within a pre-existing workbook. That way you can create pivot tables, VLOOKUPs and such like and just replace the data. Certainly the ability to target a sheet (or create a new one) within a workbook is very important (without it what’s the point?)
Shame on you no 3D capability! We have Excel here that slices and dices the data like an epileptic sushi chef. My current favourite has 42 sheets for man hours analysis. I’d like to add I don’t do any Excel development just an odd data dump to workbooks the finance bods here build.
I keep trying to convince them that OLAP is the way to go but what do I know about it 🙁 -
Why not using for ex. <cfsavecontent>?
Try to reuse as much tags as possible, or else you get much like a tagsoup. -
I have done some limited CF to Excel spreadsheet generation after reading "Excel’s Web Query" in the Dec2001 version of CFDev Journal . Is a hidden feature of Excel not very well publicied but helps start things off. From memory I did manage to get simple SUM commands to export to excel, and colours etc, was kind of through trial error more than anything else.
http://www.sys-con.com/magazine/?issueid=56&src=false
don’t know if this could help anyone ?
Cheers
Andrew -
I agree that output to excel is unrelated to the print format topic of pdf, rtf, and flashpaper.
Excel tends to be used for data manipulation, the other formats are for presentation.
In your list of functions, we would also benefit from CSVToQuery and XMLToQuery, wouldn’t we?
– Calvin -
In generating content to an Excel spreadsheet, will it be possible to have the output stored in different worksheets (tabs) within the same workbook? This is a feature that our users have asked for. Some of the custom tags that offer this capability require that Excel be installed on the Web server and that’s not allowed in our case.
-
I think if you want to work with Excel you might as well use ODBC. I found it pretty easy to write SQL that could read and update Excel spreadsheets. If anyone is interested I can send you an example CFMX tag. mcollins@macromedia.com
-
There are two big things Excel offers that others solutions don’t. First, you can use Excel to design a template which has can be formatted for viewing and printing. Once you have a template it is really easy to just fill in the data –especially if you use my custom tag. Second, Excel allows you to not only place data in cells, but also formulas. This is a really powerful feature that allows end users to get reports that can also be used for "what if" scenarios by changing specific cells and watching the others update.
-
I wrote a little how-to on POI-HSSF for some of you wondering "can I do this", "can I do that":
http://www.medlogs.com/dave/001848.html -
We all know ColdFusion can generate Excel with CFHEADER and CFCONTENT, but rather then outputting html tables, xml can be used –
<cfsetting showdebugoutput="no">
<CFHEADER NAME="Content-Disposition" VALUE="filename=file.xls">
<cfcontent type="application/msexcel"><?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
Then sophisticated formatting like cell rotation can be used:
<Style ss:ID="s21"><Alignment ss:Vertical="Bottom" ss:Rotate="90"/></Style>
<Cell ss:StyleID="s21"><Data ss:Type="String">Heading</Data></Cell>
And page layout can be used:
<PageSetup>
<Layout x:Orientation="Landscape"/>
</PageSetup>
<Selected/>
<DoNotDisplayGridlines/>
<FreezePanes/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
To try this for yourself save an excel file as xml and import it into a CFML template.
Hope you find this useful. -
Hien Nguyen asks: “will it be possible to have the output stored in different worksheets (tabs) within the same workbook?â€
You can already do this. If you create the excel file using xml as I describe above then you can use multiple worksheets:
<Worksheet ss:Name="<cfoutput>#variables.firstsheet#</cfoutput>">
Your data here.
</Worksheet>
<Worksheet ss:Name="<cfoutput>#variables.secondsheet#</cfoutput>">
Your data here.
</Worksheet> -
For years we have been providing reports in both excel and print format via the same html.
Our users are used to Excel and like to have data in it as they are used to it. I disagree slightly with Ben in that Excel can be used to produce very nice print outs, however, this is best done by the user setting things up than the server/programer in my opinion. Nice printing can be achieved by a combination of html/css/pdf IMO.
Some formulas can be done via html (sum, for instance) but better support for that and tabs would be nice to haves.
If I was spending Tim’s $100 on pritning options I’d throw down $80 on pdf and $10 each on excel and rtf (which sounds interesting but not neccesarily helpful).
People seem to want to use rtf for mailing labels but pdf support for Avery labels would be more useful and awesome. -
Richard,
Is the xml approach only good for Office Xp? I think a lot of us still need to serve Office 97 and Office 2000 users. -
Certainly you can use CFCONTENT to generate Excel. But the documentation is sparse, most examples don’t explain what is going on, and anything beyond basic layout is not described. Sure, if you know enough, you can do fancy stff, but where in the CF world is this described. For example:
<Style ss:ID="s21"><Alignment ss:Vertical="Bottom" ss:Rotate="90"/></Style> <Cell ss:StyleID="s21"><Data ss:Type="String">Heading</Data></Cell>
That makes changes in Excel? Nifty. But why do I have to go to the Forta blog to find this information? Why hasn’t someone created a site with real CF tricks like this? Why isn’t there a function on cflib.org for this sort of thing? -
I’ve been waiting for CSVToQuery for a _long_ time now. You can fake it using CFHTTP, which strangely enough, can convert CSVs to queries, but then you have the overhead of an HTTP call.
Also, the QueryToXML would be great as long as we can optionally apply an XSLT to it. Then we can format however we need, _including_ creating an XSLT for the Excel XML format. (MM could even supply some defaults . . . hint hint).
🙂 -
dave asks,
“Is the xml approach only good for Office Xp?â€
I’m sorry, but I think it’s not possible. I’m using 2003 and I have just tried with 2000, which failed. Basically if you can save in Excel as an XML spreadsheet (*.XML) then you can use this method.
“ I think a lot of us still need to serve Office 97 and Office 2000 users.â€
I have the same issue. I allow the user to download the data as a csv or as Excel 2002+. The file extension is xls, but the content is XML. -
Daniel Greenfeld asks:
“Certainly you can use CFCONTENT to generate Excel. But the documentation is sparse, most examples don’t explain what is going on, and anything beyond basic layout is not described. Sure, if you know enough, you can do fancy stff, but where in the CF world is this described.â€
Very good points. I’ve not read an article describing what I’ve done, however I wouldn’t be surprised if there is one. I discovered CFCONTENT and Excel though Ben’s WACK Chapter 32 – Generating Non-HTML Content. This shows how to do simple Excel pages with css. I found it a really good introduction. If you understand this, then you can follow my solution as just well.
I had a client require a spreadsheet with column headings rotated 90 degrees. HTML and CSS in Excel cannot do this. As CFCONTENT can create any text output I tried XML. I’m not an expert in XML and Excel, but I don’t need to be. I built a spreadsheet in the format I wanted. I saved it as XML. I then saved the XML in a CFML template and slowly changed it through trial and error to allow me to create a dynamic spreadsheet.
“Why hasn’t someone created a site with real CF tricks like this? Why isn’t there a function on cflib.org for this sort of thing?â€
This is a recent thing for me. I don’t have a website of my own, but if I did then I guess this would be my first article. I’ve only recently had my client ask for it and so was interested in Ben’s comments. I’ve thought of writing a custom tag / function to do this, but I don’t know how feasible it is. For example in the sheet I’m working on, I show header information with all cells rotated apart from one cell. Then for the rows I have a list of user names down one column and the other columns have tick boxes. Finally I have a totals row which uses the COUNTA function in Excel to count all the ticks. I’m freezing frames for the top row and the printout is rotated to landscape. What variables do you choose to send to a CF custom tag to generate this table? What if you want a dramatically different sheet to record say football scores or another for train times… The formatting will be radically different. I don’t know how feasible it would be to write a universal CF custom tag to resolve this. In the end it maybe better to provide a series of editable templates.
I’m still scratching this Excel/XML itch… I’d love to know a way to embed macros and pivot tables in this format. Any ideas? -
pivot tables and macros are probably more likely to happen in the xml format than anything else… hssf can preserve pivot tables and macros… so if you save a ‘template’ excel sheet, add the data using hssf, and then save it as something else, your macro will still work. No way to generate a macro directly though.
http://jakarta.apache.org/poi/hssf/limitations.html
Just fyi- if you do find that the pre-OfficeXP people need the headers rotated, this is possible (I was playing with it today) -
I have users that want information in Excel. I provided the ability to have any page of the application open in Excel by a very small amount of code. I use Fusebox so this works as is but it wouldn’t be difficult to use this idea outside of Fusebox with <cfsavecontent>.
<cfif IsDefined("attributes.xls")>
<cfheader name="Content-Disposition" value="inline;filename=SIMReport.xls">
<cfheader name="Expires" value="#now()#">
<cfcontent type="application/msexcel">
<cfoutput>#fusebox.layout#</cfoutput>
<cfelse>
The normal use is for reports. I use a checkbox named xls to send output to Excel. However, any page can be sent to Excel by adding the xls parameter to the URL.
Ed -
More and more people are finding out how risky Excel is as a data manipulating / reporting tool. Some of the newer programs that are coming out do their own math and then just use the spreadsheet to print the output. (This does not stop or limit the capability of Excel in any way).
The problem is, company’s can spend way over $150 million on a secure data warehouse environment for their enterprise. This will ensure total data security and access control. Then the data is pumped into spreadsheets where there is no longer an control over what happens to the data and who accessed and changes it.
This is known as the "Fragile Last Mile of Information."
See the "The Fragile Last Mile of BI: Spreadsheet Risk & Fraud Analysis" blog at
http://blogs.ittoolbox.com/bi/spreadsheet/
to gain a more in-depth insight to this issue. -
Have any notable solutions come about in the years since this article was written?
I’m simply wanting to open an esisting excel template in CF 7, replace specific cell data with CF variable content, name the new file, and save it.
We would even consider purchasing a solution if the cost was reasonable enough. Thanks. -
There is always JExcelAPI… pretty easy to use with CFMX.
I’ve blogged about how to create XLS files with it, I think, at
http://loggedden.blogspot.com/
You’ll need createObject() access and the jar in the classpath or a java loader to use this technique. -
As Richard mentioned, we can export data as xml spreadsheet if we have to write on multiple workbooks. Check out the below code sample:
Step:1
———
I am writing a query to retrieve data which is to be imported on an excel file
<!— For sheet no: 1 —>
<CFQUERY name="ReportData" datasource="#SystemDS#">
SELECT *
FROM <Table Name>
</CFQUERY>
<!— For sheet no:2 —>
<CFQUERY name="ReportData1" datasource="#SystemDS#">
SELECT *
FROM <Table Name>
</CFQUERY>
Step :2
———-
Now write the below code:
<cfxml variable="xmlDataDump"> (define your own variable name)
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="m16272500">
<Alignment ss:Horizontal="Left" ss:Vertical="Top"/>
<Borders>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Size="16" ss:Bold="1"/>
<Interior ss:Color="#FFFFFF" ss:Pattern="Solid"/>
</Style>
</Styles>
<Worksheet ss:Name="ReportData">
<Table>
<Column ss:Width="66"/>
<Column ss:Width="70.5"/>
<Column ss:Width="192"/>
<Column ss:Index="5" ss:Width="71.25"/>
<Column ss:Width="192"/>
<Row ss:AutoFitHeight="0" ss:Height="15.75">
<Cell ss:MergeAcross="6" ss:StyleID="m16276522"><Data ss:Type="String">Title</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell ss:MergeAcross="5" ss:StyleID="m16276552"></Cell>
</Row>
<!— Define your own code here. Loop thru your query and display data in rows —>
<CFOUTPUT query="Reportdata">
<Row ss:Height="54.75">
<Cell ss:StyleID="s55"><Data ss:Type="String">#column1#</Data></Cell>
<Cell ss:StyleID="s55"><Data ss:Type="String"><CFIF Abbreviation NEQ ”>#XMLFormat(column2)#<CFELSE>#XMLFormat(column3)#</CFIF></Data></Cell>
<Cell ss:StyleID="s55"><Data ss:Type="String">#XMLFormat(Background)#</Data></Cell>
</Row>
</CFOUTPUT>
</Worksheet>
<Worksheet ss:Name="ReportData2">
<!— Repeat code using second query —>
</Worksheet>
</cfxml>
<cfset xml = ToString(xmlDataDump)>
<cffile action="write" nameconflict="overwrite" file="#getDirectoryFromPath(ExpandPath("../../"))#WriteableFolderDeploymentReportsService Introduction Project Deployment Weekly Report.xls" output="#xml#">
Hope it may help !
Cheers,
Dhanya
Leave a Reply