SqlConnection mycon = con();
string sqlstr = "insert into Address select 姓名, 备注,null from OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 5.0;HDR=YES;DATABASE=D:\\Book1.xlsx',sheet2$)";(如果excel是07版本一下的'Microsoft.ACE.OLEDB.12.0'变成'Microsoft.ACE.OLEDB.4.0')
SqlCommand cmd = new SqlCommand(sqlstr, mycon);
mycon.Open();
cmd.ExecuteNonQuery();
mycon.Close();
1.sql server 中可能是ole接口问题需要改sql中的设置
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
关闭设置
exec sp_configure "Ad Hoc Distributed Queries",0
reconfigure
exec sp_configure "show advanced options",0
reconfigure
2.excel必须是关闭的