Export data to Excel in Asp.Net - C# and Vb.Net – All Excel versions

There are many ways to export data to different file formats. No, I am not not talking about printing, but exporting data extracted from database to excel, word or text files, and later email it or copy it to other sources. Here, in this article I am going to show you how to export data to an Excel file using C# and VB in Asp.Net.

Updated: I have added Code for C#. In-addition, I have added a feature to Download Excel file after exporting the data. Thank you Keith Jackson for your suggestion on download (see message below).

Export data to Excel using C# Asp.Net

Also Read: Windows Forms - Export Data to an Excel Sheet with AutoFormat Feature using C#

StreamWriter() was definitely an option, but it won’t allow us to write data into new versions of Excel, such as .xlsx format. However, using Microsoft’s Interop namespace, we can export data to any version of Excel. In addition, we will show you how to autofomat an excel sheet dynamically in Asp.Net. So, let us find out how we can achieve this.

Imports Excel = Microsoft.Office.Interop.Excel

However, if you do not want to use Interop namespace in your project, then there is another interesting procedure to export data to an Excel file using Asp.Net’s DataTable and DataGrid classes. Please check the below link.

Now, let't get back to this article.

First, create a new website and add few controls on your web page. I'll keep this example short and simple. We need a button to export the data and two more buttons to view or donwload the excel file, once we have exported the data to excel.

Related: Export data to Excel using Asp.Net DataTable and DataGrid

The Markup
<!DOCTYPE html>
<html>
<head>
    <title>Export to Excel in Asp.Net</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <!-- BUTTON CONTROL TO EXPORT DATA TO EXCEL. -->
            <p><input type="button" onserverclick="ExportToExcel" 
                value="Export data to Excel" runat="server" /></p>

            <!-- SHOW MESSAGE. -->
            <p><asp:Label ID="lblConfirm" Text="" runat="server"></asp:Label></p>

            <div>
                <!-- VIEW BUTTON WILL OPEN THE EXCEL FILE FOR VIEWING. -->
                <div style="float:left;padding-right:10px;">
                    <input type="button" onserverclick="ViewData" 
                        id="btView" value="View Data" runat="server" 
                        style="display:none;" />
                </div>

                <!--DOWNLOAD EXCEL FILE. -->
                <div style="float:left;">
                    <asp:Button ID="btDownLoadFile" Text="Download" 
                        OnClick="DownLoadFile" runat="server" style="display:none;" />
                </div>
            </div>
        </div>
    </form>
</body>
</html>

Before we start with our coding part, create the Employee Details table in your database. We will data from this table and export it to an excel file, dynamically.

Add a COM reference: “Microsoft Excel 12.0 Object Library”

