6

I'm using a cfspreadsheet read to read a sheet into a query object.

<cfspreadsheet action="read" src="TestExcel.xls" sheet="1" query="spreadsheetData" headerrow="1" excludeHeaderRow="true">

The problem is, some of the headers contain more than one word. So I end up with a query a bit like this:

ID  Name    Start Date  End Date
3   Test    1/1/2009    1/1/2013
17  Test 2  11/11/2010  11/11/2012

If I try to access one of the columns that have a space in the column name, I get an error.

<cfoutput query="spreadsheetData">
   #start date#
</cfoutput>

I've tried #[start date]# as well, but that didn't work. I cannot control the format of the excel sheet that I receive. Is there any way to access the multiple-worded-header columns?

Leigh
  • 28,765
  • 10
  • 55
  • 103
froadie
  • 79,995
  • 75
  • 166
  • 235

1 Answers1

14

When using bracket notation the contents must end up as a string, so:

<cfoutput query="spreadsheetData">
    #spreadsheetData['start date'][CurrentRow]#
</cfoutput>


If you don't use quotes, you are passing in a variable, which is done like so:

<cfset ColumnName = 'start date' />

<cfoutput query="spreadsheetData">
    #spreadsheetData[ColumnName][CurrentRow]#
</cfoutput>


Note that you must use the query name before the brackets - if you simply write [ColumnName] then this is inline array creation notation, not accessing the variable.

Also, if using this outside of a query loop (i.e. not within cfoutput/cfloop with query attribute), you also need to scope the CurrentRow variable, i.e.

spreadsheetData[ColumnName][spreadsheetData.CurrentRow]

(or provide your own explicit number/variable).


As Leigh notes below, for cfspreadsheet-specific behaviour, you can also specify the columnnames attribute, to rename the column to something directly accessible, e.g.

<cfspreadsheet query=".." columnNames="Foo,Bar,StartDate,Etcetera" ..>
Peter Boughton
  • 110,170
  • 32
  • 120
  • 176
  • 2
    In addition to Peter's answer, keep in mind you could also use the `columnNames` attribute to assign different column names if you wish ie ``. – Leigh May 20 '12 at 19:28
  • @Peter - When I try your first sample code I get the error "Complex object types cannot be converted to simple values." – froadie May 20 '12 at 19:31
  • 1
    @Peter - this code does work though - `#spreadsheetData['start date'][currentRow]#`. I wish there was a less explicit way to do this though... – froadie May 20 '12 at 19:35
  • @Leigh - thanks! It's good to know how to do that. I want this to be as dynamic as possible, though - as in, the columns can be in any order, and we can just access by name – froadie May 20 '12 at 19:38
  • 3
    @froadie - Yep, for dynamic cases, Peter's is the better overall/generic answer. FYI, IIRC you always need to use a row number when using queries and bracket notation ie `#query["column"][row]#`, regardless of whether or not you are inside a query loop. – Leigh May 20 '12 at 20:00
  • Sorry froadie, this is one of ACF's weird quirks/bugs - doing `query.col` works but doing `query['col']` requires the row number. Since I work now 99% of with [Railo](http://getrailo.org) (where bracket and dot notation are not different from each other), I had forgotton about this. – Peter Boughton May 20 '12 at 20:18
  • (Just updated/corrected the answer, and added info provided by Leigh about columnNames attribute.) – Peter Boughton May 20 '12 at 20:24