C# DataGridView Export to Excel Sheet


In my Previous Article's :
  1. Generate Barcode in C#.NET
  2. Creating User Control in C#.NET
  3. Speech Translator in C#.NET

C# DataGridView Export to Excel Sheet

Property of Button's and DataGridview

    Load Data Gridview :
  • Text : Load Data Gridview
  • Name : Btn_Load_Data_Gridview
    Export Data Grid to Excel :
  • Text : Export Data Grid to Excel
  • Name : Btn_Load_Data_Grid_to_Excel
    DataGridview :
  • Name : dataGridView1

Load Data Gridview button is used to get data from Database and display in DataGridview Controls. DataGridview Controls is used to display data in the tabular format is every easy to understand.

Export Data Gridview to Excel button clicked whatever data as showing in DataGridview Controls that will convert to Excel Sheet (i.e nothing but a export datagridview to excel) and file will be saved in C:\Users\Srikanth\ Documents Drive as a name of  "export-datagrid-to-excel-informations.xls".

Create Database

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

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);
            }
        }
    }
}
0 Komentar untuk "C# DataGridView Export to Excel Sheet"

Back To Top