Exporting to Excel (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

DesiMcK -> Exporting to Excel (4/29/2004 18:06:35)

Here is yet another silly question form me. I found this post on the site rgarding exporting to excel.
quote:

I stumbled across this at a different message board. Something to remember here is this will be screwed up if any of your fields contain a comma. This should be a good starting point.
<%
Response.Addheader "Content-Disposition", "inline; filename=filename.csv"
Response.ContentType = "application/download"

conn.Open connectString
Set rs = conn.Execute("your_sq_lhere")
output = rs.GetString(2,,",")
rs.Close
Response.Write output
%>

Name your export file with a .csv (comma separated values) extension instead of .xls or the spreadsheet will open on the browser.

[This message has been edited by jbennett (edited 09-16-2001).]


What do I do to implement it? Do I need to attach it to a button in order to activate it?

Desi



rdouglass -> RE: Exporting to Excel (4/30/2004 8:08:08)

Actually this script can be on any ASP page. If it's the only thing on the page, a download dialogue box should open for the browser. Of course, that's assuming you have a valid connection and SQL query. Myself, I prefer this script 'cause it gives you column headings:

http://www.pstruh.cz/tips/detpg_RSConvert.htm



DesiMcK -> RE: Exporting to Excel (4/30/2004 11:26:20)

OK, I think I understand the script but I am not sure how to implement it. Would you be able to advise me in very simple steps. Thanks for your time,

Desi



rdouglass -> RE: Exporting to Excel (4/30/2004 12:03:19)

1. Create a new page and go to HTML view.

2. Delete ALL code, even the <html> tags.

3. Paste this code on the page. This should be the ONLY code on the page:

<%
Dim DSN, RS, SQL, Conn

'This next line is your database connection line
DSN="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & server.mappath("/fpdb/db1.mdb")
'This next line is your SQL for the data you want to use
SQL = "SELECT * FROM Item"

set Conn=server.createobject("adodb.connection")
Conn.open DSN
Set RS = Conn.Execute (SQL)

Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
Set RS = nothing
Conn.close
%>

4. Edit the applicable areas that are in bold. They will be specific to your web site, but most Windows servers should be able to accomidate the DSN. Try just changing the db filename and the SQL query to see if you have any luck.

5. Save the page as an ASP page.

6. Put a link (to this page) on any other page in your site.

7. Click link in browser to download CSV file.

Does that help?



DesiMcK -> RE: Exporting to Excel (4/30/2004 12:41:54)

This is much more than I expected. Thank you very, very much for this. I will try it later and let you know of my success. Thanks again.

Desi



DesiMcK -> RE: Exporting to Excel (4/30/2004 17:28:14)

The instructions were excellent. Even I managed to get the export to work. Now for an ammendment... I have a custom query that sends the results to an asp page. Can I then forward the query to the export asp. For example, when a teacher select Class from a drop down box, the class list appears on a seperate page. Can I put a link on that page that will open the export asp and download the class list. I changed the code to 
 
SQL = "SELECT * FROM Results WHERE (Class =  '::Class::')"

but it doesn't work. Is there a way I can achieve what I want?

Desi



Spooky -> RE: Exporting to Excel (5/1/2004 4:29:29)

You could try something like :

SQL = "SELECT * FROM Results WHERE (Class = '"&Replace(Request.Querystring("Class"),"'","''")&"')"

The link from the querypage may look like

export.asp?Class=<%=Request("Class")%>

So......

Page #1 selects the class (from a drop down form called "Class")
Page #2 shows the results of that query and links to page 3, carrying the value of "class"
Page #3 querys the class field with that value.



DesiMcK -> RE: Exporting to Excel (5/1/2004 11:05:08)

Thanks brilliant Spooky, thank you. Unforntunately I do not understand some parts of it. Would you be able to explain this part - ..."),"'","''")&"')"

Ultimately I would like to add to the query string so that several search criteria will be passed to the csv file with a choosen oredr. I have attached one of the more complex SQL statements that I would like to adapt. 

