Reader Level:
ARTICLE

Import Data from Excel to DataGridView in C#

Posted by Ankur Gupta Articles | Learn .NET July 20, 2010
In this article you will learn how to import Data from Excel to DataGridView in C#.
  • 6
  • 0
  • 166370
Download Files:
 

First need to add the reference "Microsoft ADO Ext. 2.8". You can easily add it from COM components.

Add an open Dialog box control on form

Form1.JPG

Put the following code on Browser button click events…..

        private void button1_Click_1(object sender, EventArgs e)

        {

            OpenFileDialog fdlg = new OpenFileDialog();

            fdlg.Title = "Select file";

            fdlg.InitialDirectory = @"c:\";

            fdlg.FileName = txtFileName.Text;

            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";

            fdlg.FilterIndex = 1;

            fdlg.RestoreDirectory = true;

            if (fdlg.ShowDialog() == DialogResult.OK)

            {

                txtFileName.Text = fdlg.FileName;

                Import();

                Application.DoEvents();

            }

        }


This will filter only Excel file from your Machine.

This Excel file can contains more than one Sheet. You need to add another form to all excel sheets name so that user can select any one excel sheet which he want to import.

Write the following code on Page Load even of this form

        private void Select_Tables_Load(object sender, EventArgs e)

        {

            if (!DataTables)

            {

                if (Tables != null)

                {

                    for (int tables = 0; tables < Tables.Length; tables++)

                    {

                        try

                        {

                            ListViewItem lv = new ListViewItem();

                            lv.Text = Tables[tables].ToString();

                            lv.Tag = tables;

                            lstViewTables.Items.Add(lv);

                        }

                        catch (Exception ex)

                        { }

                    }

                }

            }

            else

            {

                if (dtTable.Rows.Count>0)

                {

                    for (int tables = 0; tables < dtTable.Rows.Count; tables++)

                    {

                        try

                        {

                            ListViewItem lv = new ListViewItem();

                            lv.Text = dtTable.Rows[tables][0].ToString();

                            lv.Tag = dtTable.Rows[tables][0];

                            lstViewTables.Items.Add(lv);

                        }

                        catch (Exception ex)

                        { }

                    }

                }

            }

        }
 

By the following function we can find the total sheets in Excel file.

        public static string[] GetTableExcel(string strFileName)

        {

            string[] strTables = new string[100];

            Catalog oCatlog = new Catalog();

            ADOX.Table oTable = new ADOX.Table();

            ADODB.Connection oConn = new ADODB.Connection();

            oConn.Open("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";", "", "", 0);

            oCatlog.ActiveConnection = oConn;

            if (oCatlog.Tables.Count > 0)

            {

                int item = 0;

                foreach (ADOX.Table tab in oCatlog.Tables)

                {

                    if (tab.Type == "TABLE")

                    {

                        strTables[item] = tab.Name;

                        item++;

                    }

                }

            }

            return strTables;

        }

 
Form2.JPG

Following function return a dataset so that you can bind it from Data Grid View  easily.

        public static DataTable GetDataTableExcel(string strFileName, string Table)

        {

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 8.0;HDR=Yes;IMEX=1\";");

            conn.Open();

            string strQuery = "SELECT * FROM [" + Table + "]";

            System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);

            System.Data.DataSet ds = new System.Data.DataSet();

            adapter.Fill(ds);

            return ds.Tables[0];

        }

 

 Form3.JPG

To see the complete source code you can download the zip file.

erver'>
Article Extensions
Contents added by mohan mohan on Sep 05, 2013
Contents added by testtestwu testtestwu on Aug 01, 2013
???? ????!!
Contents added by mohammed noori on Mar 18, 2013
thanks my dear
Contents added by Vineeth Kannan on Feb 18, 2013
I am getting error after selecting the sheet from select tables ka window.. the error says something like as follows- the microsoft access database engine could not find the object listviewitem:{Sheet1$}.make sure thet the object exists......... 

I am pasting the two cs files ka code..
LoadExcel file..

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.IO;
using System.Data.SqlClient;
using System.Windows.Documents;
using System.Windows.Controls;
using ADOX;
using System.Data.OleDb;

namespace Attendance_Record
{
    public partial class LoadExcel : Form
    {
        public LoadExcel()
        {
            InitializeComponent();
        }

