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.