开发机器:(需安装AccessDatabaseEngine2007x32,不兼容64bit的oledb,出现未在本地计算机上注册“microsoft.ACE.oledb.12.0”提供程序)
操作系统:Window8企业版64bit
数据库: MSSQL2008R2_64Bit
MS-Office: Office2010_64Bit,
服务器:
操作系统:WindowServer2003R2(32位)
数据库: MSSQL2008(32位)
MS-Office: Office2007(32位)
注:Access2007或2010都是提供Microsoft.ACE.OLEDB.12.0 。如果Excel2010请将“Excel 14.0”添加到 OLEDB 连接字符串的扩展属性中。
AccessDatabaseEngine2010下载地址:http://www.microsoft.com/zh-cn/download/details.aspx?id=13255
AccessDatabaseEngine2007下载地址:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
数据库表结构:
Eecel测试数据:
1.基于MSSQL2008数据库管理器--新建查询
--开启导入功能
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--允许在进程中使用ACE.OLEDB.12
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
--允许动态参数
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
insert into Student select * from
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;HDR=YES;DATABASE=d:\test.xlsx',sheet1$)
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0
excel 2000 ~ 2003:
Provider=Microsoft.Jet.OleDb.4.0;Data Source='excel文件路径';Extended Properties='Excel 8.0;HDR=YES'
excel 2007 :
Provider=Microsoft.Ace.OleDb.12.0;Data Source='excel文件路径';Extended Properties='Excel 12.0;HDR=YES'
Excel2000-2003:
OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0;HDR=YES;IMEX=1'");
Excel2007:
OleDbConnection ExcelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + FilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'");
注:FilePath是excel文件的路径 导入Excel2007文件时机器上必须安装有Excel2007否则会报错.
结果 :
不够智能化,列头标题也当作一行数据录入数据表,还有最后列数据混乱
二:基于Net后台编码的导入:
//前台简单一个上传控件和按钮
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" οnclick="Button1_Click" Text="导入" />
</div>
</form>
//后台按钮事件
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile == false)
{
Response.Write("<script>alert('请您选择Excel文件')</script> ");
return;//当无文件时,返回
}
int fileSize = 4;
int fileLenth = FileUpload1.PostedFile.ContentLength / (1024 * 1024);
//大小是否在限制内
if (fileLenth > fileSize)
{
Response.Write("<script>alert('文件不能大于" + fileSize + "M')</script> ");
return ;
}
string fileType = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();
if (fileType == ".xls" || fileType == ".xlsx")
{
string fileDirectory = "Excel";
string serverPath = Server.MapPath(fileDirectory);
//是否存在目录,不存在先创建
if (!System.IO.Directory.Exists(serverPath))
{
System.IO.Directory.CreateDirectory(serverPath);
}
string newPath = serverPath + "\\" + FileUpload1.FileName;
FileUpload1.SaveAs(newPath);
DataTable dt = ExcelDataSource(newPath, "sheet1").Tables[0];
ExcelToDB.BLL.Student_BLL bll = new Student_BLL();
if (bll.Add2(dt))
{
Response.Write("<script>alert('导入成功!')</script>");
}
else
{
Response.Write("<script>alert('导入失败!')</script>");
}
}
else
{
Response.Write("<script>alert('只可以选择Excel文件')</script>");
return;//当选择的不是Excel文件时,返回
}
}
/// <summary>
/// 获取Excel数据源,填充到DataSet
/// </summary>
/// <param name="filepath">excel文件绝对路径</param>
/// <param name="sheetname">excel表单名</param>
/// <returns></returns>
public DataSet ExcelDataSource(string filepath, string sheetname)
{
string strConn;
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "$]", strConn);
DataSet ds = new DataSet();
oada.Fill(ds);
return ds;
}
结果: