2 Replies - 28251 Views - Last Post: 03 March 2010 - 04:51 AM Rate Topic: -----

#1 siddharthbjoshi  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 1
  • Joined: 14-August 08

Reading Excel sheet data and Sheet name and transfer to SQL data base

Posted 14 August 2008 - 12:40 AM

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


Is This A Good Question/Topic? 1

Replies To: Reading Excel sheet data and Sheet name and transfer to SQL data base

#2 shelby poston  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 5
  • Joined: 15-April 08

Re: Reading Excel sheet data and Sheet name and transfer to SQL data base

Posted 03 September 2008 - 01:40 PM

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

View Postsiddharthbjoshi, 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();

Was This Post Helpful? 1

#3 Guest_Guest*


Reputation:

Re: Reading Excel sheet data and Sheet name and transfer to SQL data base

Posted 03 March 2010 - 04:51 AM

View Postsiddharthbjoshi, on 13 August 2008 - 11:40 PM, 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();


Thank you for you example...
my little research show me that you "c" variable must be always 2
I think it is because column 2 contains names of sheets.

You also can user as
 

//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;

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

Was This Post Helpful? 0

Page 1 of 1