Click here to Skip to main content
15,885,985 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi
I want to display the excel sheet in datagridview based on the selected sheet using comboxbox.
Below is the code for that and i am not sure why it is not working can anyone please help me in that..
Thanks in advance.

C#
public partial class Form1 : Form
    {
       public Form1()
        {
            InitializeComponent();
           
        }
        public string[] GetExcelSheetNames(string excelfilename)
        {
            OleDbConnection con = null;
            DataTable dt = null;

            string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilename + ";Extended Properties=Excel 12.0;";
con = new OleDbConnection(connStr);
                 con.Open();
                 dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            string[] excelSheetNames = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheetNames[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            con.Close();
            return excelSheetNames;
        }
  

        private void button1_Click(object sender, EventArgs e)
        {
                OpenFileDialog openDialog = new OpenFileDialog();
                openDialog.Title = "";
                openDialog.InitialDirectory = @"C:\\";
                openDialog.Filter = "ExcelSheet(*.xsls)|*.xsls|All Files(*.*)|*.*";
                openDialog.FilterIndex = 1;
                openDialog.RestoreDirectory = true;
                if (openDialog.ShowDialog() == DialogResult.OK)
                {
                    if (openDialog.FileName != "")
                    {
                        textBox1.Text = openDialog.FileName;
                       
                        comboBox1.DataSource = GetExcelSheetNames(openDialog.FileName);
     
                    }
                }
                else
                {
                    MessageBox.Show("chose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                        
        }

      private void comboBox1_SelectedIndexChanged_1(object sender, EventArgs e)
        {
            OleDbConnection con = null;
            String conStr = "Provider=Microsoft.Jet.OLEDB..0;" + "Data Source=" + textBox1.Text + ";Extended Properties=Excel 8.0;";
            con = new OleDbConnection(conStr);
            OleDbCommand oconn = new OleDbCommand("Select * From [" + comboBox1.SelectedValue + "]", con);
            OleDbDataAdapter da = new OleDbDataAdapter(oconn);


            DataTable data = new DataTable();
            da.Fill(data);
            dataGridView1.DataSource = data;
            
        }              
    }
}
Posted
Updated 13-Nov-14 22:36pm
v3
Comments
DamithSL 14-Nov-14 4:40am    
any exception? have you debug and check for the issue?
Karthik Bilakanti 14-Nov-14 4:44am    
I am getting
Exception:The 'Microsoft.Jet.OLEDB..0' provider is not registered on the local machine.
in line da.Fill(data); in comboBox1_SelectedIndexChanged_1 method
DamithSL 14-Nov-14 4:48am    
if your GetExcelSheetNames working fine why not use same provider "Microsoft.ACE.OLEDB.12.0"?
Karthik Bilakanti 14-Nov-14 4:51am    
If i use the same provider then it is showing "The 'Microsoft.Jet.OLEDB.12.0' provider is not registered on the local machine." while debugging.

1 solution

try these


string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox1.Text + ";Extended Properties='Excel 12.0 xml;HDR=YES;'";

// Create Connection to Excel Workbook
//OleDbConnection connection =new OleDbConnection();
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
//connection.Open();
{
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
OleDbDataAdapter adpt = new OleDbDataAdapter(command);
DataTable dt = new DataTable();
adpt.Fill(dt);
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900