Skip to main content
B
biene22
Inspiring
July 18, 2011
Answered

cfspreadsheet action="write" sophisticates values

  • July 18, 2011
  • 1 reply
  • 1365 views

Hi,

I have a problem writing data to an excel file using cfspreadsheet.

My query result contains for example values like '41D', but in the excelfile it is ''41'.

Here is my example code:

<cfquery  name="select_test" datasource="test">
select '41D' test from dual
</cfquery>

<cfdump  var="#select_test#">

<cfset sObj=SpreadsheetNew()>

<cfset SpreadsheetAddRows(sObj, select_test)>

<cfspreadsheet action="write" name="sObj" filename="testfile.xls" overwrite="true" >

<cfspreadsheet action="read"  src="testfile.xls" query="queryvar">
<cfdump  var="#queryvar#">

I already tried to format the column to text, but it doesn't help.

Environment: ColdFusion 9 Standard, Database Oracle 11g, Microsoft Office 2003

What's wrong with my code?

Regards Claudia

    This topic has been closed for replies.
    Correct answer Owainnorth

    I've never used the Spreadsheet functions, so thought I'd have a play. It seems the issue is that doing a SetRowFormat() or similar does as it says, but to the row sequentially after the one being worked on. Therefore chances are you're not formatting the cells you think you are.

    If you specifically format a cell, then set its value to be that from the database, it works:

    <cfset spreadsheetFormatCell(sObj, sFormat, 3, 1) />

    <cfset spreadsheetSetCellValue(sObj,select_test.test[1],3,1) />

    And displays 41D as it should. I haven't really looked into the formatting functions that much, but I suspect that's the issue - you're not formatting the cells you think you are.

    I reserve the right to be completely wrong and pointing you down some kind of blind alley

    1 reply

    Owainnorth
    OwainnorthCorrect answer
    Inspiring
    July 18, 2011

    I've never used the Spreadsheet functions, so thought I'd have a play. It seems the issue is that doing a SetRowFormat() or similar does as it says, but to the row sequentially after the one being worked on. Therefore chances are you're not formatting the cells you think you are.

    If you specifically format a cell, then set its value to be that from the database, it works:

    <cfset spreadsheetFormatCell(sObj, sFormat, 3, 1) />

    <cfset spreadsheetSetCellValue(sObj,select_test.test[1],3,1) />

    And displays 41D as it should. I haven't really looked into the formatting functions that much, but I suspect that's the issue - you're not formatting the cells you think you are.

    I reserve the right to be completely wrong and pointing you down some kind of blind alley

    B
    biene22Author
    Inspiring
    July 18, 2011

    Yes, that works.

    I tested it in reverse order, first set the cell value and in the second step format the cell.

    And that was my mistake.

    Thank you very much Owain!

    Best regards

    Claudia

    Owainnorth
    Owainnorth
    Inspiring
    July 18, 2011

    Not a problem, that's something I'll remember if I ever need to do the same.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices