public ActionResult Index(Import import) { ViewBag.MapId = new SelectList(MapSelect, "Value", "Name", import.MapId); if (!ModelState.IsValid) { if (Request.IsAjaxRequest()) return PartialView(import); return View(import); } DirectoryInfo di = new DirectoryInfo(Path.GetTempPath() + "import_product_files"); if (!di.Exists) di.Create(); foreach (FileInfo fi in di.GetFiles()) fi.Delete(); string file = di.FullName + "\\" + import.File.FileName; import.File.SaveAs(file); try { process_file(file, import); } catch (DbEntityValidationException e) { List<string> ms = new List<string>(); foreach (var eve in e.EntityValidationErrors) { ms.Add(eve.Entry.Entity.GetType() + " failed validation\n"); foreach (var error in eve.ValidationErrors) ms.Add(" - " + error.PropertyName + ":" + error.ErrorMessage); } Errors.Insert("Entity Validation Failed", string.Join("<br>", ms)); } catch (Exception e) { Errors.Insert(Errors.Expand(e)); } return View(import); }
bool process_file(string file, Import import, Action<int> progress=null) { DateTime start_time = DateTime.Now; Excel.IExcelDataReader edr; FileStream stream = System.IO.File.Open(file, FileMode.Open, FileAccess.Read); if (file.EndsWith(".xlsx", StringComparison.InvariantCultureIgnoreCase)) edr = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream); else edr = Excel.ExcelReaderFactory.CreateBinaryReader(stream); //edr = Excel.ExcelReaderFactory.CreateOpenXmlReader(stream); System.Data.DataSet ds = edr.AsDataSet(); edr.Close(); Fhr.ProductOffice.Models.ImportMap import_map = db.ImportMaps.Where(r => r.Id == import.MapId).First(); //if (import_map.C_CompanyProductIdI == null) // throw new Exception("C_CompanyProductIdI in map #" + import_map.Id + " is not specified."); //if (import_map.C_NameI == null) // throw new Exception("C_NameI in map #" + import_map.Id + " is not specified."); db.Configuration.AutoDetectChangesEnabled = false; db.Configuration.ValidateOnSaveEnabled = false; int sheet_count = ds.Tables.Count; int invalid_product_count = 0; int new_product_count = 0; int updated_product_count = 0; int processed_product_count = 0; int invalid_price_count = 0; int new_price_count = 0; int updated_price_count = 0; int processed_price_count = 0; int row_number = -1; List<string> error_messages = new List<string>(); foreach (System.Data.DataTable dt in ds.Tables) { if (import_map.SkipFirstRow) dt.Rows.RemoveAt(0); foreach (System.Data.DataRow row in dt.Rows) { row_number++; Fhr.ProductOffice.Models.Product product = new Fhr.ProductOffice.Models.Product(); product.CompanyId = import_map.CompanyId; product.ExternalId = row[import_map.C_CompanyProductIdI].ToString().Trim(); if (string.IsNullOrWhiteSpace(product.ExternalId)) { bool line_is_empty = true; for (int i = 0; i < 5; i++) if (!string.IsNullOrWhiteSpace(row[i].ToString())) { line_is_empty = false; break; } if (!line_is_empty) { string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") ExternalId is empty."; if (import.CheckNotImport) { invalid_product_count++; error_messages.Add(error); } else throw new Exception(error); } continue; } product.ModifyTime = null; product.UpdateTime = import.UpdateTime; product.Name = row[import_map.C_NameI].ToString().Trim(); if (product.Name == null) { string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") Name is empty."; if (import.CheckNotImport) { invalid_product_count++; error_messages.Add(error); } else throw new Exception(error); } product.Source = "file:" + import.File.FileName; if (import_map.C_SkuI != null) product.Sku = row[(int)import_map.C_SkuI].ToString().Trim(); if (import_map.C_CategoryI != null) product.Category = row[(int)import_map.C_CategoryI].ToString().Trim(); if (import_map.C_DescriptionI != null) product.Description = row[(int)import_map.C_DescriptionI].ToString().Trim(); if (!import.CheckNotImport) { Fhr.ProductOffice.Models.Product p = db.Products.Where(r => r.CompanyId == product.CompanyId && r.ExternalId == product.ExternalId).FirstOrDefault(); if (p == null) { product.CreateTime = import.UpdateTime; product.LinkId = null; db.Products.Add(product); new_product_count += db.SaveChanges(); } else { product.Id = p.Id; if (product.Description != null) p.Description = product.Description; if (product.ImageUrls != null) p.ImageUrls = product.ImageUrls; if (product.ModifyTime != null) p.ModifyTime = product.ModifyTime; p.UpdateTime = product.UpdateTime; if (product.Name != null) p.Name = product.Name; if (product.Sku != null) p.Sku = product.Sku; if (product.Source != null) p.Source = product.Source; if (product.Url != null) p.Url = product.Url; //db.Entry(product).State = EntityState.Modified; updated_product_count += db.SaveChanges(); } } processed_product_count++; if (progress != null) progress(processed_product_count); if (import_map.C_PriceI != null) { Price price = new Price(); price.CurrencyId = import_map.CurrencyId; price.ProductId = product.Id; price.Time = (DateTime)product.UpdateTime; decimal v; if (!decimal.TryParse(row[(int)import_map.C_PriceI].ToString().Trim(), out v)) { string error = "Cell (" + import_map.C_PriceI + ", " + row_number + ") Price cannot be parsed:" + row[(int)import_map.C_PriceI].ToString().Trim(); if (import.CheckNotImport) { invalid_price_count++; error_messages.Add(error); } else throw new Exception(error); } price.Value = v; if (!import.CheckNotImport) { Price p = db.Prices.Where(r => r.ProductId == price.ProductId && r.Time == price.Time).FirstOrDefault(); if (p == null) { db.Prices.Add(price); new_price_count += db.SaveChanges(); } else { price.Id = p.Id; db.Entry(p).CurrentValues.SetValues(price); updated_price_count += db.SaveChanges(); } } processed_price_count++; } DbApi.RenewContext(ref db); } } if (import.CheckNotImport) { Messages.Add("CHECK RESULT", "File " + import.File.FileName + " has been checked with map " + db.ImportMaps.Where(r => r.Id == import.MapId).First().Name); Messages.Add("CHECK RESULT", "Spreadsheets: " + sheet_count); Messages.Add("CHECK RESULT", "Found products: " + processed_product_count); Messages.Add("CHECK RESULT", "Invalid products: " + invalid_product_count); Messages.Add("CHECK RESULT", "Invalid prices: " + invalid_price_count); Messages.Add("CHECK RESULT", "Process time: " + String.Format("{0:0.00}", (DateTime.Now - start_time).TotalSeconds) + " secs"); add_error_messages("CHECK RESULT", error_messages); } else { Messages.Add("IMPORT RESULT", "File " + import.File.FileName + " has been imported with map " + db.ImportMaps.Where(r => r.Id == import.MapId).First().Name); Messages.Add("IMPORT RESULT", "Spreadsheets: " + sheet_count); Messages.Add("IMPORT RESULT", "Processed products: " + processed_product_count); Messages.Add("IMPORT RESULT", "Invalid products: " + invalid_product_count); Messages.Add("IMPORT RESULT", "Added products: " + new_product_count); Messages.Add("IMPORT RESULT", "Changed products: " + updated_product_count); Messages.Add("IMPORT RESULT", "Invalid prices: " + invalid_price_count); Messages.Add("IMPORT RESULT", "Added prices: " + new_price_count); Messages.Add("IMPORT RESULT", "Changed prices: " + updated_price_count); Messages.Add("CHECK RESULT", "Process time: " + String.Format("{0:0.00}", (DateTime.Now - start_time).TotalSeconds) + " secs"); add_error_messages("IMPORT RESULT", error_messages); } return true; }