How to loop through cfspreadsheet headers?

Question

I have an excel file that I am trying to read and then display the values of the headers in a drop down. The first row in my excel file has all of the values (header names).

I used the code below, but what happens is that all the header names appear in a single line with commas. I want the headers to be separated, so that it will appear in the drop down with many <option>, instead of a single <option>. How do I do that?

    <!-- Read the header values from excel -->
    <cfset spreadsheet = "uploads/spreadsheet.xlsx">

    <cfspreadsheet action="read" headerrow="1" src="uploads/spreadsheet.xlsx" query="excelHeader" rows="1" />

    <cfset excelHeaders = excelHeader.columnList>

    <!-- Display the header names as a dropdown -->
    <select name="id_headers">
        <option>
            #excelHeaders#
        </option>
    </select>

i've not done much with spreadsheets and coldfusion, but if excelHeader.columnList is coming back as a list, treat it as a list - loop through the list when you do your select box — luke, Dec 06 '16 at 07:20
I tried to loop using a list, `#excelHeader.columnList#` and it still doesn't work the way I want. — Gosi, Dec 06 '16 at 07:31
like i said, i don't know much about excel and colfusion, but to loop over list, you want something like #headerItem# — luke, Dec 06 '16 at 10:20
*I tried to loop using a list* No, that is a `query` loop. A "list" is just a string, delimited by some character. To loop through a "list", use the `list` attribute. If you read the [cfloop documentation](https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-tags/tags-j-l/cfloop-looping-over-a-list-a-file-or-an-array.html), the first two examples demonstrate how to loop through a "list". — Leigh, Dec 06 '16 at 12:28

score 4 · Accepted Answer · edited Dec 06 '16 at 17:42

You can try this code;

<!--- create new spreadsheet and populate with sample data --->
<cfset theSheet = SpreadsheetNew("Expenses")>
<cfset SpreadsheetSetCellValue(theSheet,"column1",1,1)> 
<cfset SpreadsheetSetCellValue(theSheet,"column2",1,2)>
<cfset SpreadsheetSetCellValue(theSheet,"column3",1,3)>
<cfset SpreadsheetSetCellValue(theSheet,"column4",1,4)>

<!--- Write the spreadsheet to a file, replacing any existing file. ---> 
<cfset pathToFile = GetDirectoryFromPath(GetCurrentTemplatePath()) & "newSpreadsheet.xls">
<cfspreadsheet action="write" filename="#pathToFile#" name="theSheet" overwrite=true>

<!--- Read spreadsheet into query object ---> 
<cfspreadsheet action="read" headerrow="1" src="#pathToFile#" query="excelHeader" rows="1">

<!--- Display the header names as a dropdown --->
<cfoutput>
<select name="id_headers">
<cfloop list="#excelHeader.columnList#" index="option">
    <option>#option#</option>
</cfloop>
</select>
</cfoutput>

You can run this code snippet in trycf

@Leigh hey its nice. looks like more professional now. Thanks. — Justin, Dec 07 '16 at 04:49

How to loop through cfspreadsheet headers?

1 Answers1