This article will show you how you can export gridview to
export to excel sheet using c# in windows application using data from sql
server database.
Some of my precious articles are as follows: Windows
Application In C#.Net (My first video article on youtube.com), Frozen
Rows and Columns in Asp.Net Mvc Webgrid Using jQuery Like Excel Sheet, Windows
Application - Excel Sheet Name in C#.Net, File
Upload and Display Uploaded Preview in MVC3 Razor Example Using C#.Net, Copy
File In C# Windows Application, Blinking
Text in C#.Net Windows Application.
So for this article first we will create a new windows application and create the below form as shown.
Now we will write code to bind the gridview.
private void BindGridView()
{
SqlConnection
con = new SqlConnection("Data Source=DELL-PC;Initial
Catalog=Demo;Integrated Security=True");
SqlDataAdapter
da = new SqlDataAdapter("select * from UserDetail;", con);
DataTable
dt = new DataTable();
da.Fill(dt);
if
(dt.Rows.Count > 0)
{
dataGridView1.DataSource =
dt;
}
}
|
Now we will add the code to export the excel sheet using
c#.net.
public void ExportToExcel(DataGridView
gridviewID, string excelFilename)
{
Microsoft.Office.Interop.Excel.Application objexcelapp = new Microsoft.Office.Interop.Excel.Application();
objexcelapp.Application.Workbooks.Add(Type.Missing);
objexcelapp.Columns.ColumnWidth =
25;
for
(int i = 1; i < gridviewID.Columns.Count +
1; i++)
{
objexcelapp.Cells[1, i] =
gridviewID.Columns[i - 1].HeaderText;
}
/*For
storing Each row and column value to excel sheet*/
for
(int i = 0; i < gridviewID.Rows.Count;
i++)
{
for
(int j = 0; j < gridviewID.Columns.Count;
j++)
{
if
(gridviewID.Rows[i].Cells[j].Value != null)
{
objexcelapp.Cells[i + 2, j + 1] =
gridviewID.Rows[i].Cells[j].Value.ToString();
}
}
}
MessageBox.Show("Your excel file exported successfully at
D:\\" + excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.SaveCopyAs("D:\\"
+ excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.Saved
= true;
}
|
We will add the reference for Microsoft.Office.Interop.Excel
.
Now here is the complete code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public
Form1()
{
InitializeComponent();
}
private
void Form1_Load(object
sender, EventArgs e)
{
BindGridView();
}
private
void button1_Click(object
sender, EventArgs e)
{
ExportToExcel(dataGridView1, "ExportedUserDetail");
}
private
void BindGridView()
{
SqlConnection
con = new SqlConnection("Data Source=DELL-PC;Initial
Catalog=Demo;Integrated Security=True");
SqlDataAdapter
da = new SqlDataAdapter("select * from UserDetail;", con);
DataTable
dt = new DataTable();
da.Fill(dt);
if
(dt.Rows.Count > 0)
{
dataGridView1.DataSource =
dt;
}
}
public
void ExportToExcel(DataGridView
gridviewID, string excelFilename)
{
Microsoft.Office.Interop.Excel.Application objexcelapp = new Microsoft.Office.Interop.Excel.Application();
objexcelapp.Application.Workbooks.Add(Type.Missing);
objexcelapp.Columns.ColumnWidth =
25;
for
(int i = 1; i < gridviewID.Columns.Count +
1; i++)
{
objexcelapp.Cells[1, i] =
gridviewID.Columns[i - 1].HeaderText;
}
/*For
storing Each row and column value to excel sheet*/
for
(int i = 0; i < gridviewID.Rows.Count;
i++)
{
for
(int j = 0; j < gridviewID.Columns.Count;
j++)
{
if
(gridviewID.Rows[i].Cells[j].Value != null)
{
objexcelapp.Cells[i +
2, j + 1] = gridviewID.Rows[i].Cells[j].Value.ToString();
}
}
}
MessageBox.Show("Your excel file exported successfully at
D:\\" + excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.SaveCopyAs("D:\\"
+ excelFilename + ".xlsx");
objexcelapp.ActiveWorkbook.Saved
= true;
}
}
}
|
In above code I have added the path where the file will save
as user will be able to save the file successfully.
Now check the excel output.
VIDEO TUTORIAL
Thank you so so so much! You save my life!
ReplyDeleteThanks for your valuable comment
DeleteIt is replacing the existing file,How to create a new excel file
ReplyDeletethankyou this code is very halp full
ReplyDeleteHey I received this error .. Help me
ReplyDeleteRetrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
Please check your framework library.
Delete