Dotnetindex.com

Exporting Data From mySQL to Excel

Aim of this sampel code is to show how to export data from a database server to excel. At this time we prefred to use mySQL. Because there are enough examples running with other databases eg. MS Access and MS SQL. Before we start to write our code, we need a sample table in mySQL server.

We have used a sample data and used it in a real application. All you need is to create and use a sample table.

First part of code contains common database connection codes. Notice that we did not write HTML header. Because this page will only create and response an excel file. When you call this page from your browser, only an excel file will come.

Below we have added a different html header that this will announce to target browser this is an excel file and opne or download it.

    response.ContentType = "application/vnd.ms-excel"
    response.AddHeader "Content-Disposition", "attachment; filename=excelfile.xls"

We could also add some HTML codes within ouw asp codes which will helpful to navigate rows in excel. You could modify this source code to use with other databases. You need to change database connection :

For other Database connections :

    MS Access :
    strConnection =  "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Inetpub\\wwwroot\\app\\gnewsportal\\databases\\gnews.mdb"
   MS SQL :
   strConnection =   "Provider=SQLOLEDB;Data Source=sql_server;database=gnewsadvsql;uid=sa;pwd=; 


All ASP source code :

<%@LANGUAGE="VBSCRIPT" %>
<%
    ' Exporting A Table to Excel
    strConnection = "driver={MySQL ODBC 3.51 Driver};option=4;server=localhost;user=root;password=sorma;DATABASE=webstat;"
    set cn = server.createobject("adodb.connection")
    set rs = server.createobject("adodb.recordset")
    cn.open strConnection
    set rs = cn.execute("select statDate, visitors, unique_visitors from daily_visitors")

    response.ContentType = "application/vnd.ms-excel"
    response.AddHeader "Content-Disposition", "attachment; filename=excelfile.xls"
    response.Write("<table border=""1"">")
    do while not rs.eof
    response.Write("<tr>")
    response.Write("<td>" & rs("statDate") & "</td><td>" & rs("visitors") & "</td><td>" & rs("unique_visitors") & "</td></tr>" &  vbnewline )
    rs.movenext
    loop
    response.Write("</table>")
    cn.close
    set rs = nothing
%>

Have a nice coding