Hi,i tested its working like charm, just created this DEMO as ur requirement:
and if u have any problem plz let me know.
and on .aspx page :
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div><table style="height: 70px; width: 404px;"><tbody>
<tr> <td class="style1">Select Date :</td> <td class="style2"></td> </tr>
<tr> <td class="style1">Upload Excel File :</td> <td class="style2">
<asp:fileupload id="FileUpload1" runat="server" xmlns:asp="#unknown"></asp:fileupload></td> </tr>
</tbody></table></div><asp:button id="btnUpload" onclick="btnUpload_Click" runat="server" text="Upload Excel File" xmlns:asp="#unknown">
</asp:button>
<asp:gridview id="ExcelGridView" runat="server" xmlns:asp="#unknown">
</asp:gridview>
</form>
</body>
</html>
</html>
on .cs page :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
public partial class ReadDataFromExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.FileName.ToString() != "" && FileUpload1.ToString().Contains("."))
{
String filepath = Server.MapPath("UploadedFiles");
FileUpload1.SaveAs(filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
string excelPath = (filepath + "\\" + FileUpload1.FileName.Split('\\')[FileUpload1.FileName.Split('\\').Length - 1]);
var dataset = new DataTable();
dataset = GetExcelData(excelPath); ;
ExcelGridView.DataSource = GetExcelData(excelPath);
ExcelGridView.DataBind();
foreach (DataRow row in dataset.Rows)
{
System.Data.SqlClient.SqlConnection sqlConnection1 =
new System.Data.SqlClient.SqlConnection("Data Source=GIRIJESH-PC;Initial Catalog=TestData;Integrated Security=True");
string temp1 = "'"+Convert.ToString(row[0])+"'";
string temp2 = "'"+Convert.ToString(row[1])+"'";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
string text = "INSERT INTO [TestData].[dbo].[UserInfo]([Age],[State])VALUES(" + temp1 + "," + temp2 + ")";
cmd.CommandText = text;
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
}
}
public DataTable GetExcelData(string ExcelFilePath)
{
string OledbConnectionString = string.Empty;
OleDbConnection objConn = null;
OledbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
objConn = new OleDbConnection(OledbConnectionString);
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
OleDbCommand objCmdSelect = new OleDbCommand("Select * from [Sheet1$B1:C4]", objConn);
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
objAdapter.SelectCommand = objCmdSelect;
DataSet objDataset = new DataSet();
objAdapter.Fill(objDataset, "ExcelDataTable");
objConn.Close();
return objDataset.Tables[0];
}
}