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.
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
Once OpenXML installed using Nuget package manager console, you need to install ClosedXML now using Nuget, so run the below command
Install-Package ClosedXMLNow, 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
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
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"
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)
Buy us a coffee
Become a Patron