        public static string SelectedTable = string.Empty;

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fdlg = new OpenFileDialog();
            fdlg.Title = "Select file";
            fdlg.InitialDirectory = @"c:\";
            fdlg.FileName = textBox1.Text;
            fdlg.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
            fdlg.FilterIndex = 1;
            fdlg.RestoreDirectory = true;
            if (fdlg.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = fdlg.FileName;
                Import();
                Application.DoEvents();
            }
        }

        private void Import()
        {
            if (textBox1.Text.Trim() != string.Empty)
            {
                try
                {
                    string[] strTables = GetTableExcel(textBox1.Text);

                    frmSelectTables objSelectTable = new frmSelectTables(strTables);
                    objSelectTable.ShowDialog(this);
                    objSelectTable.Dispose();
                    if ((SelectedTable != string.Empty) && (SelectedTable != null))
                    {
                       
                        DataTable dt = GetDataTableExcel(textBox1.Text, SelectedTable);
                       // 
                       // dataGridView1.DataSource = dt.DefaultView;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                   //MessageBox.Show("hii");
                }
            }
        }

        public static DataTable GetDataTableExcel(string strFileName, string Table)
        {
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";");
            conn.Open();
            string strQuery = "SELECT * FROM [" + Table + "]";
           //
            OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
            System.Data.DataSet ds = new System.Data.DataSet();
           // DataTable dt = new DataTable();
            adapter.Fill(ds);
            
            return ds.Tables[0];
        }

        public static string[] GetTableExcel(string strFileName)
        {
            string[] strTables = new string[100];
            Catalog oCatlog = new Catalog();
            ADOX.Table oTable = new ADOX.Table();
            ADODB.Connection oConn = new ADODB.Connection();
            oConn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + strFileName + "; Extended Properties = \"Excel 12.0 Xml;HDR=Yes;IMEX=1\";", "", "", 0);
            oCatlog.ActiveConnection = oConn;
            if (oCatlog.Tables.Count > 0)
            {
                int item = 0;
                foreach (ADOX.Table tab in oCatlog.Tables)
                {
                    if (tab.Type == "TABLE")
                    {
                        strTables[item] = tab.Name;
                        item++;
                    }
                }
            }
            return strTables;
        }

        private void LoadExcel_Load(object sender, EventArgs e)
        {

        }

        

       

       
    }
}


**********************
second file.. Select tables..

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;

namespace Attendance_Record
{
    public partial class frmSelectTables : Form
    {
        public frmSelectTables(string[] StrTable)
        {
            InitializeComponent();
            Tables = StrTable;
        }
        public frmSelectTables(DataTable dt)
        {
            InitializeComponent();
            dtTable = dt;
            DataTables = true;
        }
        DataTable dtTable = new DataTable(); 
        string tableName = string.Empty;
        string[] Tables;
        bool DataTables = false;
        private void Select_Tables_Load(object sender, EventArgs e)
        {
            if (!DataTables)
            {
                if (Tables != null)
                {
                    for (int tables = 0; tables < Tables.Length; tables++)
                    {
                        try
                        {
                            ListViewItem lv = new ListViewItem();
                            lv.Text = Tables[tables].ToString();
                            lv.Tag = tables;
                            lstViewTables.Items.Add(lv);
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
            else
            {
                if (dtTable.Rows.Count>0)
                {
                    for (int tables = 0; tables < dtTable.Rows.Count; tables++)
                    {
                        try
                        {
                            ListViewItem lv = new ListViewItem();
                            lv.Text = dtTable.Rows[tables][0].ToString();
                            lv.Tag = dtTable.Rows[tables][0];
                            lstViewTables.Items.Add(lv);
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            if (lstViewTables.Items.Count > 0)
            {
                tableName = lstViewTables.SelectedItems[0].ToString(); 
                if (tableName != string.Empty)
                {
                    LoadExcel.SelectedTable = tableName;
                    this.Close();
                }
                else
                {
                    MessageBox.Show("Select a Table");
                }
            }
            else
            {
                this.Close();
            }
        }

        private void lstViewTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            //tableName = lstViewTables.Items[lstViewTables.SelectedIndices].ToString();
        }

        private void lstViewTables_ItemSelectionChanged(object sender, ListViewItemSelectionChangedEventArgs e)
        {
            tableName = e.Item.Text;
        }
    
        
    }
}


Plz reply asap.. thanx in advance..
Contents added by Jesus Parada on Jan 09, 2013
thanks !
COMMENT USING

Trending up