Code behind (C#)
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

using Excel = Microsoft.Office.Interop.Excel;
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;

public partial class _Default : System.Web.UI.Page
{
    protected void ExportToExcel(object sender, EventArgs e)
    {
        // SET THE CONNECTION STRING.
        string sCon = "Data Source=DNA;Persist Security Info=False;Integrated Security=SSPI;" +
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;";

        using (SqlConnection con = new SqlConnection(sCon))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT *FROM dbo.EmployeeDetails"))
            {
                SqlDataAdapter sda = new SqlDataAdapter();
                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    if (dt.Rows.Count > 0)
                    {
                        string path = Server.MapPath("exportedfiles\\");

                        if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                        {
                            Directory.CreateDirectory(path);
                        }

                        File.Delete(path + "EmployeeDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.

                        // ADD A WORKBOOK USING THE EXCEL APPLICATION.
                        Excel.Application xlAppToExport = new Excel.Application();
                        xlAppToExport.Workbooks.Add("");

                        // ADD A WORKSHEET.
                        Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
                        xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];

                        // ROW ID FROM WHERE THE DATA STARTS SHOWING.
                        int iRowCnt = 4;

                        // SHOW THE HEADER.
                        xlWorkSheetToExport.Cells[1, 1] = "Employee Details";

                        Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
                        range.EntireRow.Font.Name = "Calibri";
                        range.EntireRow.Font.Bold = true;
                        range.EntireRow.Font.Size = 20;

                        xlWorkSheetToExport.Range["A1:D1"].MergeCells = true;       // MERGE CELLS OF THE HEADER.

                        // SHOW COLUMNS ON THE TOP.
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Employee Name";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Mobile No.";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "PresentAddress";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Email Address";

                        int i;
                        for (i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field<string>("EmpName");
                            xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field<string>("Mobile");
                            xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field<string>("PresentAddress");
                            xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field<string>("Email");

                            iRowCnt = iRowCnt + 1;
                        }

                        // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                        Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
                        range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);

                        // SAVE THE FILE IN A FOLDER.
                        xlWorkSheetToExport.SaveAs(path + "EmployeeDetails.xlsx");

                        // CLEAR.
                        xlAppToExport.Workbooks.Close();
                        xlAppToExport.Quit();
                        xlAppToExport = null;
                        xlWorkSheetToExport = null;

                        lblConfirm.Text = "Data Exported Successfully";
                        lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;");
                        btView.Attributes.Add("style", "display:block");
                        btDownLoadFile.Attributes.Add("style", "display:block");
                    }
                }
                catch (Exception ex)
                {
                    lblConfirm.Text = ex.Message.ToString();
                    lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
                }
                finally
                {
                    sda.Dispose();
                    sda = null;
                }
            }
        }
    }

    // VIEW THE EXPORTED EXCEL DATA.
    protected void ViewData(object sender, System.EventArgs e)
    {
        string path = Server.MapPath("exportedfiles\\");
        try
        {
            // CHECK IF THE FOLDER EXISTS.
            if (Directory.Exists(path))
            {
                // CHECK IF THE FILE EXISTS.
                if (File.Exists(path + "EmployeeDetails.xlsx"))
                {
                    // SHOW (NOT DOWNLOAD) THE EXCEL FILE.
                    Excel.Application xlAppToView = new Excel.Application();
                    xlAppToView.Workbooks.Open(path + "EmployeeDetails.xlsx");
                    xlAppToView.Visible = true;
                }
            }
        }
        catch (Exception ex)
        {
            //
        }
    }

    // DOWNLOAD THE FILE.
    protected void DownLoadFile(object sender, EventArgs e)
    {
        try
        {
            string sPath = Server.MapPath("exportedfiles\\");

            Response.AppendHeader("Content-Disposition", "attachment; filename=EmployeeDetails.xlsx");
            Response.TransmitFile(sPath + "EmployeeDetails.xlsx");
            Response.End();
        }
        catch (Exception ex) { }
    }
}
Code behind (Vb.Net)
Option Explicit On
Imports System.Data                 ' FOR "DataTable"
Imports System.Data.SqlClient
Imports System.IO                   ' FOR FILE ACCESS.

