How can I keep empty values ​​from numeric columns

[fvsutils]

Hi!!

How can I keep empty values ​​from numeric columns after spreadsheet.download?
Even though the columns are empty in the query, the xlsx was exported with "0".

If I set ignoreQueryColumnDataTypes=true it's works.

Thank you in advance.

[cfsimplicity]

If I set ignoreQueryColumnDataTypes=true it works

Is there any reason why you can't just do that?

[fvsutils]

not at first. But I asked if anyone knew.

[cfsimplicity]

ignoreQueryColumnDataTypes will override all query column types. If you only want to override a specific column you can use the datatypes argument, for example:

data = QueryNew( "Number1,Number2", "Integer,Integer", [ [ "", 1 ], [ 1, "" ] ] )
datatypes = { string: [ 1 ] } //only force the first column to be treated as a string
workbook = spreadsheet.newXlsx()
spreadsheet
     .addRows( workbook=workbook, data=data, datatypes=datatypes )
     .write( workbook, "c:/temp/test.xlsx", true )

[fvsutils]

in my case it doesn't matter if I override one or all columns.
But it's good to know.

[cfsimplicity]

On reflection I think this should be considered a bug. If your query has empty values then those should be preserved even if the query column is numeric.

#377

Fixed in v4.2.1

https://github.com/cfsimplicity/spreadsheet-cfml/releases/tag/v4.2.1