i have excel sheet in that records are there. i want to export the excel sheet into sqlserver.
how to do.please send the code in asp.net with csharp
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
private void Form1_Load(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\DataTableSample.xls");
Worksheet sheet = workbook.Worksheets[0];
DataTable data= sheet.ExportDataTable();
//set the string connection to be the local databasestring
string connectionStr="";
SqlConnection conn= new SqlConnection(connectionStr);
for (int i=0;i
{
DataRow row=data.Rows[i];
int columnCount=data.Columns.Count;
string[] columns=new string[columnCount];
for(int j=0;j
{
columns[j]=row[j].ToString();
}
// suppose there are only three columns in your datatable
SqlCommand command=new SqlCommand ("insert into DataTableSample(column1,column2,column3) values('"+columns[0]+"','"+columns[1]+
" ','"+columns[2]+"')");
command.ExecuteNonQuery();
}
conn.Close();
}
Munesh SharmaPosted Nov 11, 2014, 7:21 AM
Marina EberhardtPosted Nov 11, 2014, 5:13 AM
Satyapriya NayakPosted Dec 20, 2012, 1:55 AM
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
namespace Excel_data_gridview_db1
{
public partial class _Default : System.Web.UI.Page
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void insertdata_Click(object sender, EventArgs e)
{
OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("test.xls") + ";Extended Properties=Excel 8.0");
OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn);
conn.Open();
OleDbDataReader reader = com.ExecuteReader();
string Name = "";
string RollNumber = "";
while (reader.Read())
{
Name = valid(reader, 0);
RollNumber = valid(reader, 1);
insertdataintosql(Name, RollNumber);
}
conn.Close();
l1.Text = "Records Inserted successfully";
}
protected string valid(OleDbDataReader reader, int str1)
{
object val = reader[str1];
if (val != DBNull.Value)
return val.ToString();
else
return Convert.ToString(0);
}
public void insertdataintosql(string Name, string RollNumber)
{
SqlConnection conn = new SqlConnection(connStr);
SqlCommand com = new SqlCommand();
com.Connection = conn;
com.CommandText = "insert into student(Name,RollNumber)values(@Name,@RollNumber)";
com.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name;
com.Parameters.Add("@RollNumber", SqlDbType.VarChar).Value = RollNumber;
com.CommandType = CommandType.Text;
conn.Open();
com.ExecuteNonQuery();
conn.Close();
}
}
}