In the realm of C#.NET programming, efficient data handling is paramount for developers. In my previous articles, we explored topics such as generating barcodes and creating user controls. Today, let's delve into a practical aspect – exporting DataGridView to Excel in C#.NET.
Understanding the Properties
Before we jump into the coding details, let's familiarize ourselves with the essential properties involved in this process. We have two buttons and a DataGridView in play:
- Load Data Gridview Button:
- Text: Load Data Gridview
- Name: Btn_Load_Data_Gridview
- Export Data Grid to Excel Button:
- Text: Export Data Grid to Excel
- Name: Btn_Load_Data_Grid_to_Excel
- DataGridView
- Name: dataGridView1
Data Handling at a Glance
The "Load Data Gridview" button serves a crucial role by fetching data from the database and displaying it in the DataGridView control. This control, designed to exhibit data in a tabular format, enhances data readability.
On the other hand, the "Export Data Gridview to Excel" button takes the displayed data in the DataGridView and converts it into an Excel sheet. The resulting file is then saved in the "C:\Users\Srikanth\Documents" directory with the name "export-datagrid-to-excel-informations.xls."
Creating the Database
To begin, we need a database structure. In this example, a simple table named "Book" is created with fields such as Book_Id, Book_name, Book_author, Book_Publisher_name, and Book_Published_date. Data insertion is demonstrated using SQL statements.
Creating the Database
To begin, we need a database structure. In this example, a simple table named "Book" is created with fields such as Book_Id, Book_name, Book_author, Book_Publisher_name, and Book_Published_date. Data insertion is demonstrated using SQL statements.
USE [master]
GO
/****** Object: Table [dbo].[Book] Script Date: 03/29/2015 23:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Book]undefined
[Book_Id] [nvarchar]undefined5) NULL,
[Book_name] [nvarchar]undefined50) NULL,
[Book_author] [nvarchar]undefined30) NULL,
[Book_Publisher_name] [nvarchar]undefined50) NULL,
[Book_Published_date] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Book VALUES undefined'Bk1','ASP.NET PROGRAMMING EBOOKS', 'JAMES', 'HI-TECH PUBLISHERS', '2015-03-27 00:00:00.000')
INSERT INTO dbo.Book VALUES undefined'Bk2','C#.NET COOKBOOK', 'ARVIND', 'WTech PUBLISHERS', '2015-03-27 00:00:00.000')
INSERT INTO dbo.Book VALUES undefined'Bk3','SQL SERVER 2012 FOUNDATION EBOOK', 'JAMES', 'HI-TECH PUBLISHERS', '2015-03-27 00:00:00.000')
INSERT INTO dbo.Book VALUES undefined'Bk4','Beginning ASP.NET EBOOKS', 'JAMES', 'HI-TECH PUBLISHERS', '2015-03-27 00:00:00.000')
C# Coding for Data Handling
Now, let's explore the C# code responsible for the data handling process. The code is divided into sections:
Form Initialization
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;
using Excel = Microsoft.Office.Interop.Excel;
namespace Export_Datagridview_to_Excel_in_Csharp
{
public partial class Form1 : Form
{
public Form1undefined)
{
InitializeComponentundefined);
}
}
}
Loading Data into DataGridView
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;
using Excel = Microsoft.Office.Interop.Excel;
namespace Export_Datagridview_to_Excel_in_Csharp
{
public partial class Form1 : Form
{
public Form1undefined)
{
InitializeComponentundefined);
}
private void Form1_Loadundefinedobject sender, EventArgs e)
{
}
private void Btn_Load_Data_Gridview_Clickundefinedobject sender, EventArgs e)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
connectionString = "Server = .; Database = master; Trusted_Connection = Yes;";
cnn = new SqlConnectionundefinedconnectionString);
cnn.Openundefined);
sql = "SELECT * FROM Book";
SqlDataAdapter dscmd = new SqlDataAdapterundefinedsql, cnn);
DataSet ds = new DataSetundefined);
dscmd.Fillundefinedds);
dataGridView1.DataSource = ds.Tables[0];
}
}
}
Exporting DataGridView to Excel
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;
using Excel = Microsoft.Office.Interop.Excel;
namespace Export_Datagridview_to_Excel_in_Csharp
{
public partial class Form1 : Form
{
public Form1undefined)
{
InitializeComponentundefined);
}
private void Form1_Loadundefinedobject sender, EventArgs e)
{
}
private void Btn_Load_Data_Gridview_Clickundefinedobject sender, EventArgs e)
{
SqlConnection cnn;
string connectionString = null;
string sql = null;
connectionString = "Server = .; Database = master; Trusted_Connection = Yes;";
cnn = new SqlConnectionundefinedconnectionString);
cnn.Openundefined);
sql = "SELECT * FROM Book";
SqlDataAdapter dscmd = new SqlDataAdapterundefinedsql, cnn);
DataSet ds = new DataSetundefined);
dscmd.Fillundefinedds);
dataGridView1.DataSource = ds.Tables[0];
}
private void Btn_Load_Data_Grid_to_Export_Clickundefinedobject sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Applicationundefined);
xlWorkBook = xlApp.Workbooks.AddundefinedmisValue);
xlWorkSheet = undefinedExcel.Worksheet)xlWorkBook.Worksheets.get_Itemundefined1);
int i = 0;
int j = 0;
for undefinedi = 0; i <= dataGridView1.RowCount - 1; i++)
{
for undefinedj = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAsundefined"export-datagrid-to-excel-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Closeundefinedtrue, misValue, misValue);
xlApp.Quitundefined);
releaseObjectundefinedxlWorkSheet);
releaseObjectundefinedxlWorkBook);
releaseObjectundefinedxlApp);
MessageBox.Showundefined"Excel file created , you can find the file c:\\export-datagrid-to-excel-informations.xls");
}
private void releaseObjectundefinedobject obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObjectundefinedobj);
obj = null;
}
catch undefinedException ex)
{
obj = null;
MessageBox.Showundefined"Exception Occured while releasing object " + ex.ToStringundefined));
}
finally
{
GC.Collectundefined);
}
}
}
}
Conclusion
Efficient data handling is a critical aspect of software development. In this tutorial, we explored the process of exporting DataGridView to Excel in C#.NET. This functionality empowers developers to seamlessly manage and present tabular data, fostering a more user-friendly experience. By incorporating the provided code snippets, developers can enhance their C#.NET applications with this valuable feature. Stay tuned for more insightful articles on IT technologies and programming practices.
0 Comments