[siddharthbjoshi]

i trying to get excel sheet name and binding sheet name into combobox
but using below code, only last sheet name is showing in combobox.
i dont know why

 

//For Reading excel sheet name and mount into combobox
private void btnbrows_Click(object sender, EventArgs e)
		{
			if (Opendialog.ShowDialog() == DialogResult.OK)
			{
				currentfilename = Opendialog.FileName;
				tbfilepath.Text = currentfilename;

				//Excel importing function
				string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
				"Data Source=" + tbfilepath.Text.ToString() + ";Extended Properties=Excel 8.0;";
				con = new OleDbConnection(conStr);


				con.Open();
				dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

			   if (dt == null)
			   {
				   MessageBox.Show("no shhet found");;
			   }
			   string[] excelSheetNames = new string[dt.Rows.Count];
			   int i = 0;
			   int c = 0;

			   foreach (DataRow row in dt.Rows)
			   {
				   excelSheetNames[i] = row[c].ToString();
				   i++;
				   c++;
			   }
			   for (int s = 0; s < excelSheetNames.Length; s++)
			   {
				   comboBox1.Items.Add(excelSheetNames[s].ToString());
			   }
					  
			}

one more task is
i want to transfer excel data into SQL data base in existing table
for that i using following code

string constr= ConfigurationManager.AppSettings["dbpath"];
			string query="SELECT * INTO temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database='"+tbfilepath.Text+"','SELECT * FROM [Sheet2$]')";
			SqlConnection conn=new SqlConnection(constr);
			conn.Open();
			SqlCommand cmd = new SqlCommand(query, conn);
			SqlDataReader dr = cmd.ExecuteReader();//findng error in this line
			dr.Read();
			MessageBox.Show("done");
			conn.Close();

[shelby poston]

I just uploaded a solution for the excel to sql take a look.  Excel2Sql.zip (23.85K)
Number of downloads: 953

siddharthbjoshi, on 14 Aug, 2008 - 12:40 AM, said:

i trying to get excel sheet name and binding sheet name into combobox
but using below code, only last sheet name is showing in combobox.
i dont know why

 

//For Reading excel sheet name and mount into combobox
private void btnbrows_Click(object sender, EventArgs e)
		{
			if (Opendialog.ShowDialog() == DialogResult.OK)
			{
				currentfilename = Opendialog.FileName;
				tbfilepath.Text = currentfilename;

				//Excel importing function
				string conStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
				"Data Source=" + tbfilepath.Text.ToString() + ";Extended Properties=Excel 8.0;";
				con = new OleDbConnection(conStr);


				con.Open();
				dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

			   if (dt == null)
			   {
				   MessageBox.Show("no shhet found");;
			   }
			   string[] excelSheetNames = new string[dt.Rows.Count];
			   int i = 0;
			   int c = 0;

			   foreach (DataRow row in dt.Rows)
			   {
				   excelSheetNames[i] = row[c].ToString();
				   i++;
				   c++;
			   }
			   for (int s = 0; s < excelSheetNames.Length; s++)
			   {
				   comboBox1.Items.Add(excelSheetNames[s].ToString());
			   }
					  
			}

one more task is
i want to transfer excel data into SQL data base in existing table
for that i using following code

string constr= ConfigurationManager.AppSettings["dbpath"];
			string query="SELECT * INTO temp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database='"+tbfilepath.Text+"','SELECT * FROM [Sheet2$]')";
			SqlConnection conn=new SqlConnection(constr);
			conn.Open();
			SqlCommand cmd = new SqlCommand(query, conn);
			SqlDataReader dr = cmd.ExecuteReader();//findng error in this line
			dr.Read();
			MessageBox.Show("done");
			conn.Close();