tracker issue : CF-3035900

Problem:

Provide Tag-based Generation of Excel files (while keeping existing excel functions)

{NOTE: This ER is for Tag Based generation of Excel from existing HTML tags, to be supplemented by excel functions as required. Other proposals use new CFEXCEL tags (eg. CFEXCELROW). This would have some (but not all ) the advantages discussed below, but would be preferable to not having any tag-based way of generating Excel outputs}

The thing to keep in mind with cfexcel is that a primary use case will be generating reports. Using functions for this kind of layout activity can be pretty awkward. There are some nice tools for queries in the functions and tag but very very rarely is a raw query suitable.

I would go further than Adam and suggest CFEXCEL should be capable of working like CFDOCUMENT and be able to parse and translate HTML table format.

The following example would be ideal:


<cfexcel format="xls">
<cfexcelworksheet name="invoices">
  <tr>
    <!--- Report Details --->
    <td><img src="/images/reports/logo.gif" height="60" width="100" /></td>
    <td colspan="5"><h1>Invoice Report</h1></td> 
  </tr>
  <tr>
    <!--- Column Headers --->
    ....
    <th><strong>Date Issued</strong></th>
    ....
  </tr>
  <cfouput query="qinvoices">
  <tr>
    <!--- Report Body--->
    ....
    <td>#dateFormat(qInvoices.dateIssued, "dd-mmm-yyyy")#</td>
    ....
  </tr>
  ....
  </cfouput>
  <tr>
    <!--- Report Summary --->
    <td></td>
    ...
    <td><strong>Total</strong></td>
    <td>#qInvoiceSummary.invoiceTotal#</td>     
  </tr>

</cfexcelworksheet>
</cfexcel>


Some Advantages of this approach:


    * Consistency with cfdocument and cfpresentation (this is a lot more like these 2 tags than cfimage)
    * Uses HTML tables. Every web developer knows tables and understands very well how to span columns, start new rows, etc.
    * Uses the same <cfoutput> approach that every ColdFusion developer knows. It is easier for developers to apply expressions to and format query outputs.
    * Enables much greater code re-use:At my current workplace we re-use 95% of our report code to generate reports in PDF, HTML and Excel. We re-use the html view and in the case of PDF and wrap a <cfdocument> around the output or in the case of excel simply prepend Excel specific MS styles and use <cfcontent> to mime-type XLS.
    * This is aligned with the Adobe AIR strategy of extending web development skills to the desktop
    * You drastically reduce the number of calls into CFEXCEL, which has got to be good for performance



Ideally you would find the ’natural’ HTML syntax to map across to your existing functions. Eg an included style block at the top of the tag and inline style declarations on the HTML tags. But being able to layout the base data alone would be a major help by itself.

Someone else can confirm but I believe POI can open xml formated excel files. Excel itself can open both xml xls and xml xlsx files.

Some notes:


    * text based excel files are bigger than binary files. To fix, ’touch’ the excel text file and resave.
    * I would avoid making users reference cell addresses (such as a1) within this format, since that would still be difficult within a CFM page - any time you add a row etc, these need to change. Instead you could implement a named range concept. To add a cell to a named range, I can give it a specific class="" name.
    * You could potentially use a bind notation ({ }) to indicate calculations. Other cells would be listed through their named range. Eg.

<td class="summaryline">{sum:grouplines}</td>

* In addition, the thread linked below talks about deriving the correct excel html markup (as generated/read by excel itself). This could also be used as a guide for mapping HTML to Excel.

Forum thread where this issue is discussed:
https://prerelease.adobe.com/project/forum/thread.html?cap=87529BDA13744B3DB718E841890B9240&forid={267A8BEA-8D25-435C-A533-6C830DD686CA}&topid={91CE544C-7F09-48B7-9A4B-F6DADF16D5C6}
Method:


Result:

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3035900

External Customer Info:
External Company:  
External Customer Name: Ben Davies
External Customer Email: 447227C844C7765A992015A8
External Test Config: 08/25/2008

Attachments:

Comments: