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

Posted by Srilu.Nayini577 under C# on 8/7/2012 | Points: 10 | Views : 5348 | Status : [Member] | Replies : 3
In attached file i can get only employee names not the header.For combobox i need to attach Employee id and employee name column.But only employee names should be displayed.combobox text we should attach employee name and for combobox value we should attach employeeid.

Excel data like this:-
----------------------------
EMPLOYEE ID EMPLOYEE NAME BASIC HRA LTA DAYS
1 ABC 10000 5000 0 30
2 DEF 12000 6000 0 31
3 GHI 13000 7000 0 29
4 JKL 14000 8000 0 28
5 MNO 15000 9000 0 31
6 PQR 16000 7500 0 31


any help appreciable.
Thank you,

SRILATHA
.Net Developer



Responses

Posted by: Srilu.Nayini577 on: 8/8/2012 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Srilu.Nayini577 on: 8/8/2012 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Satyapriyanayak on: 1/24/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
http://www.c-sharpcorner.com/Blogs/7482/
http://www.c-sharpcorner.com/blogs/7509/retrieving-excel-data-to-combobox-export-to-excel.aspx

If this post helps you mark it as answer
Thanks

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

Login to post response