Tuesday, August 24, 2010

How to insert excel file into sql database in ASP.NET

Import EXCEL sheet data to SQL Database table in ASP.NET
Here I will explain how to read excel file data and insert into in sql server database.

 protected void btnupload_Click(object sender, EventArgs e)   
{
String excelConnectionString1;
String fname = sendupload.PostedFile.FileName;
if (sendupload.PostedFile.FileName.EndsWith(".xls"))
{
String excelsheet;
sendupload.SaveAs(Server.MapPath("~/Image/" + sendupload.FileName));
if (sendupload.PostedFile.FileName.EndsWith(".xls"))
{
excelConnectionString1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/ExcelFiles/" + sendupload.FileName) + ";Extended Properties=Excel 8.0";
OleDbConnection myEcelConnection1 = new OleDbConnection(excelConnectionString1);
myEcelConnection1.Open();
if (txtsheet.Text.Length == 0)
{
lblmsg.Text = "Please Write File Name";
}
else
{
excelsheet = "[" + txtsheet.Text + "$" + "]";
string sheet = "Select * from [" + txtsheet.Text + "$" + "]";
OleDbCommand cmd1 = new OleDbCommand(sheet, myEcelConnection1);
cmd1.CommandType = CommandType.Text;
OleDbDataAdapter myAdapter1 = new OleDbDataAdapter(cmd1);
DataSet myDataSet1 = new DataSet();
myAdapter1.Fill(myDataSet1);
int a = myDataSet1.Tables[0].Rows.Count - 1;
string name;
string id;
string cls;
string num;
for (int i = 0; i <= a; i++)
{
name = myDataSet1.Tables[0].Rows[i].ItemArray[0].ToString();
id = myDataSet1.Tables[0].Rows[i].ItemArray[1].ToString();
cls = myDataSet1.Tables[0].Rows[i].ItemArray[2].ToString();
num = myDataSet1.Tables[0].Rows[i].ItemArray[3].ToString();
SqlConnection con = new SqlConnection(constr);
con.Open();
SqlCommand command = new SqlCommand("Insert into StudentDetails(StudentName,CivilIdNumber,Class,StudentID)values(@valname,@valids,@valcls,@valnum)", con);
command.Parameters.Add("@valname", SqlDbType.VarChar, 50).Value = name;
command.Parameters.Add("@valids", SqlDbType.VarChar, 50).Value = id;
command.Parameters.Add("@valcls", SqlDbType.VarChar, 50).Value = cls;
command.Parameters.Add("@valnum", SqlDbType.VarChar, 50).Value = num;
command.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter(command);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
}
}
}
}
}

.aspx page
 <table width="100%">   
<tr>
<td colspan="3"> <span style="font-family: Segoe UI"> File Upload</span> </td>
</tr>
<tr>
<td colspan="3">
<asp:FileUpload ID="sendupload" runat="server" /> </td>
</tr>
<tr>
<td colspan="3">
<span style="font-family: Segoe UI"> Sheet Name: </span>
<asp:TextBox ID="txtsheet" runat="server"> </asp:TextBox>
<asp:Label ID="lblmsg" runat="server"> </asp:Label>
</td>
</tr>
<tr>
<td> <asp:Button ID="btnupload" runat="server" Text="Upload" OnClick="btnupload_Click" /> </td>
</tr>
</table>


Thanks & Regards
Santosh

2 comments:

  1. Hi santhosh,

    It is reaaly help ful to me

    Great post
    bhaskar
    http://csharpektroncmssql.blogspot.com

    ReplyDelete
  2. Thanks.. great example of great help

    ReplyDelete