Data type handling

Spreadsheet cells store their data as one of the following types: STRING, NUMERIC, BOOLEAN, BLANK and FORMULA (date values are stored as the number of days since 1 January 1900 and are therefore NUMERIC).

When adding data to workbooks using the Spreadsheet Library, the cell type is determined in one of two ways by default.

1. If you are adding data as a list or an array the cell type will be auto-detected from the individual value being set. So if the value of row 1 column 1 is 5, the cell type will be set as NUMERIC. If row 2 column 1's value is "Frumpo McNugget", the cell type will be STRING.

2. If you are adding data by passing in a query object, then the library will attempt to set the cell types in each column according to the query's column types, such as VarChar, Integer, Timestamp etc.

Ignoring query column types

If you are passing in a query, but you do not want the cell types in each column to be determined by the query column types, then you can use the ignoreQueryColumnDataTypes argument to force all values to be auto-detected.

data = QueryNew( "Number,Date", "VarChar,VarChar", [ [ "1234", "2020-08-24" ] ] );
spreadsheet.addRows( workbook=workbook, data=data, ignoreQueryColumnDataTypes=true );

In this case, the cell types will both be NUMERIC instead of STRING.

ignoreQueryColumnDataTypes is available to any method which accepts data as a query object.

Overriding data types

If you want more fine grained control over cell types, you can use the datatypes argument to specify how types should be set for particular columns, overriding the auto-detected and query column determined types.

For example, say you have an array of data with a column containing numbers, some of which have leading zeros. The auto-detection will set the cell type for the leading zero numbers to be STRING, so that the zeros are preserved. But if you really want the values treated as numbers with the leading zeros stripped, you could specify this as follows:

data = [ [ "01234" ], [ "05678" ] ];
spreadsheet.addRows( workbook=workbook, data=data, datatypes={ numeric: [ 1 ] } );

Another example: say your data is a query object and you are happy with most of the query column types, but there is a BigInt column called "LongNumber" that you don't want to fall victim to Excel's 15 digit limit. You can force just that column's type to be overridden and set as a string as follows:

data = QueryNew( "Firstname,Lastname,LongNumber", "VarChar,VarChar,BigInt", [ [ "Frumpo", "McNugget", 1234567890123456 ] ];
spreadsheet.addRows( workbook=workbook, data=data, datatypes={ string: [ "LongNumber" ] } );

Note though that if the columns you are overriding contain values which can't be cast to the specified type, then the type for those particular values will be auto-detected.

The datatypes argument is available to any method which allows data to be added in rows to a workbook. Columns can be specified against the following types:

• string
• numeric
• date
• time
• boolean
• auto

Hyperlinks

From version 3.8.0 you can also use the following as datatypes:

• url
• email
• file

Values in the specified columns will be automatically converted to clickable hyperlinks.