Excel to ColdFusion Query

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>

• Posted in:
• ColdFusion

3 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
3. Your solution will be helpful to several folks as Ben's POIUtility.CFC, as wonderful as it is... and it is, makes much use of structures. I am currently converting several apps to run on Railo, among the issues between CF and Railo is structs.. so this has been useful in my refactoring POIUtility.CFC to work well on Railo.. Going forward more and more companies will opt for Railo as it is an excellent cfml server and for that matterbenchmarks faster than CF8
   
   Comment by Craig Burlingame – October 11, 2011