// Импорт информации о новой группе из документа Excel public static void ImportGroup(string FilePath, DataGridView GroupInfo, string GroupName) { // Открыть документ Excel только для чтения Excel.Application ExcelApp = new Excel.Application(); Excel.Workbook ExcelWb = ExcelApp.Workbooks.Open(FilePath, Type.Missing, true); Excel.Worksheet ExcelWs1 = (Excel.Worksheet)ExcelWb.Sheets[1]; Excel.Worksheet ExcelWs2 = (Excel.Worksheet)ExcelWb.Sheets[2]; // Получить ID группы для импорта int GroupID = DBOperations.GetGroupID(GroupName); // Пройти по всем строкам и считать информацию int LastRow = ExcelWs1.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row; for (int i = 2; i <= LastRow; i++) { object[] NewChild = new object[6]; NewChild[0] = GroupID; // Name NewChild[1] = ExcelWs1.Cells[i, 2].Text; // Grade NewChild[2] = Int32.Parse(ExcelWs1.Cells[i, 3].Text); // Birthday NewChild[3] = DateTime.Parse(ExcelWs1.Cells[i, 4].Text); // Privilege NewChild[4] = Convert.ToInt32(ExcelWs1.Cells[i, 5].Value != null); // Address NewChild[5] = ExcelWs1.Cells[i, 6].Text; long ChildID = DBOperations.InsertChild(NewChild); if (ExcelWs1.Cells[i, 7].Value != null) { int ParentRow = ExcelWs2.Cells[System.Reflection.Missing.Value, 1].Find(ExcelWs1.Cells[i, 7].Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row; object[] Parent = new object[3]; Parent[0] = ExcelWs2.Cells[ParentRow, 2].Value.ToString(); Parent[1] = ExcelWs2.Cells[ParentRow, 3].Value.ToString(); Parent[2] = ExcelWs2.Cells[ParentRow, 4].Value.ToString(); DBOperations.InsertParent(Parent, ChildID); } if (ExcelWs1.Cells[i, 8].Value != null) { int ParentRow = ExcelWs2.Cells[System.Reflection.Missing.Value, 1].Find(ExcelWs1.Cells[i, 7].Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row; object[] Parent = new object[3]; Parent[0] = ExcelWs2.Cells[ParentRow, 2].Value.ToString(); Parent[1] = ExcelWs2.Cells[ParentRow, 3].Value.ToString(); Parent[2] = ExcelWs2.Cells[ParentRow, 4].Value.ToString(); DBOperations.InsertParent(Parent, ChildID); } ExcelWb.Close(); ExcelApp.Quit(); } }
// Добавить ребенка в группу public static int AddChild(string GroupName, string Name, string Grade, bool Privilege, string Birthday, string Address) { object[] Child = new object[6]; Child[0] = DBOperations.GetGroupID(GroupName); // Name Child[1] = Name; // Grade Child[2] = Int32.Parse(Grade); // Birthday Child[3] = DateTime.Parse(Birthday); // Privilege Child[4] = Convert.ToInt32(Privilege); // Address Child[5] = Address; return((int)DBOperations.InsertChild(Child)); }