How to use ASP & Export data from database to excel (Excel.Application) This is learn/tutorial asp developers how to using ASP script Export data from database to excel.
ShotDev Focus:
- ASP & Export data from database to excel.
Example
asp_database_excel.asp
<%Option Explicit%> <html> <head> <title>ShotDev.Com Tutorial</title> </head> <body> <body> <% Dim Conn,strSQL,objRec Dim xlApp,xlBook,xlSheet1,FileName,intRows Dim Fso,MyFile Set Conn = Server.Createobject("ADODB.Connection") Conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("MyXls/mydatabase.mdb"),"" , "" strSQL = "SELECT * FROM customer " Set objRec = Server.CreateObject("ADODB.Recordset") objRec.Open strSQL, Conn, 1,3 If Not objRec.EOF Then FileName = "MyXls/MyExcel.xls" '*** Create Exce.Application ***' Set xlApp = Server.CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add '*** Create Sheet 1 ***' xlBook.Worksheets(1).Name = "My Customer" xlBook.Worksheets(1).Select '*** Header ***' With xlApp.ActiveSheet.Cells(1,1) .Value = "CustomerID" End With With xlApp.ActiveSheet.Cells(1,2) .Value = "Name" End With With xlApp.ActiveSheet.Cells(1,3) .Value = "Email" End With With xlApp.ActiveSheet.Cells(1,4) .Value = "CountryCode" End With With xlApp.ActiveSheet.Cells(1,5) .Value = "Budget" End With With xlApp.ActiveSheet.Cells(1,6) .Value = "Used" End With '***********' intRows = 2 While Not objRec.EOF '*** Detail ***' With xlApp.ActiveSheet.Cells(intRows,1) .Value = objRec.Fields("CustomerID").Value End With With xlApp.ActiveSheet.Cells(intRows,2) .Value = objRec.Fields("Name").Value End With With xlApp.ActiveSheet.Cells(intRows,3) .Value = objRec.Fields("Email").Value End With With xlApp.ActiveSheet.Cells(intRows,4) .Value = objRec.Fields("CountryCode").Value End With With xlApp.ActiveSheet.Cells(intRows,5) .Value = objRec.Fields("Budget").Value End With With xlApp.ActiveSheet.Cells(intRows,6) .Value = objRec.Fields("Used").Value End With intRows = intRows + 1 objRec.MoveNext Wend '*** If Files Already Exist Delete files ***' Set Fso = CreateObject("Scripting.FileSystemObject") If (Fso.FileExists(Server.MapPath(FileName))) Then Set MyFile = Fso.GetFile(Server.MapPath(FileName)) MyFile.Delete End If '*** Save Excel ***' 'xlBook.PrintOut 1 '*** Print to printer ***' xlBook.SaveAs Server.MapPath(FileName) xlApp.Application.Quit '*** Quit and Clear Object ***' Set xlSheet1 = Nothing Set xlBook = Nothing Set xlApp = Nothing End If objRec.Close() Conn.Close() Set objRec = Nothing Set Conn = Nothing %> Excel Created <a href="<%=FileName%>">Click here</a> to Download. </body> </html>
Create a asp file and save to path root-path/myasp/
Run
http://localhost/myasp/asp_database_excel.asp
Screenshot
2polyphonic…
…