2

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.

Heather
  • 45
  • 4
  • I run cf9. What are some sample values that cause problems? – Dan Bracuk Sep 10 '14 at 18:59
  • Yes, please provide some sample values for testing. – Sks Sep 10 '14 at 19:02
  • Can you post a small repro case (including values)? I do not have access to 9.0.1 right now, but remember there were several format/data type bugs with the various tags and functions. Some were fixed in the various updates between 9.0->9.0.1->9.0.2. Others required some "creative" (ie convoluted) work-arounds. So there may be an existing work-around if we could see the code. – Leigh Sep 10 '14 at 19:22
  • I can't post exact code for various reasons. Let me work up a model of it. I'll post it soon. – Heather Sep 10 '14 at 23:17
  • I posted some sample code and identified the lines with values that will cause problems. As an additional note, I was able to install 9.0.2 locally and on production I have 9.0.1. – Heather Sep 10 '14 at 23:36
  • 1
    Side note, AFAIK 9.0.1 is not available anymore. Their contract with Verity expired. Since 9.0.1 still includes Verity, [they are not allowed to offer it for download anymore](http://blogs.coldfusion.com/post.cfm/availability-of-coldfusion-9). (Would be nice if they mentioned that on the old download pages, so people aren't chasing their tail following old links that go nowhere ...) – Leigh Sep 11 '14 at 00:28
  • 1
    (Edit) It has been a while and I cannot recall whether CF or Excel is responsible for the conversion stuff. Try formatting the column/cells as text first. See the example in the docs, under [Enhancement in ColdFusion 9.0.1](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6747.html) ie `{dataformat="@"}`. Not sure if works in combination with `spreadsheetAddRows`. If not, you may need to resort to a loop and `spreadSheetSetCellValue`. – Leigh Sep 11 '14 at 00:38
  • I was just starting to work on looping through and manually adding the rows and cells to the spreadsheet. I found that to be the only way to format the spreadsheet cell before adding the data and doing it after has no effect at all. I was hoping for something more elegant and efficient, but its good to know that's a valid option and I'm not way out in left field. – Heather Sep 11 '14 at 00:45
  • Unfortunately that was one of the quirks I remember about spreadsheet functions in 9.x. Good to hear they fixed it in CF10, but I think that is as good as it gets for 9.x. – Leigh Sep 11 '14 at 01:41
  • 1
    I think some of the 9.0.1 downloads are still available, e.g. look here http://web.archive.org/web/20120314103840/http://www.adobe.com/support/coldfusion/downloads_updates.html – duncan Sep 11 '14 at 08:14

3 Answers3

2

See this previous question for an approach that should work in CF9:

  1. populate the sheet from your query (the values will be incorrect)
  2. set the correct formatting on the problematic cells (the incorrect values won't change) then
  3. loop over the query and re-insert the problematic values using SpreadsheetSetCellValue() (they will now be in the correct format)

More detail and code to make this easier is available at http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text

Community
  • 1
  • 1
CfSimplicity
  • 2,338
  • 15
  • 17
  • @Heather - I know you said pre-formatting the cell worked for you. However, as noted earlier, there were some differences in behavior between 9.0->9.0.1-9.0.2. So if ultimately does not work in your prod environ, this approach should. (It is one of the creative workarounds I was talking about earlier, to get around the quirkiness of spreadsheet functions in CF9.x). – Leigh Sep 13 '14 at 19:37
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);
   ...
Leigh
  • 28,765
  • 10
  • 55
  • 103
1

If Leigh's option fails you could try generating the Excel file in a different way. You can create an xml file and save it for excel. This way you can set the value and data type for each cell or column. Below just a very simple example.

<cfsavecontent variable="test">
<?xml version="1.0"?>
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Worksheet ss:Name="Sheet1">
    <ss:Table>
        <ss:Column ss:Width="80"/>
        <ss:Column ss:Width="80"/>
        <ss:Column ss:Width="80"/>
        <ss:Row>
            <ss:Cell>
                <ss:Data ss:Type="String">185019-001</ss:Data>
            </ss:Cell>
            <ss:Cell>
                <ss:Data ss:Type="String">0.06200</ss:Data>
            </ss:Cell>
            <ss:Cell>
                <ss:Data ss:Type="String">6529-02</ss:Data>
            </ss:Cell>
        </ss:Row>
    </ss:Table>
</ss:Worksheet>
</ss:Workbook>
</cfsavecontent>

<cffile action="write" file="c:/CF9ExcelTest.xls"  output ="#test#"> 
Nebu
  • 1,753
  • 1
  • 17
  • 33