针对网上的一些代码片段,增加了程序的健壮性控制.
前台界面如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!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 runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="fuOpen" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="导入" onclick="btnUpload_Click" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
后台代码:
using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//确保已经选择了待导入的文件，首先上传，然后在服务器端完成导入
if (this.fuOpen.PostedFile.FileName != "")
{
//确保文件是excel格式
//Response.Write(this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.')));
if (this.fuOpen.FileName.Substring(this.fuOpen.FileName.LastIndexOf('.')+1) == "xls")
{
Random rd = new Random(1);
string filename = DateTime.Now.Date.ToString("yyyymmdd") + DateTime.Now.ToLongTimeString().Replace(":", "") + rd.Next(9999).ToString() + ".xls";
try
{
this.fuOpen.PostedFile.SaveAs(@Server.MapPath("fileupload/") + filename);
}
catch (HttpException he)
{
Response.Write("文件上传不成功，请检查文件是否过大，是否有写权限！");
return;
}
#region --------读取文件内容到服务器内存----------
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + Server.MapPath("fileupload") + "/" + filename + ";Extended Properties=Excel 8.0";
OleDbConnection thisconnection = new OleDbConnection(conn);
thisconnection.Open();
//要保证字段名和excel表中的字段名相同
string Sql = "select StuName,MajorID,CardID from [Sheet1$]";
OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, thisconnection);
DataSet ds = new DataSet();
mycommand.Fill(ds, "[Sheet1$]");
thisconnection.Close();
this.GridView1.DataSource = ds;
this.GridView1.DataBind();
#endregion
#region --------插入到数据库中---------
string conn1 = "User ID=sa;Data Source=127.0.0.1//SQLSERVER;Password=sa;Initial Catalog=Fee;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1 = new OleDbConnection(conn1);
thisconnection1.Open();
int count = ds.Tables["[Sheet1$]"].Rows.Count;
for (int i = 0; i < count; i++)
{
string stuName, majorID, cardID;
stuName = ds.Tables["[Sheet1$]"].Rows[i]["StuName"].ToString();
majorID = ds.Tables["[Sheet1$]"].Rows[i]["majorID"].ToString();
cardID = ds.Tables["[Sheet1$]"].Rows[i]["cardid"].ToString();
//id_3 = ds.Tables["[Sheet1$]"].Rows[i]["id_3"].ToString();
string excelsql = "insert into Fee_stu(stuname,majorid,cardid) values ('" + stuName + "','" + majorID + "','" + cardID + "') ";
OleDbCommand mycommand1 = new OleDbCommand(excelsql, thisconnection1);
try
{
mycommand1.ExecuteNonQuery();
}
catch (OleDbException ode)
{
Response.Write( "<b>导入不成功，请重试！</b>");
return;
}
}
Response.Write("更新成功");
thisconnection1.Close();
#endregion
}
else
{
Response.Write("导入文件的格式不正确！");
}
}
else
{
Response.Write("您还没有选择要导入的文件！");
}
}
}