asp.net excel 导入
aspx文件
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<link href="../css/site.css" rel="stylesheet">
<link href="../css/style.css" rel="stylesheet">
<style type="text/css">
<!--
.STYLE1 {color: #FFFFFF}
body {
margin-left: 0px;
margin-top: 0px;
padding-right: 0px;
padding-bottom: 0px;
padding-top: 0px;
width:100% ;
height:100%;
}
-->
</style>
</head>
<body style="padding-right: 0px; padding-left: 0px; padding-bottom: 0px; margin: 0px;
padding-top: 0px; width: 100%; height: 100%">
<form id="form1" runat="server">
<div>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse"
bordercolor="#C0C0C0" width="100%" id="AutoNumber1">
<tr>
<td colspan="2" align="center" style="height: 1px" bgcolor="#7C7CB5">
<strong>信息</strong></td>
</tr>
<tr>
<td align="right" class="lanyuds" style="width: 30%; height: 22px">
数据文件:</td>
<td class="lanyuds" style="height: 22px" align="left">
<input id="File1" runat="server" name="File1" type="file" />数据文件格式是(.xls)
<asp:Label ID="Label1" runat="server" ForeColor="Red"></asp:Label>
</td>
</tr>
<tr>
<td align="center" class="TDtop1" colspan="2">
<asp:Button ID="btnSubmit" SkinID="ButSkin" runat="server" Text=" 导入 " OnClick="btnSubmit_Click"
ValidationGroup="uu" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
cs主要代码
string dt = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
string tnphoto = string.Empty;
if (this.File1.Value.ToString() != string.Empty)
{
string qpath = this.File1.PostedFile.FileName;//全路径
string fname = qpath.Substring(qpath.LastIndexOf("\\") + 1);//上传文件名
string ftype = qpath.Substring(qpath.LastIndexOf(".") + 1);//上传文件类型
if (ftype == "xls")
{
fname = "esf" + dt + '.' + ftype;
this.File1.PostedFile.SaveAs(Server.MapPath("wenjian") + "\\" + fname);
tnphoto = "wenjian\\" + fname;
}
else
{
Response.Write("<script>alert('数据文件格式不正确111!')</script>");
Response.Write("<script>history.go(-1)</script>");
Response.End();
}
try
{
string strconn = ConfigurationManager.AppSettings["DBConnectionString"];
SqlConnection cn = new SqlConnection(strconn);
cn.Open();
String sql = "insert into ESF select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=" + base.Server.MapPath(".") + "\\" + tnphoto + "',Sheet1$)";
SqlCommand cm = new SqlCommand(sql, cn);
int i = cm.ExecuteNonQuery();
cn.Close();
Label1.Text = "数据导入成功";
Response.Redirect("esfdefault.aspx");
}
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
else
{
Label1.Text = "请选择数据文件";
}