CODEWITHSUNDEEP
excelcoldfusioncfspreadsheet
Kirsten
Kirsten

Reputation: 27

cfspreadsheet - how to write to excel WITHOUT showing column headers

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!

Upvotes: 1

Views: 2181

Answers (3)

Twillen
Twillen

Reputation: 1466

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>

Upvotes: 3

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Mark A Kruger
Mark A Kruger

Reputation: 7193

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.

Upvotes: -2

Related Questions