read

Reads a spreadsheet file into one of the following:

• a spreadsheet object;
• a CFML query object;
• a CFML array (inside a struct which also has any columns/headers, see below);
• a CFML array of structs;
• an HTML string;
• a CSV string.

read( src [, format [, columns [, columnNames [, headerRow [, rows [, sheetName [, includeHeaderRow [, includeBlankRows [, fillMergedCellsWithVisibleValue [, includeHiddenColumns [, includeHiddenRows  [, includeRichTextFormatting [, password [, csvDelimiter [, queryColumnTypes, [ makeColumnNamesSafe, [ returnVisibleValues ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] )

or

read( src [, format [, columns [, columnNames [, headerRow [, rows [, sheetNumber [, includeHeaderRow [, includeBlankRows [, fillMergedCellsWithVisibleValue [, includeHiddenColumns[, includeHiddenRows [, includeRichTextFormatting [, password [, csvDelimiter [, queryColumnTypes, [ makeColumnNamesSafe, [ returnVisibleValues ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] ] )

Required arguments

• src string: absolute path to the file to read

Optional arguments

• format string: "query", "array", "arrayOfStructs", "html" or "csv". If omitted, returns a spreadsheet object.
  • "query" returns a CFML query object.
  • "array" (from 5.1.0) returns an ordered struct with 2 keys:
    1. columns: array of header/column names (empty if none specified);
    2. data: array of the row arrays.
  • "arrayOfStructs" (from 5.1.0) returns an array of row structs. If columns/headers are specified they will be used as the keys, otherwise key names will be generated in the form columnN.
  • "html" returns the sheet data as a string containing a set of HTML table rows/columns, including the <thead>/<tbody> tags, but excluding the <table> start/end tags.
  • "csv" returns the sheet data as a CSV string with values delimited by commas and qualified by double-quotes. Rows are separated by new lines (CRLF).
• columns string: specify the columns you want to read as a comma-delimited list of ranges. Each value can be either a single number, a range of numbers with a hyphen, e.g. "1,2-5", or an open-ended range, e.g. "2-" (meaning all starting from 2).
• columnNames string OR array: a comma-delimited list or an array of the names to use for the query columns in the order the columns appear in the spreadsheet. Note that specifying columnNames overrides the use of a headerRow for column names. Alias: queryColumnNames.
• headerRow numeric: specify which row is the header to be used for the query column names
• rows string: specify the rows you want to read as a comma-delimited list of ranges. Each value can be either a single number, a range of numbers with a hyphen, e.g. "1,2-5", or an open-ended range, e.g. "2-" (meaning all starting from 2).
• sheetName string: name of the sheet to read OR
• sheetNumber numeric default=1: number of the sheet to read (1 based, not zero-based)
• includeHeaderRow boolean default=false: whether to include the header row from the spreadsheet (NB: the default is the opposite to Adobe ColdFusion 9, which is excludeHeaderRow=false).
• includeBlankRows boolean default=false: whether to include blank rows from the spreadsheet in the query data set. By default blank rows are suppressed.
• fillMergedCellsWithVisibleValue boolean default=false: if the source sheet contains merged cells, set the value of each cell in the merged region to the visible value stored in the top/left-most cell
• includeHiddenColumns boolean default=true: if set to false, columns formatted as "hidden" will not be included when reading into a query
• includeHiddenRows boolean default=true: if set to false, rows formatted as "hidden" will not be included when reading into a query
• includeRichTextFormatting boolean default=false: if set to true, basic font formatting of text within cells will be converted to HTML using <span> tags with inline CSS styles. Only the following formats are supported:
  • bold
  • color
  • italic
  • strikethrough
  • underline
• password string: if supplied the file will be treated as encrypted and the password used to try and open it.
• csvDelimiter string default=",": delimiter to use if reading the file into a CSV string.
• queryColumnTypes string or struct: when reading a spreadsheet into a query, this allows you to specify the column types (see below for details).
• makeColumnNamesSafe boolean default=false: ensure that the CSV column names are safe for use in the resulting query (duplicate free and valid CFML variable names).
• returnVisibleValues boolean default=false: when reading into a query/csv/html, return values as they are visible/formatted in each cell rather than the "raw" values. This mostly affects numbers and dates, where you might for example wish to avoid scientific notation.

Chainable? Yes. If you specify a format then the data will be returned and the chain will end.

Specifying query column types

When you specify query as the format, by default no column types are used because spreadsheets have no such concept (only cells have types). If you wish to specify column types for the query you can use the optional queryColumnTypes argument in one of four ways:

1. Pass the list of types you want in exactly the same way as you would with the CFML function QueryNew()
2. Pass a single type to apply to all columns. Normally you would specify VARCHAR.
3. Specify auto to have the types auto-detected from the values in each spreadsheet column. If they are all one type, that will be used. If they are mixed, VARCHAR will be used. Blank cells are ignored. Note that auto-detection only supports the following types: VARCHAR, DOUBLE (numeric values) and TIMESTAMP (date or datetime values). Any other types will be set to VARCHAR. Be aware also that this option has a performance overhead which may be significant on large sheets.
4. Pass a struct which maps each column name to the desired type. Use the column name as the key and the type as the value (see example below). The names/values don't have to be in the same order as they appear in the sheet. Note that you must also either specify the headerRow in which to find the column names, or supply the columnNames argument (see above). If a header/column is not found in the struct its type will default to VARCHAR.

Related

readLargeFile, processLargeFile, readCSV

Examples

Read a spreadsheet file into a spreadsheet workbook object

filepath = ExpandPath( "report.xls" )
workbook = spreadsheet.read( filepath )

Read a spreadsheet file into an array

columns = [ "firstname", "lastname", "email" ]
data = [ [ "Frumpo", "McNugget", "frumpo@mcnugget.com" ], [ "Susi", "Sorglos", "susi@sorglos.com" ] ]
filepath = ExpandPath( "customers.xlsx" )
// create a spreadsheet file to read
spreadsheet.newChainable( "xlsx" )
  .addRow( columns )
  .addRows( data )
  .write( filepath, true )
// read the file
result = spreadsheet.read( src=filepath, format="array", headerRow=1 )
WriteDump( result )

Read a spreadsheet file into a query

result = spreadsheet.read( src=filepath, format="query" )
WriteDump( result )

Read a spreadsheet file in to an array of structs

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

Read a spreadsheet file into a query and specify the column types as a struct

data = [ [ 1, 1.1, "text", CreateTime( 1, 0, 0 ) ] ]
columns = "integer,double,string column,time"
columnTypes = { "string": "VARCHAR", "integer": "INTEGER", "time": "TIME", "double": "DOUBLE" }
result = spreadsheet.read( src=filepath, format="query", columnTypes=columnTypes, columnNames=columns )