Imports Excel = Microsoft.Office.Interop.Excel
Imports ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub btExport_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles btExport.ServerClick

        ' SET THE CONNECTION STRING.
        Dim sCon As String = "Data Source=dna;Persist Security Info=False;Integrated Security=SSPI;" & _
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=demo;Connect Timeout=30;"

        Using con As SqlConnection = New SqlConnection(sCon)
            Using cmd As SqlCommand = New SqlCommand("SELECT *FROM dbo.EmployeeDetails")

                Dim sda As SqlDataAdapter = New SqlDataAdapter

                Try
                    cmd.Connection = con : con.Open()
                    sda.SelectCommand = cmd

                    Dim dt As DataTable = New DataTable
                    sda.Fill(dt)

                    If dt.Rows.Count > 0 Then

                        Dim path As String = Server.MapPath("exportedfiles\")

                        ' CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                        If Not Directory.Exists(path) Then
                            Directory.CreateDirectory(path)
                        End If

                        File.Delete(path & "EmployeeDetails.xlsx")      ' DELETE THE FILE BEFORE CREATING A NEW ONE.

                        ' ADD A WORKBOOK USING THE EXCEL APPLICATION.
                        Dim xlAppToExport As New Excel.Application
                        xlAppToExport.Workbooks.Add()

                        ' ADD A WORKSHEET.
                        Dim xlWorkSheetToExport As Excel.Worksheet
                        xlWorkSheetToExport = xlAppToExport.Sheets("Sheet1")

                        ' ROW ID FROM WHERE THE DATA STARTS SHOWING.
                        Dim iRowCnt As Integer = 4

                        With xlWorkSheetToExport
                            ' SHOW THE HEADER.
                            .Cells(1, 1).value = "Employee Details"
                            .Cells(1, 1).FONT.NAME = "Calibri"
                            .Cells(1, 1).Font.Bold = True
                            .Cells(1, 1).Font.Size = 20

                            .Range("A1:D1").MergeCells = True       ' MERGE CELLS OF THE HEADER.

                            ' SHOW COLUMNS ON THE TOP.
                            .Cells(iRowCnt - 1, 1).value = "Employee Name"
                            .Cells(iRowCnt - 1, 2).value = "Mobile No."
                            .Cells(iRowCnt - 1, 3).value = "PresentAddress"
                            .Cells(iRowCnt - 1, 4).value = "Email Address"

                            For i = 0 To dt.Rows.Count - 1
                                .Cells(iRowCnt, 1).value = dt.Rows(i).Item("EmpName")
                                .Cells(iRowCnt, 2).value = dt.Rows(i).Item("Mobile")
                                .Cells(iRowCnt, 3).value = dt.Rows(i).Item("PresentAddress")
                                .Cells(iRowCnt, 4).value = dt.Rows(i).Item("Email")

                                iRowCnt = iRowCnt + 1
                            Next

                            ' FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                            xlAppToExport.ActiveCell.Worksheet.Cells(4, 1).AutoFormat( _
                                ExcelAutoFormat.xlRangeAutoFormatList3)
                        End With

                        ' SAVE THE FILE IN A FOLDER.
                        xlWorkSheetToExport.SaveAs(path & "EmployeeDetails.xlsx")

                        ' CLEAR.
                        xlAppToExport.Workbooks.Close() : xlAppToExport.Quit()
                        xlAppToExport = Nothing : xlWorkSheetToExport = Nothing

                        lblConfirm.Text = "Data Exported Sucessfully"
                        lblConfirm.Attributes.Add("style", "color:green; font: normal 14px Verdana;")
                        btView.Attributes.Add("style", "display:block")
                        btDownLoadFile.Attributes.Add("style", "display:block")
                    End If

                Catch ex As Exception
                    lblConfirm.Text = ex.Message
                    lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial")
                Finally
                    sda.Dispose() : sda = Nothing
                End Try
            End Using
        End Using
    End Sub

    ' VIEW THE EXPORTED EXCEL DATA.
    Protected Sub btView_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles btView.ServerClick
        Dim path As String = Server.MapPath("exportedfiles\")
        Try
            If Directory.Exists(path) Then                  ' CHECK IF THE FOLDER EXISTS.
                If File.Exists(path & "EmployeeDetails.xlsx") Then  ' CHECK IF THE FILE EXISTS.
                
                    ' SHOW (NOT DOWNLOAD) THE EXCEL FILE.
                    Dim xlAppToView As New Excel.Application
                    xlAppToView.Workbooks.Open(path & "EmployeeDetails.xlsx")
                    xlAppToView.Visible = True
                    
                End If
            End If
        Catch ex As Exception
            '
        End Try
    End Sub

    ' DOWNLOAD THE FILE.
    Protected Sub DownLoadFile(ByVal sender As Object, ByVal e As EventArgs)
        Try
            Dim sPath As String = Server.MapPath("exportedfiles\")

            Response.AppendHeader("Content-Disposition", "attachment;filename=EmployeeDetails.xlsx")
            Response.TransmitFile(sPath & "EmployeeDetails.xlsx")
            Response.[End]()

        Catch ex As Exception
        End Try
    End Sub
End Class

Also Read: Export Data from a Paging Enabled GridView to Excel in Asp.Net C#

There are so many ways you can use this format rich data once exported to excel. Email it, print it or share the file online on Skype etc.

Conclusion

Now you know how to export data into an excel file, particularly its new versions. However, it is not a recommend procedure for obvious reasons; there is no harm in knowing and learning this technique. You might use it in your personal project or even for you client, if they approve. In addition we have seen how to autoformat the excel sheet, dynamically, once you have exported the data. You can experiment with other available autoformat options.

Let us know your opinion about this article. Please do not forget to share it with your friends. Thank you.

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Facebook Google+
comments powered by Disqus

Join our Google Plus Community and be a part of a discussion!