/// <summary> /// /// </summary> /// <param name="excelApp"> /// Приложение Excel, в котором открыты книги /// <paramref name="wbkTarget"/> и <paramref name="wbkTemplates"/> /// </param> /// <param name="wbkTarget"> /// Книга, в которую добавляется отчёт. Добавление всегда происходит в конец /// </param> /// <param name="wbkTemplates"> /// Книга с шаблонами отчётов /// </param> /// <returns></returns> public override bool CreateReport(MSExcel.Application excelApp, MSExcel.Workbook wbkTarget, MSExcel.Workbook wbkTemplates) { // Копируем в конец новой книги лист-шаблон MSExcel.Worksheet wsh = null; MSExcel.Range rng = null; lock (DBManagerApp.m_AppSettings.m_SettingsSyncObj) { wsh = wbkTemplates.Worksheets[DBManagerApp.m_AppSettings.m_Settings.ExcelSettings.dictReportTemplates[enReportTypes.Total]]; } wsh.Copy(After: wbkTarget.Worksheets[wbkTarget.Worksheets.Count]); // Лист, в который нужно будет вставлять данные wsh = wbkTarget.Worksheets[wbkTarget.Worksheets.Count]; wsh.Name = CurTask.m_GroupToExport.Rounds.First(arg => arg.id == enRounds.Total).SheetName; groups GroupInDB = CurTask.m_CompDesc.groups.First(arg => { return(arg.id_group == CurTask.m_GroupToExport.id); }); CCompSettings CompSettings = new CCompSettings(GroupInDB); wsh.Range[RN_COMP_NAME].Value = CompSettings.CompName; wsh.Range[RN_ROUND_DATE].Value = GlobalDefines.CreateCompDate(CompSettings.StartDate, CompSettings.EndDate == null ? null : (DateTime?)CompSettings.EndDate.Date); wsh.Range[RN_MAIN_JUDGE].Value = CompSettings.MainJudge; wsh.Range[RN_MAIN_SECRETARY].Value = CompSettings.MainSecretary; wsh.Range[RN_SECOND_COL_NAME].Value = CompSettings.SecondColName; if (CompSettings.Row6 == GlobalDefines.DEFAULT_XML_STRING_VAL) { wsh.Cells[wsh.Range[RN_ROW_6].Row, 1].Value = ""; } else { wsh.Range[RN_ROW_6].Value = CompSettings.Row6; } string ReportName = wsh.Range[RN_REPORT_NAME].Value; int SelectedStartYear, SelectedEndYear; wsh.Range[RN_REPORT_NAME].Value = ReportName.Replace(GlobalDefines.EXCEL_REPORT_NAME_TEXT_TO_REPLACE, CreateGroupName(CurTask.m_GroupToExport, CompSettings.AgeGroup, out SelectedStartYear, out SelectedEndYear)); // Выводим участников соревнования int FirstRow = wsh.Range[RN_FIRST_DATA_ROW].Row; List <enRounds> CompRounds = (from round in CurTask.m_GroupToExport.Rounds orderby round.id select round.id).ToList(); int FirstMiddleSheetRoundMembers = 0; if (CompRounds.Contains(enRounds.Qualif2)) { FirstMiddleSheetRoundMembers = GroupInDB.from_2_qualif.Value; } else { FirstMiddleSheetRoundMembers = GroupInDB.from_1_qualif.Value; // Удаляем столбцы со второй квалификацией int ColQ = wsh.Range[RN_QUALIF_2].Columns.Count; for (int col = 0; col < ColQ; col++) { wsh.Columns[wsh.Range[RN_QUALIF_2].Column].Delete(); } } // Удаляем столбцы для тех раундов, которых нет if (!CompRounds.Contains(enRounds.OneEighthFinal)) { wsh.Columns[wsh.Range[RN_ONE_EIGHTH_FINAL].Column].Delete(); } if (!CompRounds.Contains(enRounds.QuaterFinal)) { wsh.Columns[wsh.Range[RN_QUATERFINAL].Column].Delete(); } if (!CompRounds.Contains(enRounds.SemiFinal)) { wsh.Columns[wsh.Range[RN_SEMIFINAL].Column].Delete(); } List <results_speed> AllGroupResultsInDB = (from part in DBManagerApp.m_Entities.participations join result in DBManagerApp.m_Entities.results_speed on part.id_participation equals result.participation where part.Group == CurTask.m_GroupToExport.id select result).ToList(); List <CMemberInTotal> lstResults = (from member in DBManagerApp.m_Entities.members join part in DBManagerApp.m_Entities.participations on member.id_member equals part.member where part.Group == GroupInDB.id_group && part.result_place.HasValue orderby part.result_place select new CMemberInTotal { MemberInfo = new CFullMemberInfo() { IDMember = member.id_member, Surname = member.surname, Name = member.name, YearOfBirth = member.year_of_birth, Coach = part.coach, Team = part.team, InitGrade = part.init_grade, }, TotalGrade = part.result_grade, Place = part.result_place, id_part = part.id_participation }).ToList(); byte CurPlace = 1; byte ResultIndex = 0; int RowsQ = 0; int? PrevResult = null; // Такого результата не может быть foreach (CMemberInTotal MemberInTotal in lstResults) { RowsQ++; if (MemberInTotal.MemberInfo.YearOfBirth.HasValue && (MemberInTotal.MemberInfo.YearOfBirth.Value < SelectedStartYear || MemberInTotal.MemberInfo.YearOfBirth.Value > SelectedEndYear)) { // Фильтруем участников continue; } ResultIndex++; // Заносим результат всех раундов для участника IEnumerable <results_speed> CurMemberResults = AllGroupResultsInDB.Where(arg => arg.participation == MemberInTotal.id_part); foreach (results_speed MemberResult in CurMemberResults) { if (MemberResult.round == CurMemberResults.Max(arg => arg.round)) { wsh.Range[RN_BALLS_FOR_PLACES].Offset[RowsQ].Value = MemberResult.balls; } switch ((enRounds)MemberResult.round) { case enRounds.Qualif: wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_ROUTE1_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route1, (enAdditionalEventTypes?)MemberResult.event_1); wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_ROUTE2_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route2, (enAdditionalEventTypes?)MemberResult.event_2); wsh.Range[RN_QUALIF_1].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; case enRounds.Qualif2: wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_ROUTE1_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route1, (enAdditionalEventTypes?)MemberResult.event_1); wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_ROUTE2_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.route2, (enAdditionalEventTypes?)MemberResult.event_2); wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; case enRounds.OneEighthFinal: wsh.Range[RN_ONE_EIGHTH_FINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; case enRounds.QuaterFinal: wsh.Range[RN_QUATERFINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; case enRounds.SemiFinal: wsh.Range[RN_SEMIFINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; case enRounds.Final: wsh.Range[RN_FINAL].Offset[RowsQ].Value = GlobalDefines.EncodeSpeedResult(MemberResult.sum, (enAdditionalEventTypes?)MemberResult.event_sum); break; } } if (MemberInTotal.Place.HasValue && MemberInTotal.Place != PrevResult) { CurPlace = ResultIndex; } wsh.Cells[RowsQ + FirstRow - 1, EXCEL_PLACE_COL_NUM].Value = GlobalDefines.EncodePlace(CurPlace); wsh.Cells[RowsQ + FirstRow - 1, EXCEL_PERSONAL_COL_NUM].Value = MemberInTotal.MemberInfo.SurnameAndName; if (CompSettings.SecondColNameType == enSecondColNameType.Coach) { wsh.Cells[RowsQ + FirstRow - 1, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.coaches.First(arg => arg.id_coach == MemberInTotal.MemberInfo.Coach).name; } else { wsh.Cells[RowsQ + FirstRow - 1, EXCEL_TEAM_COL_NUM].Value = DBManagerApp.m_Entities.teams.First(arg => arg.id_team == MemberInTotal.MemberInfo.Team).name; } wsh.Cells[RowsQ + FirstRow - 1, EXCEL_YEAR_OF_BIRTH_COL_NUM].Value = MemberInTotal.MemberInfo.YearOfBirth; GradeMarkupConverter conv = new GradeMarkupConverter(); wsh.Cells[RowsQ + FirstRow - 1, EXCEL_GRADE_COL_NUM].Value = conv.Convert(MemberInTotal.MemberInfo.InitGrade, MemberInTotal.MemberInfo.InitGrade.GetType(), null, CultureInfo.GetCultureInfo(GlobalDefines.RUSSIAN_CULTURE_NAME)); if (MemberInTotal.TotalGrade.HasValue) { wsh.Range[RN_RESULT_GRADE].Offset[RowsQ].Value = conv.Convert(MemberInTotal.TotalGrade, MemberInTotal.TotalGrade.GetType(), null, CultureInfo.GetCultureInfo(GlobalDefines.RUSSIAN_CULTURE_NAME)); } PrevResult = MemberInTotal.Place; } // Редактируем сетку // в квалификации rng = wsh.Range[wsh.Cells[FirstMiddleSheetRoundMembers + FirstRow, EXCEL_PLACE_COL_NUM], wsh.Cells[RowsQ + FirstRow - 1, EXCEL_GRADE_COL_NUM + EXCEL_SUM_COL_OFS]]; rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].Weight = rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; // В выполненном разряде rng = wsh.Range[wsh.Range[RN_RESULT_GRADE].Offset[FirstMiddleSheetRoundMembers + 1], wsh.Range[RN_RESULT_GRADE].Offset[RowsQ + FirstRow]]; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; // В баллах rng = wsh.Range[wsh.Range[RN_BALLS_FOR_PLACES].Offset[FirstMiddleSheetRoundMembers + 1], wsh.Range[RN_BALLS_FOR_PLACES].Offset[RowsQ + FirstRow]]; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; if (CompRounds.Contains(enRounds.Qualif2)) { // Во второй квалификации // Удаляем подчёркивание, где оно не нужно и добавляем там, где нужно rng = wsh.Range[wsh.Cells[FirstMiddleSheetRoundMembers + FirstRow, EXCEL_PLACE_COL_NUM], wsh.Cells[GroupInDB.from_1_qualif.Value + FirstRow - 1, wsh.Range[RN_QUALIF_2].Column + EXCEL_SUM_COL_OFS - 1]]; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; // Удаляем сетку, где она не нужна rng = wsh.Range[wsh.Range[RN_QUALIF_2].Offset[GroupInDB.from_1_qualif.Value + 1].Cells[1, 1], wsh.Range[RN_QUALIF_2].Offset[RowsQ].Cells[1, EXCEL_SUM_COL_OFS]]; rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeRight].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlInsideVertical].LineStyle = MSExcel.Constants.xlNone; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; // В выполненном разряде // Выполняем подчёркивание rng = wsh.Range[RN_RESULT_GRADE].Offset[GroupInDB.from_1_qualif.Value + 1]; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; // В баллах rng = wsh.Range[RN_BALLS_FOR_PLACES].Offset[GroupInDB.from_1_qualif.Value + 1]; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].LineStyle = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlInsideHorizontal].Weight = rng.Borders[MSExcel.XlBordersIndex.xlEdgeBottom].Weight = MSExcel.XlBorderWeight.xlThin; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].LineStyle = MSExcel.XlLineStyle.xlContinuous; rng.Borders[MSExcel.XlBordersIndex.xlEdgeTop].Weight = MSExcel.XlBorderWeight.xlMedium; } bool NeedCorrectBorders = false; int MaxRows = EXCEL_MAX_LINES_IN_REPORTS; rng = wsh.Range[RN_TABLE_HEADER].Cells[1, EXCEL_PERSONAL_COL_NUM]; for (int r = 0; r < RowsQ;) { if (string.IsNullOrEmpty(rng.Offset[r + 1].Value as string)) { wsh.Rows[rng.Offset[r + 1].Row].Delete(); MaxRows--; RowsQ--; if (r < GroupInDB.from_1_qualif.Value) { NeedCorrectBorders = true; } } else { r++; } } wsh.Rows[(RowsQ + FirstRow).ToString() + ":" + (MaxRows + FirstRow - 1).ToString()].Delete(MSExcel.XlDirection.xlUp); if (NeedCorrectBorders) { // Ещё раз корректируем подчёркивание if (CompRounds.Contains(enRounds.Qualif2)) { GlobalDefines.CorrectBordersInTotal(wsh, wsh.Range[RN_QUALIF_2].Offset[1].Cells[1, EXCEL_SUM_COL_OFS], RowsQ, EXCEL_PLACE_COL_NUM, RN_RESULT_GRADE, RN_BALLS_FOR_PLACES); } if (CompRounds.Contains(enRounds.OneEighthFinal)) { GlobalDefines.CorrectBordersInTotal(wsh, wsh.Range[RN_ONE_EIGHTH_FINAL].Offset[1], RowsQ, EXCEL_PLACE_COL_NUM, RN_RESULT_GRADE, RN_BALLS_FOR_PLACES); } if (CompRounds.Contains(enRounds.QuaterFinal)) { GlobalDefines.CorrectBordersInTotal(wsh, wsh.Range[RN_QUATERFINAL].Offset[1], RowsQ, EXCEL_PLACE_COL_NUM, RN_RESULT_GRADE, RN_BALLS_FOR_PLACES); } if (CompRounds.Contains(enRounds.SemiFinal)) { GlobalDefines.CorrectBordersInTotal(wsh, wsh.Range[RN_FINAL].Offset[1], // Здесь должен быть RN_FINAL RowsQ, EXCEL_PLACE_COL_NUM, RN_RESULT_GRADE, RN_BALLS_FOR_PLACES); } } if (!CurTask.m_GroupToExport.Rounds.First(arg => arg.id == enRounds.Total).ExtraOption.Value) { // Удаляем столбец с баллами wsh.Columns[wsh.Range[RN_BALLS_FOR_PLACES].Column].Delete(MSExcel.XlDirection.xlToLeft); } return(true); }