In previous article, I have mentioned reading excel file in C# and how to create excel file in C# without using Office or Interop but now In this article, I am going to explain you how you can can export your ASP.NET web-form datatable into excel file using C#, in which we will first fetch data from database and have used Open Source library OpenXML and CloseXML to get data from datatable into Excel file in C# / VB.

Let's get started with this, before we proceed here is the image of the database, which we will be using in this example.

datatabase-used-in-export-datatable-to-excel-c-sharp-min.png

Basically we are using AdventureWorks2012 database and it's table [Production].[Product]

(You can skip this if you have already created project)Now, let's create a new ASP.NET web-project in your Visual Studio, navigate to File-> New -> Project -> Select "Web" in the left-pne and "ASP.NET web-application" in right-pane, name it and click OK, then select "Empty" template -> Click OK (then your project must be generated with empty template.)

Now, as we will be using OpenXML opn source library, let's install it using Nuget library, so navigate to Tools->Nuget Package manager -.> Nuget packge manager console and use the below command to install it

Install-Package DocumentFormat.OpenXml

nuget-package-manager-console-install-openxml-for-export-to-excel-min.png

Once OpenXML installed using Nuget package manager console, you need to install ClosedXML now using Nuget, so run the below command

Install-Package ClosedXML

Now, we have both the required files in our project, let's create a "Default.aspx" file.

We will be using create a Button on the HTML or Front end Side and will be calling the C# code on button Click, so in the Default.aspx your code can be as below

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
       <asp:Button Text="Export" OnClick="ExportToExcel" runat="server" />
    </form>
</body>
</html>

as you can see we are calling "ExportToExcel" on button Click, so C# code in the .cs file will be as below