SELECT * FROM Results WHERE (Class =  '::Class::' AND Gender = '::Gender::' AND IEP = "Yes") ORDER BY ::sortcol:: ::sortorder::,::sortcol2:: ::sortorder2::,::sortcol3:: ::sortorder3::"


Thanks agin for you help,

Desi



frontpagenick -> RE: Exporting to Excel (5/3/2004 3:57:00)

Hi

I have developed an ASP programme using front page and if you add a search feature then when you rt hand click to export to Excel no records are returned.

http://www.hunterchasers.co.uk/database/horseinquiry.asp

Has anyone else had this issue and come up with an answer?

Nick



Jessop -> RE: Exporting to Excel (7/12/2004 10:39:21)

I am using the following code:

<%
Dim RS
dim DataConn
dim DataCmd
dim datapath
dim SQL

dim rsEMSC

%>
<!--#include File="../Includes/DBOpenClose2.asp"-->
<%

SQL = "SELECT * FROM AuthorizedUsers"
DataCmd.CommandText = SQL
Set RS = server.CreateObject("ADODB.Recordset")
RS.Open DataCmd, , 1, 3

Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
Response.Addheader "Content-Disposition", "inline; filename=myExportName.csv"
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
%>

and when the page completes loading i would expect ONE LONG CSV string to include all the field names and all the record names in the long string. SO I copy and paste the data into a CSV file. But when excell tries to load the csv file it tell me that its going to truncate the string becuase its too long. which i understand because there is a ton of data in the access database. What I don't understand is I am using the vbcrlf and I expect it to do a new line in excel but excel isn't recognizing it. any ideas / suggestions? I had a thought where the browser is stripping out the vbcrlf's and its not when i view source it looks like i would expect but in the main broswer its showing one long string. Thanks in advance and have a great day.



JohnH -> RE: Exporting to Excel (7/7/2005 4:24:02)

I am using the code rdouglass recommends above and it works great. The only problem I am having is when I export dates. I am in the UK and have the age old problem of displaying dates in the dd/mm/yyyy format and not the mm/dd/yyyy format. Does anyone have any ideas on how I can achieve this within the code?

My existing code is: 

"CREATION_DATE AS 'CREATION DATE', " & _


Many thanks,

John



rubyaim -> RE: Exporting to Excel (7/7/2005 19:06:28)

Hi John, at the top of my export pages I've found that using the LCID actually works well.

At the top of the page I have:

Session.lcid = 3081

Am in Australia, I think for the UK it is '2057'.

This may not be the best way to handle it, but as it's actually working I've not been tempted to play around with it at all.


Sally



JohnH -> RE: Exporting to Excel (7/28/2005 6:49:41)

Thanks for responding Sally. I have tried using the Session.lcid = 2057 at the top of my page but it did nothing. Any other ideas?

Thanks John




rubyaim -> RE: Exporting to Excel (7/28/2005 19:46:01)

