adding data from excel to combobox control in windows form [Resolved]

Iam attaching my requirement.For that i got solution.If anybody wants the solution Please take code:



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;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Threading;
using System.Globalization;
using System.IO;
using Microsoft.Office.Interop.Word;





namespace Payrollsystem
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
comboBox1.Text = "Please select";
}

public OleDbConnection con;
public void pintu(string s)
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + s + ";Extended Properties=Excel 8.0;";

con = new OleDbConnection(excelConnectionString);


}

public OleDbCommand com;
public DataSet ds;
public OleDbDataAdapter oledbda;
public System.Data.DataTable dt;
public string str;


private void btnbrowse_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
openFileDialog1.Filter = "Excel files|*.xls";
textBox1.Text = openFileDialog1.FileName;


}




private void button2_Click(object sender, EventArgs e)
{
panel1.Enabled = true;

int year = DateTime.Now.Year;
for (int i = year - 1; i <= year + 3; i++)
{
comboBox3.Items.Add(i.ToString());
}

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();



}
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++)
{

string s = dt.Rows[i].ItemArray[1].ToString();
if (s != null && s != "" && s != "EMPLOYEE NAME")
{
comboBox1.Items.Add(dt.Rows[i].ItemArray[1]);



}

Excel.Application app = new Excel.Application();

Excel.Workbook wbook = null;

Excel.Worksheet wsheet = null;

Excel.Range range = null;

app.Visible = false;

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

string filepath = textBox1.Text;

if (filepath != "")
{

wbook = app.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value);



string currentSheet = "Sheet1";

wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item(currentSheet);

range = wsheet.get_Range("A7", "F7");

System.Array myvalues = (System.Array)range.Cells.Value2;

string[] valueArray = ConvertToStringArray(myvalues);

comboBox2.SelectedItem = valueArray[4];

comboBox3.SelectedItem = valueArray[5];

}



}
}


catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

}

string[] ConvertToStringArray(System.Array values)
{

// create a new string array
string[] theArray = new string[values.Length];

// loop through the 2-D System.Array and populate the 1-D String Array
for (int i = 1; i <= values.Length; i++)
{
if (values.GetValue(1, i) == null)
theArray[i - 1] = "";
else
theArray[i - 1] = (string)values.GetValue(1, i).ToString();
}

return theArray;
}





}
}

Thank you,
 Download source file

SRILATHA
.Net Developer

Srilu.Nayini577, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
I got solution for my problem.

This is the code:

private void Form1_Load(object sender, EventArgs e)
{
comboBox1.Text = "Please select";
}

public OleDbConnection con;
public void pintu(string s)
{
string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + s + ";Extended Properties=Excel 8.0;";

con = new OleDbConnection(excelConnectionString);


}

public OleDbCommand com;
public DataSet ds;
public OleDbDataAdapter oledbda;
public System.Data.DataTable dt;
public string str;


private void btnbrowse_Click(object sender, EventArgs e)
{
openFileDialog1.ShowDialog();
openFileDialog1.Filter = "Excel files|*.xls";
textBox1.Text = openFileDialog1.FileName;


}
private void button2_Click(object sender, EventArgs e)
{
panel1.Enabled = true;

int year = DateTime.Now.Year;
for (int i = year - 1; i <= year + 3; i++)
{
comboBox3.Items.Add(i.ToString());
}

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();



}
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++)
{

string s = dt.Rows[i].ItemArray[1].ToString();
if (s != null && s != "" && s != "EMPLOYEE NAME")
{
comboBox1.Items.Add(dt.Rows[i].ItemArray[1]);



}


catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

}

Thank you,

SRILATHA
.Net Developer

Srilu.Nayini577, if this helps please login to Mark As Answer. | Alert Moderator