Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

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

Sep 1, 2015
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

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

Excel File Output

Export Datagridview Data to Excel in C# Windows Application (VB.NET)

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 RSS subscribe by email Subscribe by Email

4 comments :

Unknown said...

How to export data table to word and pdf in vb.net for window forms ?

Blogger said...

How to export multiple datatables in diff sheet of excel file (c#)?

Anonymous said...

Dear Suresh,
I have a form that loads data dynamically for every click separately. How can i name the excel file that is going to imported every time.

kanhaiya said...

thank you sir u helped a lot to me

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.