QueryNew() Data Types in CF9

Question

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.

Answer 1

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);
   ...