exporting webpage to excel formatting problem

Discussion in 'ASP General' started by Jeremy, Jul 25, 2003.

  1. Jeremy

    Jeremy Guest

    Hi, I have a webpage that is taking input from a form and using it as
    criteria to select data out of an sql database. The page displays an
    html table with the results. The user can then click a button "excel"
    to open or save the file as an excel document. This works, and
    everything in the excel document is formatted fine except for the
    first column which in this case is "Part Number". The problem is that
    this part number: 883100105100 is displayed like this: 8.831E+11 in
    excel. I am not sure how to format the excel cells using the asp/html
    so that it doesn't try to throw everything into scientific notation.
    I have thought that maybe if I gave excel a string value instead of a
    number value then it would fix the problem but I am pretty new to asp
    and am not sure how to do this. Below is the block that throws
    everything into excel in the first place. Any help would be great,
    thank you!

    <%
    'Change HTML header to specify Excel's MIME content type

    Response.Buffer = TRUE
    Response.ContentType = "application/vnd.ms-excel"
    %>
     
    Jeremy, Jul 25, 2003
    #1
    1. Advertising

  2. Jeremy

    Ray at Guest

    Precede the values with a ' character, like, instead of 883100105100, make
    it so it's '883100105100.

    Ray at home

    --
    Will trade ASP help for SQL Server help


    "Jeremy" <> wrote in message
    news:...
    > Hi, I have a webpage that is taking input from a form and using it as
    > criteria to select data out of an sql database. The page displays an
    > html table with the results. The user can then click a button "excel"
    > to open or save the file as an excel document. This works, and
    > everything in the excel document is formatted fine except for the
    > first column which in this case is "Part Number". The problem is that
    > this part number: 883100105100 is displayed like this: 8.831E+11 in
    > excel. I am not sure how to format the excel cells using the asp/html
    > so that it doesn't try to throw everything into scientific notation.
    > I have thought that maybe if I gave excel a string value instead of a
    > number value then it would fix the problem but I am pretty new to asp
    > and am not sure how to do this. Below is the block that throws
    > everything into excel in the first place. Any help would be great,
    > thank you!
    >
    > <%
    > 'Change HTML header to specify Excel's MIME content type
    >
    > Response.Buffer = TRUE
    > Response.ContentType = "application/vnd.ms-excel"
    > %>
     
    Ray at, Jul 25, 2003
    #2
    1. Advertising

  3. Jeremy

    Tim Williams Guest

    If you have XL2k or greater, format a few cells on a worksheet as text,
    select them, and save the selection as HTML.

    This will help you construct a style sheet to format the cells

    eg:

    <style>
    <!--table
    ..xlText {mso-number-format:"\@";}
    -->
    </style>


    Then use

    <td class="xlText">883100105100</td>


    Tim.



    "Jeremy" <> wrote in message
    news:...
    > Hi, I have a webpage that is taking input from a form and using it as
    > criteria to select data out of an sql database. The page displays an
    > html table with the results. The user can then click a button "excel"
    > to open or save the file as an excel document. This works, and
    > everything in the excel document is formatted fine except for the
    > first column which in this case is "Part Number". The problem is that
    > this part number: 883100105100 is displayed like this: 8.831E+11 in
    > excel. I am not sure how to format the excel cells using the asp/html
    > so that it doesn't try to throw everything into scientific notation.
    > I have thought that maybe if I gave excel a string value instead of a
    > number value then it would fix the problem but I am pretty new to asp
    > and am not sure how to do this. Below is the block that throws
    > everything into excel in the first place. Any help would be great,
    > thank you!
    >
    > <%
    > 'Change HTML header to specify Excel's MIME content type
    >
    > Response.Buffer = TRUE
    > Response.ContentType = "application/vnd.ms-excel"
    > %>
     
    Tim Williams, Jul 25, 2003
    #3
  4. Jeremy

    Jeremy Guest

    Hi Ray, thanks for the suggestion, but it didn't work. . it no longer
    displays the number in scientific notation but now it displays it like
    this: '883100105100 . . .it doesn't hide the apostrophe. Though the
    strange thing is, once I'm in excel if I delete the apostrophe and
    type it in myself, then it hides it. I guess excel doesn't like
    apostrophe's from html :)

    Thanks Again,

    Jeremy


    "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message news:<>...
    > Precede the values with a ' character, like, instead of 883100105100, make
    > it so it's '883100105100.
    >
    > Ray at home
    >
    > --
    > Will trade ASP help for SQL Server help
    >
    >
    > "Jeremy" <> wrote in message
    > news:...
    > > Hi, I have a webpage that is taking input from a form and using it as
    > > criteria to select data out of an sql database. The page displays an
    > > html table with the results. The user can then click a button "excel"
    > > to open or save the file as an excel document. This works, and
    > > everything in the excel document is formatted fine except for the
    > > first column which in this case is "Part Number". The problem is that
    > > this part number: 883100105100 is displayed like this: 8.831E+11 in
    > > excel. I am not sure how to format the excel cells using the asp/html
    > > so that it doesn't try to throw everything into scientific notation.
    > > I have thought that maybe if I gave excel a string value instead of a
    > > number value then it would fix the problem but I am pretty new to asp
    > > and am not sure how to do this. Below is the block that throws
    > > everything into excel in the first place. Any help would be great,
    > > thank you!
    > >
    > > <%
    > > 'Change HTML header to specify Excel's MIME content type
    > >
    > > Response.Buffer = TRUE
    > > Response.ContentType = "application/vnd.ms-excel"
    > > %>
     
    Jeremy, Jul 25, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Luis Esteban Valencia
    Replies:
    1
    Views:
    1,405
    Carl Prothman [MVP]
    Jan 12, 2005
  2. =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=

    Problem with Excel reports ::::Excel 2003 Migration To Excel 2007

    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=, Oct 5, 2007, in forum: ASP .Net
    Replies:
    15
    Views:
    1,548
    =?Utf-8?B?c2hhc2hhbmsga3Vsa2Fybmk=?=
    Oct 24, 2007
  3. Doogie
    Replies:
    1
    Views:
    461
    sloan
    Nov 19, 2008
  4. John Walker

    Column Formatting When Exporting to Excel

    John Walker, Nov 16, 2008, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    173
    John Walker
    Nov 16, 2008
  5. Replies:
    0
    Views:
    259
Loading...

Share This Page