我接管了运行 CF9.0.1 的生产服务器上的系统,但在开发人员版本中找不到该系统的副本,因此我正在运行 CF10。
我正在将数据从数据库导出到 Excel。由于数据来自多个数据源,因此需要手动将结果输入到查询中,然后用于输出到 Excel。我要解决的第一个问题是,因为 Excel 自动键入数据,所以会发生奇怪的事情,例如删除尾随零、数字变成日期等。经过大量研究,我尝试指定进入 Excel 的数据的数据类型。查询为“varchar”,以便 Excel 将其读取为文本。为此,我用以下代码行替换了原始的 QueryNew。
dataQuery = QueryNew("row_number,function,nomenclature,hw,crit,load,sw,media,svd,bds,ecp,install,notes", "VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar, VarChar");
这在 CF10 上效果很好。然后,它被发布到 CF9 的生产中,但没有解决任何问题。 Excel 仍然没有接收文本类型的数据,并且正在自动设置格式。所以,我尝试了以下方法。
dataQuery = QueryNew("row_number,function,nomenclature,hw,crit,load,sw,media,svd,bds,ecp,install,notes", "CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR, CF_SQL_VARCHAR");
同样,它在 CF9 上不起作用,但在 CF10 上却很棒。
CF9 是否有我遗漏的东西导致此功能无法正常工作?任何帮助都会很棒!
很抱歉没有早点这样做。这是示例代码,准确地显示了我遇到的问题。这在 CF10 上完美导出到 Excel,但在 CF9 上有问题。
<cfscript>
dataQuery = QueryNew("row_number,function,nomenclature,hw,crit,load,sw,media,svd,bds,ecp,install,notes", "VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,VarChar");
//Row #1
newRow = queryaddRow(dataQuery);
querySetCell(dataQuery,"row_number","1");
querySetCell(dataQuery,"function","Function 1");
querySetCell(dataQuery,"nomenclature","Nomen 1");
querySetCell(dataQuery,"hw","185019-001"); //Sometimes axports as an exponent
querySetCell(dataQuery,"crit","2");
querySetCell(dataQuery,"load","Load 12B RL");
querySetCell(dataQuery,"sw","0.0620"); //This one get the trailing 0 left off
querySetCell(dataQuery,"media","Media 1");
querySetCell(dataQuery,"svd","6529-02"); // Sometimes turned into a date
querySetCell(dataQuery,"bds","BDS 1");
querySetCell(dataQuery,"ecp","ECP1");
querySetCell(dataQuery,"install","Install 1");
querySetCell(dataQuery,"notes","Note1");
//Row #2
newRow = queryaddRow(dataQuery);
querySetCell(dataQuery,"row_number","2");
querySetCell(dataQuery,"function","Function 2");
querySetCell(dataQuery,"nomenclature","Nomen 2");
querySetCell(dataQuery,"hw","185019-005"); //Sometimes axports as an exponent
querySetCell(dataQuery,"crit","2");
querySetCell(dataQuery,"load","Load 12B RL");
querySetCell(dataQuery,"sw","0.06200"); //This one get the trailing 0 left off
querySetCell(dataQuery,"media","Media 2");
querySetCell(dataQuery,"svd","6529-03"); // Sometimes turned into a date
querySetCell(dataQuery,"bds","BDS 2");
querySetCell(dataQuery,"ecp","ECP 2");
querySetCell(dataQuery,"install","Install 2");
querySetCell(dataQuery,"notes","Note2");
sheet= spreadSheetNew("New", "true");
spreadsheetAddRows(sheet,dataQuery);
</cfscript>
<cfspreadsheet action="write" filename="c:/CF9ExcelTest.xlsx" name="sheet" overwrite="true" >
感谢您的任何帮助。