John, sorry, no ideas - I find working with dates very frustrating at the best of times [:(]

I've just played around with a few functions and formats with no luck. About the only thing I've not tried is XML (opening the file in Excel with <%Response.ContentType="Application/vnd.ms-excel"%> )
maybe this could be done with the date format in the styling.

Hopefully someone else can help us out - I've really no idea why using the Session is working here, it generally does not elsewhere..

Sally



JohnH -> RE: Exporting to Excel (7/29/2005 9:52:17)

Can anyone else help, my code is below 

<%
Session.lcid = 2057 
%>
<%
Dim DSN, RS, SQL, Conn 

SQL =  	"SELECT A.STAFFNO AS 'Staff number', +ISNULL(FIRSTNAME,'')+' '+ISNULL(SURNAME,'') AS 'Full Name',  IDNUMBER AS 'Employee ID',  B.LOCATION AS 'Location', EMPLOYMENT AS 'Employer Name',INDUCTION AS 'Plant Induction',  DONOTUSE AS 'Banned From Site',DLEXPIRY AS 'Driving License Expiry'  FROM MASTER A, IMERYSCOMPETENCE B  WHERE A.STAFFNO = B.STAFFNO  AND A.STAFFNO LIKE '%"&Replace(Request.Querystring("staff_number"),"'","''")&"%' ORDER BY SURNAME"

set Conn = server.createobject("ADODB.Connection") 
Conn.Open "PROVIDER=SQLOLEDB;" & _
             "DATA SOURCE=server;DATABASE=COMPLIANCE;" & _
             "USER ID=sa;PASSWORD=password;"
             
Set RS = Conn.Execute (SQL) 

Dim F, Head 
For Each F In RS.Fields 
Head = Head & ", " & F.Name 
Next 
Head = Mid(Head,3) & vbCrLf 
Response.Addheader "Content-Disposition", "inline; filename=Export.csv" 
Response.ContentType = "application/download" 
Response.Write Head 
Response.Write RS.GetString(,,", ",vbCrLf,"") 
Set RS = nothing 
Conn.close 
%>



sentinel -> RE: Exporting to Excel (8/10/2005 17:21:11)

I get an SYLK : Format Invalid Error.

The first column in my database is "ID". I tried to make it lowercase but it had no effect.



davith2 -> RE: Exporting to Excel (8/16/2005 10:49:53)

Does anyone know how I can use a button to export results from a SQL Database to Excel. I have a Form that I created that pulls information from the database and and displays it on a webpage, I want to have a button on that same results page that will (with the results still showing on the page), export the results that I have already called to Excel. I have tried using

Response.ContentType = "appliaction/vnd.ms-excel"
Response.AddHeader "Content-Disposition","attachment; filename=results.xls"
%>

and it works great, but the problem is it automatically exports to Excel and then there is no longer any results on the webpage. I would like to see the results and then have the choice if I want to export the same results to Excel with a push botton or link.



rdouglass -> RE: Exporting to Excel (8/17/2005 13:13:48)

quote:

I would like to see the results and then have the choice if I want to export the same results to Excel


Hi and Welcome to OutFront.

I have used a small form with just a hidden field and a button called "Export". The hidden field contains the same query that the page you're viewing used to generate the table. I call the hidden field "exportQuery" and POST the form to the ASP page that holds the Excel export code like above.

Then, in the export code, instead of this:

SQL = "SELECT * FROM AuthorizedUsers"

use something like this:

SQL = Request.form("exportQuery")

What you're using on the page that displays results generally will dictate how you get that query in the form. Myself, I generally use mySQL for a variable so my form field would look like:

<input type="hidden" name="exportQuery" value="<%=mySQL%>">

See what I'm doing? I'm just passing the exact same SQL to the export scriipt using a hidden field. That works fine unless you get some huge SQL statement but I think the actual size of the string is quite large - 10K characters or so IIRC.

That help any?



rdouglass -> RE: Exporting to Excel (8/17/2005 13:20:52)

quote:

I get an SYLK : Format Invalid Error.


One quick thing I'd try is changing this:

"inline; filename=Export.csv"

to this:

"inline; filename=Export.xls"

You may get funny results but I suspect you're getting the error from some text fields that have either special characters or quotes in them. See, the CSV files we're generating are using double quotes as a text delimiter and a SYLK file interprets it the same IIRC. If you have quotes in the data itself, that'll usually cause errors like that.

A quick way to check would be to do a simple replace script at least to rule that out:

Response.Write RS.GetString(,,", ",vbCrLf,"")

becomes:

Response.Write (replace(RS.GetString(,,", ",vbCrLf,""),CHR(034),""))

That'll just remove all double quotes from the data.

That any help?



sentinel -> RE: Exporting to Excel (8/22/2005 10:54:16)

Using the export query in a hidden field. WHat is the best way to put the SQL statement into a variable and call it from the hidden field?

<%
varMysql="SELECT * FROM asset where physical_location = 'mount prospect'"
%>
<%=Mysql%>

Something like that?



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125