Exporting html table to excel

Discussion in 'HTML' started by Gobi, Jun 7, 2006.

  1. Gobi

    Gobi Guest

    I have a JSP page which contains a table that I would like to export to
    excel just by clicking a link. I can do this by specifying:

    contentType="application/vnd.ms-excel"

    This automatically opens excel and imports the html table directly into
    it. This is fine for the most part. What my problem is, one of the
    columns has IDs that has zeros in front (eg. 00001). In the html table,
    it is displaying fine. But in the excel table it strips the zeros so
    now my id is 1 when in fact I still want to display it as 00001. Any
    ideas on how to do this? TIA.
     
    Gobi, Jun 7, 2006
    #1
    1. Advertising

  2. Gazing into my crystal ball I observed Gobi <> writing
    in news:RXthg.252277$P01.133600@pd7tw3no:

    > I have a JSP page which contains a table that I would like to export
    > to excel just by clicking a link. I can do this by specifying:
    >
    > contentType="application/vnd.ms-excel"
    >
    > This automatically opens excel and imports the html table directly
    > into it. This is fine for the most part. What my problem is, one of
    > the columns has IDs that has zeros in front (eg. 00001). In the html
    > table, it is displaying fine. But in the excel table it strips the
    > zeros so now my id is 1 when in fact I still want to display it as
    > 00001. Any ideas on how to do this? TIA.
    >


    You could try putting ' infront of the data. That _might_ help.

    --
    Adrienne Boswell at Home
    Arbpen Web Site Design Services
    http://www.cavalcade-of-coding.info
    Please respond to the group so others can share
     
    Adrienne Boswell, Jun 7, 2006
    #2
    1. Advertising

  3. Gobi

    Gobi Guest

    Adrienne Boswell wrote:
    >
    > You could try putting ' infront of the data. That _might_ help.
    >


    Actually, I tried putting " " + ID and excel still strips the zeros.
    This is one time when excel has outsmarted itself. I looked at Options
    in excel to try to turn this feature off but can't find anything.

    Any other ideas, anybody?
     
    Gobi, Jun 7, 2006
    #3
  4. Gobi

    Mark Parnell Guest

    Deciding to do something for the good of humanity, Gobi
    <> declared in alt.html:

    > What my problem is, one of the
    > columns has IDs that has zeros in front (eg. 00001). In the html table,
    > it is displaying fine. But in the excel table it strips the zeros so
    > now my id is 1 when in fact I still want to display it as 00001.


    Adrienne is on the right track. You need to somehow tell Excel to treat
    the cell value as a string instead of an integer. If putting quotes
    around it doesn't help though, I don't know how you'd achieve that.

    --
    Mark Parnell
    My Usenet is improved; yours could be too:
    http://blinkynet.net/comp/uip5.html
     
    Mark Parnell, Jun 7, 2006
    #4
  5. Gobi

    Gobi Guest

    Mark Parnell wrote:
    > Deciding to do something for the good of humanity, Gobi
    > <> declared in alt.html:
    >
    > Adrienne is on the right track. You need to somehow tell Excel to treat
    > the cell value as a string instead of an integer. If putting quotes
    > around it doesn't help though, I don't know how you'd achieve that.
    >


    Actually, I put a space in front of the data instead of quotes. I was
    hoping for a solution where I don't have to modify the data. I am
    thinking maybe I can somehow inform excel that this column is a string.
    Perhaps something in the <tr>? Or some way to turn off
    auto-formatting in excel?
     
    Gobi, Jun 7, 2006
    #5
  6. Gobi wrote:
    > I have a JSP page which contains a table that I would like to export to
    > excel just by clicking a link. I can do this by specifying:
    >
    > contentType="application/vnd.ms-excel"
    >
    > This automatically opens excel and imports the html table directly into
    > it. This is fine for the most part.


    This is basically lying. You write some HTML and claim it is an excel
    file, you then hope that whatever software the user has configured to
    open Excel files is capable of recognising that its recieving HTML and
    importing it automatically.

    Not generally a good idea.

    Your problem would be solved by sending a genuine Excel file to the
    client.
     
    David Dorward, Jun 7, 2006
    #6
  7. Gobi

    Stan McCann Guest

    Gobi <> wrote in news:nRuhg.253432$WI1.8625@pd7tw2no:

    > Mark Parnell wrote:
    >> Deciding to do something for the good of humanity, Gobi
    >> <> declared in alt.html:
    >>
    >> Adrienne is on the right track. You need to somehow tell Excel to
    >> treat the cell value as a string instead of an integer. If putting
    >> quotes around it doesn't help though, I don't know how you'd
    >> achieve that.
    >>

    >
    > Actually, I put a space in front of the data instead of quotes. I
    > was hoping for a solution where I don't have to modify the data. I
    > am thinking maybe I can somehow inform excel that this column is a
    > string.
    > Perhaps something in the <tr>? Or some way to turn off
    > auto-formatting in excel?
    >


    As Mark said, Adrienne is on the right track. A space in front won't
    do it, use ' and if won't be seen; it's just how you designate the
    difference between a string and a number in Excel.

    --
    Stan McCann, "Uncle Pirate" http://stanmccann.us/
    Webmaster, NMSU Alamogordo http://alamo.nmsu.edu/
    Implementing http://blinkynet.net/comp/uip5.html
     
    Stan McCann, Jun 7, 2006
    #7
  8. Gobi

    Moochie

    Joined:
    Feb 18, 2009
    Messages:
    1
    Here is the answer folks

    Simple add style=""mso-number-format:\@"" to the td .

    <td style="mso-number-format:\@">

    This tells access to keep leading zeros.
     
    Moochie, Feb 18, 2009
    #8
  9. Gobi

    mbalhoot

    Joined:
    May 18, 2011
    Messages:
    1
    how i can do this

    dear
    pleas how i can export table to excel file through link useng jsp
     
    mbalhoot, May 18, 2011
    #9
    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,431
    Carl Prothman [MVP]
    Jan 12, 2005
  2. =?Utf-8?B?VGltX2s=?=

    Exporting datagrid to Excel Pivot Table

    =?Utf-8?B?VGltX2s=?=, Jan 26, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    659
    =?Utf-8?B?VGltX2s=?=
    Jan 26, 2005
  3. =?Utf-8?B?c2NobWlkdGU=?=

    Exporting HTML table to Excel

    =?Utf-8?B?c2NobWlkdGU=?=, Mar 20, 2007, in forum: ASP .Net
    Replies:
    5
    Views:
    1,457
    Mark Rae
    Mar 22, 2007
  4. Doogie
    Replies:
    1
    Views:
    483
    sloan
    Nov 19, 2008
  5. M P

    Exporting HTML Table to Excel?

    M P, Jul 6, 2004, in forum: ASP General
    Replies:
    2
    Views:
    220
    Greg Griffiths
    Jul 18, 2004
Loading...

Share This Page