Home > DeveloperSection > Forums > How to import excel data in dataGridView in c# winform.
Ankit Singh

Total Post:341

Points:2389
Posted on    January-07-2016 10:21 PM

 C# C#  Excel 
Ratings:


 1 Reply(s)
 1391  View(s)
Rate this:
I want to  Import Excel data in DataGridView In c# winform. How to do this please help me.


aditya kumar Patel

Total Post:250

Points:1782
Posted on    January-07-2016 10:40 PM

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

Don't want to miss updates? Please click the below button!

Follow MindStick