using ClosedXML.Excel;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace ExportDatatabletoExcel
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void ExportToExcel(object sender, EventArgs e)
        {
            string constr = @"Data Source=DESKTOP-4K41ID9\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated Security=true";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT ProductID,Name, ProductNumber FROM[AdventureWorks2012].[Production].[Product]"))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            using (XLWorkbook wb = new XLWorkbook())
                            {
                                wb.Worksheets.Add(dt, "Products");

                                Response.Clear();
                                Response.Buffer = true;
                                Response.Charset = "";
                                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                Response.AddHeader("content-disposition", "attachment;filename=DataTableToExcelExport.xlsx");
                                using (MemoryStream MyMemoryStream = new MemoryStream())
                                {
                                    wb.SaveAs(MyMemoryStream);
                                    MyMemoryStream.WriteTo(Response.OutputStream);
                                    Response.Flush();
                                    Response.End();
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Note: you cannot select GUID based column to export using ClosedXML/OpenXML as they don't support it, you may get error like "Unable to set cell value to 694215b7-08f7-4c0d-acb1-d734ba44c0c8 (GUID)"

Build and run it in browser.

After opening the browser Click "Export" button, you will see output as below

export-to-excel-using-c-sharp-asp-net-min.png

VB.NET code for the above code, converted using online tools

Imports ClosedXML.Excel
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Namespace ExportDatatabletoExcel
    Public Partial Class [Default]
        Inherits System.Web.UI.Page

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        End Sub

        Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)
            Dim constr As String = "Data Source=DESKTOP-4K41ID9\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated Security=true"

            Using con As SqlConnection = New SqlConnection(constr)

                Using cmd As SqlCommand = New SqlCommand("SELECT ProductID,Name, ProductNumber FROM[AdventureWorks2012].[Production].[Product]")

                    Using sda As SqlDataAdapter = New SqlDataAdapter()
                        cmd.Connection = con
                        sda.SelectCommand = cmd

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

                            Using wb As XLWorkbook = New XLWorkbook()
                                wb.Worksheets.Add(dt, "Products")
                                Response.Clear()
                                Response.Buffer = True
                                Response.Charset = ""
                                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                                Response.AddHeader("content-disposition", "attachment;filename=DataTableToExcelExport.xlsx")

                                Using MyMemoryStream As MemoryStream = New MemoryStream()
                                    wb.SaveAs(MyMemoryStream)
                                    MyMemoryStream.WriteTo(Response.OutputStream)
                                    Response.Flush()
                                    Response.[End]()
                                End Using
                            End Using
                        End Using
                    End Using
                End Using
            End Using
        End Sub
    End Class
End Namespace

That's it, we are done.

Export datatable to excel in C# using Interop

Another way of exporting datatable to excel in C# is using Microsoft.Office.Interop.Excel, let's take a look how to do it usinfg InterOp.

First, you need to add reference of Microsoft.Office.Interop.Excel.dll in your project, as shown in the below image

export-to-excel-using-interop-min.png

Once you have added the reference in your project, you can use the below C# code which will generate excel file on button click

using System;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using Microsoft.Office.Interop.Excel;

namespace ExportDatatabletoExcel
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void ExportToExcelUsingInterop(object sender, EventArgs e)
        {
            Application oXL;
            Workbook oWB;
            Worksheet oSheet;
            Range oRange;
            var filepath = @"F:\ExportToExcel\ExportToExcel.xlsx";
            string constr = @"Data Source=DESKTOP-1PM1CJ9\SQLEXPRESS2;Initial Catalog=AdventureWorks2012;Integrated Security=true";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT ProductID,Name, ProductNumber FROM[AdventureWorks2012].[Production].[Product]"))
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        try
                        {
                            // Start Excel and get Application object. 
                            oXL = new Microsoft.Office.Interop.Excel.Application();

                            // Set some properties 
                            oXL.Visible = true;
                            oXL.DisplayAlerts = false;

                            // Get a new workbook. 
                            oWB = oXL.Workbooks.Add(Missing.Value);

                            // Get the Active sheet 
                            oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWB.ActiveSheet;
                            oSheet.Name = "ExportToExcelUsingInterOp";
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            using (System.Data.DataTable dt = new System.Data.DataTable())
                            {
                                sda.Fill(dt);
                                int rowCount = 1;
                                foreach (DataRow dr in dt.Rows)
                                {
                                    rowCount += 1;
                                    for (int i = 1; i < dt.Columns.Count + 1; i++)
                                    {
                                        // Add the header the first time through 
                                        if (rowCount == 2)
                                        {
                                            oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                                        }
                                        oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                                    }
                                }

                                // Resize the columns 
                                Range c1 = oSheet.Cells[1, 1];
                                Range c2 = oSheet.Cells[rowCount, dt.Columns.Count];
                                oRange = oSheet.get_Range(c1,c2);
                                oRange.EntireColumn.AutoFit();
                            }
                            // Save the sheet and close 
                            oSheet = null;
                            oRange = null;
                           
                            oWB.SaveAs(filepath,XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
            false, false, XlSaveAsAccessMode.xlNoChange,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                            oWB.Close(Missing.Value, Missing.Value, Missing.Value);
                            oWB = null;
                            oXL.Quit();
                        }
                        catch
                        {
                            throw;
                        }
                        finally
                        {
                            // Clean up 
                            // NOTE: When in release mode, this does the trick 
                            GC.WaitForPendingFinalizers();
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            GC.Collect();
                        }
                    }
                }
            }
        }
    }
}

Again in HTML we are just placing the button

 <form id="form1" runat="server">
        <div>
            <asp:Button Text="Export To Excel using InterOp" OnClick="ExportToExcelUsingInterop" runat="server" />
        </div>
    </form>

Here is the output image of the updated Excel File found at "F:\ExportToExcel\ExportToExcel.xslx"

generated-excel-file-using-interop-min.png

We are done with this method also.

I will prefer to use ClosedXML/OpenXMl based method to export datatable to excel in asp.net web-forms, because it allows user to add formatting in Excel file easily using C# code, what do you think, feel free to add your comments below.

You may also like to read

Read Excel file and import data into GridView using Datatable in ASP.NET

Convert String to List in C# and string array to list

Convert List to string in C# (Comma-separated, New-Line)

Understanding ASP.NET Gridview control with an example

Connect to Oracle Database in C# (ASP.NET Web-Forms Example)

Understanding session state in asp.net