read

Reads a spreadsheet file into one of the following:

• a spreadsheet object;
• a CFML query object;
• a HTML string;
• a CSV string.

read( src[, format, columns, columnNames, headerRow, rows, sheetName, includeHeaderRow, includeBlankRows, fillMergedCellsWithVisibleValue, includeHiddenColumns,includeRichTextFormatting ] )

or

read( src[, format, columns, columnNames, headerRow, rows, sheetNumber, includeHeaderRow, includeBlankRows, fillMergedCellsWithVisibleValue, includeHiddenColumns,includeRichTextFormatting ] )

##Required arguments

• src string: full path to the file to read

##Optional arguments

• format string: "query", "html" or "csv". If omitted, returns a spreadsheet object.
• "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 or a range of numbers with a hyphen, e.g. "1,2-5"
• columnNames string: a comma-delimited list of the names to use for the query columns in the order the columns appear in the spreadsheet. If you specify fewer names than the total number of columns, the remaining column names will be in the form columnX. Note that specifying columnNames overrides use of a headerRow for column names.
• 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 or a range of numbers with a hyphen, e.g. "1,2-5"
• 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
• 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

##Examples ###Read a spreadsheet file into a query

spreadsheet = New spreadsheet();
filepath = ExpandPath( "report.xls" );
myQuery = spreadsheet.read( src=filepath,format="query" );

###Read a spreadsheet file into a spreadsheet object

spreadsheet = New spreadsheet();
filepath = ExpandPath( "report.xls" );
mySpreadSheetObject = spreadsheet.read( filepath );