Although Spreadsheet CFML has over 130 methods for dealing with spreadsheets and CSVs, probably the most common spreadsheet related task is simply reading the contents of an Excel file into a variable we can use in our CFML code.

Given a spreadsheet such as the following...

...we can use the library's read() method to turn it into a query object:

spreadsheet = New spreadsheetCFML.Spreadsheet()
path = ExpandPath( "pokemon.xlsx" )
result = spreadsheet.read( src=path, format="query", headerRow=1 )
WriteDump( result )

Queries are a variable type peculiar to CFML and it makes sense here since it matches the usual format of spreadsheets: a set of columns with headers, and data rows below.

From Spreadsheet CFML 5.1.0, however, you can now read spreadsheets into two other types of variable.

Arrays

By changing the format argument of our read() call to "array"...

result = spreadsheet.read( src=path, format="array", headerRow=1 )
WriteDump( result )

...we now get the following:

What's returned is actually a struct containing 2 arrays: the column headers in the first (result.columns) and then all the row data in the second (result.data).

There is a small amount of additional processing involved in generating a query object so if it suits your needs, returning an array will also be the most efficient option.

Array of structs

Although less efficient, you now also have the option of returning an array of structs, each of which includes the headers as keys to the values in each row.

result = spreadsheet.read( src=path, format="arrayOfStructs", headerRow=1 )
WriteDump( result )

Full documentation of read()