.NET导入表格到数据库的方法
HTMLa代码
<div class="r-m"> <div class="excel"> <div> <input type="file" id="wageExcel" />工资导入 </div> </div> <div class="excel"> <div> <input type="file" id="supperExcel" />供应商导入 </div> </div> <div class="excel"> <div> <input type="file" id="GnExcel" />线路导入 </div> </div>线路列表 </div>
JS代码，注意要导入：jquery.uploadify.min.js
$("#GnExcel").uploadify({
swf: '/Theme/NewBlueVacation/images/uploadify.swf',
uploader: '/HeadOffice/UploadExcel',
successTimeout:100000,
width: 32,
height: 32,
buttonText: ' ',//上传按钮文字
buttonImage: "/Theme/NewBlueVacation/King/excel.png",//上传按钮路径
fileTypeExts: '*.xls;*.xlsx',
onUploadSuccess: function (file, data, response) {
if (data == "0") {
$.messager.alert("提示", "导入失败!", 'error');
}
else {
$.messager.confirm('提示', '文件上传成功，是否开始导入数据？', function (r) {
if (r) {
$('#process').dialog({ content: '<iframe id="tabFrame" frameborder="0" src="/HeadOffice/ImportExcel?fileName=' + data + '" style="width:100%;height:100%;" scrolling="auto"></iframe>' });
$('#process').dialog('open');
//$.post('/HeadOffice/ImportExcel', { fileName: data }, function (data) {
// $.messager.progress('close');
// if (data.sucess) {
// location.reload();
// }
// $.messager.alert('提示', data.msg, 'info');
//}, 'json');
}
});
}
}
});
$("#supperExcel").uploadify({
swf: '/Theme/NewBlueVacation/images/uploadify.swf',
uploader: '/HeadOffice/UploadExcel',
successTimeout: 100000,
width: 32,
height: 32,
buttonText: ' ',//上传按钮文字
buttonImage: "/Theme/NewBlueVacation/King/excel.png",//上传按钮路径
fileTypeExts: '*.xls;*.xlsx',
onUploadSuccess: function (file, data, response) {
if (data == "0") {
$.messager.alert("提示", "导入失败!", 'error');
}
else {
$.messager.confirm('提示', '文件上传成功，是否开始导入数据？', function (r) {
if (r) {
$('#process').dialog({ content: '<iframe id="tabFrame" frameborder="0" src="/HeadOffice/ImportSupplier?fileName=' + data + '" style="width:100%;height:100%;" scrolling="auto"></iframe>' });
$('#process').dialog('open');
//$.post('/HeadOffice/ImportExcel', { fileName: data }, function (data) {
// $.messager.progress('close');
// if (data.sucess) {
// location.reload();
// }
// $.messager.alert('提示', data.msg, 'info');
//}, 'json');
}
});
}
}
});
$("#wageExcel").uploadify({
swf: '/Theme/NewBlueVacation/images/uploadify.swf',
uploader: '/HeadOffice/UploadExcel',
successTimeout: 100000,
width: 32,
height: 32,
buttonText: ' ',//上传按钮文字
buttonImage: "/Theme/NewBlueVacation/King/excel.png",//上传按钮路径
fileTypeExts: '*.xls;*.xlsx',
onUploadSuccess: function (file, data, response) {
if (data == "0") {
$.messager.alert("提示", "导入失败!", 'error');
}
else {
$.messager.confirm('提示', '文件上传成功，是否开始导入数据？', function (r) {
if (r) {
$('#process').dialog({ content: '<iframe id="tabFrame" frameborder="0" src="/HeadOffice/ImportWage?fileName=' + data + '" style="width:100%;height:100%;" scrolling="auto"></iframe>' });
$('#process').dialog('open');
//$.post('/HeadOffice/ImportExcel', { fileName: data }, function (data) {
// $.messager.progress('close');
// if (data.sucess) {
// location.reload();
// }
// $.messager.alert('提示', data.msg, 'info');
//}, 'json');
}
});
}
}
});
$('#process').dialog({
title: '导入进度',
width: 800,
height: 400,
closed: true,
cache: false,
modal: true
});
后台代码：
1 public string ImportExcel(string fileName) 2 { 3 string ExcelPath = Tool.AppPath + fileName; 4 ExcelPath = ExcelPath.Replace("/", "\\"); 5 DataSet GnDs = Web.ExtendClass.ExcelSqlConnection(ExcelPath, "国内线路"); 6 if (GnDs != null) 7 { 8 9 DataTable dt = GnDs.Tables[0]; 10 Response.Write("<style>p{margin:0;padding:2px;font-size:12px;height:18px;line-height:18px;color:red;}</style><p>共有" + dt.Rows.Count + "条国内线路记录</p>"); 11 Response.Flush(); 12 for (int i = 1; i < dt.Rows.Count; i++) 13 { 14 //using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew)) 15 //{ 16 DataRow Rs = dt.Rows[i]; 17 string supperNumber = Rs[1].ToString(); 18 if (string.IsNullOrEmpty(supperNumber)) 19 { 20 Response.Write("<p>没有供应商编号，跳过国内线路导入</p>"); 21 Response.Flush(); 22 break; 23 } 24 else 25 { 26 BLL.IBLL DB = new BLL.IBLL(); 27 MODEL.Supplier supplierModel = DB.I_Supplier.GetListBy(s => s.SupplierNumber.Equals(supperNumber)).OrderByDescending(s=>s.SupplierId).FirstOrDefault(); 28 if (supplierModel == null) 29 { 30 continue; 31 } 32 Response.Write("<p>正在导入"+Rs[9].ToString()+"</p>"); 33 Response.Flush(); 34 MODEL.TravelProduct model = new MODEL.TravelProduct(); 35 model.SupplierId = supplierModel.SupplierId; 36 model.DepartureCity = Rs[2].ToString(); 37 model.ObjectiveCity = Rs[3].ToString(); 38 //数据库中没有该字段 39 //model.DepartureCity = Rs[产品类型].ToString(); 40 model.TravelEssentials = Rs[5].ToString(); 41 model.LineLevel = Rs[6].ToString(); 42 model.LineKeyWords = Rs[7].ToString(); 43 model.PlayType = Rs[8].ToString(); 44 model.LineName = Rs[9].ToString(); 45 model.AdvanceDays = Rs[10].ToString().ToInt(); 46 model.TravelNight = Rs[11].ToString().ToInt(); 47 model.TravelDays = Rs[12].ToString().ToInt(); 48 //往返交通要分开 49 model.ReturnTraffic = Rs[13].ToString(); 50 model.ComeTraffic = Rs[14].ToString(); 51 model.HotelRating = Rs[15].ToString(); 52 model.AdultPrice = Rs[16].ToString().ToInt(); 53 model.AdultFare = Rs[17].ToString().ToInt(); 54 model.AdultRemark = Rs[18].ToString(); 55 model.ChildrenPrice = Rs[19].ToString().ToInt(); 56 model.ChildrenRemark = Rs[20].ToString(); 57 model.EntireSingleRoom = Rs[21].ToString().ToInt(); 58 model.QuchengZili = Rs[22].ToString().ToInt(); 59 model.HuichengZili = Rs[23].ToString().ToInt(); 60 model.DocumentsFree = Rs[24].ToString().ToInt(); 61 model.ChildrenAccountForBed = Rs[25].ToString().ToInt(); 62 model.AdmissionTicket = Rs[26].ToString().ToInt(); 63 model.RoundtripTickets = Rs[27].ToString().ToInt(); 64 model.LineFeatures = Rs[28].ToString(); 65 model.ChildrenFare = 0; 66 model.SpecialCrowdSurcharge = 0; 67 model.DomesticInternational = "国内"; 68 if (DB.I_TravelProduct.Add(model)) 69 { 70 model = DB.I_TravelProduct.GetSingleModelBy(s => s.TravelProductId == model.TravelProductId); 71 ExtendClass.MakeLineNumber(model); 72 //导入费用说明 73 //包含费用 74 Response.Write("<p>正在导入"+model.LineName+"的费用说明中的包含费用</p>"); 75 Response.Flush(); 76 string[] baoan = Rs[29].ToString().Split("\\n"); 77 foreach (var item in baoan) 78 { 79 DB = new BLL.IBLL(); 80 string title = Tool.GetBody(item, "【(.+?)】"); 81 string Intro = item; 82 if (Intro.IndexOf("】") > 0) 83 { 84 Intro = Intro.Substring(Intro.IndexOf("】") + 1); 85 } 86 MODEL.TravelFee tmodel = new MODEL.TravelFee(); 87 tmodel.FeeItem = title; 88 tmodel.IsContains = true; 89 tmodel.TravelProductId = model.TravelProductId; 90 tmodel.Description = Intro; 91 DB.I_TravelFee.Add(tmodel); 92 } 93 Response.Write("<p>正在导入" + model.LineName + "的费用说明中的不包含费用</p>"); 94 Response.Flush(); 95 //包含费用 96 string[] bubaoan = Rs[30].ToString().Split("\\n"); 97 foreach (var item in bubaoan) 98 { 99 DB = new BLL.IBLL(); 100 string title = Tool.GetBody(item, "【(.+?)】"); 101 string Intro = item; 102 if (Intro.IndexOf("】") > 0) 103 { 104 Intro = Intro.Substring(Intro.IndexOf("】") + 1); 105 } 106 if (string.IsNullOrEmpty(Intro)) 107 { 108 continue; 109 } 110 MODEL.TravelFee tmodel = new MODEL.TravelFee(); 111 tmodel.FeeItem = title; 112 tmodel.IsContains = false; 113 tmodel.TravelProductId = model.TravelProductId; 114 tmodel.Description = Intro; 115 DB.I_TravelFee.Add(tmodel); 116 } 117 Response.Write("<p>正在导入" + model.LineName + "的预定须知</p>"); 118 Response.Flush(); 119 //导入预定须知 120 string[] bookArray = Rs[31].ToString().Split("\\n"); 121 foreach (var item in bookArray) 122 { 123 DB = new BLL.IBLL(); 124 MODEL.BookingInformation bModel = new MODEL.BookingInformation(); 125 bModel.TravelProductId = model.TravelProductId; 126 bModel.Item = item; 127 DB.I_BookingInformation.Add(bModel); 128 } 129 Response.Write("<p>正在导入" + model.LineName + "的团期导入</p>"); 130 Response.Flush(); 131 //团期导入 132 string[] dateArray = Rs[32].ToString().Replace("，", ",").Split(','); 133 foreach (var item in dateArray) 134 { 135 if (string.IsNullOrEmpty(item) == false) 136 { 137 MODEL.GroupStage GroupModel = new MODEL.GroupStage(); 138 GroupModel.LineId = model.TravelProductId; 139 GroupModel.OutDate = item.ToDate(); 140 GroupModel.PlanBit = 6; 141 GroupModel.ComeDate = GroupModel.OutDate.Value.AddDays(model.TravelDays.Value); 142 GroupModel.GroupStageStatus = true; 143 GroupModel.PublishState = "正在接客"; 144 GroupModel.EndRegistration = GroupModel.OutDate.Value.AddDays(-model.AdvanceDays.Value); 145 GroupModel.OccupationOverTime = 24; 146 GroupModel.AdultPrice = model.AdultPrice; 147 GroupModel.AdultTakeoutPrice = model.AdultFare; 148 GroupModel.ChildrenPrice = model.ChildrenPrice; 149 GroupModel.ChildrenTakeoutPrice = model.ChildrenFare; 150 if (GroupModel.EndRegistration > DateTime.Now) 151 { 152 var dModel = DB.I_GroupStage.GetSingleModelBy(s => s.OutDate == GroupModel.OutDate && s.LineId == model.TravelProductId); 153 if (dModel == null) 154 { 155 if (DB.I_GroupStage.Add(GroupModel)) 156 { 157 GroupModel = DB.I_GroupStage.GetSingleModelBy(s => s.GroupStageId == GroupModel.GroupStageId); 158 ExtendClass.MakeGroupNumber(GroupModel); 159 } 160 } 161 } 162 } 163 } 164 } 165 } 166 // ts.Complete(); 167 //} 168 169 } 170 } 171 DataSet GwDs = Web.ExtendClass.ExcelSqlConnection(ExcelPath, "国际线路"); 172 if (GwDs != null) 173 { 174 DataTable dt = GwDs.Tables[0]; 175 Response.Write("<p>共有" + dt.Rows.Count + "条国际线路记录</p>"); 176 Response.Flush(); 177 for (int i = 1; i < dt.Rows.Count; i++) 178 { 179 //using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew)) 180 //{ 181 DataRow Rs = dt.Rows[i]; 182 string supperNumber = Rs[1].ToString(); 183 if (string.IsNullOrEmpty(supperNumber)) 184 { 185 Response.Write("<p>没有供应商编号，结束国际线路导入</p>"); 186 Response.Flush(); 187 break; 188 } 189 else 190 { 191 BLL.IBLL DB = new BLL.IBLL(); 192 MODEL.Supplier supplierModel = DB.I_Supplier.GetListBy(s => s.SupplierNumber.Equals(supperNumber)).OrderByDescending(s => s.SupplierId).FirstOrDefault(); 193 if (supplierModel == null) 194 { 195 continue; 196 } 197 Response.Write("<p>正在导入" + Rs[9].ToString() + "</p>"); 198 Response.Flush(); 199 MODEL.TravelProduct model = new MODEL.TravelProduct(); 200 model.SupplierId = supplierModel.SupplierId; 201 model.DepartureCity = Rs[2].ToString(); 202 model.ObjectiveCity = Rs[3].ToString(); 203 //数据库中没有该字段 204 //model.DepartureCity = Rs[产品类型].ToString(); 205 model.TravelEssentials = Rs[5].ToString(); 206 model.LineLevel = Rs[6].ToString(); 207 model.LineKeyWords = Rs[7].ToString(); 208 model.PlayType = Rs[8].ToString(); 209 model.LineName = Rs[9].ToString(); 210 model.AdvanceDays = Rs[10].ToString().ToInt(); 211 model.TravelNight = Rs[11].ToString().ToInt(); 212 model.TravelDays = Rs[12].ToString().ToInt(); 213 //往返交通要分开 214 model.ReturnTraffic = Rs[13].ToString(); 215 model.ComeTraffic = Rs[14].ToString(); 216 model.HotelRating = Rs[15].ToString(); 217 model.AdultPrice = Rs[16].ToString().ToInt(); 218 model.AdultFare = Rs[17].ToString().ToInt(); 219 model.AdultRemark = Rs[18].ToString(); 220 model.ChildrenPrice = Rs[19].ToString().ToInt(); 221 model.ChildrenRemark = Rs[20].ToString(); 222 model.EntireSingleRoom = Rs[21].ToString().ToInt(); 223 model.DiscountTicket = Rs[22].ToString().ToInt(); 224 model.SelfCheck = Rs[23].ToString().ToInt(); 225 model.RemovalTour = Rs[24].ToString().ToInt(); 226 model.OldSurcharge = Rs[25].ToString().ToInt(); 227 model.ChildrenSurcharge = Rs[26].ToString().ToInt(); 228 model.ChildrenWithoutBed = Rs[27].ToString().ToInt(); 229 model.SpecialRegion = Rs[28].ToString().ToInt(); 230 model.SpecialAge = Rs[29].ToString().ToInt(); 231 model.ForeignNationality = Rs[30].ToString().ToInt(); 232 model.LineFeatures = Rs[31].ToString(); 233 model.ChildrenFare = 0; 234 model.SpecialCrowdSurcharge = 0; 235 model.DomesticInternational = "国际"; 236 if (DB.I_TravelProduct.Add(model)) 237 { 238 model = DB.I_TravelProduct.GetSingleModelBy(s => s.TravelProductId == model.TravelProductId); 239 ExtendClass.MakeLineNumber(model); 240 //导入费用说明 241 //包含费用 242 Response.Write("<p>正在导入" + model.LineName + "的包含费用</p>"); 243 Response.Flush(); 244 string[] baoan = Rs[32].ToString().Split("\\n"); 245 foreach (var item in baoan) 246 { 247 DB = new BLL.IBLL(); 248 string title = Tool.GetBody(item, "【(.+?)】"); 249 string Intro = item; 250 if (Intro.IndexOf("】")>0) 251 { 252 Intro = Intro.Substring(Intro.IndexOf("】")+1); 253 } 254 MODEL.TravelFee tmodel = new MODEL.TravelFee(); 255 tmodel.FeeItem = title; 256 tmodel.IsContains = true; 257 tmodel.TravelProductId = model.TravelProductId; 258 tmodel.Description = Intro; 259 DB.I_TravelFee.Add(tmodel); 260 } 261 Response.Write("<p>正在导入" + model.LineName + "的不包含费用</p>"); 262 Response.Flush(); 263 //包含费用 264 string[] bubaoan = Rs[33].ToString().Split("\\n"); 265 foreach (var item in bubaoan) 266 { 267 DB = new BLL.IBLL(); 268 string title = Tool.GetBody(item, "【(.+?)】"); 269 string Intro = item; 270 if (Intro.IndexOf("】") > 0) 271 { 272 Intro = Intro.Substring(Intro.IndexOf("】")+1); 273 } 274 if (string.IsNullOrEmpty(Intro)) 275 { 276 continue; 277 } 278 MODEL.TravelFee tmodel = new MODEL.TravelFee(); 279 tmodel.FeeItem = title; 280 tmodel.IsContains = false; 281 tmodel.TravelProductId = model.TravelProductId; 282 tmodel.Description = Intro; 283 DB.I_TravelFee.Add(tmodel); 284 } 285 Response.Write("<p>正在导入" + model.LineName + "的预定须知</p>"); 286 Response.Flush(); 287 //导入预定须知 288 string[] bookArray= Rs[34].ToString().Split("\\n"); 289 foreach (var item in bookArray) 290 { 291 DB = new BLL.IBLL(); 292 MODEL.BookingInformation bModel = new MODEL.BookingInformation(); 293 bModel.TravelProductId = model.TravelProductId; 294 bModel.Item = item; 295 DB.I_BookingInformation.Add(bModel); 296 } 297 Response.Write("<p>正在导入" + model.LineName + "的签证</p>"); 298 Response.Flush(); 299 //导入签证 300 string[] VisaArray = Rs[35].ToString().Split("\\n"); 301 if (VisaArray.Count()>0) 302 { 303 MODEL.Visa vModel = new MODEL.Visa(); 304 vModel.TravelProductId = model.TravelProductId; 305 vModel.IsTitle =true; 306 vModel.VisaName = "签证"; 307 DB.I_Visa.Add(vModel); 308 } 309 foreach (var item in VisaArray) 310 { 311 DB = new BLL.IBLL(); 312 MODEL.Visa vModel = new MODEL.Visa(); 313 vModel.TravelProductId = model.TravelProductId; 314 vModel.VisaRequire = item; 315 DB.I_Visa.Add(vModel); 316 } 317 Response.Write("<p>正在导入" + model.LineName + "的团期</p>"); 318 Response.Flush(); 319 //导入团期 320 string[] dateArray = Rs[36].ToString().Replace("，", ",").Split(','); 321 foreach (var item in dateArray) 322 { 323 if (string.IsNullOrEmpty(item) == false) 324 { 325 DB = new BLL.IBLL(); 326 MODEL.GroupStage GroupModel = new MODEL.GroupStage(); 327 GroupModel.LineId = model.TravelProductId; 328 GroupModel.OutDate = item.ToDate(); 329 GroupModel.PlanBit = 6; 330 GroupModel.ComeDate = GroupModel.OutDate.Value.AddDays(model.TravelDays.Value); 331 GroupModel.GroupStageStatus = true; 332 GroupModel.PublishState = "正在接客"; 333 GroupModel.EndRegistration = GroupModel.OutDate.Value.AddDays(-model.AdvanceDays.Value); 334 GroupModel.OccupationOverTime = 24; 335 GroupModel.AdultPrice = model.AdultPrice; 336 GroupModel.AdultTakeoutPrice = model.AdultFare; 337 GroupModel.ChildrenPrice = model.ChildrenPrice; 338 GroupModel.ChildrenTakeoutPrice = model.ChildrenFare; 339 if (GroupModel.EndRegistration > DateTime.Now) 340 { 341 var dModel = DB.I_GroupStage.GetSingleModelBy(s => s.OutDate == GroupModel.OutDate && s.LineId == model.TravelProductId); 342 if (dModel == null) 343 { 344 if (DB.I_GroupStage.Add(GroupModel)) 345 { 346 GroupModel = DB.I_GroupStage.GetSingleModelBy(s => s.GroupStageId == GroupModel.GroupStageId); 347 ExtendClass.MakeGroupNumber(GroupModel); 348 } 349 } 350 } 351 } 352 } 353 } 354 } 355 // ts.Complete(); 356 //} 357 358 } 359 } 360 return "<p>全部导入成功！</p>"; 361 } 362 public void ImportSupplier(string fileName) 363 { 364 365 string ExcelPath = Tool.AppPath + fileName; 366 ExcelPath = ExcelPath.Replace("/", "\\"); 367 DataSet supplierDs = Web.ExtendClass.ExcelSqlConnection(ExcelPath, "供应商"); 368 DataTable dt = supplierDs.Tables[0]; 369 Response.Write("<style>p{margin:0;padding:2px;font-size:12px;height:18px;line-height:18px;color:red;}</style><p>共有" + dt.Rows.Count + "条供应商信息</p>"); 370 Response.Flush(); 371 for (int i = 0; i < dt.Rows.Count; i++) 372 { 373 DataRow Rs = dt.Rows[i]; 374 DB = new BLL.IBLL(); 375 MODEL.Supplier model = new MODEL.Supplier(); 376 377 model.AccountBalance = Rs[2].ToString().ToInt(); 378 model.PaidAmount = Rs[3].ToString().ToInt(); 379 model.PlatformCommission = Rs[4].ToString().ToInt(); 380 model.SupplierName = Rs[5].ToString(); 381 Response.Write("<p>正在导入" + model.SupplierName + "</p>"); 382 Response.Flush(); 383 if (string.IsNullOrEmpty(model.SupplierName)) 384 { 385 break; 386 } 387 388 model.SupplierProp = Rs[6].ToString(); 389 model.PayType = Rs[7].ToString(); 390 model.Contacts = Rs[8].ToString(); 391 model.MobilePhone = Rs[9].ToString(); 392 model.BankName = Rs[10].ToString(); 393 model.AccountName = Rs[12].ToString(); 394 model.BankAccount = Rs[13].ToString(); 395 model.CustomerService = Rs[14].ToString(); 396 model.Company = Rs[15].ToString(); 397 model.Fax = Rs[16].ToString(); 398 model.Email = Rs[17].ToString(); 399 model.Area1 = Rs[22].ToString(); 400 model.Area2 = Rs[23].ToString(); 401 model.Area3 = Rs[24].ToString(); 402 model.Address = Rs[25].ToString(); 403 model.BlockedBalances = Rs[26].ToString().ToInt(); 404 model.StartPlace = Rs[27].ToString(); 405 model.ObjectPlace = Rs[28].ToString(); 406 model.BrandName= Rs[30].ToString(); 407 model.CheckForm = Rs[31].ToString(); 408 if (string.IsNullOrEmpty(model.StartPlace)) 409 { 410 model.StartPlace = model.Area2; 411 } 412 if (DB.I_Supplier.GetListBy(s => s.BrandName.Equals(model.BrandName)).Count() > 0) 413 { 414 Response.Write("<p>已存在" + model.SupplierName + "跳过导入</p>"); 415 Response.Flush(); 416 } 417 if(DB.I_Supplier.Add(model)) 418 { 419 ExtendClass.MakeSupplierNumber(model); 420 MODEL.SupplierContact scmodel = new MODEL.SupplierContact(); 421 scmodel.LoginName = model.SupplierNumber; 422 scmodel.Password = Tool.Md5("123.com", scmodel.LoginName); 423 scmodel.WorkArea = model.ObjectPlace; 424 scmodel.FullName = model.Contacts; 425 scmodel.MobilePhone = model.MobilePhone; 426 scmodel.SupplierId = model.SupplierId; 427 scmodel.IsUse = true; 428 DB.I_SupplierContact.Add(scmodel); 429 var placeArray = scmodel.WorkArea.Split('、'); 430 for (int j = 0; j < placeArray.Count(); j++) 431 { 432 scmodel = new MODEL.SupplierContact(); 433 scmodel.LoginName = model.SupplierNumber+"-"+model.SupplierContact.Count.ToString("00"); 434 scmodel.Password = Tool.Md5("123.com", scmodel.LoginName); 435 scmodel.WorkArea = placeArray[j]; 436 scmodel.SupplierId = model.SupplierId; 437 scmodel.IsUse = true; 438 DB.I_SupplierContact.Add(scmodel); 439 } 440 441 } 442 } 443 Response.Write("导入成功！"); 444 445 } 446 447 public void ImportWage(string fileName) 448 { 449 string ExcelPath = Tool.AppPath + fileName; 450 ExcelPath = ExcelPath.Replace("/", "\\"); 451 DataSet empDs = Web.ExtendClass.ExcelSqlConnection(ExcelPath, "Sheet1"); 452 DataTable dt = empDs.Tables[0]; 453 Response.Write("<style>p{margin:0;padding:2px;font-size:12px;height:18px;line-height:18px;color:red;}</style><p>共有" + dt.Rows.Count + "条人员信息</p>"); 454 Response.Flush(); 455 int count = 0; 456 for (int i = 0; i < dt.Rows.Count; i++)//从表格文件的第二行开始 457 { 458 DataRow Rs = dt.Rows[i]; 459 DB = new BLL.IBLL(); 460 MODEL.Employee model = new MODEL.Employee(); 461 string storenumber = Rs[1].ToString(); 462 if (string.IsNullOrEmpty(storenumber)) 463 { 464 Response.Write("<p>门市编号为空，跳过导入</p>"); 465 continue; 466 } 467 MODEL.RetailSales store = DB.I_RetailSales.GetSingleModelBy(s => s.StoreNumber.Equals(storenumber)); 468 model.RetailSalesId = store.RetailSalesId; 469 string idnumber = Rs[9].ToString(); 470 if (string.IsNullOrEmpty(idnumber)) 471 { 472 Response.Write("<p>身份证号为空，跳过导入</p>"); 473 continue; 474 } 475 if (DB.I_Employee.GetListBy(s => s.IdNumber.Equals(idnumber)).Count() > 0) 476 { 477 Response.Write("<p>已存在身份证号：" + Rs[9].ToString() + "跳过导入</p>"); 478 continue; 479 } 480 model.FullName = Rs[2].ToString(); 481 model.EmploymentDate =Convert.ToDateTime(Rs[3].ToString()); 482 model.TerminationDate = Convert.ToDateTime(Rs[4].ToString()); 483 model.BankOfDeposit = Rs[5].ToString(); 484 model.AccountNo = Rs[6].ToString(); 485 model.Position = Rs[7].ToString(); 486 model.Sex = Rs[8].ToString(); 487 model.IdNumber = Rs[9].ToString(); 488 model.StaffBirthday = Convert.ToDateTime(Rs[10].ToString()); 489 model.Nation = Rs[11].ToString(); 490 model.AccountProp = Rs[12].ToString(); 491 model.MobilePhone = Rs[13].ToString(); 492 model.Email = Rs[14].ToString(); 493 model.EmergencyContact = Rs[15].ToString(); 494 model.EmergencyPhone = Rs[16].ToString(); 495 model.MaritalStatus = Rs[17].ToString(); 496 model.CulturalDegree = Rs[18].ToString(); 497 model.AuditStatus = true; 498 model.EmployeeStatus = true; 499 count++; 500 if (!DB.I_Employee.Add(model)) 501 { 502 Response.Write("<style>p{margin:0;padding:2px;font-size:12px;height:18px;line-height:18px;color:red;}</style><p>只导入了" + count + "条人员信息</p>"); 503 break; 504 } 505 506 } 507 Response.Write("导入成功！"); 508 }
