Ben Nadel's POI Utility

August 13, 2008

A client has just supplied me an Excel speadsheet with 4,000+ rows and 20+ columns to import into their website database. I decided to try out Ben Nadel's POI Utility for converting Excel spreadsheets into a ColdFusion Query. It's very cool - nice work Ben!

As I mentioned above the spreadsheet that I had been provided with had 20+ columns. Ben's example read_sheet.cfm script required that you listed all the columns; I couldn't be bothered to type them all in so I made a simple modification to the script and now it lists all the columns for me.

Here it is:

<cfoutput>
  <!--- Create an instance of the POIUtility.cfc. --->
  <cfset objPOI = CreateObject( 
    "component", 
    "POIUtility" 
    ).Init() 
    />
  
  
  <!--- 
    Read in the Exercises excel sheet. This has Push, 
    Pull, and Leg exercises split up on to three different
    sheets. Instead of reading in all the sheets, we are 
    going to read in just the Push sheet (currently the 
    sheet indexes are zero-based). And, since our excel 
    sheet has a header row, we want to strip it out of our 
    returned queries.
  --->
  <cfset objSheet = objPOI.ReadExcel( 
    FilePath = ExpandPath( "./companions.xls" ),
    HasHeaderRow = true,
    SheetIndex = 0
    ) />
  
  
  <!--- 
    Since we provided a sheet index, the ReadExcel() has 
    returned a single Sheet object. Let's loop over the
    single sheet and output the data. NOTE: This could be
    also done to insert into a DATABASE!
  --->
  
  <!--- 
    Output the name of the sheet. This is taken from 
    the Tabs at the bottom of the workbook.
  --->
  <h3>
    #objSheet.Name#
  </h3>
  <!--- 
    Output the data from the Excel sheet in a table. 
    We know the structrure of the Excel, so we can
    use the auto-named columns. Also, since we flagged
    the workbook as using column headers, the first 
    row of the excel was stripped out and put into an
    array of column names.
  --->
  <table border="1">
    <!--- Loop through the number of columns. --->
    <tr>
      <cfloop from="1" to="#ListLen(objSheet.Query.ColumnList)#" index="index">
      <th>
        #objSheet.ColumnNames[ index ]#
      </th>
      </cfloop>
    </tr>
  
  <!--- Loop over the data query. --->
  <cfloop query="objSheet.Query">
  
    <!--- 
      It is possible that the query read in read in 
      blank rows of data. For our scenario, we know 
      that we HAVE to have an exercise name. 
      Therefore, if there is no exercise name returned
      (in Column1), then this row is not valid - skip 
      over it.
    --->
    <cfif Len( objSheet.Query.column1 )>
    
      <tr>
      <!--- Loop through each column. --->
      <cfloop list="#objSheet.Query.ColumnList#" index="index">
        <td>
          #objSheet.Query[ index ][ objSheet.Query.CurrentRow ]#
        </td>
      </cfloop>
      </tr>
      
    </cfif>
  
  </cfloop>
  </table>
  
</cfoutput>

 

2 comments

  1. John, glad you like it :) I haven't put much effort into updating the actual Utility lately. My efforts have been focused on the custom tags for generating formatted Excel documents. Let me know if see anything that could use some attention.

    Comment by Ben Nadel – August 13, 2008
  2. Hi Ben, yeah it's great - I just wished I found it earlier :) A while back I spent ages writing my own version and then someone told me about yours!

    If you're interested my attempt is here:
    www.aliaspooryorik.com/blog/index.cfm/e/posts.details/post/excel-to-coldfusion-query-41

    Comment by John Whish – August 13, 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.