后台代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
public partial class Excel : System.Web.UI.Page
{
int count = 0;
int counts = 0;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnExcel_Click(object sender, EventArgs e)
{
string filePath = "";
//DirectoryInfo mydir = new DirectoryInfo(@"F:\Excel");
DirectoryInfo mydir = new DirectoryInfo(Server.MapPath("file/"));
FileInfo[] files = mydir.GetFiles();
for (int i = 0; i < files.Length; i++)
{
//filePath = "F:\\Excel\\" + files[i].ToString();
filePath = Server.MapPath("file/" + files[i].ToString());
//string xlsName = files[i].ToString().Replace(".xls", "");
string connString03 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=no;IMEX=1;'";//hdr=no或yes：是否输入标题（如：姓名，性别，==） IMEX=1：表示是否强制转换为文本
// string connString07 = "Provider=Microsoft.Ace.OleDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;HDR=Yes;IMEX=1;'";
OleDbConnection excelconn = new OleDbConnection(connString03);
excelconn.Open();
string excelsel = "select * from [Sheet1$]";
OleDbCommand excelCmd = new OleDbCommand(excelsel,excelconn);
OleDbDataAdapter excelda = new OleDbDataAdapter();
excelda.SelectCommand = excelCmd;
DataSet excelDs = new DataSet();
excelda.Fill(excelDs);
DataTable dt = excelDs.Tables[0];
DataView myview = new DataView(dt);
try
{
foreach (DataRowView myDrv in myview)
//for (int j = 0; j < excelDs.Tables[0].Rows.Count;j++ )
{
count++;
string pro_Excel = "excel_Insert";
SqlParameter[] param = {new SqlParameter("@Name",SqlDbType.NVarChar),
new SqlParameter("@Info",SqlDbType.NVarChar),
new SqlParameter("@address",SqlDbType.NVarChar)
};
//param[0].Value = excelDs.Tables[0].Rows[j][0].ToString().Trim();
//param[1].Value = excelDs.Tables[0].Rows[j][1].ToString().Trim();
//param[2].Value = excelDs.Tables[0].Rows[j][2].ToString().Trim();
param[0].Value = myDrv[0].ToString().Trim();
param[1].Value = myDrv[1].ToString().Trim();
param[2].Value = myDrv[2].ToString().Trim();
counts = sqlHelper.ExecuteNonQuery(sqlHelper.conn, CommandType.StoredProcedure, pro_Excel, param);
}
if (counts > 0)
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('导入成功')</script>");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('操作失败')</script>");
}
}
catch
{
ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('第 "+ count.ToString() + "条数据出错')</script>");
excelconn.Close();
}
excelconn.Close();
}
}
}
//前台
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="Excel" %>
<%@ Register src="WebUserControl.ascx" tagname="WebUserControl" tagprefix="uc1" %>
<!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>
<uc1:WebUserControl ID="WebUserControl1" runat="server" /><asp:button ID="btnExcel" runat="server" Text="导入Excel" onclick="btnExcel_Click" />
</div>
</form>
</body>
</html>
//存储过程
create proc excel_Insert
(
@name nvarchar(50),
@Info nvarchar(50),
@address nvarchar(50)
)
as
insert into Excel(Name,Info,address) values(@name,@Info,@address)
另附一句话导入
insert into ProJHInfo_Bas select * from OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Excel 5.0;HDR=YES;DATABASE=" +url + "',sheet1$)
url为excel地址，注：此语句 数据表列与Excel列相同