Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

Connectivity between excel and C#

Posted by Praveen Kumar on February 18, 2008

This code shows the connectivity with Excel to C#

Description–

Here we have one excelsheet which contains no of sheets and going to import on page and export to database. we have 4 dropdownlist box named valueDrpDwn(Sheet name) empnoDrpDwn(EmpNo),remarksDrpDwn(Sex) joindateDrpdwnList1(joinDate)

protected void Button1_Click(object sender, EventArgs e)
{
string fileUpload = (System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName));
string temp = “D:/”;

temp += FileUpload1.FileName;
TextBox1.Text = temp;
DataTable dt = new DataTable();
DataSet ds = new DataSet();
OleDbConnection connection = new OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;data source=” + TextBox1.Text + “;Extended Properties=\”Excel 5.0;HDR=NO;IMEX=1;\””);
OleDbDataReader myReader;
OleDbCommand mycmd;
connection.Open();
string query = “SELECT * FROM [” + sheetDrpDwn.SelectedValue + “$A1:M1]”;
mycmd = new OleDbCommand(query, connection);
myReader = mycmd.ExecuteReader();
valueDrpDwn.Items.Clear();
empnoDrpDwn.Items.Clear();
remarksDrpDwn.Items.Clear();
joindateDrpDwnList1.Items.Clear();

while (myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
valueDrpDwn.Items.Add(myReader[i].ToString());//this dropdown is for Name
empnoDrpDwn.Items.Add(myReader[i].ToString());
remarksDrpDwn.Items.Add(myReader[i].ToString());// this dropdown is for gender
joindateDrpDwnList1.Items.Add(myReader[i].ToString());
}
}
myReader.Close();
}

protected void import_Click(object sender, EventArgs e)
{
myconnection1.Open();
OleDbConnection ExcelConnection = new OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + TextBox1.Text + “;Extended Properties=Excel 8.0;”);
ExcelConnection.Open();
OleDbCommand ExcelCommand = new OleDbCommand(
“INSERT INTO [ODBC;Driver={MySql ODBC 5.1 Driver};Server=192.168.2.140;Database=yodlee_dbo;UID=praveen;Trusted_ Connection=yes].[TBLEMPLOYEE1] ” +
“(EMPLOYEENO,NAME,SEX) ” +
“SELECT [” + empnoDrpDwn.Text.ToString() + “], [” + valueDrpDwn.Text.ToString() + “],[” + remarksDrpDwn.Text.ToString() + “]” +
“FROM [Sheet1$] ” + “;”, ExcelConnection);
OdbcCommand ToTblCmd = new OdbcCommand(“insert TBLEMPLOYEE(EMPLOYEENO,NAME,SEX) select EMPLOYEENO,NAME,SEX from TBLEMPLOYEE1”, myconnection1);
OdbcCommand DelTblCmd = new OdbcCommand(“delete from TBLEMPLOYEE1”, myconnection1);
ExcelCommand.CommandType = CommandType.Text;
ExcelCommand.ExecuteNonQuery();
ToTblCmd.CommandType = CommandType.Text;
ToTblCmd.ExecuteNonQuery();
DelTblCmd.CommandType = CommandType.Text;
DelTblCmd.ExecuteNonQuery();
Page.Response.Write(“Congrats Data inserted”);
ExcelConnection.Close();

Advertisements

One Response to “Connectivity between excel and C#”

  1. Sandhya said

    Hi Praveen This post was really favorable for me.
    But i am not getting FileUpload1.PostedFile.FileName why we are using??? please help me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: