批量导入excel中数据

 

//获取excel数据
public DataSet ExecleDs(string filePath)
{
try
{
int exc = filePath.LastIndexOf('.');
string excelType = filePath.Substring(exc, filePath.Length - exc);
string strConn = "";
if (excelType == ".xls")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source= " + filePath + ";" + "Extended Properties= Excel 8.0;";
else if (excelType == ".xlsx")
strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 12.0;";

//strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";
OleDbConnection OleConn = new OleDbConnection(strConn);
OleConn.Open();
String sql = "SELECT * FROM [Sheet1$]";//可是更改Sheet名称，比如sheet2，等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
DataSet OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, "Sheet1");
OleConn.Close();
return OleDsExcle;
}
catch
{
return null;
}
}

 

 

//重组数据
public DataTable LeadCustomeDataSet(DataSet ds, string orid)
{
DataTable newTable = new DataTable();
try
{
DataTable upTable = ds.Tables[0];
newTable.Columns.Add("UserName", typeof(string));
newTable.Columns.Add("PasswordHash", typeof(string));
newTable.Columns.Add("Gender", typeof(string));
newTable.Columns.Add("BirthDay", typeof(DateTime));
newTable.Columns.Add("RegistrationDate", typeof(DateTime));
newTable.Columns.Add("OrgID", typeof(int));
newTable.Columns.Add("Active", typeof(Boolean));
newTable.Columns.Add("ConsumeNumber", typeof(string));
newTable.Columns.Add("Status", typeof(int));
for (int i = 0; i < upTable.Rows.Count; i++)
{
DataRow dr = newTable.NewRow();
string password = upTable.Rows[i][1].ToString();
string sPasswordHash = Helper.CreatePasswordHash(password, "");
dr["UserName"] = upTable.Rows[i][0].ToString();
dr["PasswordHash"] = sPasswordHash;
dr["Gender"] = "1";
dr["BirthDay"] = DateTime.Now;
dr["RegistrationDate"] = DateTime.Now;
dr["OrgID"] = Convert.ToInt32(orid);
dr["Active"] = true;
dr["ConsumeNumber"] = password;
dr["Status"] = 1;
newTable.Rows.Add(dr);
}
}
catch { }
return newTable;
}

 

public static readonly string ConnectionStr=ConfigurationManager.ConnectionStrings["CONNSTR"].ToString();

/// <summary>
/// 将抓取的Mysql数据导入Sqlserver
/// </summary>
/// <param name="sqldb">数据集</param>
public static bool InsertSqlServer(DataTable sqldb)
{
try
{
//数据批量导入sqlserver,创建实例
System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(ConnectionStr);
//目标数据库表名
sqlbulk.DestinationTableName = "Nop_Customer";
//数据集字段索引与数据库字段索引映射
sqlbulk.ColumnMappings.Add(0, 34);
sqlbulk.ColumnMappings.Add(1, 3);
sqlbulk.ColumnMappings.Add(2, 41);
sqlbulk.ColumnMappings.Add(3, 40);
sqlbulk.ColumnMappings.Add(4, 31);
sqlbulk.ColumnMappings.Add(5, 50);
sqlbulk.ColumnMappings.Add(6, 29);
sqlbulk.ColumnMappings.Add(7, 20);
sqlbulk.ColumnMappings.Add(8, 53);
//导入
sqlbulk.WriteToServer(sqldb);
sqlbulk.Close();
return true;
}
catch
{
return false;
}
}