HOW TO IMPORT EXCEL DATA IN DATAGRIDVIEW IN C# WINFORM.

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
C#  Excel 
C#
 3575  View(s)
Ratings:
Rate this:
I want to  Import Excel data in DataGridView In c# winform. How to do this please help me.
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: How to Import Excel data in DataGridView In c# winform.

    I will explain here how to import data from excel to datagridview  in c# windows application.
    Show below Code and excel formate.

     

    using System;
    using System.Windows.Forms;
    using System.IO;
    using System.Data;
    using System.Data.OleDb;

    namespace Import_Excel
    {
        public partial class Form1 : Form
        {
            private string Excel03Con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
            private string Excel07Con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
            public Form1()
            {
                InitializeComponent();
            }

            private void btnSelect_Click(object sender, EventArgs e)
            {
                openFileDialog1.ShowDialog();
            }

    private void openFileDialog1_FileOk(object sender, System.ComponentModel.CancelEventArgs e)
    {
        string filePath = openFileDialog1.FileName;
        string extension = Path.GetExtension(filePath);
        string header = rtobtnHeaderYes.Checked ? "YES" : "NO";
        string conStr, sheetName;

        conStr = string.Empty;
        switch (extension)
        {

            case ".xls": //Excel 97-03
                conStr = string.Format(Excel03Con, filePath, header);
                break;

            case ".xlsx": //Excel 07
                conStr = string.Format(Excel07Con, filePath, header);
                break;
        }

        //Get the name of the First Sheet.
        using (OleDbConnection con = new OleDbConnection(conStr))
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                cmd.Connection = con;
                con.Open();
                DataTable dtExcel = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                sheetName = dtExcel.Rows[0]["TABLE_NAME"].ToString();
                con.Close();
            }
        }

       
        using (OleDbConnection cn = new OleDbConnection(conStr))
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                using (OleDbDataAdapter oda = new OleDbDataAdapter())
                {
                    DataTable dt = new DataTable();
                    cmd.CommandText = "SELECT * From [" + sheetName + "]";
                    cmd.Connection = cn;
                    cn.Open();
                    oda.SelectCommand = cmd;
                    oda.Fill(dt);
                    cn.Close();

                  
                    GridView1.DataSource = dt;
                }
            }
        }
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }
        }
    }

    Excel File

    Result

    s

      Modified On Jan-07-2016 11:58:43 PM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!