Friday, September 30, 2011
Read Excell and Insert to DB
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>