how to create xlsx files using coldfusion

Question

Disclaimer: I am new into coldfusion. I am trying to create an Excel 2010 document with images and multiple tabs. I have been able to get this to output into XLS, but I cannot get the image into the file.

I have not been able to find a complete example of how to Properly create an XLSx file. I would prefer to learn the right way and develop my own bad habits later, rather than just have bad habits.

Here is an example:

<!--- Make CF export to Excel --->
<!--- This will create a XLS file --->
<!--- <cfheader name="Content-Disposition" value="attachment; filename=#URL.TRNo#_image.xls">
<cfcontent type="application/vnd.msexcel"> --->

<!--- This does not work to create an XLSX file --->
<cfheader name="Content-Disposition" value="inline; filename=#URL.TRNo#_image.xlsx">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"> 

<cfparam name="URL.TRNo" default="AD0310">

<cfoutput>
    <?xml version="1.0"?>
    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
        <ss:Styles>
            <ss:Style ss:ID="Default" ss:Name="Normal">
                <ss:Font ss:Size="11" ss:FontName="Calibri"/>
            </ss:Style>
            <ss:Style ss:ID="Left">
                <ss:Alignment ss:Horizontal="Left"/>
            </ss:Style>
            <ss:Style ss:ID="Center">
                <ss:Alignment ss:Horizontal="Center"/>
            </ss:Style>
            <ss:Style ss:ID="Right">
                <ss:Alignment ss:Horizontal="Right"/>
            </ss:Style>
        </ss:Styles>


        <Worksheet ss:Name="#URL.TRNo# page 1">
            <ss:Table ss:DefaultColumnWidth="15" ss:DefaultRowHeight="15">
                <Row ss:Height="51"><!--- Start Row 1 --->
                    <Cell ss:Index="1" ss:MergeAcross="9">
                        <Data ss:Type="String">Final Test Report</Data>
                    </Cell>
                    <Cell ss:Index="11" ss:MergeAcross="10" ss:StyleID="Center">
                        <Data ss:Type="String"><!--- <img src="http://wwwdev.elmsweb.ford.com/elmsGEN3/SafetyLab/FMVSS/ReportWriter/img/fordLogo_transparent_small.png" height="68" width="181" alt="13"> ---></Data>
                    </Cell>
                    <Cell ss:Index="22" ss:MergeAcross="9" ss:StyleID="Right">
                        <Data ss:Type="String">Confidential</Data>
                    </Cell>
                </Row><!--- End Row 1 --->
                <Row/><!--- Row 2 Blank --->
                <Row><!--- Start Row 3 --->
                    <Cell ss:Index="1" ss:MergeAcross="1" ss:StyleID="Right">
                        <Data ss:Type="String">To:</Data>
                    </Cell>
                    <Cell ss:Index="3" ss:MergeAcross="12">
                        <Data ss:Type="String"></Data>
                    </Cell>
                    <Cell ss:Index="16" ss:MergeAcross="10" ss:StyleID="Right">
                        <Data ss:Type="String">Test Order:</Data>
                    </Cell>
                    <Cell ss:Index="27" ss:MergeAcross="4">
                        <Data ss:Type="String">#URL.TRNo#</Data>
                    </Cell>
                </Row><!--- End Row 3 --->
            </ss:Table>
        </Worksheet>
    </Workbook>
</cfoutput>

