Help to populate combobox from excel

Question

Hi I'm new to visual studio .. and i have a requirement to read a complete column from excel to a combo box in visual studio 2008 . Can anyone help me with this with the help of screenshots .. Thanks Venu

Satyapriya Nayak · Answer

Hi Venu Here is your solution.Run the attachments. Imports System.Data.OleDb Module Module1 Public con As OleDbConnection Sub pintu(ByVal s As String) con = New OleDbConnection(provider=Microsoft.Jet.OLEDB.4.0;  & _ data source=' & s &  ';  & Extended Properties=Excel 8.0;) End Sub Public com As OleDbCommand Public ds As DataSet Public oledbda As OleDbDataAdapter Public dt As DataTable Public str As String End Module Imports System.Data.OleDb Public Class Form1 Private Sub btnbrowse_Click(ByVal sender As System.Object ByVal e As System.EventArgs) Handles btnbrowse.Click Dim openfiledialog1 As New OpenFileDialog openfiledialog1.ShowDialog() openfiledialog1.Filter = allfiles|*.xls TextBox1.Text = openfiledialog1.FileName End Sub Private Sub btndisplay_Click(ByVal sender As System.Object ByVal e As System.EventArgs) Handles btndisplay.Click 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 ex As Exception MsgBox(ex.Message) End Try 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$]) Dim i As Integer For i = 0 To dt.Rows.Count - 1 ComboBox1.Items.Add(dt.Rows(i).ItemArray(0)) Next Catch ex As Exception MsgBox(ex.Message) End Try End Sub Private Sub Form1_Load(ByVal sender As System.Object ByVal e As System.EventArgs) Handles MyBase.Load ComboBox1.Text = Please select End Sub End Class Thanks If this post helps you mark it as answer

Satyapriya Nayak · Answer

Hi Venu This is a another question.This thread is solved.Can you please post it as a new thread. Thanks

venu Gajjala · Answer

This worked .. Thank you very much !! can u help me on how to insert values from a combobox to another excel sheet in C # ?? Thanks in advance Venu

Satyapriya Nayak · Answer

Hi Venu  Here is your solution.Run the attachments. 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 { comboBox1.Items.Add(dt.Rows[i].ItemArray[0]); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } } } Thanks If this post helps you mark it as answer

venu Gajjala · Answer

Hi can you please help me to get the code in c# as my requirement is with C# . Thanks Venu

Sam Hobbs · Answer

Oh Satyapriya! Where were you last night (night in India morning in America)? Well at least Venu has two suggestions to look at. I hope that Venu also uses many of the articles that are available in this web site.

Sam Hobbs · Answer

The following piece of code works for me; it uses ADO not ODBC. Note that there is at least one article in this web site that can help you with this; please read it. const string ConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + sourceFile + ;Extended Properties=Excel 8.0;; const string SQLCommand = SELECT * FROM [Sheet1$]; using (OleDbConnection objConnection = new System.Data.OleDb.OleDbConnection(ConnectionString)) { OleDbDataAdapter da = new OleDbDataAdapter(SQLCommand objConnection); DataSet ds = new DataSet(); try { da.Fill(ds); } catch (Exception ex) { Console.WriteLine(ex.ToString()); return; } foreach (DataRow row in ds.Tables[0].Rows) { Console.WriteLine(row.Field(1)); } }

venu Gajjala · Answer

when i trying to use a spread sheet as a data source it is not being accepted as a database .. im using odbc connection

Sam Hobbs · Answer

There are many useful articles in this web site; have a look at them. There are two general ways to accomplish your requirements; you can either use the spreadsheet as a data source or you could use the Excel object model to read the spreadsheet.