Exporting html table to excel

G

Gobi

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.
 
A

Adrienne Boswell

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.
 
G

Gobi

Adrienne said:
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?
 
M

Mark Parnell

Deciding to do something for the good of humanity, Gobi
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.
 
G

Gobi

Mark said:
Deciding to do something for the good of humanity, Gobi
<[email protected]> 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?
 
D

David Dorward

Gobi said:
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.
 
S

Stan McCann

Gobi said:
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.
 
Joined
Feb 18, 2009
Messages
1
Reaction score
0
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,902
Latest member
Elena68X5

Latest Threads

Top