#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0

    Cfspreadsheet and leading zeros in cells


    Hi guys,

    i'm trying to export a query into an xls file. everything is working as it should and i get the xls file but there is a problem with a column that contains leading zeros ( i.e 022334).

    I want to keep those zeros but it appears cfspreadsheet creates a new xls file with a cell format of "General" so that removes the leading zeros.

    using SpreadsheetFormatColumns to change the dataformat to "text" doesn't help since the leading zeros have already dissapeared.

    any thoughts on how to fix this problem.?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    This one I'm afraid I can't help with, as I haven't used cfspreadsheet much. If no one else can chime in, I'd try Google or possibly the CF forums at Adobe.com.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2001
    Location
    Maryland
    Posts
    74
    Rep Power
    13
    I would convert the field to text in your query before sending the rs to cfspreadsheet. This works especially well with date fields, I have an oracle backend and formatting dates using cfspreadsheet function was a crap shoot as to the output. formatting the data in the rs cleared that up.

    hope this helps
    J. Birdsell,
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0
    @jbird4k what exactly do you mean by "convert the field to text in your query ".?

    The field is already a text field .The issue appears when storing into the xls file for the reason i mentioned above ( General dataformat vs Text in Excel).

    I have found this link http://cfsimplicity.com/16/forcing-values-to-be-inserted-into-spreadsheets-as-text but i have not tested it yet.

    I'll let you know when i do.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    13
    Rep Power
    0
    Update: Ok guys. everything worked using the above solution.

    It's not the best solution since you
    save your query in the excel file.
    change the column in the excel that contains the leading zeros dataformat to text.
    re-write everything using SpreadsheetSetCellValue in order to avoid reusing cfspreadsheet.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2008
    Posts
    131
    Rep Power
    7
    Unfortunately that may be as good as it gets. CF9 has a several formatting related bugs, some of which were fixed in 9.0.1. FWIW, CF10 does preserve the zeroes:

    Code:
    <cfset qry = queryNew("")>
    <cfset queryAddColumn(qry, "MyColumn", "varchar", listToArray("02222,01111"))>
    <cfset sheet = spreadSheetNew()>
    <cfset SpreadsheetFormatColumn(sheet,{dataFormat="@"},1)>
    <cfset SpreadSheetAddRows(sheet, qry)>
    <cfset SpreadSheetWrite(sheet, "c:/path/to/someFile.xls", true)>

IMN logo majestic logo threadwatch logo seochat tools logo