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"));
}
