Can cfspreadsheet output null values from a query without changing them to blanks?

Question

I'm running a SQL query to grab some data. Then using CF's spreadsheet functions to export it as an Excel file. The problem is that cells in the query that are null are getting a non-null character in them. They LOOK blank when you open the spreadsheet, but they are not. And it specifically interferes with the use of Excel's ctrl-arrow functionality to find the next non-blank cell.

Field looks null but actually isn't In the database, the color column is null if there is no value. In Excel, ctrl-downarrow should take you to cell D9 or "blue", but it doesn't. It takes you all the way to the bottom of the column.

If, in Excel, I go to each "blank" cell, and press the delete key, then the functionality returns. So clearly, Coldfusion is not handling it properly.

I narrowed it down to Coldfusion because if I run the same query in SSMS, and cut and paste the data into Excel from there, it preserves the null, and ctrl-downarrow works properly.

<cfquery datasource="test" name="qdata">
    select ID,Name,Email,Color from TestTable
</cfquery>
<cfscript>
        columns =   qdata.getMetaData().getColumnLabels();
        sheet=spreadsheetNew("Sheet1",true);
        spreadsheetAddrows(sheet,qdata,1,1,true,[""],true);
        sheetAsBinary   =   SpreadSheetReadBinary( sheet );
</cfscript>
<cfset  filename    =   "TestFile.xlsx">
<cfheader name="Content-Disposition" value="attachment; filename=#filename#">
<cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" variable="#sheetAsBinary#" reset="true">

The query used is actually irrelevant, as I can reproduce the issue with any query that returns fields with null in some of them.

Answer 1

I ended up using a combination of things to get this to work. Looping over the query and using SpeadSheetSetCellValue for each column, with an if statement checking if the colum was null. If it was null, I just didn't populate that column at all. Now, this works.

Thanks to all for your comments, and to @Ageax for steering me towards my ultimate solution.

This was the final code (excluding the query, which doesn't matter), that I adapted from this post:

<cfsilent>
    <cfscript>
                variables.cont = false;
            /*variables.qdata is the name of my query object*/
                switch(IsQuery(variables.qdata)){
                    case true:
                        variables.cont = true;
                        variables.rqCols = ArrayToList(variables.qdata.getColumnNames(),',');
                        variables.rqLen = ListLen(variables.rqCols,',');
                        variables.thisFileName = "JSM2020ProgramExport-" & DateTimeFormat(now(),'yyyymmdd_HHnnss') & ".xlsx";
                        variables.ssObj = SpreadsheetNew(left(trim(variables.thisFileName),30),'true');/* Setting last argument to 'true' makes this an xlsx, not xls. */
                        variables.format = StructNew();
                        variables.format.font = "Arial";
                        variables.format.textwrap = "true";
                        variables.format.verticalalignment = "VERTICAL_TOP";
            variables.format.dataformat = "text";
            SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen)#");
            SpreadsheetFormatRows(variables.ssObj,variables.format,"1,2");
                        SpreadsheetSetCellValue(variables.ssObj,variables.thisFileName, 1, 1); /* This is the name of the report, top row */
                        SpreadsheetAddFreezePane(variables.ssObj,0,2); /* Freeze top two rows */

                        for(x = 1; x lte val(variables.rqLen); x++){ /* This inserts the column names as row headers */
                            variables.colName = ListGetAt(variables.rqCols,x);
                            SpreadsheetSetCellValue(variables.ssObj,variables.colName,2,x);
                            }

                        for(y = 1; y lte val(variables.qdata.recordCount); y++){ /* This loops the query records */
                            for(x = 1; x lte val(variables.rqLen); x++){ /* This loops each column per recordset */
                                variables.colName = ListGetAt(variables.rqCols,x);
                                variables.thisValue = REreplaceNoCase(variables.qdata[variables.colName][y],"&##59;",";","all"); /* These make sure that no HTML entities are in the data */
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&apos(&##59)?;","'","all");
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&quot(&##59)?;",'"',"all");
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&lt(&##59)?;",'<',"all");
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&gt(&##59)?;",'>',"all");
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&##40(&##59|;)","(","all");
                                variables.thisValue = REreplaceNoCase(variables.thisValue,"&##41(&##59|;)",")","all");
                                if (variables.thisValue is not 'NULL'){SpreadsheetSetCellValue(variables.ssObj,variables.thisValue,val(y + 2),x);}
                                }
                            }
                        SpreadsheetFormatColumns(variables.ssObj,variables.format,"1-#val(variables.rqLen)#");
                        SpreadsheetFormatRows(variables.ssObj,variables.format,"1,2");
                    break;
                    default: /* Do nothing if the query object doesn't exist */
                    break;
                    }

    </cfscript>
</cfsilent>