cf电子表格可以从查询中输出空值而不将其更改为空值吗

Question

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

字段看起来为空，但实际上不是在数据库中，如果没有值，则颜色列为null。在Excel中，ctrl向下箭头应该会将您带到单元格D9或"蓝色"，但它没有。它会把你一直带到柱子的底部。

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

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

<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语句检查列是否为null。如果它为null，那么我根本没有填充该列。现在，这是有效的。

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

这是我从这篇文章中改编的最后一段代码(不包括查询，这无关紧要(：

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