private void DoSales ( ExcelWorksheet worksheet, Dictionary <string, string> colDic, MasterMap mMap, DTO.ImportPost ip ) { int iStart = worksheet.Dimension.Start.Row; iStart = 2; int iEnd = GetLastRow(worksheet); if (iEnd > 0) { for (int i = iStart; i <= iEnd; i++) { var row = worksheet.Row(i); string warnings = ""; string requires = ""; var rec = new Sale(); foreach (string key in colDic.Keys) { try { var els = colDic[key].Split('|'); int ix = int.Parse(els[0]); string val = worksheet.GetValue(i, ix).ToString().Trim(); string pattern = els[1]; if (!String.IsNullOrEmpty(pattern)) { Regex regex = new Regex(pattern); Match match = regex.Match(val); if (match.Success) { val = match.Groups[1].Value; } } var colSpecs = mMap.Columns.First(o => o.ColumnName == key); if (!String.IsNullOrEmpty(val)) { dynamic realVal = null; switch (colSpecs.ColumnDataType) { case 1: realVal = val; break; case 2: val = Regex.Replace(val, "[^0-9.]", ""); var ppos = val.IndexOf('.'); if (ppos != -1) { val = val.Substring(0, ppos - 1); } realVal = Convert.ToInt64(val); break; case 3: val = Regex.Replace(val, "[^0-9.]", ""); realVal = Convert.ToDouble(val); break; case 4: DateTime realDT; if (DateTime.TryParse(val, out realDT)) { realVal = realDT; } if (realVal == null) { long ldate = long.Parse(val); realVal = DateTime.FromOADate(ldate); } break; case 5: realVal = OrmHelper.IsTrue(val); break; case 6: val = Regex.Replace(val, "[^0-9.]", ""); realVal = Decimal.Parse(val); break; } if (realVal != null) { OrmHelper.SetPropertyValue <Sale>(ref rec, key, realVal); } } } catch { warnings += $"'{key}',"; } } bool isFubar = false; foreach (var col in mMap.Columns) { if (col.Required) { dynamic realVal = OrmHelper.GetPropertyValue <Sale>(rec, col.ColumnName); if (realVal == null) { requires += $"'{col.ColumnName}',"; isFubar = true; } } } if (!isFubar) { rec.ImportId = ip.ImportId; rec.Row = i; rec.Sheet = ip.SheetNo; OrmHelper.SetAuditColumns <Sale>(ref rec, true, ip.PersonId); _db.Sale.Add(rec); } if (isFubar || warnings.Length > 0) { if (isFubar) { ip.SkipRows++; } if (warnings.Length > 0) { ip.WarnRows++; } string errInfo = "{"; errInfo += "warns:[" + warnings.TrimEnd(',') + "],"; errInfo += "skips:[" + requires.TrimEnd(',') + "]"; errInfo += "}"; var ie = new ImportError() { ImportId = ip.ImportId, Status = isFubar ? 1 : 0, Row = i, Sheet = ip.SheetNo, Errors = errInfo }; OrmHelper.SetAuditColumns <ImportError>(ref ie, true, ip.PersonId); _db.ImportError.Add(ie); } } } }