private static TblBankStatDetail PrepareDetail(ccnewEntities entities, TblBankStatHeader header, ImportedBankStatement transaction, List <string> errors) { string recInfo = string.Format( "Doc Date:{0}, Transaction Type:{1}, Amount:{2}, ChequeNo:{3}, DepositNo:{4}, DepositNo:{5}" , transaction.DocDate, transaction.TransactionType, transaction.Amount, transaction.ChequeNo, transaction.DepositNo, transaction.Description); var BankTransactionType = entities.TblBankTransactionTypes.Where(i => i.Code == transaction.TransactionType); if (BankTransactionType.Count() != 1) { if (BankTransactionType.Count() == 0) { errors.Add(string.Format("{1} -->> Bank transaction type not Found. More info -->> {0}", recInfo, DateTime.Now)); } else { errors.Add(string.Format("{1} -->> found more than one bank transaction type . More info -->> {0}", recInfo, DateTime.Now)); } return(null); } var detail = new TblBankStatDetail() { TblBankStatHeader = header.Iserial, TblBankTransactionType = BankTransactionType.FirstOrDefault().Iserial, DocDate = transaction.DocDate, Description = transaction.Description, Amount = transaction.Amount, ChequeNo = transaction.ChequeNo, DepositNo = transaction.DepositNo, }; return(detail); }
private static void FillList(List <ImportedBankStatement> importedList, Excel._Worksheet sheet, int startRow)// ExcelWorksheet sheet { int docDateIndex = 27; int transactionTypeIndex = 28; int descriptionIndex = 29; int amountIndex = 30; int chequeNoIndex = 31; int depositNoIndex = 32; for (int i = startRow; i < sheet.UsedRange.Rows.Count; i++)// // sheet.Cells.End.Row { try { var newemp = new ImportedBankStatement(); //sheet.Cells[i, docDateIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, docDateIndex]).Text)) //if (!string.IsNullOrWhiteSpace(sheet.Cells[i, docDateIndex].Text)) { var docDate = ((Excel.Range)sheet.Cells[i, docDateIndex]).Text.ToUpper().Trim(); //var docDate = sheet.Cells[i, docDateIndex].Text.ToUpper().Trim();//.Replace("/", "").Replace("-", ""); //newemp.DocDate = DateTime.ParseExact(docDate, "dd-mm-yyyy", // System.Globalization.CultureInfo.InvariantCulture, // System.Globalization.DateTimeStyles.None); if (((string)docDate).Split(new char[] { '-' }, StringSplitOptions.RemoveEmptyEntries).Length == 3) { var y = ((Excel.Range)sheet.Cells[i, 3]).Text.ToUpper().Trim(); // sheet.Cells[i, 3].Text.ToUpper().Trim(); var m = ((Excel.Range)sheet.Cells[i, 2]).Text.ToUpper().Trim(); // sheet.Cells[i, 2].Text.ToUpper().Trim(); var d = ((Excel.Range)sheet.Cells[i, 1]).Text.ToUpper().Trim(); // sheet.Cells[i, 1].Text.ToUpper().Trim(); newemp.DocDate = new DateTime(int.Parse(y), int.Parse(m), int.Parse(d)); } } //sheet.Cells[i, transactionTypeIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, transactionTypeIndex]).Text)) { var transactionType = //sheet.Cells[i, transactionTypeIndex].Text.ToUpper().Trim(); ((Excel.Range)sheet.Cells[i, transactionTypeIndex]).Text.ToUpper().Trim(); newemp.TransactionType = transactionType; } //sheet.Cells[i, descriptionIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, descriptionIndex]).Text)) { var description = //sheet.Cells[i, descriptionIndex].Text.ToUpper().Trim(); ((Excel.Range)sheet.Cells[i, descriptionIndex]).Text.ToUpper().Trim(); newemp.Description = description; } //sheet.Cells[i, amountIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, amountIndex]).Text)) { var amount = //sheet.Cells[i, amountIndex].Text.ToUpper().Trim(); ((Excel.Range)sheet.Cells[i, amountIndex]).Text.ToUpper().Trim(); newemp.Amount = Convert.ToDecimal(amount); } //sheet.Cells[i, chequeNoIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, chequeNoIndex]).Text)) { var chequeNo = //sheet.Cells[i, chequeNoIndex].Text.ToUpper().Trim(); ((Excel.Range)sheet.Cells[i, chequeNoIndex]).Text.ToUpper().Trim(); if (chequeNo.Trim().Length > 0) { newemp.ChequeNo = Convert.ToInt64(chequeNo); } } //sheet.Cells[i, depositNoIndex].Calculate(); if (!string.IsNullOrWhiteSpace(((Excel.Range)sheet.Cells[i, depositNoIndex]).Text)) { var depositNo = //sheet.Cells[i, depositNoIndex].Text.ToUpper().Trim(); ((Excel.Range)sheet.Cells[i, depositNoIndex]).Text.ToUpper().Trim(); newemp.DepositNo = depositNo; } importedList.Add(newemp); } catch (Exception ex) { throw ex; } } }
private static void FillList(ObservableCollection <ImportedBankStatement> importedList, Worksheet sheet, ref int docDateIndex, ref int transactionTypeIndex, ref int descriptionIndex, ref int amountIndex, ref int chequeNoIndex, ref int depositNoIndex) { for (int j = sheet.Cells.FirstColIndex; j < sheet.Cells.LastColIndex + 1; j++) { switch (sheet.Cells[0, j].StringValue.ToLower()) { case "docdate": docDateIndex = j; break; case "transactiontype": transactionTypeIndex = j; break; case "description": descriptionIndex = j; break; case "amount": amountIndex = j; break; case "chequeno": chequeNoIndex = j; break; case "depositno": depositNoIndex = j; break; } } for (int i = sheet.Cells.FirstRowIndex + 1; i < sheet.Cells.LastRowIndex + 1; i++) { var newemp = new ImportedBankStatement(); if (sheet.Cells[i, docDateIndex].Value != null) { var docDate = sheet.Cells[i, docDateIndex].Value.ToString().ToUpper().Trim(); newemp.DocDate = Convert.ToDateTime(docDate); } if (sheet.Cells[i, transactionTypeIndex].Value != null) { var transactionType = sheet.Cells[i, transactionTypeIndex].Value.ToString().ToUpper().Trim(); newemp.TransactionType = transactionType; } if (sheet.Cells[i, descriptionIndex].Value != null) { var description = sheet.Cells[i, descriptionIndex].Value.ToString().ToUpper().Trim(); newemp.Description = description; } if (sheet.Cells[i, amountIndex].Value != null) { var amount = sheet.Cells[i, amountIndex].Value.ToString().ToUpper().Trim(); newemp.Amount = Convert.ToDecimal(amount); } if (sheet.Cells[i, chequeNoIndex].Value != null) { var chequeNo = sheet.Cells[i, chequeNoIndex].Value.ToString().ToUpper().Trim(); newemp.ChequeNo = Convert.ToInt64(chequeNo); } if (sheet.Cells[i, depositNoIndex].Value != null) { var depositNo = sheet.Cells[i, depositNoIndex].Value.ToString().ToUpper().Trim(); newemp.DepositNo = depositNo; } importedList.Add(newemp); } }