Creating Multiple Tabs in Excel with 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.
<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?