public List <Dictionary <string, string> > getExcelFile(string path) { //Student s; //Create COM Objects. Create a COM object for everything that is referenced Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; int numEffected = 0; Teacher t; List <Teacher> nEd = new List <Teacher>(); List <Classroom> newC = new List <Classroom>(); List <string> firstRow = new List <string>(); List <Dictionary <string, string> > report = new List <Dictionary <string, string> >(); List <Dictionary <string, string> > slist = new List <Dictionary <string, string> >(); Dictionary <int, Dictionary <int, Dictionary <int, Classroom> > > schoolDict = new Dictionary <int, Dictionary <int, Dictionary <int, Classroom> > >(); for (int fr = 1; fr <= colCount; fr++) { firstRow.Add(xlRange.Cells[1, fr].Value2); } for (int i = 2; i <= rowCount; i++) { User u = new User(); Classroom c1 = new Classroom(); string type = xlRange.Cells[i, 6].Value2; for (int j = 1; j <= colCount; j++) { if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null) { switch (firstRow[j - 1]) { case "id": u.IdNumber = xlRange.Cells[i, j].Value2.ToString(); break; case "Email": u.Mail = xlRange.Cells[i, j].Value2; break; case "lastName": u.LastName = xlRange.Cells[i, j].Value2; break; case "name": u.Name = xlRange.Cells[i, j].Value2; break; case "school": c1.InSchool = (int)xlRange.Cells[i, j].Value2; break; case "Grade": c1.Grade = (int)xlRange.Cells[i, j].Value2; break; case "ClassNumber": c1.GradeNumber = (int)xlRange.Cells[i, j].Value2; break; } } } if (type == "מורה") { t = new Teacher(u.IdNumber, u.Name, u.LastName, u.IdNumber, u.Mail, c1.InSchool, false, false); if (c1.GradeNumber > 0) { c1.Name = u.Name + "-" + c1.Grade + "`" + c1.GradeNumber; c1.EdTeacher = t; if (!schoolDict.ContainsKey(c1.InSchool)) { schoolDict.Add(c1.InSchool, new Dictionary <int, Dictionary <int, Classroom> >()); } if (!schoolDict[c1.InSchool].ContainsKey(c1.Grade)) { schoolDict[c1.InSchool].Add(c1.Grade, new Dictionary <int, Classroom>()); } if (!schoolDict[c1.InSchool][c1.Grade].ContainsKey(c1.GradeNumber)) { schoolDict[c1.InSchool][c1.Grade].Add(c1.GradeNumber, new Classroom()); } schoolDict[c1.InSchool][c1.Grade][c1.GradeNumber] = c1; } else { nEd.Add(t); } } else if (type == "תלמיד") { Dictionary <string, string> sdict = new Dictionary <string, string>(); sdict.Add("IdNumber", u.IdNumber); sdict.Add("Mail", u.Mail); sdict.Add("Name", u.Name); sdict.Add("LastName", u.LastName); sdict.Add("Password", u.IdNumber); sdict.Add("SCode", c1.InSchool.ToString()); sdict.Add("Grade", c1.Grade.ToString()); sdict.Add("ClassNumber", c1.GradeNumber.ToString()); slist.Add(sdict); } } //cleanup GC.Collect(); GC.WaitForPendingFinalizers(); //rule of thumb for releasing com objects: // never use two dots, all COM objects must be referenced and released individually // ex: [somthing].[something].[something] is bad //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); DBservices dbs = new DBservices(); foreach (var item in schoolDict) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postNewClasses(item.Value); foreach (var row in r) { report.Add(row); } } if (nEd.Count > 0) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postnEdlFile(nEd); foreach (var row in r) { report.Add(row); } } if (slist.Count > 0) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postStudentFile(slist); foreach (var row in r) { report.Add(row); } } return(report); }
public List <Dictionary <string, string> > readFile(ISheet sheet) { Teacher t; List <Teacher> nEd = new List <Teacher>(); List <Classroom> newC = new List <Classroom>(); Dictionary <string, int> firstRow = new Dictionary <string, int>(); List <Dictionary <string, string> > report = new List <Dictionary <string, string> >(); List <Dictionary <string, string> > slist = new List <Dictionary <string, string> >(); Dictionary <int, Dictionary <int, Dictionary <int, Classroom> > > schoolDict = new Dictionary <int, Dictionary <int, Dictionary <int, Classroom> > >(); int flg = 0; int colRange = 0; User u = new User(); while (sheet.GetRow(0).GetCell(colRange) != null) { firstRow[sheet.GetRow(0).GetCell(colRange).ToString()] = colRange; colRange++; } for (int row = 1; row <= sheet.LastRowNum; row++) { if (report.Count > 0) { return(report); } string type; u = new User(); Classroom c1 = new Classroom(); try { type = sheet.GetRow(row).GetCell(firstRow["Type"]).ToString(); } catch (NullReferenceException ex) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", " שים לב שמחיקת רשומה באקסל מתבצעת על ידי סימון השורות בסרגל שסופר את התשובות בצד לחצן ימני ומחיקה!,הקובץ אינו תקין "); report.Add(reportRow); return(report); } /* xlRange.Cells[i, 6].Value2;*/ foreach (var j in firstRow) { if (flg == 1) { return(report); } switch (j.Key) { case "id": u.IdNumber = sheet.GetRow(row).GetCell(j.Value).ToString(); if (!(IsAllDigits(u.IdNumber) && u.IdNumber.Length == 9)) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line" + row + "column" + j.Key + " content is invalid,must be 9 digits ID"); report.Add(reportRow); flg = 1; } break; case "Email": u.Mail = sheet.GetRow(row).GetCell(j.Value).ToString(); if (!IsValid(u.Mail)) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line" + row + "column" + j.Key + " content is invalid,email is incorrect"); report.Add(reportRow); flg = 1; } break; case "lastName": u.LastName = sheet.GetRow(row).GetCell(j.Value).ToString(); if (!Regex.IsMatch(u.LastName, "[א-ת]{2,15}")) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line" + row + "column" + j.Key + " content is invalid,must be only hebrew letters"); report.Add(reportRow); flg = 1; } break; case "name": u.Name = sheet.GetRow(row).GetCell(j.Value).ToString(); if (!Regex.IsMatch(u.Name, "[א-ת]{2,15}")) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line " + row + "column " + j.Key + " content is invalid,must be only hebrew letters"); report.Add(reportRow); flg = 1; } break; case "school": try { c1.InSchool = Convert.ToInt32(sheet.GetRow(row).GetCell(j.Value).ToString()); } catch { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line " + row + "column" + j.Key + " content is invalid,must be only digits"); report.Add(reportRow); flg = 1; continue; } break; case "Grade": if (sheet.GetRow(row).GetCell(j.Value) == null) { c1.Grade = Convert.ToInt32(sheet.GetRow(row).GetCell(j.Value)); } else { c1.Grade = Convert.ToInt32(sheet.GetRow(row).GetCell(j.Value).ToString()); } if (c1.Grade < 7 || c1.Grade > 9) { Dictionary <string, string> reportRow = new Dictionary <string, string>(); reportRow.Add("msg", "in line " + row + " column" + j.Key + " content is invalid,must be only digits(7,8,9)"); report.Add(reportRow); flg = 1; } break; case "ClassNumber": if (sheet.GetRow(row).GetCell(j.Value) == null) { c1.GradeNumber = Convert.ToInt32(sheet.GetRow(row).GetCell(j.Value)); } else { c1.GradeNumber = Convert.ToInt32(sheet.GetRow(row).GetCell(j.Value).ToString()); } if (type == "תלמיד" && c1.GradeNumber == 0) { flg = 1; } break; } } if (type == "מורה") { t = new Teacher(u.IdNumber, u.Name, u.LastName, u.IdNumber, u.Mail, c1.InSchool, false, false); if (c1.GradeNumber > 0) { c1.Name = u.Name + "-" + c1.Grade + "`" + c1.GradeNumber; c1.EdTeacher = t; if (!schoolDict.ContainsKey(c1.InSchool)) { schoolDict.Add(c1.InSchool, new Dictionary <int, Dictionary <int, Classroom> >()); } if (!schoolDict[c1.InSchool].ContainsKey(c1.Grade)) { schoolDict[c1.InSchool].Add(c1.Grade, new Dictionary <int, Classroom>()); } if (!schoolDict[c1.InSchool][c1.Grade].ContainsKey(c1.GradeNumber)) { schoolDict[c1.InSchool][c1.Grade].Add(c1.GradeNumber, new Classroom()); } schoolDict[c1.InSchool][c1.Grade][c1.GradeNumber] = c1; } else { nEd.Add(t); } } else if (type == "תלמיד") { Dictionary <string, string> sdict = new Dictionary <string, string>(); sdict.Add("IdNumber", u.IdNumber); sdict.Add("Mail", u.Mail); sdict.Add("Name", u.Name); sdict.Add("LastName", u.LastName); sdict.Add("Password", u.IdNumber); sdict.Add("SCode", c1.InSchool.ToString()); sdict.Add("Grade", c1.Grade.ToString()); sdict.Add("ClassNumber", c1.GradeNumber.ToString()); slist.Add(sdict); } } DBservices dbs = new DBservices(); foreach (var item in schoolDict) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postNewClasses(item.Value); foreach (var row in r) { report.Add(row); } } if (nEd.Count > 0) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postnEdlFile(nEd); foreach (var row in r) { report.Add(row); } } if (slist.Count > 0) { List <Dictionary <string, string> > r = new List <Dictionary <string, string> >(); r = dbs.postStudentFile(slist); foreach (var row in r) { report.Add(row); } } return(report); }