ASP.NET Export to Excel and Word using VB.NET and C#

In most ASP.NET web applications, there is a need to export data. This is particularly useful if the information will be used for further analysis and archiving purposes offline. This tutorial will illustrate how you can export your data from your ASP.NET webpage (example if it is coming from a MSSQL database) to one of the most common file export formats in Windows: MS Excel and MS Word.

Getting Started: Designing the Application

Suppose you have an existing web application project that pulls data out of the database and display it on the browser. The exporting feature can be added by the use of a query string in the URL. For example, suppose this is the normal URL containing the desired content to be exported:

http://www.example.com/Default.aspx

Then say you assign a query string variable known as “export” and let the value of:

1= An MS Excel export file type
2 = An MS Word export file type

So you can simply add a link on your existing web page:  http://www.example.com/Default.aspx pointing to these URLs if you want to add an export feature. These URLs will look like:

http://www.example.com/Default.aspx?export=1
http://www.example.com/Default.aspx?export=2

When these URLs are loaded in the browser, it will execute the page load event handler in its background code (Default.aspx.vb for VB.NET or Default.aspx.cs for C#): http://www.aspfree.com/c/a/ASP.NET/Understanding-Event-Handlers-in-ASPNET-35/3/, which does the following task:

1.) Connect to the database
2.) Retrieve the data
3.) Retrieve the query string value.
4.) If the query string value is 1, then format the export data as MS Excel, otherwise MS Word.
5.) Show the download dialog box back to the user to save the file to their computer.

Creating the ASP.NET Export to Excel and Word Project

Let’s create a test project where you can learn how to export your ASP.NET content data to either MS Excel or MS Word:

1.) Launch Visual Web Developer Express
2.) Go to File – New Web Site
3.) Under “Visual Studio Installed Templates”, select “ASP.NET Web Site” and then in Location, select “File System”. For the language select either Visual Basic or C#.

Provide a path to your ASP.NET project. For example, this project will be named export_to_excel_word. See the screenshot below:

4.) Click OK.
5.) Download this test SQL server database: http://www.dotnetdevelopment.net/tutorials/Firstdatabase.zip

6.) Right click on the zip file and click “Extract here”.
7.) Copy Firstdatabase.mdf to App_data in your website project.

Complete Default.aspx source code for VB.NET and C#

You need to replace the existing Default.aspx source code. Replace it with the complete Default.aspx code below:

If you are using C#:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export retrieve data from MS SQL database to MS Excel and MS Word</title>
</head>
<body>
<form id="Form1" method="post" runat="server">
<asp:Repeater id="Repeater1" runat="server">
<HeaderTemplate>
<table>
<tr>
<th>Movie ID</th>
<th>Movie title</th>
<th>Movie genre</th>
<th>Running time</th>
<th>Director</th>
<th>Date released</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%#DataBinder.Eval(Container.DataItem, "movieid")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "movietitle")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "moviegenre")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "runningtime")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "director")%></td>
<td><%#DataBinder.Eval(Container.DataItem, "datereleased")%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
<br /><br />
<font face="Courier">
<a href="Default.aspx?export=1">Click here to download as MS Excel</a>
<br />
<a href="Default.aspx?export=2">Click here to download as MS Word file</a>
</font>
</form>
</body>
</html>

If you are using VB.NET simply replace the top most code:

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

With this:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

Go to Design View; this is how it should look:

It is a table of six columns:

a.) Movie ID
b.) Movie title
c.) Movie genre
d.) Running time
e.) Director
f.) Date Released

The information of this table is basically saved in the SQL server database: Firstdatabase.mdf. The job of the repeater control is to display the retrieved data from the database. You may also have noticed that there are two hyperlinks at the bottom:

Default.aspx?export=1
Default.aspx?export=2

It uses a querystring variable “export” that will tell the server side script whether the user needs an MS Excel export or MS Word.

Complete Default.aspx.vb script (VB.NET)

Below is the final Default.aspx.vb script - go to Default.aspx.vb and replace it with the code below. IMPORTANT:  (‘) single quotes before the black fonts are VB.NET comments, do not delete it as they are part of the code:

