11 Replies Latest reply on Feb 2, 2007 12:01 AM by Rouset

    Export to Excel formating problem

      I am having a problem with the formatting of a an export file to Excel, which is created by a Coldfusion page. My export file contains numbers that need to be formatted as text. I found the style code below on this forum, but I am not having any luck with it. I tried adding it to my style file or directly as style formatting on the export page, but neither one of the two are getting recognized.

      Is there anything else I need to code before this works? Your help is greatly appreciated. Thanks,

      Rose

      <html>
      <STYLE TYPE="text/css">
      TD {
      mso-number-format:\@;
      }
      </style><body>

      <!---<cfheader name="Content-Disposition" value="inline; filename=Exportdata1.xls">--->
      <cfcontent type="application/vnd.msexcel">
      <cfheader name="Content-Disposition" value="inline; filename=export.xls">
      <cfquery name = "getclub" datasource="#DSN#">
      Select mail_name name,…
      </cfquery>
      <table width="200" border="2">
      <cfoutput query='getclub'> <tr>
      <td>#customer#</td>

      <td>#dateformat(begin_date,'mm/dd/yyyy')# - #dateformat(end_date,'mm/dd/yyyy')#</td>

      </tr></cfoutput>

      </table>

        • 1. Re: Export to Excel formating problem
          insuractive Level 3
          Could it be a case sensitivity issue? I also noticed that you are missing a <head> tag pair, and that your date range field is not actually numeric. I doubt that excel is that delicate when it comes to those issues, but you might try:

          <html><head>
          <STYLE TYPE="text/css">
          TD.number {
          mso-number-format:\@;
          }
          </style></head><body>

          <cfcontent type="application/vnd.msexcel">
          <cfheader name="Content-Disposition" value="inline; filename=export.xls">
          <cfquery name = "getclub" datasource="#DSN#">
          Select mail_name name,…
          </cfquery>
          <table width="200" border="2">
          <cfoutput query='getclub'> <tr>
          <td class="number">#customer#</td>

          <td>#dateformat(begin_date,'mm/dd/yyyy')# - #dateformat(end_date,'mm/dd/yyyy')#</td>

          </tr></cfoutput>

          </table>
          </body>
          </html>
          • 2. Re: Export to Excel formating problem
            Dan Bracuk Level 5
            Try prepending a single quote to your numbers.
            • 3. Re: Export to Excel formating problem
              Level 1

              Thanks a bunch, Michael.

              I am still having the same problem. If I understand your rec. correctly, you have flagged the records to be formatted as numbers.

              The problem I have is that the data values such as 0000456786987 get displayed as a numeric value (456786987) when the export file is opened in Excel. Or, this number 27572587298572 is displayed as 2.75726E+13
              This is particularly a problem with long international phone numbers. So, I need to format the values in those columns as ***text***, so that the data is displayed as is, when the file is opened in Excel.

              Let me know if you have any thoughts on how to pass a flag that would TEXT format the cells instead.

              Thanks,
              Ros
              • 4. Re: Export to Excel formating problem
                Level 1
                Hi Dan,

                I tried your suggested as my initial attempt. It fixes the formatting problem, but it displays the single quote in front of the number. When the single quote is directly entered in the cell, Excel recognizes it as its text formatting flag, but not when the file is created as a table export in CF.

                any other thoughts... this tiny little glitch has really stumbled me for the past few weeks..
                • 5. Re: Export to Excel formating problem
                  Dan Bracuk Level 5
                  Come to think about it, I had the same problem a couple of years ago. Nothing I tried work so eventually I put formatting instructions into the worksheet.
                  • 6. Export to Excel formating problem
                    insuractive Level 3
                    Rouset,
                    I got something to work (sort of) along the lines of what you are trying to do. I created a simple XLS file in the format that I wanted, saved it as an HTM file and took a look at the content. When I import it back into excel, it maintains the correct formatting for the numbers. Below is a trimmed down version of the code I used. Now why it seems to work, I have no idea. But I would recommend creating a sample XLS->HTM document like this for your project, then recreating the HTM code exactly using CFM. Crazy MS Office attributes and all.

                    Hope that helps.

                    • 7. Re: Export to Excel formating problem
                      MikerRoo Level 1
                      Case and quotes are critical in certain areas.
                      See this post:
                      http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=1014953

                      You can also get fine grained control using Apache POI -- but that may be overkill for you.
                      • 8. Re: Export to Excel formating problem
                        Level 1
                        Thanks, Michael.

                        Great minds think a like! :-) I had tried your strategy early on, but did not have any luck. I did notice though that I had missed defining x:124 as a style class.. will give it a try and let you know..

                        MikerRoo, I liked the suggestion in your link, too. will give that a try, too.

                        Thanks, thanks, thanks
                        Ros
                        • 9. Re: Export to Excel formating problem
                          Level 1


                          It worked! Thanks for your help, Ros
                          • 10. Re: Export to Excel formating problem
                            Level 1


                            It worked! Thanks for your help, Ros
                            • 11. Re: Export to Excel formating problem
                              Level 1

                              I have a differen problem now. I am having a STRANGE problem with numbers getting converted to characters.
                              The data file itself contains the data as follows: <td style='mso-number-format:"\@"'>+973-39573722</td> ,
                              However, when I open the file in Excel, it displays it as: 39573722

                              and <td style='mso-number-format:"\@"'>973-17713979</td> as : 973-17713979

                              For some reason it does not like the + in front of +973, eventhough I have formatted the field as a text field. Any idea???

                              Thanks,
                              Ros