forum

Home / DeveloperSection / Forums / How to import excel data in dataGridView in c# winform.

Ask Question

How to import excel data in dataGridView in c# winform.

Anonymous User 7635 07-Jan-2016
I want to  Import Excel data in DataGridView In c# winform. How to do this please help me.
c# c#  excel 
Updated on 07-Jan-2016
Anonymous User

Anonymous User

Follow
I am a content writter !

Can you answer this question?

Answer

1 Answers

Anonymous User

Anonymous User

07-Jan-2016

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

answer 415870701104006

Result

sanswer 415870701104006
MindStick Training

Liked By