[code] ' Import the System.Data and System.Data.SqlClient namespaces ' if you are working with the databases Imports System.Data Imports System.Data.SqlClient Partial Class _Default Inherits System.Web.UI.Page 'This is the start of the page load event handler 'This handler will be executed everytime Default.aspx is loaded by the browser. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Define database connection parameters.
'Change the path according to your current database path. Example you need to change L:aspdotnetprojectsgridviewtoexcelApp_DataFirstdatabase.mdf
'to reflect your own path
Dim cnn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=L:aspdotnetprojectsgridviewtoexcelApp_DataFirstdatabase.mdf;Integrated Security=True;User Instance=True") 'Define SQL query to be made to the database
'Select * from movie means to retrieve the entire contents of the database table named as "movie" Dim cmd As SqlDataAdapter = New SqlDataAdapter("select * from movie", cnn) Dim ds As DataSet = New DataSet() cmd.Fill(ds) Repeater1.DataSource = ds 'Bind the retrieved data from the database to Repeater1 web control in Default.aspx Repeater1.DataBind() 'Now this is the section of the script that will handle the file exporting process
'Check if the user wants the exported file to be in MS Excel format If Request.QueryString("export") = "1" Then 'Assign content type as MS Excel. Response.ContentType = "application/vnd.ms-excel"; 'Assign filename, force download Response.AddHeader("content-disposition", "attachment;filename=Exportdata.xls")

' Remove the charset from the Content-Type header.

Response.Charset = ""

' Turn off the view state.

Me.EnableViewState = False
Dim stringwriter As New System.IO.StringWriter()
Dim htmltextwriter As New System.Web.UI.HtmlTextWriter(stringwriter)

' Get the HTML for the control.

Repeater1.RenderControl(htmltextwriter)

' Write the HTML back to the browser.

Response.Write(stringwriter.ToString())

' End the response.

Response.End()

End If

'Check if the user wants the exported file to be in MS Word format

If Request.QueryString("export") = "2" Then

' Set the content type to MS Word.

Response.ContentType = "application/ms-word"
Response.AddHeader("content-disposition", "attachment;filename=Exportdata.doc")
Response.Charset = ""
Me.EnableViewState = False
Dim stringwriter As New System.IO.StringWriter()
Dim htmltextwriter As New System.Web.UI.HtmlTextWriter(stringwriter)
Repeater1.RenderControl(htmltextwriter)
Response.Write(stringwriter.ToString())
Response.End()
End If
End Sub
End Class

Complete Default.aspx.cs script (C#)

If you are using C#, below is the complete script:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;
using System.Data;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic;
using System.Collections;
using System.Diagnostics;


public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection cnn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=L:\aspdotnetprojects\gridviewtoexcel\App_Data\Firstdatabase.mdf;Integrated Security=True;User Instance=True");
        SqlDataAdapter cmd = new SqlDataAdapter("select * from movie", cnn);
        DataSet ds = new DataSet();
        cmd.Fill(ds);
        Repeater1.DataSource = ds;
        Repeater1.DataBind();
        if (Request.QueryString["export"] == "1")
        {           
            Response.ContentType = "application/vnd.ms-excel";           
            Response.AddHeader("content-disposition", "attachment;filename=Exportdata.xls");           
            Response.Charset = "";           
            this.EnableViewState = false;
            System.IO.StringWriter stringwriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmltextwriter = new System.Web.UI.HtmlTextWriter(stringwriter);           
            Repeater1.RenderControl(htmltextwriter);           
            Response.Write(stringwriter.ToString());           
            Response.End();
        }       
        if (Request.QueryString["export"] == "2")
        {           
            Response.ContentType = "application/ms-word";
            Response.AddHeader("content-disposition", "attachment;filename=Exportdata.doc");
            Response.Charset = "";
            this.EnableViewState = false;
            System.IO.StringWriter stringwriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmltextwriter = new System.Web.UI.HtmlTextWriter(stringwriter);
            Repeater1.RenderControl(htmltextwriter);
            Response.Write(stringwriter.ToString());
            Response.End();
        }
    }
    public _Default()
    {
        Load += Page_Load;
    }
} [/code]

Browser Testing

Regardless of whether you are using C# or VB.NET in your behind the scenes code, the output is the same. Open the project in Visual Web Developer and go to File – View in Browser. This will launch the project in the browser. It will look like this:

Now try clicking “Click here to download as MS Excel”. You see a download dialog box asking you to save to file. After downloading the file, try to open it with MS Excel. Do the same with MS Word.

The file name is Exportdata.xls for MS Excel and Exportdata.doc for MS Word. When you open these files, it should like the screenshot provided below:

[gp-comments width="770" linklove="off" ]