ColdFusion: Converting Records From Excel to Query

Have you ever needed to get data from Excel to ColdFusion to be manipulated? Before ColdFusion 9, reading records from Excel was cumbersome. <cfspreadsheet> makes the task handy. You can convert the records to spreadsheet object, query, CSV, or even HTML.

Here is an example on how to read an Excel spreadsheet and convert its rows to ColdFusion query.

<cfspreadsheet  
    action = "read" 
    src = "spreadsheet.xls" 
    sheet = "1"
    excludeHeaderRow = "true" 
    headerrow = "1" 
    query = "queryName"
    rows = "2-10" />
 
<cfdump var="#queryName#" />

Attribute rows is useful during development. Instead of reading all records in once, you can just output several records for testing/debugging. For more information on other attributes, please see the reference below.

Reference:

• cfspreadsheet