ASP Exporting to Excel

I have an asp page of data.
using the following. I am able to export the page to Excel

<% Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=excelfile.xls"%>

Works great. except the numbers on the spreadsheet are not treated as Numbers by Excel.

How do we export the numbers so they can be manipulated on the spreadsheet?
ParisBPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott FellDeveloper & EE ModeratorCommented:
Make sure there are not any spaces in the data.  You may have " 2 " when you meant "2".

<%
x=" 2 "
y=trim(x)

'I don't think this would do anything but you could try
z=cdbl(y)

%>
ParisBPAuthor Commented:
We have no spaces on the numbers in the query.
Also after I export to excel, I cannot format the cells with datat to numbers.
Scott FellDeveloper & EE ModeratorCommented:
it's possible your html is creating spaces.  Your other option is in excel to multiply by 1.    If all of your numbers are in column A, Make column B =b1*a1 and that will force a number.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ParisBPAuthor Commented:
That was a great thought. but muliplying by one is giving me #Value errors as the number isn't a number to begin with.
Scott FellDeveloper & EE ModeratorCommented:
Are you using trim like I suggested?

trim(response.write(myValue))
Scott FellDeveloper & EE ModeratorCommented:
Actually, make sure you are using a number.

x=trim(myValue)
if not isnumeric(x) then
   x="This was not numeric |"&x&"|"
end if
response.write x

Open in new window

Scott FellDeveloper & EE ModeratorCommented:
For now, try just using

<% response.ContentType="application/vnd.ms-excel"%>
ParisBPAuthor Commented:
this did the same. i am going to try the code you pasted just above .
Scott FellDeveloper & EE ModeratorCommented:
Can you post the rendered html?  Just remove the <% response.ContentType="application/vnd.ms-excel"%> and send the page to the screen, view source, copy and paste your code here.

Maybe you have some white space in the html?  <td> 2 </td> should be <td>2</td>

What if you try using a different browser?  

I do the very same thing you are doing and don't remember this issue.

What I have been doing lately is using fso and generate a csv file on the server, then have scripting that downloads the csv file and deletes from the server (I do this for very large files).  Then excel opens up the csv file.
ParisBPAuthor Commented:
Sorry got way away from this project.

Exporting to csv? what do I have to do there.

no whitespace btw tested that.
Scott FellDeveloper & EE ModeratorCommented:
http://www.w3schools.com/asp/asp_ref_filesystem.asp
Make sure to give write permissions to the folder you are writing to.
<%
' Assume a recordset named rsName of contact info


dim fs,myCSV
set fs=Server.CreateObject("Scripting.FileSystemObject")
set myCSV=fs.CreateTextFile("c:\webroot\download.csv",true)
myCSV.WriteLine("Name,Address, Phone, Email")

Do Until rsName.eof
     myCSV.WriteLine(rsName("Name")&","&rsName("Address")&","&rsName("Phone")&","& rsName("Email"))

rsName.MoveNext


myCSV.Close
set myCSV=nothing
set fs=nothing
%>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.