cfspreadsheet - how to write to excel WITHOUT showing column headers

Question

I'm writing query results to an excel spreadsheet using cfspreadsheet (in ColdFusion). The customer does not want the header rows showing up on the excel sheet. The excludeheaderrow attribute is only for reading. Does anyone know if it's possible to NOT write the header rows to the spreadsheet?

Thanks!

Can you define a 'named range' dynamically with a formula (that starts in A2) and query against the named range rather than the whole worksheet? — , Mar 27 '15 at 19:32
I'm not sure what a 'named range' is. Would I specify the Excel cell that each query field goes into? — Kirsten, Mar 27 '15 at 19:45

score 3 · Accepted Answer · answered Mar 27 '15 at 20:52

spreadSheetAddRows and spreadSheetWrite will create the spreadsheet without the column headers.

This bellow snippet will write a .xls file that has just the values 1,2,3 in row 1. The last 3 lines in the cfscript block can be used in place of: <cfspreadsheet action="write" query="qTest" filename="temp.xls">. Additional attributes and function will be required if you have additional attributes in your cfspreasheet tag.

<cfscript>
    qTest = queryNew("Column1,Column2,Column3");
    queryaddRow(qTest);
    querySetCell(qTest ,"Column1",1);
    querySetCell(qTest ,"Column2",2);
    querySetCell(qTest ,"Column3",3);

    spreadSheetObj= spreadsheetNew(false); //true for xlsx format
    spreadsheetAddRows(spreadSheetObj,qTest);
    spreadSheetWrite(spreadSheetObj,expandPath("./") &"temp.xls");
</cfscript>

Thanks - you're right, it removes the headers. The only final problem I have is formatting one of my date columns - I have 2 ways of formatting the dates, but excel seems to be overriding it. Know of any way to do that? (I need it as 03/30/2015.) — Kirsten, Mar 30 '15 at 14:15
@Kirsten look at the function [SpreadsheetFormatColumn](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6806.html). You would format the data after adding the data, and before writing to the file. — Twillen, Mar 30 '15 at 14:28

score 0 · Answer 2 · edited May 23 '17 at 11:43

If it can be reasonably assumed that the number of columns to be retrieved is fairly static and that the rows may grow/shrink then querying against a 'named range' with a workbook scope may be the best method.

You've provided no sample data but let us further assume that the data block goes from column A to column N and that while there may be some blank fields, column A always has a part number/serial number/identifier of a numeric nature. The data resides on a worksheet named Sheet2.

Go to Formulas ► Defined Names ► Name Manager. Start a New named range. Use myData for the Name:, make sure that the Scope: is Workbook, not Worksheet, and use the following for the Refers to:

=Sheet2!$A$2:INDEX(Sheet2!$N:$N, MATCH(1e99, Sheet2!$A:$A))

If the column you choose to define the extents of your data was alphabetic (aka Text) in nature, that formula would,

=Sheet2!$A$2:INDEX(Sheet2!$N:$N, MATCH("zzz", Sheet2!$A:$A))

Click OK to create the new named range then Close to close the Name Manager. Test your defined data range by tapping F5 and typing myData into the Reference: box and clicking OK.

If everything has worked according to plan, your data range should be selected. You should be able to query against that rather than a worksheet name or worksheet range of cells.

The following may be of help in designing your query: Using excels named range in a sql string in VBScript. There are numerous other resources that a search will produce.

I think the OP was looking for a way to build his excel file using ColdFusion. — Dan Bracuk, Mar 28 '15 at 15:12
@DanBracuk - If ColdFusion cannot accept a query to a named range, then I apologize on behalf of the rest of the cross-platform world that does. — , Mar 28 '15 at 16:50
Sorry about not providing sample data! This seems like a good approach, but more than I need. Thanks, though! — Kirsten, Mar 30 '15 at 14:16

score -2 · Answer 3 · answered Mar 27 '15 at 20:01

Exporting your data as a straight HTML table may work for you. Not sure exactly how much you are doing in your output, but it's quite straight forward. Something like this:

<Cfsavecontent name="myExcelFile">
<table>
<cfoutput query="blah">
    <tr border="1">
        <td width="400">#col1#</td>
        <td><p>#col2#</p></td>
        <td>#col3#</td>
    </tr>
</cfoutput>
</table>
</CFSAVECONTENT>

<cfheader name="Content-Disposition" value="inline; filename=somefilename.xls">
<cfcontent type="application/vnd.ms-excel"><cfoutput>#myExcelFile#</cfoutput>

Note the use of border, width, the P tag (for specifying text). It might take a little experimentation, but it's not terribly difficult.

Downvoted because using html tags like this became a bad idea when Office 2007 came out. The results are ugly. — Dan Bracuk, Mar 28 '15 at 03:30

cfspreadsheet - how to write to excel WITHOUT showing column headers

3 Answers3