.aspx:
<%@ Page Language="C#" AutoEventWireup="true" Codebehind="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>
<script type="text/javascript">
function check(){
if( document.getElementById("fuUploadingFile").value == ""){
alert("文件不能为空!");
return false;
}
return true;
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="fuUploadingFile" runat="server" /><asp:Button ID="btnSubmit"
runat="server" Text="上传" OnClientClick="return check()" OnClick="btnSubmit_Click" />
</div>
</form>
</body>
</html>
.CS
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace 导入数据
{
public partial class _Default : System.Web.UI.Page
{
SqlConnection con = null;
SqlCommand cmd = null;
string constr = @"Data Source=.;Initial Catalog=MyDB;Integrated Security=True";
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 上传文件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (fuUploadingFile.HasFile)
{
//有文件
string fileName = fuUploadingFile.FileName;
string serverPath = Server.MapPath("./UpExcelFile/");
//检查文件路径
CheckFileMapPath(fileName, serverPath);
//上传至服务器
fuUploadingFile.SaveAs(serverPath + fileName);
//获取Excel内容
GetExcel(fileName, serverPath);
}
}
/// <summary>
/// 检查目录
/// </summary>
/// <param name="fileName"></param>
/// <param name="serverPath"></param>
protected void CheckFileMapPath(string fileName, string serverPath)
{
if (!Directory.Exists(serverPath))
{
//不存在路径
Directory.CreateDirectory(serverPath);
}
if (File.Exists(serverPath + "\\" + fileName))
{
//同路径下有同名文件
File.Delete(serverPath + "\\" + fileName);
}
}
/// <summary>
/// 读取Excel文件内容
/// </summary>
/// <param name="fileName"></param>
/// <param name="serverPath"></param>
protected void GetExcel(string fileName, string serverPath)
{
string olbcn = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + serverPath + fileName + ";Extended Properties=Excel 8.0";
OleDbConnection ocon = new OleDbConnection(olbcn);
ocon.Open();
OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", ocon);
DataSet ds = new DataSet();
oda.Fill(ds);
ocon.Close();
SaveSQL(ds);
}
/// <summary>
/// Excel数据导入到数据库中
/// </summary>
/// <param name="ds"></param>
protected void SaveSQL(DataSet ds)
{
int sum = 0;
if (ds.Tables[0].Rows.Count <= 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('没有数据')</script>");
}
else
{
using (con = new SqlConnection(constr))
{
con.Open();
cmd = con.CreateCommand();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string sqlstr = "insert into T_MingPieces (FDepartment,Name,Photo,HandPhone,Email) values(";
sqlstr += "'" + ds.Tables[0].Rows[i][0].ToString() + "',";
sqlstr += "'" + ds.Tables[0].Rows[i][1].ToString() + "',";
sqlstr += "'" + ds.Tables[0].Rows[i][2].ToString() + "',";
sqlstr += "'" + ds.Tables[0].Rows[i][3].ToString() + "',";
sqlstr += "'" + ds.Tables[0].Rows[i][4].ToString() + "')";
cmd.CommandText = sqlstr;
sum += cmd.ExecuteNonQuery();
}
}
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script>alert('共导入" + sum + "条数据!')</script>");
}
}
}
}