Retrieving Excel Data and Binding to a ComboBox in C#

The following code snippet allows you to browser an excel file on your machine and get data from the Excel sheet and bind one column of the Excel sheet to a ComboBox control.

The code starts by creating a connection with the Excel sheet using OleDb data provider and retrieves all data in a DataSet and binds with a DataGridView control.

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.Data.OleDb;

namespace Retrieving_excel_data_to_combobox

{

public partial class Form1 : Form

{

public OleDbConnection con;

public void pintu(string s)

{

con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source='" + s + " '; " + "Extended Properties=Excel 8.0;");

}

public OleDbCommand com;

public DataSet ds;

public OleDbDataAdapter oledbda;

public DataTable dt;

public string str;

public Form1()

{

InitializeComponent();

}

private void btnbrowse_Click(object sender, EventArgs e)

{

OpenFileDialog openfiledialog1 = new OpenFileDialog();

openfiledialog1.ShowDialog();

openfiledialog1.Filter = "allfiles|*.xls";

TextBox1.Text = openfiledialog1.FileName;

}

private void Form1_Load(object sender, EventArgs e)

{

comboBox1.Text = "Please select";

}

private void btndisplay_Click(object sender, EventArgs e)

{

pintu(TextBox1.Text);

try

{

con.Open();

str = "select * from [sheet1$]";

com = new OleDbCommand(str, con);

ds = new DataSet();

oledbda = new OleDbDataAdapter(com);

oledbda.Fill(ds, "[sheet1$]");

con.Close();

DataGridView1.DataSource = ds;

DataGridView1.DataMember = "[sheet1$]";

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

try

{

con.Open();

str = "select * from [sheet1$]";

com = new OleDbCommand(str, con);

oledbda = new OleDbDataAdapter(com);

ds = new DataSet();

oledbda.Fill(ds, "[sheet1$]");

con.Close();

dt = ds.Tables["[sheet1$]"];

int i = 0;

for (i = 0; i <= dt.Rows.Count -1; i++)

{

comboBox1.Items.Add(dt.Rows[i].ItemArray[0]);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

Thanks for reading.