//此方法可以一次导入百万数据
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();

}