Friday, September 30, 2011

Read Excell and Insert to DB


How to read excel data and insert into in sql server database.
Here this example show read excel file from pc and insert into Db

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 code
<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>
<td>
</td>
<td>
</td>
</tr>
</table>