如何将ＥＸＣＥＬ檔案導入資料庫裡
#region 讀取 儲存 excel資訊
void ExcelToData(string filePath)
{
#region 擷取excel文檔資訊
string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
DataSet ds = new DataSet();
DataTable dtnew = null;
int count = 0;
using (OleDbConnection objConn = new OleDbConnection(strConn))
{
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "SELECT * FROM [" + tableName + "]";
OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
OleDbDataAdapter sqlada = new OleDbDataAdapter();
sqlada.SelectCommand = objCmd;
sqlada.Fill(ds);
}
#endregion
#region 去除手機重複
DataTable dtold = ds.Tables[0];
dtold.Rows[0].Delete();//删除标題行
DataTable dtnewmobile = SelectDistinctByField(dtold, "F6");
#endregion
#region 去除帳号重複
DataTable dtnewaccount = SelectDistinctByField(dtnewmobile, "F11");
#endregion
#region 去除email重複
dtnew = SelectDistinctByField(dtnewaccount, "F19");
#endregion
//過濾掉的重複行數
count += dtold.Rows.Count - dtnew.Rows.Count;
DataTable dt = GetCustomerData();
#region 定義些變量存儲客戶資訊
string sex = string.Empty;
string brithday_y = string.Empty;
string brithday_m = string.Empty;
string brithday_d = string.Empty;
string fromid = string.Empty;
string jobtypeid = string.Empty;
string provinceid = string.Empty;
string cityid = string.Empty;
int IsPrivate = 0;
int type = 1;
#endregion
#region 資訊判斷 儲存
int cou = 0, same = 0, isnull = 0;
Transaction t = new Transaction();
foreach (DataRow dr in dtnew.Rows)
{
if (Convert.ToString(dr[0]).Trim() == "" && Convert.ToString(dr[1]).Trim() == ""
&& Convert.ToString(dr[2]).Trim() == "" && Convert.ToString(dr[3]).Trim() == ""
&& Convert.ToString(dr[4]).Trim() == "" && Convert.ToString(dr[5]).Trim() == ""
&& Convert.ToString(dr[6]).Trim() == "" && Convert.ToString(dr[7]).Trim() == ""
&& Convert.ToString(dr[8]).Trim() == "" && Convert.ToString(dr[9]).Trim() == ""
&& Convert.ToString(dr[10]).Trim() == "" && Convert.ToString(dr[11]).Trim() == ""
&& Convert.ToString(dr[12]).Trim() == "" && Convert.ToString(dr[13]).Trim() == ""
&& Convert.ToString(dr[14]).Trim() == "" && Convert.ToString(dr[15]).Trim() == ""
&& Convert.ToString(dr[16]).Trim() == "" && Convert.ToString(dr[17]).Trim() == ""
&& Convert.ToString(dr[18]).Trim() == "" && Convert.ToString(dr[19]).Trim() == ""
&& Convert.ToString(dr[20]).Trim() == "" && Convert.ToString(dr[21]).Trim() == ""
&& Convert.ToString(dr[22]).Trim() == "" && Convert.ToString(dr[23]).Trim() == "")
{
break;
}
#region 客戶資訊
sex = Convert.ToString(dr[3]);
brithday_y = Convert.ToString(dr[12]);
brithday_m = Convert.ToString(dr[13]);
brithday_d = Convert.ToString(dr[14]);
//添加客戶出境資訊
if (this.CurrentQyml.ZfxInfoPostType == 8)
{
fromid = Convert.ToString(dr[28]);
jobtypeid = Convert.ToString(dr[29]);
provinceid = Convert.ToString(dr[30]);
cityid = Convert.ToString(dr[31]);
}
else
{
fromid = Convert.ToString(dr[21]);
jobtypeid = Convert.ToString(dr[22]);
provinceid = Convert.ToString(dr[23]);
cityid = Convert.ToString(dr[24]);
}
type = int.Parse(this.IntoDDLType.SelectedValue);
IsPrivate = IntoIsPrivate.Checked ? 1 : 0;
Line_TCPF_CustomerEntity custom = new Line_TCPF_CustomerEntity();
custom.Address = Convert.ToString(dr[7]);
custom.B2C_UserId = Supplier.MemberId;
DateTime brithday = DateTime.MaxValue;
if (brithday_d != "" && brithday_m != "" && brithday_y != "" &&
DateTime.TryParse(brithday_y + "-" + brithday_m + "-" + brithday_d, out brithday))
{
custom.BirthdayDay = brithday.Day;
custom.BirthdayMonth = brithday.Month;
custom.BirthdayYear = brithday.Year;
custom.IsSMSBirthday = 1;
}
custom.CertNo = Convert.ToString(dr[17]);
custom.CompanyName = Convert.ToString(dr[0]);
custom.CompanyPhone = Convert.ToString(dr[4]);
custom.CreateDate = DateTime.Now;
custom.Creator = CurrentUser.Truename;
custom.Fax = Convert.ToString(dr[6]);
custom.Flag = 1;
if (sex != string.Empty && (sex == "0" || sex == "1"))
custom.Gender = Convert.ToInt16(sex);
else
custom.Gender = sex == "男" ? 1 : 0;
custom.LastIP = "";
custom.LastTime = DateTime.Now;
custom.LoginTimes = 0;
custom.Mobile = Convert.ToString(dr[5]);
custom.MSN = Convert.ToString(dr[16]);
custom.Name = Convert.ToString(dr[1]);
custom.Account = Convert.ToString(dr[10]);
custom.Password = Convert.ToString(dr[11]);
custom.Position = Convert.ToString(dr[2]);
custom.Postcode = Convert.ToString(dr[8]);
custom.QQ = Convert.ToString(dr[15]);
custom.Remark = Convert.ToString(dr[9]);
custom.Type = type > 1 ? type : 1;
custom.IsPrivate = CommonMethod.ConvertToInt(IsPrivate, 0);
custom.CreateId = CurrentUser.Id;
custom.Email = Convert.ToString(dr[18]);
custom.Amount = CommonMethod.ConvertToDecimal(Convert.ToString(dr[19]), 0);
custom.FirstConsumeDate = CommonMethod.ConvertToDateTime(dr[20], DateTime.MaxValue);
custom.FromId = BusinessFacade.CustomerFacade.GetCusSourceIdByName(fromid);
custom.JobTypeId = BusinessFacade.CustomerFacade.GetCusJobTypeIdByName(jobtypeid);
custom.ProvinceId = BusinessFacade.ProvinceCity.Intance().GetProvinceIdByProvinceName(provinceid);
custom.CityId = BusinessFacade.ProvinceCity.Intance().GetCityIdByCityName(cityid);
//添加客戶出境資訊
if (this.CurrentQyml.ZfxInfoPostType == 8)
{
custom.Place = Convert.ToString(dr[21]);
custom.EngFirst = Convert.ToString(dr[22]);
custom.EngLast = Convert.ToString(dr[23]);
custom.PassportNo = Convert.ToString(dr[24]);
custom.PassportFrom = Convert.ToString(dr[25]);
DateTime beginDate = DateTime.MinValue;
DateTime expireDate = DateTime.MinValue;
DateTime.TryParse(Convert.ToString(dr[26]), out beginDate);
DateTime.TryParse(Convert.ToString(dr[27]), out expireDate);
custom.PassportBeginDate = beginDate;
custom.PassportExpireDate = expireDate;
}
#endregion
bool nullname = true;
bool thesame = true;
if (custom.Name.Trim().Length == 0)
{
isnull++; //客戶名為空的數量
nullname = false;
}
else if ((custom.Mobile.Trim().Length > 0 && dt.Select(" Mobile=\'" + custom.Mobile.Trim() + "\'").Length > 0)
|| (custom.Account.Trim().Length > 0 && dt.Select(" Account=\'" + custom.Account.Trim() + "\'").Length > 0)
|| (custom.Email.Trim().Length > 0 && dt.Select(" Email=\'" + custom.Email.Trim() + "\'").Length > 0))
{
same++;
thesame = false;
}
if (nullname && thesame)
{
t.AddSaveObject(custom);
cou++;
}
}
try
{
t.Process();
string alert = cou.ToString() + "條資料成功導入！";
if (isnull > 0)
{
alert += isnull.ToString() + "條資料因聯系人為空未被導入！";
}
if (same + count > 0)
{
alert += (same + count).ToString() + "條資料因資料重複未被導入！";
}
Alert(alert);
}
catch (Exception ex)
{
t.RollBack();
Alert("抱歉：導入出錯！" + ex.Message);
}
#endregion
}
#endregion