excel - cfspreadsheet 能否在不将它们更改为空白的情况下从查询中输出空值？

Question

我正在运行 SQL 查询来获取一些数据。然后使用 CF 的电子表格功能将其导出为 Excel 文件。问题是查询中为空的单元格在其中得到了一个非空字符。当您打开电子表格时，它们看起来是空白的，但事实并非如此。它特别干扰了使用 Excel 的 ctrl-arrow 功能来查找下一个非空白单元格。

Field looks null but actually isn't
在数据库中，如果没有值，则颜色列为空。在 Excel 中，ctrl-downarrow 应该将您带到单元格 D9 或“蓝色”，但它不会。它将带您一直到列的底部。

如果在 Excel 中，我转到每个“空白”单元格，然后按删除键，则功能返回。很明显，Coldfusion 没有正确处理它。

我将其缩小到 Coldfusion，因为如果我在 SSMS 中运行相同的查询，并将数据从那里剪切并粘贴到 Excel 中，它会保留空值，并且 ctrl-downarrow 可以正常工作。

<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">

使用的查询实际上是无关紧要的，因为我可以使用任何返回其中一些字段为 null 的查询来重现该问题。

Answer 1

我最终使用了多种方法来实现它。循环查询并为每一列使用 SpeadSheetSetCellValue，并使用 if 语句检查该列是否为空。如果它为空，我根本没有填充该列。现在，这行得通。

感谢大家的评论，感谢@Ageax 引导我走向最终解决方案。

这是我改编自 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>