Creating Multiple Tabs in Excel with Coldfusion
Posted By : todd sharp Posted At : July 19, 2006 2:48 PM Posted In: ColdFusion
20
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.
<cfsetting showdebugoutput="false">
<cfheader name="content-disposition" value="inline;filename=ExcelWithMultipleWorksheets.xls">
<cfcontent type="application/msexcel">
<?xml version="1.0"?>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Worksheet 1">
<Table>
<Row>
<Cell ss:Index="1"><Data ss:Type="String">Test Data 1</Data></Cell>
</Row>
</Table>
</Worksheet>
<Worksheet ss:Name="Worksheet 2">
<Table>
<Row>
<Cell ss:Index="1"><Data ss:Type="String">Test Data 2</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
<cfheader name="content-disposition" value="inline;filename=ExcelWithMultipleWorksheets.xls">
<cfcontent type="application/msexcel">
<?xml version="1.0"?>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="Worksheet 1">
<Table>
<Row>
<Cell ss:Index="1"><Data ss:Type="String">Test Data 1</Data></Cell>
</Row>
</Table>
</Worksheet>
<Worksheet ss:Name="Worksheet 2">
<Table>
<Row>
<Cell ss:Index="1"><Data ss:Type="String">Test Data 2</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
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?
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>
Thank you,
Derek
In the meantime, post your code here if it's not working for you and I'll see if I can see a problem.
Do you have that code (Example ?) can you post it ?
--------------------------------------------
<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>
<?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?
It will give you all the xml settings you need to make the changes. I use this all the time now for my spreadsheet exports at work.
compared to when i save same file. Any suggestion