Hi Vincenzo67,
Refer below sample. I have used the ClosedXml library.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Download OpenXml SDK 2.0
Download ClosedXml Library
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
HTML
The HTML markup consists of a GridView consisting of BoundField and TemplateFields and a Button control.
The TemplateField consists of TextBox, HyperLink, DropDownList controls.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Id" />
<asp:TemplateField HeaderText="Contact Name">
<ItemTemplate>
<asp:TextBox ID="txtName" runat="server" Text='<%# Eval("ContactName") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="City">
<ItemTemplate>
<asp:HyperLink ID="lnkCity" runat="server" NavigateUrl="#" Text='<%# Eval("City") %>'></asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Country">
<ItemTemplate>
<asp:DropDownList ID="ddlCountries" runat="server" BackColor="Yellow">
<asp:ListItem Text="Select" Value=""></asp:ListItem>
<asp:ListItem Text="India" Value="India"></asp:ListItem>
<asp:ListItem Text="USA" Value="USA"></asp:ListItem>
<asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView> <br />
<asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />
Namespaces
You will need to import the following namespaces.
C#
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using ClosedXML.Excel;
VB.Net
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports ClosedXML.Excel
Code
Inside the Page Load event handle, the GridView is populate from database.
When the Export Button is clicked, a DataTable is created and columns are added to the DataTable by looping through the GridView Header cells.
Then, a loop is executed over the GridView rows and inside that another loop is executed over Controls present inside the GridView.
Then, using a SWITCH-CASE statement the value of each control is extracted and updated in the DataTable row.
Finally, the DataTable is exported to Excel using the ClosedXml.
For more details on exporting DataTable to excel please refer my article Export DataTable to Excel in ASP.Net using C# and VB.Net.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string strConnString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(strConnString))
{
using (SqlDataAdapter sda = new SqlDataAdapter("SELECT TOP 5 * FROM Customers", con))
{
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
protected void ExportToExcel(object sender, EventArgs e)
{
DataTable dt = new DataTable("GridView");
//Adding columns to DataTable.
foreach (TableCell cell in GridView1.HeaderRow.Cells)
{
dt.Columns.Add(cell.Text);
}
foreach (GridViewRow row in GridView1.Rows)
{
dt.Rows.Add();
int i = 0;
foreach (TableCell cell in row.Cells)
{
List<Control> controls = new List<Control>();
if (cell.Controls.Count == 0)
{
dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
}
//Add controls to be removed to Generic List.
foreach (Control control in cell.Controls)
{
controls.Add(control);
}
//Loop through the controls to be removed and replace with Text.
foreach (Control control in controls)
{
switch (control.GetType().Name)
{
case "HyperLink":
dt.Rows[dt.Rows.Count - 1][i] = (control as HyperLink).Text;
break;
case "TextBox":
dt.Rows[dt.Rows.Count - 1][i] = (control as TextBox).Text;
break;
case "LinkButton":
dt.Rows[dt.Rows.Count - 1][i] = (control as LinkButton).Text;
break;
case "DropDownList":
dt.Rows[dt.Rows.Count - 1][i] = (control as DropDownList).SelectedItem.Text;
break;
}
cell.Controls.Remove(control);
}
i++;
}
}
//Exporting DataTable using ClosedXML.
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
using (MemoryStream MyMemoryStream = new MemoryStream())
{
wb.SaveAs(MyMemoryStream);
MyMemoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(strConnString)
Using sda As SqlDataAdapter = New SqlDataAdapter("SELECT TOP 5 * FROM Customers", con)
Using dt As DataTable = New DataTable()
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
End Using
End Using
End Using
End Sub
Protected Sub ExportToExcel(ByVal sender As Object, ByVal e As EventArgs)
Dim dt As DataTable = New DataTable("GridView")
'Adding columns to DataTable.
For Each cell As TableCell In GridView1.HeaderRow.Cells
dt.Columns.Add(cell.Text)
Next
For Each row As GridViewRow In GridView1.Rows
dt.Rows.Add()
Dim i As Integer = 0
For Each cell As TableCell In row.Cells
Dim controls As List(Of Control) = New List(Of Control)()
If cell.Controls.Count = 0 Then
dt.Rows(dt.Rows.Count - 1)(i) = row.Cells(i).Text
End If
'Add controls to be removed to Generic List.
For Each control As Control In cell.Controls
controls.Add(control)
Next
'Loop through the controls to be removed and replace with Text.
For Each control As Control In controls
Select Case control.GetType().Name
Case "HyperLink"
dt.Rows(dt.Rows.Count - 1)(i) = (TryCast(control, HyperLink)).Text
Case "TextBox"
dt.Rows(dt.Rows.Count - 1)(i) = (TryCast(control, TextBox)).Text
Case "LinkButton"
dt.Rows(dt.Rows.Count - 1)(i) = (TryCast(control, LinkButton)).Text
Case "DropDownList"
dt.Rows(dt.Rows.Count - 1)(i) = (TryCast(control, DropDownList)).SelectedItem.Text
End Select
cell.Controls.Remove(control)
Next
i += 1
Next
Next
'Exporting DataTable using ClosedXML.
Using wb As XLWorkbook = New XLWorkbook()
wb.Worksheets.Add(dt)
Response.Clear()
Response.Buffer = True
Response.Charset = ""
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx")
Using MyMemoryStream As MemoryStream = New MemoryStream()
wb.SaveAs(MyMemoryStream)
MyMemoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.End()
End Using
End Using
End Sub
Screenshots
The GridView
Exported Excel
