Export Datatable (Gridview) to Excel in Windows Application using C#, VB.NET

Introduction: 

Here I will explain how to export datatable or datagridview to excel in windows forms application using  c# vb.net or export datagridview or datatable data to excel in windows application using c# , vb.net or export datagridview / datatable to excel in windows forms using c# , vb.net.

Description:
  
In previous articles I explained bind data to datagridview in windows application, create windows form application file, delete windows service from system, use progressbar control in windows application and many articles relating to windows application, asp.net, c#,vb.net and jQuery. Now I will explain how to export data from datagridview to excel in c# windows application

Before implement this example first design one table UserDetails in your database as shown below

Column Name	Data Type	Allow Nulls
UserId	Int(IDENTITY=TRUE)	Yes
UserName	varchar(50)	Yes
FirstName	varchar(50)	Yes
LastName	varchar(50)	Yes

Once table created in database enter some dummy data to test application after that create new windows application with following steps

Open visual studio à File à New à select Windows Forms Application à Give Name and Click OK

Now drag and drop DataGridView control from Data section, button and label control from toolbox like as shown below

Once we placed controls in our form that will be like as shown below

Now open code behind file and add following namespaces

C# Code

using System; using System.Data; using System.IO; using System.Windows.Forms;

VB.NET

Imports System.Data Imports System.IO Imports System.Windows.Forms

After that add following code to export datagridview data to excel

C# Code

public partial class Form1 : Form { public Form1() { InitializeComponent(); BindDataGrid(); } private void BindDataGrid() { DataTable dt = new DataTable(); dt.Columns.Add("UserId", typeof(Int32)); dt.Columns.Add("UserName", typeof(string)); dt.Columns.Add("Education", typeof(string)); dt.Columns.Add("Location", typeof(string)); dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai"); dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur"); dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu"); dt.Rows.Add(4, "Rohini", "MSC", "Chennai"); dt.Rows.Add(5, "Mahendra", "CA", "Guntur"); dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur"); dataGridView1.DataSource = dt; } private void button1_Click(object sender, EventArgs e) { if (dataGridView1.Rows.Count > 0) { try { // Bind Grid Data to Datatable DataTable dt = new DataTable(); foreach (DataGridViewColumn col in dataGridView1.Columns) { dt.Columns.Add(col.HeaderText, col.ValueType); } int count = 0; foreach (DataGridViewRow row in dataGridView1.Rows) { if (count < dataGridView1.Rows.Count - 1) { dt.Rows.Add(); foreach (DataGridViewCell cell in row.Cells) { dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString(); } } count++; } // Bind table data to Stream Writer to export data to respective folder StreamWriter wr = new StreamWriter(@"E:\\Book1.xls"); // Write Columns to excel file for (int i = 0; i < dt.Columns.Count; i++) { wr.Write(dt.Columns[i].ToString().ToUpper() + "\t"); } wr.WriteLine(); //write rows to excel file for (int i = 0; i < (dt.Rows.Count); i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (dt.Rows[i][j] != null) { wr.Write(Convert.ToString(dt.Rows[i][j]) + "\t"); } else { wr.Write("\t"); } } wr.WriteLine(); } wr.Close(); label1.Text = "Data Exported Successfully"; } catch (Exception ex) { throw ex; } } } }

VB.NET

Imports System.Data Imports System.IO Imports System.Windows.Forms Public Class Form1 Public Sub New() InitializeComponent() BindDataGrid() End Sub Private Sub BindDataGrid() Dim dt As New DataTable() dt.Columns.Add("UserId", GetType(Int32)) dt.Columns.Add("UserName", GetType(String)) dt.Columns.Add("Education", GetType(String)) dt.Columns.Add("Location", GetType(String)) dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai") dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur") dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu") dt.Rows.Add(4, "Rohini", "MSC", "Chennai") dt.Rows.Add(5, "Mahendra", "CA", "Guntur") dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur") dataGridView1.DataSource = dt End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click If dataGridView1.Rows.Count > 0 Then Try ' Bind Grid Data to Datatable Dim dt As New DataTable() For Each col As DataGridViewColumn In dataGridView1.Columns dt.Columns.Add(col.HeaderText, col.ValueType) Next Dim count As Integer = 0 For Each row As DataGridViewRow In dataGridView1.Rows If count < dataGridView1.Rows.Count - 1 Then dt.Rows.Add() For Each cell As DataGridViewCell In row.Cells dt.Rows(dt.Rows.Count - 1)(cell.ColumnIndex) = cell.Value.ToString() Next End If count += 1 Next ' Bind table data to Stream Writer to export data to respective folder Dim wr As New StreamWriter("E:\\Book1.xls") ' Write Columns to excel file For i As Integer = 0 To dt.Columns.Count - 1 wr.Write(dt.Columns(i).ToString().ToUpper() & vbTab) Next wr.WriteLine() 'write rows to excel file For i As Integer = 0 To (dt.Rows.Count) - 1 For j As Integer = 0 To dt.Columns.Count - 1 If dt.Rows(i)(j) IsNot Nothing Then wr.Write(Convert.ToString(dt.Rows(i)(j)) & vbTab) Else wr.Write(vbTab) End If Next wr.WriteLine() Next wr.Close() label1.Text = "Data Exported Successfully" Catch ex As Exception Throw ex End Try End If End Sub End Class

Now run your code and check output that will be like as shown below

Demo

Excel File Output

Download Sample Attached

If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS Subscribe by Email