Excel to ColdFusion Query

March 28, 2008

A client recently supplied some products for their new eCommerce website as an Excel file. ColdFusion 8 doesn't have an Excel data driver, so I needed to find a different way to import the data. I figured I could either save the Excel worksheet as a CSV file (ugh) or, save it as an XML file.

The XML route seemed to be the way to go, unfortunately, the XPATH implementation in XmlSearch doesn't play nicely with the namespaces that Microsoft like to litter their XML with so, this what I came up with in the end.

<cfoutput>
<cffile action="read" file="#ExpandPath('data-import.xml')#" variable="xmlImport" />

<cfset xmlImport = XmlParse(xmlImport) />

<cfset aRowNodes = xmlImport.Workbook.Worksheet.Table.XmlChildren />

<!--- Set up the Column names in the Workbook --->
<cfset lstColumns = 'CATEGORY,SKU,TITLE,DESCRIPTION,PRICE' />

<cfset qryExcel = QueryNew(lstColumns) />

<cfloop from="1" to="#ArrayLen(aRowNodes)#" index="index">
  <cfif aRowNodes[index].XmlName eq "Row">
    <cfif ArrayLen(aRowNodes[index].XmlChildren) eq ListLen(lstColumns)>
      <cfset QueryAddRow(qryExcel) />

      <cfloop from="1" to="#ListLen(lstColumns)#" index="colindex">
        <cfset colname = ListGetAt(lstColumns, colindex) />
        <cfif ArrayLen(aRowNodes[index].XmlChildren) gte colindex And ArrayLen(aRowNodes[index].XmlChildren[colindex].XmlChildren) neq 0>
          <cftry>
            <cfset QuerySetCell(qryExcel, colname, aRowNodes[index].XmlChildren[colindex].XmlChildren[1].XmlText) />
            <cfcatch>
              <cfdump var="#aRowNodes[index]#" />
              <cfdump var="#cfcatch#" />
              <cfset QuerySetCell(qryExcel, colname, "") />
            </cfcatch>
          </cftry>
        <cfelse>
          <!--- blank node --->
          <cfset QuerySetCell(qryExcel, colname, "") />
        </cfif>
      </cfloop>    
    </cfif>
  </cfif>
  
</cfloop>

2 comments

  1. That is an interesting way to do it.

    CF8 does come with the POI .jar that reads excel.

    Did you try the POIUtility.cfc?

    www.bennadel.com/projects/poi-utility.htm />
    It uses the POI .jar that comes with CF8 and can read and write excel./ It can even work with multiple sheets.

    Comment by     Kevin Pepperman – April 02, 2008
  2. Hi Kevin,
    I hadn't seen that Ben Nadel had done that. Will definitely check it out. I think that the xls driver was dropped in CFMX, so my solution may still be useful to someone :)

    Comment by John Whish – April 02, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.