addRows - cfsimplicity/spreadsheet-cfml GitHub Wiki

Adds multiple rows from a query to a spreadsheet.

addRows( workbook, data [, row [, column [, insert [, autoSizeColumns [, includeQueryColumnNames [, ignoreQueryColumnDataTypes [, datatypes ] ] ] ] ] ] ] )

Required arguments

  • workbook spreadsheet object
  • data query OR array of arrays

Optional arguments

  • row numeric: Row number at which to insert or replace the data. When inserting, existing rows below will be shifted down. If you omit this value, the rows are inserted after the last row in the sheet.
  • column numeric: The column at which to add the row data. Columns to the left of this column will be empty.
  • insert boolean default=true: whether to insert the new data or replace existing values.
  • autoSizeColumns boolean default=false: whether to adjust the column widths automatically to fit the data added (see Note 2 below).
  • includeQueryColumnNames boolean default=false: whether to include the query column names as the first of the rows being added.
  • ignoreQueryColumnDataTypes boolean default=false: when passing data as a query, whether to ignore the query column data types (e.g. VarChar) and auto-detect each cell type from the value.
  • datatypes struct: specify data types as keys and the columns they should apply to in your data as an array of column names or (positional) numbers. These types will override the query column types or auto-detection where possible. Possible data type keys are: string, numeric, date, time, boolean, auto. See examples below. More details about data type handling

Chainable? Yes.

Notes

  1. Binary (.xls) spreadsheets only allow a maximum of 65535 rows. For XML (.xlsx) files the limit is much higher. See the POI documentation for more details.
  2. Using autoSizeColumns with streaming xlsx workbooks requires a lot of extra processing which can affect performance.

Example 1: passing data as a query

data = QueryNew( "First,Last", "VarChar,VarChar", [ [ "Susi","Sorglos" ], [ "Frumpo","McNugget" ] ] );
spreadsheet = New spreadsheet();
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, data );

Example 2: passing data as an array of arrays

data = [ [ "Susi","Sorglos" ], [ "Frumpo","McNugget" ] ];
spreadsheet = New spreadsheet();
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, data );

Example 3: Overriding query data types using column names

data = QueryNew( "Number,Date,String,Time,Boolean", "VarChar,VarChar,BigInt,VarChar,VarChar", [ [ "01234", "2020-08-24", 1234567890123456, "2020-08-24 09:15:00", "yes" ] ] );
datatypes = { numeric: [ "Number" ], date: [ "Date" ], string: [ "String" ], time: [ "Time" ], boolean: [ "boolean" ] };
spreadsheet = New spreadsheet();
workbook = spreadsheet.new();
spreadsheet.addRows( workbook=workbook, data=data, datatypes=datatypes );

Example 4: Overriding array data types using column numbers

data = [ [ "01234", 1234567890123456 ] ];
datatypes = { numeric: [ 1 ], string: [ 2 ] }
spreadsheet = New spreadsheet();
workbook = spreadsheet.new();
spreadsheet.addRows( workbook=workbook, data=data, datatypes=datatypes );