In this ColdFusion sample I'm going to demonstrate how to allow users to upload Excel files and use ColdFusion to both validate and read the content within. Let's begin by designing a simple upload form.
<cfif structKeyExists(variables, "errors")>
<cfoutput>
<p>
<b>Error: #variables.errors#</b>
</p>
</cfoutput>
</cfif>
<form action="test.cfm" enctype="multipart/form-data" method="post">
<input type="file" name="xlsfile" required>
<input type="submit" value="Upload XLS File">
</form>
Nothing too complex here. The form has a grand total of one field - the file field named xlsfile. Note above the form is a simple set of ColdFusion logic to notice an errors variable and output it. In case you're curious, this value will be created a bit later in our example. So - let's process the upload. Here's the code that handles that.
<cfif structKeyExists(form, "xlsfile") and len(form.xlsfile)>
<!--- Destination outside of web root --->
<cfset dest = getTempDirectory()>
<cffile action="upload" destination="#dest#" filefield="xlsfile" result="upload" nameconflict="makeunique">
<cfif upload.fileWasSaved>
<cfset theFile = upload.serverDirectory & "/" & upload.serverFile>
<cfif isSpreadsheetFile(theFile)>
<cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
<cffile action="delete" file="#theFile#">
<cfset showForm = false>
<cfelse>
<cfset errors = "The file was not an Excel file.">
<cffile action="delete" file="#theFile#">
</cfif>
<cfelse>
<cfset errors = "The file was not properly uploaded.">
</cfif>
</cfif>
This code block begins with the field check used for our upload. If it exists, and has a value, we have to do some processing. We need a place to store the upload, and as we all know, you never upload files to a directory under web root. Therefore I used the temp directory as a quick storage place. I upload the file using cffile/action=upload. If the file was successfully uploaded, I use isSpreadsheetFile() to determine if the file was a valid spreadsheet. This covers XLS, XLSX, and even OpenOffice documents. If it is a valid spreadsheet, I read it in using the cfspreadsheet tag. Notice the last two arguments.
The query argument tells ColdFusion to parse the spreadsheet data into a query. This assumes we only want the first sheet. If you want to work with other sheets, that's definitely possible.
The last argument, headerrow, tells ColdFusion to consider the first row to be column headers. It may not always be advisable to assume this. But for now, we will.
The rest of that block simply handles errors and specifying if we should show the form again. If the user uploaded a valid spreadsheet we don't want to show the form. Instead, we want to display the contents. Let's look at how I did this.
<style>
.ssTable { width: 100%;
border-style:solid;
border-width:thin;
}
.ssHeader { background-color: #ffff00; }
.ssTable td, .ssTable th {
padding: 10px;
border-style:solid;
border-width:thin;
}
</style>
<p>
Here is the data in your Excel sheet (assuming first row as headers):
</p>
<cfset metadata = getMetadata(data)>
<cfset colList = "">
<cfloop index="col" array="#metadata#">
<cfset colList = listAppend(colList, col.name)>
</cfloop>
<cfif data.recordCount is 1>
<p>
This spreadsheet appeared to have no data.
</p>
<cfelse>
<table class="ssTable">
<tr class="ssHeader">
<cfloop index="c" list="#colList#">
<cfoutput><th>#c#</th></cfoutput>
</cfloop>
</tr>
<cfoutput query="data" startRow="2">
<tr>
<cfloop index="c" list="#colList#">
<td>#data[c][currentRow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfif>
So skipping over the CSS, the real meat of the work begins when we get the metadata. Why do we do this? ColdFusion's query object does not maintain the same order of columns that our spreadsheet had. I can use the getMetadata function on the query to get the proper column order. That's the array list you see there.
Next - we do a quick check of the size of the query. We are assuming our spreadsheet has a first row being used as headers. So if we assume that, and there is only one row, then we really don't have any data. Notice then in the next block of the conditional, we use startRow=2 to begin with where we figure the real data starts. After that it's a simple matter of outputting the query dynamically. (For an example of working with dynamic ColdFusion queries, see this blog entry.)
How does it look? Here's the result of uploading a sample XLS sheet.
And below is the complete template. Read on though for more...
<cfset showForm = true>
<cfif structKeyExists(form, "xlsfile") and len(form.xlsfile)>
<!--- Destination outside of web root --->
<cfset dest = getTempDirectory()>
<cffile action="upload" destination="#dest#" filefield="xlsfile" result="upload" nameconflict="makeunique">
<cfif upload.fileWasSaved>
<cfset theFile = upload.serverDirectory & "/" & upload.serverFile>
<cfif isSpreadsheetFile(theFile)>
<cfspreadsheet action="read" src="#theFile#" query="data" headerrow="1">
<cffile action="delete" file="#theFile#">
<cfset showForm = false>
<cfelse>
<cfset errors = "The file was not an Excel file.">
<cffile action="delete" file="#theFile#">
</cfif>
<cfelse>
<cfset errors = "The file was not properly uploaded.">
</cfif>
</cfif>
<cfif showForm>
<cfif structKeyExists(variables, "errors")>
<cfoutput>
<p>
<b>Error: #variables.errors#</b>
</p>
</cfoutput>
</cfif>
<form action="test.cfm" enctype="multipart/form-data" method="post">
<input type="file" name="xlsfile" required>
<input type="submit" value="Upload XLS File">
</form>
<cfelse>
<style>
.ssTable { width: 100%;
border-style:solid;
border-width:thin;
}
.ssHeader { background-color: #ffff00; }
.ssTable td, .ssTable th {
padding: 10px;
border-style:solid;
border-width:thin;
}
</style>
<p>
Here is the data in your Excel sheet (assuming first row as headers):
</p>
<cfset metadata = getMetadata(data)>
<cfset colList = "">
<cfloop index="col" array="#metadata#">
<cfset colList = listAppend(colList, col.name)>
</cfloop>
<cfif data.recordCount is 1>
<p>
This spreadsheet appeared to have no data.
</p>
<cfelse>
<table class="ssTable">
<tr class="ssHeader">
<cfloop index="c" list="#colList#">
<cfoutput><th>#c#</th></cfoutput>
</cfloop>
</tr>
<cfoutput query="data" startRow="2">
<tr>
<cfloop index="c" list="#colList#">
<td>#data[c][currentRow]#</td>
</cfloop>
</tr>
</cfoutput>
</table>
</cfif>
</cfif>
Your Homework!
Your homework, if you chose to accept it, is to simply take the template and add a checkbox to toggle if the code should assume the first row is the header. It's not as simple as you think. Sure you can just get rid of that attribute, but you also have to update the display as well. Post your code to Pastebin and then share the url.
Notes
Why didn't I use the VFS to store the file? I did - but isSpreadsheetFile() always returns false on an XLS file in the VFS. Boo!
Like the style of this blog entry? (Simple example with a homework assignment.) If so - I'm thinking of doing more like it.