private void setValue(FixedStrings fixedStrings, string str, int cCnt, string strBackup) { if (string.IsNullOrEmpty(str)) return; if (cCnt == fixedStrings.Column_StudentID) { this.StudentID = str; } else if (cCnt == fixedStrings.Column_Parent2Num) { this.Parent2Num = str; } else if (cCnt == fixedStrings.Column_Parent2Name) { this.Parent2Name = str; } else if (cCnt == fixedStrings.Column_Parent1Num) { this.Parent1Num = str; } else if (cCnt == fixedStrings.Column_Parent1Name) { this.Parent1Name = str; } else if (cCnt == fixedStrings.Column_Parent1Email) { this.Parent1Email = str; } else if (cCnt == fixedStrings.Column_LastName) { this.LastName = str; } else if (cCnt == fixedStrings.Column_HomeNum) { this.HomeNum = str; } else if (cCnt == fixedStrings.Column_HealthIssues) { this.HealthIssues = str.Split(',').ToList(); } else if (cCnt == fixedStrings.Column_Parent2Email) { this.Parent2Email = str; } else if (cCnt == fixedStrings.Column_GradeNClass) { if (str.ToLower().Contains(fixedStrings.Gan_Hova.ToLower())) { this.Grade = fixedStrings.Gan_Hova; var sclassTemp = str.Split('-'); if (sclassTemp != null && sclassTemp.Count() > 1) this.SClass = sclassTemp[1].Trim(); else this.SClass = "1"; } else if (str.ToLower().Contains(fixedStrings.Gan_Trom_Hova.ToLower())) { this.Grade = fixedStrings.Gan_Trom_Hova; var sclassTemp = str.Split('-'); if (sclassTemp != null && sclassTemp.Count() > 1) this.SClass = sclassTemp[1].Trim(); else this.SClass = "1"; } else if (str.Length == 1) { this.Grade = str; this.SClass = "1"; } else if (str.Length == 2) { this.Grade = str[0].ToString(); this.SClass = str[1].ToString(); } } else if (cCnt == fixedStrings.Column_Gender) { this.Gender = str; } else if (cCnt == fixedStrings.Column_FirstName) { this.FirstName = str; } else if (cCnt == fixedStrings.Column_BirthDay) { string exactDatetime = null; try { var temp = str.Split('/'); if (temp == null || temp.Count() != 3) { temp = strBackup.Split('/'); if (temp == null || temp.Count() != 3) { this.BirthDay = DateTime.MinValue; return; } } if (temp[0].Length == 1) exactDatetime = "0" + temp[0]; else exactDatetime = temp[0]; exactDatetime += "/"; if (temp[1].Length == 1) exactDatetime += "0" + temp[1]; else exactDatetime += temp[1]; exactDatetime += "/"; if (temp[2].Length == 2) exactDatetime += "20" + temp[2]; else if (temp[2].Length == 4) exactDatetime += temp[2]; else { this.BirthDay = DateTime.MinValue; return; } this.BirthDay = DateTime.ParseExact(exactDatetime, "MM/dd/yyyy", System.Globalization.CultureInfo.InvariantCulture); ; } catch (Exception ex) { try { this.BirthDay = DateTime.ParseExact(exactDatetime, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture); ; } catch (Exception) { this.BirthDay = DateTime.MinValue; } } } else if (cCnt == fixedStrings.Column_Address) { this.Address = str; } else if (cCnt == fixedStrings.Column_PickUpOptions) { try { this.PickUpOptions = str.Split(fixedStrings.pickupSeperators).ToList().Where(p => !fixedStrings.IgnorePicks.Contains(p)).ToList(); } catch (Exception) { this.PickUpOptions = str.Split(fixedStrings.pickupSeperators).ToList(); } } }
internal static List<StudentModel> LoadXlsx(Range range, int branchId) { string str; int rCnt = 0; int cCnt = 0; bool isLastRowEmpty = true; FixedStrings fixedStrings = new FixedStrings(); List<StudentModel> list = new List<StudentModel>(); for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { isLastRowEmpty = true; try { if (rCnt > 499) break; StudentModel s = new StudentModel(); for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { var strBackup = (range.Cells[rCnt, cCnt] as Excel.Range).Text.ToString(); var temp = (range.Cells[rCnt, cCnt] as Excel.Range).Value2; str = temp != null ? temp.ToString() : ""; if (isLastRowEmpty && !string.IsNullOrEmpty(str)) { isLastRowEmpty = false; } //initalize the data array if (rCnt == 1) { fixedStrings.setValue(str, cCnt); continue; } else { s.setValue(fixedStrings, str, cCnt, strBackup); } //Console.WriteLine(str); } if (rCnt != 1) list.Add(s); } catch (Exception ex) { Console.WriteLine("Error in one of the studens."); } if (isLastRowEmpty) break; } return list; }