How to import Data into Excel sheet i try my self but ..

Question

i'm trying to import Data into Excel sheet from Sql server 2014 using C # but it does not Work.

 

 

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.SqlClient;

using System.IO;

using System.Data.OleDb;

namespace TelerikWinFormsApp2

{

public partial class RadForm1 : Telerik.WinControls.UI.RadForm

{

public RadForm1()

{

InitializeComponent();

}

private void button1_Click(object sender, EventArgs e)

{

string Excelfile = _uploaddata.Text;

int ID;

String PerName;

String PerLast;

String PerDesignation;

int PerCnic;

int PerPhoneNo;

string sWorkbook = "[Sheet1$]";

// string path = Path.GetFileName(FileUpload1.FileName);

Excelfile = Excelfile.Replace(" ", "");

// FileUpload1.SaveAs(Server.MapPath("~/ExcelFile/") + path);

// String ExcelPath = Server.MapPath("~/ExcelFile/") + path;

OleDbConnection mycon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; " +

"Data Source = " + Excelfile + "; Extended Properties=Excel 8.0; Persist Security Info = False");

mycon.Open();

OleDbCommand cmd = new OleDbCommand("select * from" + sWorkbook, mycon);

// OleDbCommand cmd = new OleDbCommand(@"Select * from [" + Excelfile + "]", mycon);

OleDbDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

// Response.Write("<br/>"+dr[0].ToString());

ID = Convert.ToInt32(dr[0].ToString());

PerName = dr[1].ToString();

PerLast = dr[2].ToString();

PerDesignation = dr[3].ToString();

PerCnic = Convert.ToInt32(dr[4].ToString());

PerPhoneNo =Convert.ToInt32(dr[5].ToString());

savedata(ID, PerName, PerLast, PerDesignation, PerCnic, PerPhoneNo);

}

// Label3.Text = "Data Has Been Saved Successfully";

}

private void button2_Click(object sender, EventArgs e)

{

OpenFileDialog OFD = new OpenFileDialog();

if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)

{

this._uploaddata.Text = OFD.FileName;

}

}

private void savedata(int ID, String PerName, String PerLast, String PerDesignation, int PerCnic, int PerPhoneNo )

{

String query = "insert into Excel sheet(ID,PerName,PerLast,PerDesignation,PerCnic,PerPhoneNo) values(" + ID + ",'"

+ PerName + "','" + PerLast + "','" + PerDesignation + "','"+PerCnic +"','"+ PerPhoneNo +"')";

String mycon = "Data Source=PC105\\SQLSVR2014;Initial Catalog=Excel_sheet; User ID=sa;Password=sa@123";

SqlConnection con = new SqlConnection(mycon);

con.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = query;

cmd.Connection = con;

cmd.ExecuteNonQuery();

}

}

}

 

 

 

 Please Help me with your valuable thoughts..