//此方法可以一次导入百万数据
public int GetDevicesExel(string fileName, string users,
out string repIMEI)
{
int res = 0;
#region exel导入到Datatable
DataTable dt = new DataTable();
string strCon = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=‘Excel 8.0;HDR=False;IMEX=1‘";
OleDbConnection conn = new OleDbConnection(strCon);
OleDbDataAdapter myCommand;
conn.Open();
//返回Excel的架构,包括各个sheet表的名称,类型,创建时间和修改时间等
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//包含excel中表名的字符串数组
string[] strTableNames = new string[dtSheetName.Rows.Count];
for (int k = 0; k < dtSheetName.Rows.Count; k++)
{
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
}
for (int j = 0; j < strTableNames.Length; j++)
{
//从指定的表明查询数据,可先把所有表明列出来供用户选择
string strExcel = "select * from [" + strTableNames[j] + "]";
myCommand = new OleDbDataAdapter(strExcel, strCon);
myCommand.Fill(dt);
}
#endregion
#region 填充虚拟表数据(列要和数据库的一致包括主键 主键可以随便写)
Devices
de = new Devices();
for (int i = 0; i < dt.Rows.Count;
i++)
{
int count =
Regex.Matches(dt.Rows[i][5].ToString(), @"\d").Count;
if
(count > 0)
{
continue;
}
if (dt.Rows[i][0].ToString() == "" ||
dt.Rows[i][0].ToString() == null)
{
continue;
}
DataRow dataRow =
dataTable.NewRow();
dataRow[0] = 5555; //DeviceID
dataRow[1] = dt.Rows[i][0].ToString(); //SerialNumber
dataRow[2] = ""; //DeviceName
dataRow[3] = "123456"; //DevicePassword
dataRow[4] = "";//CarUserName
dataRow[5] = "";//CarNum
dataRow[6] = "";//CellPhone
dataRow[7] = 1;
//Status
dataRow[8] =
dt.Rows[i][2].ToString(); //PhoneNum
dataRow[9] =
Utility.SafeInt(dt.Rows[i][1].ToString()); //Model
dataTable.Rows.Add(dataRow);
}
#endregion
SqlBulkCopy sqlBulkCopy = new
SqlBulkCopy(conString);
sqlBulkCopy.DestinationTableName =
"Devices";
if (dataTable != null &&
dataTable.Rows.Count != 0)
{
sqlBulkCopy.WriteToServer(dataTable); //开始添加数据
res = 1;
}
sqlBulkCopy.Close();
}
原文:http://www.cnblogs.com/zhujingbiao/p/3729648.html