Have you tried using ColdFusion's built-in spreadsheet functions? [Documentation for SpreadsheetNew function](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-747b.html). If you set the `xmlformat` parameter to 'true' it will create an .xlsx file. _You need at least ColdFusion 9 for these functions_ — Miguel-F, Feb 21 '13 at 16:40
Neither of these will make an XLS or an XLSX file technically, they make an XML file that's served to Excel. You can easily use the `cfheader/content` from the first line and change the extension to have it "appear" as an XLSX file, but it's not going to be in the XLS/XLSX format. — Busches, Feb 21 '13 at 17:33
@Miguel-F Thank you, I think that is a step in the right dirrection. but how can the user get the file, I do not want to store them on the server? — BTThomas, Feb 21 '13 at 19:00
@busches Could you give an example? I could not get a valid file created by changing the cfheader/content, Excel would give an error and not open it. — BTThomas, Feb 21 '13 at 19:03
This code, http://pastebin.com/wgMW1ggN, correctly opens up in excel, but it does give me the "different extension" error, but I get that no matter if it's xls or xlsx. — Busches, Feb 21 '13 at 20:17
When I tried it, I recieved an error message stating: "Excel cannot open the file 'Teset.xlsx' because the file format or file extension is not valid. Verify that the file has not been corupted and that the file extension matches the format of the file." It does not open the file after clicking OK. I am using office 2010. — BTThomas, Feb 21 '13 at 20:35
@webejeepin - I second the suggestions of looking at the Spreadsheet functions in CF9+. You do not have to save the file to disk. You can streamed it to the browser from memory. Btw, one problem with using faux-excel files is that later versions of Excel are a lot pickier about file format and file extension. Even if the content is valid, if it does not match the extension you are using, users may get the annoying extension mismatch warning due to [extension hardening](http://support.microsoft.com/kb/948615). — Leigh, Feb 22 '13 at 16:04
Thank you all for you responses, the Spreadsheet functions are mostly working for me, there are a few quirks with that solution. Sure to be a subject of another post. — BTThomas, Feb 28 '13 at 20:43
I have posted my original comment as an answer. Thanks @Leigh. Sorry I just got to this, was on vacation. :) — Miguel-F, Mar 04 '13 at 13:51

score 7 · Accepted Answer · answered Mar 04 '13 at 13:50

7

I would suggest using the built-in spreadsheet functions of ColdFusion (first introduced in version 9). Here is the documentation for the SpreadsheetNew function. If you set the xmlformat parameter to 'true' it will create an .xlsx file.

There are several examples on the web (and here on SO) of how to use these ColdFusion functions. Raymond Camden has a nice example here of how to generate a spreadsheet and deliver it directly to the user using the cfcontent tag. Here is another example from Raymond that builds on the first example.

answered Mar 04 '13 at 13:50

Miguel-F

13,450
6
38
63

CFSpreadsheet has some serious scalability issues with large spreadsheets. I experienced it firsthand when creating a spreadsheet from a query, so I'm not completely sure if it is as bad when not creating from a query. I had to stop using cfspreadsheet because it was much too slow for my purposes. It may not be an issue for you, but it is definitely a big issue with large spreadsheets. – Dylan Vander Berg Dec 31 '18 at 18:41

score 4 · Answer 2 · answered Jul 25 '13 at 11:59

4

I've been working on this all week and wanted to share the results. This code works for me using CF 9 on Windows Server 2008 R2. By the way, CFSpreadsheet seems to consume a lot of memory on larger exports. For this reason, we increased the server's physical memory then used CF Administrator to increase the jvm heap size in the Administrator -> Java and JVM settings. My max java heap size is now 3072 Gig. Need help? I recommend you contact Charlie Arehart on server issues: charlie@carehart.org

Code:

Example query is called "Myquery" Spreadsheet is called "Myspreadsheet" I export the spreadsheet to a subdirectory called "xlsx" I use cflocation to direct the browser to the spreadsheet. The browser prompts the user to open, save or cancel.

<cfset var_filenameis = "Myspreadsheet.xlsx">
<cfset SpreadsheetObj = spreadsheetNew("true")>
<cfset SpreadsheetObj = spreadsheetNew("#var_filenameis#","yes")>

<cfspreadsheet action="write" filename="./xlsx/#var_filenameis#" query="Myquery" overwrite="true"> 

<cflocation url = "./xlsx/#var_filenameis#">

Dave Kraft

answered Jul 25 '13 at 11:59

user2618574

41
1

Does this save an actual excel file locally on the server? Is that a necessary step? (Seems a weird way to do it but I've not found an alternative yet) – Richard Tingle Jul 10 '17 at 10:56
@RichardTingle you've probably already found that yes this method saves the file. cflocation precludes further processing (like deleting the file) – gordon Jun 14 '23 at 20:16
the first `` gets overwritten by the second so isn't needed (could be edited out of answer?). How do you delete the file from the server after streaming to the user? – gordon Jun 14 '23 at 20:19
1

@gordon yes, I think I knew at the time. I was more saying it was weird; I would have expected to build the file in memory and stream that straight out to the user. It's all so long ago (and I no longer work with CF) but I can't remember finding a better solution. – Richard Tingle Jun 14 '23 at 20:47

how to create xlsx files using coldfusion

2 Answers2

Linked