public static string BD_MaterialImport(string FilePath)
{
try
{
#region 读取工作表,导入数据
//此连接可以操作.xls与.xlsx文件
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source="
+ FilePath //execel路径
+ ";Extended Properties='Excel 12.0; HDR=NO; IMEX=0'";
DataSet ds = new DataSet();
//Sheet1工作表名称
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);
#endregion
#region 数据写入数据库
DataTable data = ds.Tables[0];//数据源表
using(SqlConnection con=new SqlConnection(ConStr)){
//使用Bulk批量插入大数据
Stopwatch sw = new Stopwatch();//运行时间
SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
bulkCopy.DestinationTableName = "mainUser"; //数据库表名
bulkCopy.BatchSize = data.Rows.Count;
con.Open();
sw.Start();//开始计时
bulkCopy.WriteToServer(data);
sw.Stop();
Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒,{2}分钟", data.Rows.Count, sw.ElapsedMilliseconds, Convert.ToInt32(sw.ElapsedMilliseconds)/60/60));
return "数据插入结束...";
}
#endregion
}
catch (Exception ex)
{
return ex.Message;
}
}
读取的DataTable