public ActionResult ImportProduct(HttpPostedFileBase ImportFile) { //check file format FileUploadValidate fs = new FileUploadValidate(); fs.filesize = 550; string us = fs.UploadUserFile(ImportFile); string message = ""; if (us != null) { message += fs.ErrorMessage; ExcelPackage ep = new ExcelPackage(ImportFile.InputStream); var workbook = ep.Workbook; if (workbook != null) { if (workbook.Worksheets.Count > 0) { var currentWorkSheet = workbook.Worksheets.First(); object colHeader = currentWorkSheet.Cells[2, 2].Value; int col = 1; int row = 3; foreach (var item in currentWorkSheet.Cells) { Product product = new Product(); if (currentWorkSheet.Cells[row, col].Value != null) { product.ProductID = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductName = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductExplain = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductPrice = Convert.ToDecimal((double)currentWorkSheet.Cells[row, col++].Value); product.Create_Date = DateTime.Now; product.Delete_Flag = false; byte[] temp = BitConverter.GetBytes(0); product.ProductImg_DB = temp; col = 1; row++; } else { break; } if (ModelState.IsValid) { if (db.Product.Any(p => p.ProductID.Equals(product.ProductID))) //判斷資料是否重複 { message += "<p>" + product.ProductID + "資料已重複<p/>"; //ViewBag.DBResultErrorMessage continue; } try { db.Product.Add(product); db.SaveChanges(); message += "<p>" + product.ProductID + "上傳成功<p/>"; } catch (Exception ex) { logger.Error(ex.Message); ViewBag.ExcelResultErrorMessage = ex.Message; return(View("CreateProduct", Session["UserTag"].ToString())); } } } ViewBag.ExcelResultErrorMessage = message; } } } return(View("CreateProduct", Session["UserTag"].ToString())); }
public ActionResult ImportLargeProduct(HttpPostedFileBase ImportLargeFile) { //check file format FileUploadValidate fs = new FileUploadValidate(); fs.filesize = 2000; ExcelValidate ev = new ExcelValidate(); string message = ""; if (fs.UploadUserFile(ImportLargeFile)) //判斷檔案是否合法 { if (ev.CheckExcelData(ImportLargeFile)) //判斷excel是否有內容 { var currentWorkSheet = ev.workbook.Worksheets.First(); int col = 1; int row = 2; int count = 0; DateTime time_start = DateTime.Now; ShoppingCartEntities dbn = null; try { dbn = new ShoppingCartEntities(); dbn.Configuration.AutoDetectChangesEnabled = false; foreach (var item in currentWorkSheet.Cells) { ++count; Product product = new Product(); if (currentWorkSheet.Cells[row, col].Value != null) { product.ProductID = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductName = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductExplain = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductPrice = Convert.ToDecimal((double)currentWorkSheet.Cells[row, col++].Value); product.Create_Date = DateTime.Now; product.Delete_Flag = false; product.Shelf_Flag = true; byte[] temp = BitConverter.GetBytes(0); product.ProductImg_DB = temp; dbn = InsertProductContext(dbn, product, count, 100, true); col = 1; row++; } else { break; } } dbn.SaveChanges(); } finally { if (dbn != null) { dbn.Dispose(); } } DateTime time_end = DateTime.Now; TempData["ExcelLargeInsertTime"] = "新增了" + (count - 1) + "筆資料共花了" + (((TimeSpan)(time_end - time_start)).TotalMilliseconds / 1000).ToString() + "秒"; logger.Info("Excel Insert Product:" + "新增了" + (count - 1) + "筆資料共花了" + (((TimeSpan)(time_end - time_start)).TotalMilliseconds / 1000).ToString() + "秒"); TempData["ExcelLargeResultMessage"] = message; } else { TempData["ExcelLargeResultMessage"] = ev.ErrorMessage; } } else {//檔案驗證失敗 TempData["ExcelLargeResultErrorMessage"] = fs.ErrorMessage; } return(View("CreateProduct")); }
//[Filters.MemberFilter] public ActionResult ImportOrder(HttpPostedFileBase ImportOrder) { string UserID = Session["Member"].ToString(); FileUploadValidate fs = new FileUploadValidate(); fs.filesize = 2000; string message = ""; ExcelValidate ev = new ExcelValidate(); if (fs.UploadUserFile(ImportOrder)) { if (ev.CheckExcelData(ImportOrder)) { var currentWorkSheet = ev.workbook.Worksheets.First(); int col = 1; int row = 2; foreach (var item in currentWorkSheet.Cells) { if (currentWorkSheet.Cells[row, col].Value != null) { try { string ProductID = currentWorkSheet.Cells[row, col].Value.ToString(); var currentCar = db.ShoppingCarList.Where(m => m.ProductID == ProductID && m.UserID == UserID).FirstOrDefault(); if (currentCar == null) { var product = db.Product.Where(m => m.ProductID == ProductID).FirstOrDefault(); if (product != null) { ShoppingCarList shoppingCarList = new ShoppingCarList(); shoppingCarList.UserID = UserID; shoppingCarList.ProductID = product.ProductID; shoppingCarList.ProductQty = 1; shoppingCarList.Order_Flag = true; shoppingCarList.Create_Date = DateTime.Now; db.ShoppingCarList.Add(shoppingCarList); db.SaveChanges(); message += "<p>[" + row + "," + ProductID + "]新增成功</p>"; } else { message += "<p>[" + row + "]該產品不存在</p>"; } } else { currentCar.ProductQty += 1; message += "<p>[" + row + "," + ProductID + "]新增成功</p>"; } } catch (DbEntityValidationException ex) { TempData["ImportCarMessage"] = "請確認資料格式是否正確"; foreach (var err in ex.EntityValidationErrors) { foreach (var erro in err.ValidationErrors) { var ErrID = erro.PropertyName; if (ErrID == "ProductID") { ErrID = "產品編號"; } else if (ErrID == "ProductName") { ErrID = "產品名稱"; } else if (ErrID == "ProductExplain") { ErrID = "產品說明"; } else if (ErrID == "ProductPrice") { ErrID = "產品價錢"; } message += "<p>[第" + row + "列," + ErrID + "]" + erro.ErrorMessage + "<p/>"; } } } catch (InvalidCastException ex) { var ErrID = ""; if ((col - 1) == 1) { ErrID = "產品編號"; } else if ((col - 1) == 2) { ErrID = "產品名稱"; } else if ((col - 1) == 3) { ErrID = "產品說明"; } else if ((col - 1) == 4) { ErrID = "產品價錢"; } message += "<p>[" + row + "," + ErrID + "]資料型態輸入錯誤</p>"; } catch (Exception ex) { message += ex.Message; } row++; TempData["ExcelResultErrorMessage"] = message; } else { break; } } } return(RedirectToAction("Check_Car")); } else { //ViewBag.ExcelResultErrorMessage = fs.ErrorMessage; TempData["ExcelResultErrorMessage"] = fs.ErrorMessage; return(RedirectToAction("Index", "Home")); } }
public ActionResult ImportProduct(HttpPostedFileBase ImportFile) { //check file format FileUploadValidate fs = new FileUploadValidate(); fs.filesize = 2000; ExcelValidate ev = new ExcelValidate(); string message = ""; if (fs.UploadUserFile(ImportFile)) //判斷檔案是否合法 { if (ev.CheckExcelData(ImportFile)) //判斷excel是否有內容 { var currentWorkSheet = ev.workbook.Worksheets.First(); if (currentWorkSheet.Cells[102, 1].Value != null) { message = "最多上傳100筆產品"; TempData["ExcelResultMessage"] = message; return(View("CreateProduct")); } int col = 1; int row = 2; DateTime time_start = DateTime.Now; ShoppingCartEntities dbn = new ShoppingCartEntities(); foreach (var item in currentWorkSheet.Cells) { Product product = new Product(); if (currentWorkSheet.Cells[row, col].Value != null) { try { product.ProductID = currentWorkSheet.Cells[row, col++].Value.ToString(); if (db.Product.Any(p => p.ProductID.Equals(product.ProductID))) //判斷資料是否重複 { message += "<p>[第" + row + "列]" + product.ProductID + "資料已重複<p/>"; //ViewBag.DBResultErrorMessage continue; } product.ProductName = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductExplain = currentWorkSheet.Cells[row, col++].Value.ToString(); product.ProductPrice = Convert.ToDecimal((double)currentWorkSheet.Cells[row, col++].Value); product.Create_Date = DateTime.Now; product.Delete_Flag = false; product.Shelf_Flag = true; byte[] temp = BitConverter.GetBytes(0); product.ProductImg_DB = temp; db.Product.Add(product); db.SaveChanges(); message += "<p>[第" + row + "列]" + product.ProductID + "上傳成功 <p/>"; } catch (DbEntityValidationException ex) { logger.Error(ex.Message); TempData["ExcelResultErrorMessage"] = "請確認資料格式是否正確"; foreach (var err in ex.EntityValidationErrors) { foreach (var erro in err.ValidationErrors) { var ErrID = erro.PropertyName; if (ErrID == "ProductID") { ErrID = "產品編號"; } else if (ErrID == "ProductName") { ErrID = "產品名稱"; } else if (ErrID == "ProductExplain") { ErrID = "產品說明"; } else if (ErrID == "ProductPrice") { ErrID = "產品價錢"; } message += "<p>[第" + row + "列," + ErrID + "]" + erro.ErrorMessage + "<p/>"; } } db.Product.Remove(product); //移除錯誤實體避免判斷錯誤 } catch (InvalidCastException ex) { var ErrID = ""; if ((col - 1) == 1) { ErrID = "產品編號"; } else if ((col - 1) == 2) { ErrID = "產品名稱"; } else if ((col - 1) == 3) { ErrID = "產品說明"; } else if ((col - 1) == 4) { ErrID = "產品價錢"; } message += "<p>[" + row + "," + ErrID + "]資料型態輸入錯誤</p>"; } catch (Exception ex) { TempData["ExcelResultErrorMessage"] = ex.Message; } finally { col = 1; row++; } } else { break; } } //db.SaveChanges(); //db.Dispose(); DateTime time_end = DateTime.Now; TempData["ExcelInsertTime"] = "共花了" + (((TimeSpan)(time_end - time_start)).TotalMilliseconds / 1000).ToString() + "秒"; TempData["ExcelResultMessage"] = message; } else { TempData["ExcelResultMessage"] = ev.ErrorMessage; } } else {//檔案驗證失敗 TempData["ExcelResultErrorMessage"] = fs.ErrorMessage; } return(View("CreateProduct")); }