connectseekers.com

Tuesday, 17 March 2015

Export GridView Data To Excel Sheet Using C#.Net In Windows Application

3/17/2015 - By Pranav Singh 6

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.


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


About the Author

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

6 comments:

  1. Thank you so so so much! You save my life!

    ReplyDelete
  2. It is replacing the existing file,How to create a new excel file

    ReplyDelete
  3. thankyou this code is very halp full

    ReplyDelete
  4. Hey I received this error .. Help me
    Retrieving 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)).

    ReplyDelete

Please let me know your view

Free Ebooks


About Us

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Contact Us

For writing article in this website please send request by your

GMAIL ID: dotnetpools@gmail.com
Email Id : contact@aspdotnet-pools.com

Bugs and Suggestions

As we all know that this website is for sharing knowledge and providing proper solution. So while reading the article is you find any bug or if you have any suggestion please mail us at contact@aspdotnet-pools.com.

Partners


Top jQuery Plugins

Global Classified : Connectseekers.com
© 2014
back to top