<cfsilence>

Creating Multiple Tabs in Excel with Coldfusion

Posted At : July 19, 2006 3:48 PM | Posted By : todd sharp
Related Categories: ColdFusion

I know I've seen this before, but I thought it couldn't hurt to throw it up for those who haven't seen it. Beginning with Office 2002 (XP) you can create Excel spreadsheets using the XML Spreadsheet schema. The documentation is pretty good. As with any XML it must be well formed. To get a taste of the schema check out the documentation, or save any spreadsheet as .xml and check out the saved file. Here's a quick example of how to create a multiple tabbed workbook in Excel.

Here is what you get...

Comments (11) |

Print |

Send |

Code |

del.icio.us |

Linking Blogs | 9767 Views

Comments

[Add Comment]

I had tried this before, and found that with large spreadsheets, the xml file was often still far too large (and took far too long to create). For smaller sheets this works fine, for anything much larger than 100 rows or so, we wrote a custom tag to use the poi project with excellent results.

# Posted By Rich Rein | 7/20/06 9:34 AM

Rich:

I used this method yesterday to create a workbook with around 12 tabs populated from a storedproc that returns 5 resultsets. All told, the final product is around 9mb. I'm not sure if that's going to work...

The weird thing is that when it's saved as .xls (instead of the prompted .xml) it drops down to around 1.5mb. Still haven't figured that out.

What's up with the poi project and your custom tag? Can you share?

# Posted By todd | 7/20/06 11:30 AM

Drop me an email, we can discuss this and I don't mind you sharing once you get it to work...

# Posted By Rich Rein | 7/21/06 11:04 AM

With my limited knowledge of CF.. I can't seem to loop over a query and get this to work.

Suggestion?

Basically:

<Table>
    <cfoutput query="test">
<Row>
<Cell ss:Index="1"><Data ss:Type="String">#empID#</Data></Cell>
          <Cell ss:Index="2"><Data ss:Type="String">#ReportType_ID#</Data></Cell>
</Row>
       </cfoutput>
</Table>

# Posted By Terrence | 9/20/06 2:48 PM

Seems like that should work Terrence, what error are you getting?

# Posted By todd | 9/26/06 4:47 PM

Is it possible to loop over an include with this? I would like to loop over an include of a table that i need to display multiple times but don't want to recreate that table in XML.

Thank you,

Derek

# Posted By Derek Brigner | 10/4/06 5:56 PM

Derek: You should absolutely be able to loop over a table or row in this method. Let me check my code that I have in production tomorrow at work and I'll post an example here in the comments.

In the meantime, post your code here if it's not working for you and I'll see if I can see a problem.

# Posted By todd | 10/4/06 6:46 PM

were you guys able to loop through it ?

Do you have that code (Example ?) can you post it ?

# Posted By se | 6/25/08 4:23 PM

this is how it works...

--------------------------------------------

<cfoutput query="topAllDataQry">
<cfset topAllData = topAllData
& ' <Row>
<Cell ss:Index="2" ss:StyleID="s92"><Data ss:Type="Number">#num#</Data></Cell>
<Cell ss:StyleID="s94"><Data ss:Type="String">#txtype#</Data></Cell>
<Cell ss:StyleID="s93"><Data ss:Type="String">&##160;#productCode#</Data></Cell>
<Cell ss:StyleID="s94"><Data ss:Type="String">#name#</Data></Cell>
<Cell ss:StyleID="s94"><Data ss:Type="String">#region#</Data></Cell>
<Cell ss:StyleID="s92"><Data ss:Type="Number">#cnt#</Data></Cell>
</Row>
'>
</cfoutput>

# Posted By se | 6/27/08 5:49 PM

I am having problems getting Excel to read the xml file..... I have it formatted as such:

<?xml version='1.0'?>
<workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>
<worksheet ss:Name='Sheet1 - Month DD, YYYY'>
<table>
<Row>
<Cell><Data ss:Type='String'>Stuff</Data></Cell>
...repeat 5 times...
</Row></table></worksheet>
...Repeat 7-8 times...
</workbook>

And I get an "unable to open" error in Excel..... What am I doing wrong?

# Posted By js | 7/17/08 1:31 PM

I get a similiar error if I add additional rows to a table....Thoughts?

# Posted By James | 8/5/08 10:31 AM

[Add Comment]