转：Asp.Net将Excel数据导入Sql Server数据库

淩亂°似流年 2021-12-22 03:29 226阅读 0赞

在网上找到一个Asp.Net将Excel数据导入Sql Server数据库的的例子,是调用存储过程的,大家分享.

**我刚用过，感觉还可以。其基本思想是先新建一个页面，放一个HTML控件 File FIled 控件，然后将这个控件转化为服务器控件，    再放一个按钮（服务器控件）用来获取File FIled中的内容，接下来就是在后台编程了，首先引入两个命名空间,using System.data.oledb和using system.data.sqlclint 其中oledb空间用来提供Excel的驱动，sqlclint用来连接sql server 2000用,接下来就可以在按钮事件中把下面的代码搞上去就行了，对了还要编写自己的存储过程哦！**

前言:cmd.CommandText="Proc\_Address";这句话是调用存储过程"Proc\_Address"  
该存储过程代码为:  
CREATE Proc Proc\_Address @CardNo char(20),@ToAddress char(50),@CCAddress char(50) ,@YYYYMM char(12)  
as  
declare @strSql      char (400)  
  
set @strSql = 'Insert into EmailAddress' + @YYYYMM + ' values(''+@CardNo+'',''+@ToAddress+'',''+@CCAddress+'',''+'1')'      --插入一条记录  
exec (@strSql)                                                                       --也可以用insert into 语句  
GO  
  
  
  
private void Button1\_Click(object sender, System.EventArgs e)  
\{  
CreateTable();  
  
//先将EXCEL导入到数据库,一:先把EXCEL导入dateView,二:然后将dateView里的数据导入到数据库里面  
  
//EXCEL 的连接串  
string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +  
"Data Source=" + File1.PostedFile.FileName.ToString() + ";" +  
"Extended Properties=Excel 8.0;";  
  
//建立EXCEL的连接  
OleDbConnection objConn = new OleDbConnection(sConnectionString);  
  
objConn.Open();  
  
OleDbCommand objCmdSelect =new OleDbCommand("SELECT \* FROM \[Sheet1＄\]", objConn);  
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();  
objAdapter1.SelectCommand = objCmdSelect;  
  
DataSet objDataset1 = new DataSet();  
  
objAdapter1.Fill(objDataset1, "XLData";  
  
// DataGrid1.DataSource = objDataset1.Tables\[0\].DefaultView; //测试代码,用来测试是否能读出EXCEL上面的数据  
// DataGrid1.DataBind();  
  
DataTable dt = objDataset1.Tables\[0\];  
DataView myView = new DataView(dt);  
  
//SQL SERVER的数据库连接  
SqlConnection conn;  
string dns =System.Configuration.ConfigurationSettings.AppSettings\["ConStr"\];//连接串  
conn=new SqlConnection(dns);  
  
SqlCommand cmd =conn.CreateCommand();  
cmd.CommandType =CommandType.StoredProcedure;  
cmd.CommandText="Proc\_Address";  
  
int count=0;//用来记录出错的条数  
  
try  
\{  
foreach (DataRowView myDrv in myView)  
\{  
count++;  
//要关闭上一次的SQL Server的连接  
if (conn.State.ToString()!="Closed"  
conn.Close();  
  
//每一次都要清空所有的CMD的参数  
cmd.Parameters.Clear();  
  
//执行存储过程  
//首先获得参数 共 3个  
//@CardNo,@ToAddress,@CCAddress  
SqlParameter paraCardNo =cmd.Parameters.Add("@CardNo",SqlDbType.Char);  
SqlParameter paraToAddress =cmd.Parameters.Add("@ToAddress",SqlDbType.Char);  
SqlParameter paraCCAddress =cmd.Parameters.Add("@CCAddress",SqlDbType.Char);  
SqlParameter paraYYYYMM =cmd.Parameters.Add("@YYYYMM",SqlDbType.Char);  
  
//表示是输出参数  
paraCardNo.Direction = ParameterDirection.Input;  
paraToAddress.Direction = ParameterDirection.Input;  
paraCCAddress.Direction = ParameterDirection.Input;  
paraYYYYMM.Direction = ParameterDirection.Input;  
  
//参数赋值  
paraCardNo.Value = myDrv\[0\].ToString().Trim();  
paraToAddress.Value = myDrv\[1\].ToString().Trim();  
paraCCAddress.Value = myDrv\[2\].ToString().Trim();  
paraYYYYMM.Value = ddlYear.Items\[ddlYear.SelectedIndex\].Value + ddlMonth.Items\[ddlMonth.SelectedIndex\].Value;  
  
conn.Open();  
cmd.ExecuteNonQuery();//写入SQL数据库  
\}  
\}  
catch  
\{  
Page.Response.Write("alert('第"+count.ToString()+"条数据出错!');";  
objConn.Close();//关闭EXCEL的连接  
\}  
  
objConn.Close();//关闭EXCEL的连接

转自：[http://www.wybcom.cn][http_www.wybcom.cn]

转载于:https://www.cnblogs.com/zsli-2008-20/archive/2008/03/14/1105525.html

[http_www.wybcom.cn]: http://www.wybcom.cn/