simply_jessie
asked on
How to write from database to excel using ASP?
seems to be easy but i cant run this properly, anyone please help me!
<%
Dim sTable
Dim sDSN
Dim sFields
sFields = "collectorid, attempts, calls, contacts, promise, amount" 'List of fields comma delimited
sTable = "dummyallcoll" 'Name of your table or View
Set DBConn = Server.Createobject("ADODB
dbconn.commandtimeout = 0
DBConn.connectionString = "File Name=" & onRequestConnection()
DBconn.open
Set RS = Server.CreateObject("ADODB
RS.Open "select "& sFields &" from "& sTable,DBConn
Response.ContentType = "application/csv"
Response.AddHeader "Content-Disposition", "filename=mydata.csv;"
' lets print the fields on top
for i = 0 to RS.Fields.Count-1
if i = (RS.Fields.Count - 1) then
Response.Write RS.Fields(i).Name
else
Response.Write RS.Fields(i).Name & " "
end if
next
Response.write vbNewLine
while not RS.EOF
for u=0 to RS.Fields.Count - 1
if u = (RS.Fields.Count - 1) then
Response.Write RS.Fields(u).Value
else
Response.Write RS.Fields(u).Value & " "
end if
next
response.write vbNewLine
rs.MoveNext
wend
Response.write vbNewLine
Set RS = Nothing
Set DB = Nothing
%>
this function writes to excel alright, but it doesnt recognize vbNewLine to write to next row, thereby writing all the data in one row........
if there's someone who knows how to write database to excel without using comma delimited format, please help me. thanks.
<%
Dim sTable
Dim sDSN
Dim sFields
sFields = "collectorid, attempts, calls, contacts, promise, amount" 'List of fields comma delimited
sTable = "dummyallcoll" 'Name of your table or View
Set DBConn = Server.Createobject("ADODB
dbconn.commandtimeout = 0
DBConn.connectionString = "File Name=" & onRequestConnection()
DBconn.open
Set RS = Server.CreateObject("ADODB
RS.Open "select "& sFields &" from "& sTable,DBConn
Response.ContentType = "application/csv"
Response.AddHeader "Content-Disposition", "filename=mydata.csv;"
' lets print the fields on top
for i = 0 to RS.Fields.Count-1
if i = (RS.Fields.Count - 1) then
Response.Write RS.Fields(i).Name
else
Response.Write RS.Fields(i).Name & " "
end if
next
Response.write vbNewLine
while not RS.EOF
for u=0 to RS.Fields.Count - 1
if u = (RS.Fields.Count - 1) then
Response.Write RS.Fields(u).Value
else
Response.Write RS.Fields(u).Value & " "
end if
next
response.write vbNewLine
rs.MoveNext
wend
Response.write vbNewLine
Set RS = Nothing
Set DB = Nothing
%>
this function writes to excel alright, but it doesnt recognize vbNewLine to write to next row, thereby writing all the data in one row........
if there's someone who knows how to write database to excel without using comma delimited format, please help me. thanks.
why not to write directly in html table formats?!
Excel accepts html.. so use <tr> 's as row delimters and td's as cell delimiters..
regards
Excel accepts html.. so use <tr> 's as row delimters and td's as cell delimiters..
regards
simply_jessie,
>>>this function writes to excel alright, but it doesnt recognize vbNewLine to
>>>write to next row, thereby writing all the data in one row........
Is this your only problem?
Best Regards,
>apollois<
>>>this function writes to excel alright, but it doesnt recognize vbNewLine to
>>>write to next row, thereby writing all the data in one row........
Is this your only problem?
Best Regards,
>apollois<
more examples:
http://www.aspin.com/home/tutorial/document/xls
http://www.aspin.com/home/tutorial/document/xls
simply_jessie,
>>>if there's someone who knows how to write database to excel without using
>>>comma delimited format, please help me. thanks.
Here's a great example:
http://www.asp101.com/samples/xl.asp
Let me know if you have any more follow-up questions.
Best Regards,
>apollois<
>>>if there's someone who knows how to write database to excel without using
>>>comma delimited format, please help me. thanks.
Here's a great example:
http://www.asp101.com/samples/xl.asp
Let me know if you have any more follow-up questions.
Best Regards,
>apollois<
simply_jessie,
Using Excel Web queries is a great way to get web data into an Excel spreadsheet. The Web page is a standard HTML table. Excel queries the web page and loads the data into the worksheet. The Excel user can build other sheet and graphs based on this. The data can be easily refreshed from the web server anytime. Here are some good references on this:
How to Programmatically Perform a Web Query
http://support.microsoft.com/default.aspx?scid=KB;en-us;q213730
HOW TO: Use a Web Data Source for a PivotTable in Excel 2000
http://support.microsoft.com/default.aspx?scid=KB;en-us;q211908
Understanding Web Queries
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp
Best Regards,
>apollois<
Using Excel Web queries is a great way to get web data into an Excel spreadsheet. The Web page is a standard HTML table. Excel queries the web page and loads the data into the worksheet. The Excel user can build other sheet and graphs based on this. The data can be easily refreshed from the web server anytime. Here are some good references on this:
How to Programmatically Perform a Web Query
http://support.microsoft.com/default.aspx?scid=KB;en-us;q213730
HOW TO: Use a Web Data Source for a PivotTable in Excel 2000
http://support.microsoft.com/default.aspx?scid=KB;en-us;q211908
Understanding Web Queries
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office06012000.asp
Best Regards,
>apollois<
ASKER
Apollois, thanks for all ur replies, i just want to write it in Excel without having to display the excel application in my browser, im trying to look at all ur examples, il get back to you after ive seen it. thanks for ur help.
ASKER
Apollois, ive already seen all ur examples and almost all them opens up an excel application in the browser. what i need is to just write to excel file without opening excel in browser. thanks a lot.
ASKER
My code already writes to excel, i just need a code dat will write to a new row.......because response.write vbNewLine doesnt work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
simply_jessie,
>>>Response.Write RS.Fields(u).Value & " "
If you're trying to output a CSV (comma separated value) file, shouldn't this be:
Response.Write RS.Fields(u).Value & ", "
Best Regards,
>apollois<
>>>Response.Write RS.Fields(u).Value & " "
If you're trying to output a CSV (comma separated value) file, shouldn't this be:
Response.Write RS.Fields(u).Value & ", "
Best Regards,
>apollois<
ASKER
Thanks, it worked.....lol....il accept ur answer, i just hope u can help me how to write in excel without making it as comma delimited....
usually, response.contenttype "application/vnd.ms-excel"
do u have any idea how? thanks
usually, response.contenttype "application/vnd.ms-excel"
do u have any idea how? thanks
simply_jessie,
>>> opens up an excel application in the browser. what i need is to just write
>>>to excel file without opening excel in browser
To force the "SAVE AS" dialog, use the following:
Response.ContentType = "application/x-msdownload"
response.AddHeader "content-disposition", "attachment; filename=YourFileName.xls"
YOu can change "YourFileName.xls" to whatever you want.
Best Regards,
>apollois<
>>> opens up an excel application in the browser. what i need is to just write
>>>to excel file without opening excel in browser
To force the "SAVE AS" dialog, use the following:
Response.ContentType = "application/x-msdownload"
response.AddHeader "content-disposition", "attachment; filename=YourFileName.xls"
YOu can change "YourFileName.xls" to whatever you want.
Best Regards,
>apollois<
ASKER
thanks, dat worked too.....lastly.....and i hope u dnt mind, how to save it without making it comma delimited?
uve been a great help! thanks a lot
uve been a great help! thanks a lot
simply_jessie,
>>>how to save it without making it comma delimited?
Just output as a HTML table, but with the XLS extension. Excel will open and convert.
Does this make sense?
Best Regards,
>apollois<
>>>how to save it without making it comma delimited?
Just output as a HTML table, but with the XLS extension. Excel will open and convert.
Does this make sense?
Best Regards,
>apollois<
HOW TO BUILD HTML TABLE FROM DB
You will first output a heading row containing the names of the fields in the Recordset object. Because you do not know the names, you will just iterate through the Fields collection of the Recordset to obtain the field names. In this code, you obtain the field names by using "objRS.Fields(i).Name":
==========================
' -- Output the Field Names as the first row in the table
Response.Write "<TR BGCOLOR=""#CCCCCC"">"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TH>" & objRS.Fields(i).Name & "</TH>"
Next
Response.write "</TR>"
==========================
You then "walk" through the Recordset outputting the field values. Again, in this code, you obtain the value of a field by using "objRS.Fields(i)". This defaults to the .Value property of the field. You could have also used the code "objRS(i)" and obtained the same results:
==========================
' -- Now output the contents of the Recordset
objRS.MoveFirst
Do While Not objRS.EOF
' -- output the contents
Response.Write "<TR>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TD>" & objRS.Fields(i) & "</TD>"
Next
Response.write "</TR>"
' -- move to the next record
objRS.MoveNext
Loop
==========================
>apollois<
You will first output a heading row containing the names of the fields in the Recordset object. Because you do not know the names, you will just iterate through the Fields collection of the Recordset to obtain the field names. In this code, you obtain the field names by using "objRS.Fields(i).Name":
==========================
' -- Output the Field Names as the first row in the table
Response.Write "<TR BGCOLOR=""#CCCCCC"">"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TH>" & objRS.Fields(i).Name & "</TH>"
Next
Response.write "</TR>"
==========================
You then "walk" through the Recordset outputting the field values. Again, in this code, you obtain the value of a field by using "objRS.Fields(i)". This defaults to the .Value property of the field. You could have also used the code "objRS(i)" and obtained the same results:
==========================
' -- Now output the contents of the Recordset
objRS.MoveFirst
Do While Not objRS.EOF
' -- output the contents
Response.Write "<TR>"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TD>" & objRS.Fields(i) & "</TD>"
Next
Response.write "</TR>"
' -- move to the next record
objRS.MoveNext
Loop
==========================
>apollois<
ASKER
ok, il try this one. but i tink it will work for sure. and im getting ur point. thanks a lot.
btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...
thanks so much.......
btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...
thanks so much.......
ASKER
ok, il try this one. but i tink it will work for sure. and im getting ur point. thanks a lot.
btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...
thanks so much.......
btw, i saw dat uve done foxpro too. ive coded some foxpro 2.6 systems before...
thanks so much.......
>why not to write directly in html table formats?!
Well.. I guess I said that first.. :o/
Anyways.. I'm deep in .Net now..
Well.. I guess I said that first.. :o/
Anyways.. I'm deep in .Net now..
ASKER
sorry silvers5, i just didnt get it the first time. sorry.
>>>Response.write vbNewLine
Response.write "<BR>" & vbNewLine
Best Regards,
>apollois<