ColdFusion in Context: Paste Spreadsheets

Suppose your customer needs to replace tables of a page or two in size on the customer's Web site frequently. They might be grade reports. They might be poll results. Perhaps neither you nor your customer wants to hassle with uploading files. Manually retyping the data is not an option.

This tip shows that your customer can simply paste spreadsheets to the Web site and have them automatically converted to HTML tables.

Code

When you copy a rectangle of cells from Excel and paste them into a simple word processor, you get tab-delimited rows that end in a linefeed. Put the code to accept data from the user into paste.cfm. Begin by setting a empty default for the input variable. For clarity, use variables to represent a horizontal tab, a linefeed, and a carriage return. 

<!--- Initialize --->
<cfparam name="form.Pastein" default="">
<cfset Tab="#chr(9)#">
<cfset LF="#chr(10)#">
<cfset CR="#chr(13)#">

If all cells were guaranteed to have values, we could loop through the cells and rows, replacing tabs with td pairs and rows with tr pairs. However, if empty cells are not accounted for, Michael Gillespie warns that data will shift to fill gaps.

To handle missing data, characters are needed to mark the place of empty cells and rows. For an empty cell, unlikely data (@* in this case) will suffice. Call this placeholder "Ow" [ouch]. For an empty row, use a vertical tab (VT) to hold its place.

There are six "missing data" problems that need to be handled. The easiest way to store the problems and apply the solutions is to put them in separate strings (Oops and Fix) and arrange them so that the first item in Oops corresponds to the first item in Fix, the second item in Oops corresponds to the second item in Fix, and so forth. Call the strings Oops and Fixes. 

<!--- Ow stands in for an empty cell;
we hope it will never be valid text; 
VT (vertical tab) stands in for an empty line --->
<cfset Ow="@*">
<cfset VT="#chr(11)#">

<!--- Define six problems, then their solutions --->
<!--- Missing cell at left edge; missing cell not at an edge; --->
<cfset Oops="#LF##Tab#,#Tab##Tab#">
<!--- missing cell at right edge in Unix; at right edge in Windows; --->
<cfset Oops="#Oops#,#Tab##LF#,#Tab##CR#">
<!--- empty row in Unix; empty row in Windows --->
<cfset Oops="#Oops#,#LF##LF#,#LF##CR#">
<!--- Fix contains desired replacements in the same sequence --->
<cfset Fix="#LF##Ow##Tab#,#Tab##Ow##Tab#">
<cfset Fix="#Fix#,#Tab##Ow##LF#,#Tab##Ow##CR#">
<cfset Fix="#Fix#,#LF##VT##LF#,#LF##VT##CR#">

If the input is not empty, prepend a linefeed to make it possible to tell if the first cell in a row is missing. Use the replaceList function to replace each problem in the Oops list with the corresponding fix in the Fix list. Then because fixing some problems may bring other errors to light, check for and fix problems again in a second identical pass.

Set an outer loop delimited by the linefeed character, chr(10), to control the placement of tr tags. Then, set an inner loop delimited by the tab character to place the row data one cell at a time within td tags. If the data consists of the Ow placeholder, replace it with a non-breaking space. (The browser won't render borders around just a normal space.) Otherwise, use the data. Wrap all of this effort within cfoutput and table tags, and be sure to give the table a border for this demonstration. 

<cfif len(trim(form.Pastein))>
  <!--- Append a leading line feed
  so the absence of a leading cell will be handled --->
  <cfset temp="#LF##form.Pastein#">
  <!--- For each problem, apply the fix; then DO IT AGAIN --->
  <cfset temp=replaceList(temp,"#Oops#","#Fix#")>
  <cfset temp=replaceList(temp,"#Oops#","#Fix#")>
  <!--- Build the table --->
  <cfoutput>
  <table border="1">
  <cfloop list="#temp#" index="TheRow" delimiters="#CR##LF#">
  <tr>
  <cfloop list="#TheRow#" index="TheCol" delimiters="#Tab#">
  <td>#iif(TheCol is "#Ow#",de("&nbsp;"),de("#TheCol#"))#</td>
  </cfloop>
  </tr>
  </cfloop>
  </table>
  </cfoutput>
</cfif>

Create a simple form that contains a textarea tag and a submit button. Let the value for the textarea tag be the previously submitted value (or an empty default). Use a cfoutput tag to expand it. When the Read button is selected, the page is submitted to itself. 

<form name="paste" action="paste.cfm" method="post">
<cfoutput><textarea name="Pastein" rows="10" cols="70">
#form.Pastein#</textarea></cfoutput>
<input type="submit" name="do" value="Read">
</form>

When copying this code, notice that the

Test

Open an Excel spreadsheet. Highlight and copy data. Paste it into paste.cfm, and press the Read button. If all goes well, you'll see the same data above the textarea as you copied from the spreadsheet. If you didn't get all the data you wanted, copy it again (or edit it in the textarea field) and press Read. Repeat until satisfied.

Extend

It only takes one more button and a few more lines to save the information to tab-delimited files for later parsing and display. Indeed, if the spreadsheet has an agreed-upon format, you can even use this technique to parse and post dozens of rows to a database all at once. If you can parse it, it's yours. =Marty=

[This version of the tip incorporates a suggestion provided by Michael Gillespie into an in-depth explanation of a working procedure.]