在CF9中的QueryNew（）数据类型 [英] QueryNew() Data Types in CF9

问题描述

我已经接管了运行CF9.0.1的生产服务器上的系统，而我在开发者版本中找不到该文件的副本，因此我正在运行CF10。

我将数据从数据库导出到Excel。因为数据来自多个数据源，所以结果被手动输入到查询中，然后用于输出到Excel。我首先要解决的问题之一是，因为Excel自动键入数据，发生的事情就像尾随零被删除，数字变成日期等。经过大量的研究，我试图指定数据的数据类型进入查询为varchar，以便Excel将其作为文本读取。为此，我用以下代码行代替了原来的QueryNew。

  dataQuery = QueryNew（row_number，function，nomenclature ，varChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar，VarChar），hw，crit，load，sw，media，svd，bds，ecp，install， ; 
  

在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 
  

同样，它在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）; 
 
 
 //行＃1 
 newRow = queryaddRow（dataQuery）; 
 querySetCell（dataQuery，row_number，1）; 
 querySetCell（dataQuery，function，Function 1）; 
 querySetCell（dataQuery，nomenclature，Nomen 1）; 
 querySetCell（dataQuery，hw，185019-001）; //有时轴位为指数
 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）; //有时变成日期
 querySetCell（dataQuery，bds，BDS 1）; 
 querySetCell（dataQuery，ecp，ECP1）; 
 querySetCell（dataQuery，install，Install 1）; 
 querySetCell（dataQuery，notes，Note1）; 
 
 //行＃2 
 newRow = queryaddRow（dataQuery）; 
 querySetCell（dataQuery，row_number，2）; 
 querySetCell（dataQuery，function，Function 2）; 
 querySetCell（dataQuery，nomenclature，Nomen 2）; 
 querySetCell（dataQuery，hw，185019-005）; //有时轴位为指数
 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）; //有时变成日期
 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 =writefilename =c：/CF9ExcelTest.xlsxname =sheetoverwrite =true>感谢您的帮助。
 >解决方案
尝试将列/单元格格式化为文本。请参阅文档中的格式示例，位于 ColdFusion 9.0.1中的增强功能。即
  //格式化单个单元格... 
 SpreadsheetFormatCell（sheet，{dataformat =@} rowNum，columnNum）; 
 //格式化列4和7 
 SpreadsheetFormatColumns（sheet，{dataformat =@}，4,7）
  
不幸的是，CF9.x中的一些电子表格函数有点古怪，所以我不知道这是否可以与 SpreadsheetAddRows 。如果没有，您可能需要求助于一个循环，并在其格式和单独分配单元格值：
  ... 
 SpreadsheetFormatCell（sheet，{dataformat =@}，rowNum，columnNum）; 
 SpreadSheetSetCellValue（sheet，some value，rowNum，columnNum）; 
 ... 
  
 
I have taken over a system that is on a production server that is running CF9.0.1 and I cannot find a copy of that in the developer's edition, so I am running CF10.

I am exporting data from the database to Excel.  Because the data comes from multiple datasources, the results are manually entered into a query that is then used to output to Excel.  One of my first problems to solve was that, because Excel automatically types data, wierd things were happening like trailing zeros being dropped, numbers turned into dates, etc.  After a lot of research, I tried specifying the datatypes of the data going into the query as "varchar" so that Excel would read it as text.  To do this, I replaced the original QueryNew with the following line of code.
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");
That worked great on CF10.  Then, it got posted to production with CF9 and it didn't solve anything.  Excel is still not receiving the data as a text type and is autoformatting it. So, I tried the following instead.
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");
Again, it didn't work on CF9, but is great on CF10.

Is there something about CF9 that I am missing that is making this not work?  Any help would be wonderful!

Sorry for not doing this sooner.  This is sample code that exactly shows the problem I'm having.  This exports to Excel perfectly on CF10, but has problems on 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" > 
Thank you for any help.
解决方案
Try formatting the column/cells as text first. See the format example in the docs, under Enhancement in ColdFusion 9.0.1. ie 
// Format an individual cell  ...
SpreadsheetFormatCell(sheet, {dataformat="@"}, rowNum, columnNum); 
// Format columns 4 and 7
SpreadsheetFormatColumns(sheet, {dataformat="@"}, "4,7")
Unfortunately some of the spreadsheet functions in CF9.x were a bit quirky, so I am not sure if that will work in combination with SpreadsheetAddRows. If not, you may need to resort to a loop and within it format and assign the cell values individually:
   ...
   SpreadsheetFormatCell(sheet, {dataformat="@"}, rowNum, columnNum); 
   SpreadSheetSetCellValue(sheet, "some value", rowNum, columnNum);
   ...


                        
这篇关于在CF9中的QueryNew（）数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！