Example #1
0
        public static void ExportToExcel(Core.BusinessTrip.Domain.BusinessTrip businessTrip, Dictionary <string, string> targets)
        {
            if (!GetDataTemplete(businessTrip)) //test vs
            {
                return;
            }

            try
            {
                SetCellValue(businessTrip.HeadStructuralDivision != null ? Regex.Replace(string.Format("{0} {1} {2}",
                                                                                                       HelperReports.TransformationBeginStr(businessTrip.HeadStructuralDivision.Position.Nominative, false),
                                                                                                       !HelperReports.IsPositionHead(businessTrip.HeadStructuralDivision.Position.Nominative) && businessTrip.HeadStructuralDivision.Department != null ?
                                                                                                       HelperReports.GetCategoryGenitiveByDepartmentCategory(businessTrip.HeadStructuralDivision.Department.Category) : "",
                                                                                                       businessTrip.HeadStructuralDivision.Department != null ?
                                                                                                       HelperReports.TransformationBeginStr(businessTrip.HeadStructuralDivision.Department.Nominative) : ""), "[ ]+", " ").TrimEnd(' ') : "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionPositionOne");
                SetCellValue(businessTrip.HeadStructuralDivision != null ? Regex.Replace(string.Format("{0} {1} {2}",
                                                                                                       HelperReports.TransformationBeginStr(businessTrip.HeadStructuralDivision.Position.Nominative, false),
                                                                                                       !HelperReports.IsPositionHead(businessTrip.HeadStructuralDivision.Position.Nominative) && businessTrip.HeadStructuralDivision.Department != null ?
                                                                                                       HelperReports.GetCategoryGenitiveByDepartmentCategory(businessTrip.HeadStructuralDivision.Department.Category) : "",
                                                                                                       businessTrip.HeadStructuralDivision.Department != null ?
                                                                                                       HelperReports.TransformationBeginStr(businessTrip.HeadStructuralDivision.Department.Nominative) : ""), "[ ]+", " ").TrimEnd(' ') : "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionPositionTwo");

                SetCellValue(businessTrip.HeadStructuralDivision != null ? HelperReports.GetNameShort(businessTrip.HeadStructuralDivision.Name) :
                             "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionNameOne");
                SetCellValue(businessTrip.HeadStructuralDivision != null ? HelperReports.GetNameShort(businessTrip.HeadStructuralDivision.Name) :
                             "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionNameTwo");

                if (businessTrip.HeadStructuralDivision == null)
                {
                    SetCellYellowColor("HeadStructuralDivisionNameOne");
                    SetCellYellowColor("HeadStructuralDivisionNameTwo");
                    SetCellYellowColor("HeadStructuralDivisionPositionOne");
                    SetCellYellowColor("HeadStructuralDivisionPositionTwo");
                }

                //SetCellValue(businessTrip.HeadOrganization != null ? HelperReports.TransformationBeginStr(businessTrip.HeadOrganization.Position.Nominative, false) :
                //    "<не задан руководитель организации>", "HeadOrganizationPosition");
                SetCellValue(businessTrip.HeadOrganization != null ? Regex.Replace(string.Format("{0} {1}",
                                                                                                 HelperReports.TransformationBeginStr(businessTrip.HeadOrganization.Position.Nominative, false),
                                                                                                 HelperReports.IsPositionHead(businessTrip.HeadOrganization.Position.Nominative) && businessTrip.HeadOrganization.Department != null ?
                                                                                                 HelperReports.TransformationBeginStr(businessTrip.HeadOrganization.Department.Nominative) : ""), "[ ]+", " ").TrimEnd(' ')
                    : "<не задан руководитель организации>", "HeadOrganizationPosition");
                SetCellValue(businessTrip.HeadOrganization != null ? HelperReports.GetNameShort(businessTrip.HeadOrganization.Name) :
                             "<не задан руководитель организации>", "HeadOrganizationName");

                if (businessTrip.HeadOrganization == null)
                {
                    SetCellYellowColor("HeadOrganizationPosition");
                    SetCellYellowColor("HeadOrganizationName");
                }

                SetCellValue(businessTrip.NumberDocument, "NumberDocument");
                SetCellValue(businessTrip.DateFormulationToString, "DateFormulation");

                SetCellValue(string.Format("Выполнение {0} на об.:", businessTrip.TypeWork != null ? HelperReports.TransformationBeginStr(businessTrip.TypeWork.Name) :
                                           "<не задан вид работ>"), "TypeWork");

                if (businessTrip.TypeWork == null)
                {
                    SetCellYellowColor("TypeWork");
                }

                PartyPersonComparer partyPersonComparer = new PartyPersonComparer();
                var persons = businessTrip.Party != null ? (businessTrip.Party.PartyPersons != null ? businessTrip.Party.PartyPersons.ToList() : new List <PartyPerson>()) : new List <PartyPerson>();
                persons.Sort(partyPersonComparer);

                DirectionComparer directionComparer = new DirectionComparer();
                var directions = businessTrip.Directions != null ? businessTrip.Directions : new List <Direction>();
                directions.Sort(directionComparer);

                //if (persons.Count > 0)
                //{
                var responsiblePerson = persons.FirstOrDefault(p => p.IsResponsible);
                SetCellValue(responsiblePerson != null ? HelperReports.TransformationBeginStr(responsiblePerson.Position, false) :
                             "<не задан ответственный за командировку>", "PersonPosition");
                SetCellValue(responsiblePerson != null ? HelperReports.GetNameShort(responsiblePerson.Name) :
                             "<не задан ответственный за командировку>", "PersonName");

                SetCellValue(responsiblePerson != null ? (responsiblePerson.Person.Department != null ? Regex.Replace(string.Format("{0} {1} {2}",
                                                                                                                                    HelperReports.TransformationBeginStr(responsiblePerson.Person.Department.Category, false),
                                                                                                                                    HelperReports.TransformationBeginStr(responsiblePerson.Person.Department.Nominative),
                                                                                                                                    !string.IsNullOrWhiteSpace(responsiblePerson.Person.Department.Number) ?
                                                                                                                                    string.Format("({0} № {1})", HelperReports.TransformationBeginStr(responsiblePerson.Person.Department.Category),
                                                                                                                                                  responsiblePerson.Person.Department.Number) : ""), "[ ]+", " ").TrimEnd(' ') : "<не задан отдел для ответственного за командировку>") :
                             "<не задан ответственный за командировку>", "Department");

                if (responsiblePerson == null)
                {
                    SetCellYellowColor("PersonPosition");
                    SetCellYellowColor("PersonName");
                    SetCellYellowColor("Department");
                }
                else
                {
                    if (responsiblePerson.Person.Department == null)
                    {
                        SetCellYellowColor("Department");
                    }
                }
                //}



                var saveRowCount = 0;

                if (persons.Count > 0)
                {
                    var countRowInsert = directions.Count != 0 ? persons.Count * directions.Count - 1 : persons.Count - 1;

                    while (countRowInsert != 0)
                    {
                        InsertRow(16);
                        MergeCell(16, 2, 16, 8);
                        SetBorders(16, 2, 16, 8);
                        MergeCell(16, 26, 16, 49);
                        SetBorders(16, 26, 16, 49);
                        MergeCell(16, 50, 16, 63);
                        SetBorders(16, 50, 16, 63);
                        MergeCell(16, 88, 16, 106);
                        SetBorders(16, 88, 16, 106);
                        MergeCell(16, 107, 16, 124);
                        SetBorders(16, 107, 16, 124);
                        MergeCell(16, 126, 16, 136);
                        SetBorders(16, 126, 16, 136);
                        MergeCell(16, 137, 16, 144);
                        SetBorders(16, 137, 16, 144);
                        MergeCell(16, 149, 16, 162);
                        SetBorders(16, 149, 16, 162);

                        countRowInsert--;
                    }


                    var rowCount = directions.Count != 0 ? persons.Count * directions.Count : persons.Count;
                    saveRowCount = rowCount;

                    var numPerson = 0;

                    if (directions.Count > 0)
                    {
                        for (int i = 0; i < rowCount; i = i + directions.Count)
                        {
                            if (numPerson < persons.Count)
                            {
                                SetCellValue((numPerson + 1).ToString(), i + 15, 2);
                                SetCellValue(persons[numPerson].PersonnelNumber, i + 15, 25);
                                SetCellValue(HelperReports.GetNameShort(persons[numPerson].Name), i + 15, 26);
                                SetCellValue(HelperReports.TransformationBeginStr(persons[numPerson].Position, false), i + 15, 50);

                                numPerson++;
                            }

                            if (directions.Count > 0)
                            {
                                MergeCell(i + 15, 2, i + 15 + directions.Count - 1, 2);
                                SetBorders(i + 15, 2, i + 15 + directions.Count - 1, 2);
                                MergeCell(i + 15, 25, i + 15 + directions.Count - 1, 25);
                                SetBorders(i + 15, 25, i + 15 + directions.Count - 1, 25);
                                MergeCell(i + 15, 26, i + 15 + directions.Count - 1, 26);
                                SetBorders(i + 15, 26, i + 15 + directions.Count - 1, 26);
                                MergeCell(i + 15, 50, i + 15 + directions.Count - 1, 50);
                                SetBorders(i + 15, 50, i + 15 + directions.Count - 1, 50);

                                var numDirections = 0;

                                for (int j = i; j < directions.Count + i; j++)
                                {
                                    if (numDirections < directions.Count)
                                    {
                                        SetCellValue(directions[numDirections].Location != null ? directions[numDirections].Location.ShortAddress : "", i + numDirections + 15, 88);
                                        SetCellValue(directions[numDirections].Organization != null ? directions[numDirections].Organization.ShortName : "", i + numDirections + 15, 107);
                                        SetCellValue(directions[numDirections].DateBeginAsString, i + numDirections + 15, 126);
                                        SetCellValue(directions[numDirections].DateEndAsString, i + numDirections + 15, 137);
                                        if (directions[numDirections].DateBegin != null && directions[numDirections].DateEnd != null)
                                        {
                                            var countEqualDirections = directions.Count(d => d.DateBeginAsString == directions[numDirections].DateBeginAsString && d.DateEndAsString == directions[numDirections].DateEndAsString);
                                            if (countEqualDirections > 1)
                                            {
                                                SetCellValue(Math.Round((double)1 / countEqualDirections, 2).ToString(), i + numDirections + 15, 148);
                                            }
                                            else
                                            {
                                                SetCellValue(Math.Round((directions[numDirections].DateEnd.Value - directions[numDirections].DateBegin.Value).TotalDays + 1).ToString(), i + numDirections + 15, 148);
                                            }
                                        }
                                        SetCellValue(string.Format("Об. {0}", directions[numDirections].Reason), i + numDirections + 15, 149);

                                        numDirections++;
                                    }
                                }
                            }
                        }
                    }
                    else
                    {
                        for (int i = 0; i < persons.Count; i = i + 1)
                        {
                            if (numPerson < persons.Count)
                            {
                                SetCellValue((numPerson + 1).ToString(), i + 15, 2);
                                SetCellValue(persons[i].PersonnelNumber, i + 15, 25);
                                SetCellValue(HelperReports.GetNameShort(persons[i].Name), i + 15, 26);
                                SetCellValue(HelperReports.TransformationBeginStr(persons[i].Position, false), i + 15, 50);

                                numPerson++;
                            }
                        }
                    }
                }

                if (targets != null)
                {
                    if (targets.Count != 0)
                    {
                        if (targets.Count > 3)
                        {
                            var rowCount = targets.Count;

                            while (rowCount != 0)
                            {
                                InsertRow(saveRowCount - 1 + 19);
                                MergeCell(saveRowCount - 1 + 19, 2, saveRowCount - 1 + 19, 124);
                                SetBorders(saveRowCount - 1 + 19, 2, saveRowCount - 1 + 19, 124);
                                MergeCell(saveRowCount - 1 + 19, 126, saveRowCount - 1 + 19, 162);
                                SetBorders(saveRowCount - 1 + 19, 126, saveRowCount - 1 + 19, 162);

                                rowCount--;
                            }
                        }

                        for (int i = 0; i < directions.Count; i++)
                        {
                            if (targets.Keys.Any(d => d == directions[i].Reason))
                            {
                                SetCellValue(targets[directions[i].Reason], i + saveRowCount - 1 + 18, 2);
                            }


                            //_workSheet.Range[_workSheet.Cells[i + saveRowCount - 1 + 18, 2], _workSheet.Cells[i + saveRowCount - 1 + 18, 124]].NamedRange.AutoFit();

                            //_workSheet.Cells[i + saveRowCount - 1 + 18, 2].NamedRange.AutoFit();

                            //_workSheet.Cells[i + saveRowCount - 1 + 18, 2].EntireRow.AutoFit();

                            //double CellHeight = _workSheet.Cells[i + saveRowCount - 1 + 18, 2].RowHeight;//узнать высоту
                            ////objSheet.get_Range(SecondCell, SecondCell).Value2 = ""; //очистить ячейку

                            //_workSheet.Cells[i + saveRowCount - 1 + 18, 2].RowHeight = CellHeight;
                            ////SetAutoFit(i + saveRowCount - 1 + 18, 2, i + saveRowCount - 1 + 18, 124);
                        }
                    }
                }
                Visible = true;
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} | {1} | {2}", DirectoryName, ex.Message, ex.InnerException != null ? (ex.InnerException.InnerException != null ? ex.InnerException.InnerException.Message : "") : ""));
                MessageBox.Show("Ошибка при экспорте служебного задания.", DirectoryName, MessageBoxButtons.OK, MessageBoxIcon.Error);

                Close();
            }
        }
        public static void ExportToExcel(Core.BusinessTrip.Domain.BusinessTrip businessTrip)
        {
            if (!GetDataTemplete(businessTrip))
            {
                return;
            }

            try
            {
                SetCellValue(businessTrip.HeadStructuralDivision != null ? Regex.Replace(string.Format("от {0} {1} {2}",
                                                                                                       HelperReports.TransformationBeginStr(businessTrip.HeadStructuralDivision.Position.Genitive),
                                                                                                       !HelperReports.IsPositionHead(businessTrip.HeadStructuralDivision.Position.Nominative) && businessTrip.HeadStructuralDivision.Department != null ?
                                                                                                       HelperReports.GetCategoryGenitiveByDepartmentCategory(businessTrip.HeadStructuralDivision.Department.Category) : "",
                                                                                                       string.Format("№ {0}", !string.IsNullOrWhiteSpace(businessTrip.HeadStructuralDivision.Department.Number) ?
                                                                                                                     businessTrip.HeadStructuralDivision.Department.Number : "<не задан номер отдела>")), "[ ]+", " ").TrimEnd(' ') :
                             "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionPosition");
                SetCellValue(businessTrip.HeadStructuralDivision != null ? HelperReports.GetNameGenitiveByName(businessTrip.HeadStructuralDivision.Name) :
                             "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionNameOne");
                SetCellValue(businessTrip.HeadStructuralDivision != null ? HelperReports.GetNameShort(businessTrip.HeadStructuralDivision.Name) :
                             "<не задан руководитель структурного подразделения>", "HeadStructuralDivisionName");

                if (businessTrip.HeadStructuralDivision == null)
                {
                    SetCellYellowColor("HeadStructuralDivisionPosition");
                    SetCellYellowColor("HeadStructuralDivisionNameOne");
                    SetCellYellowColor("HeadStructuralDivisionName");
                }
                else
                {
                    if (businessTrip.HeadStructuralDivision.Department != null)
                    {
                        if (string.IsNullOrWhiteSpace(businessTrip.HeadStructuralDivision.Department.Number))
                        {
                            SetCellYellowColor("HeadStructuralDivisionPosition");
                        }
                    }
                }

                SetCellValue(businessTrip.RequestTransport.DateFormulation != null ? businessTrip.RequestTransport.DateFormulation.Value.ToShortDateString() :
                             "<не задана дата формирования заявки на транспорт>", "DateFormulation");

                if (businessTrip.RequestTransport.Date == null)
                {
                    SetCellYellowColor("DateFormulation");
                }

                SetCellValue(businessTrip.HeadOrganization != null ? Regex.Replace(string.Format("{0} {1}",
                                                                                                 HelperReports.TransformationBeginStr(businessTrip.HeadOrganization.Position.Nominative, false),
                                                                                                 HelperReports.IsPositionHead(businessTrip.HeadOrganization.Position.Nominative) && businessTrip.HeadOrganization.Department != null ?
                                                                                                 HelperReports.TransformationBeginStr(businessTrip.HeadOrganization.Department.Nominative) : ""), "[ ]+", " ").TrimEnd(' ') :
                             "<не задан руководитель организации>", "HeadOrganizationPosition");
                SetCellValue(businessTrip.HeadOrganization != null ? HelperReports.GetNameShort(businessTrip.HeadOrganization.Name) :
                             "<не задан руководитель организации>", "HeadOrganizationName");

                if (businessTrip.HeadOrganization == null)
                {
                    SetCellYellowColor("HeadOrganizationPosition");
                    SetCellYellowColor("HeadOrganizationName");
                }

                SetCellValue(businessTrip.RequestTransport.ProjectManager != null ? HelperReports.TransformationBeginStr(businessTrip.RequestTransport.ProjectManager.Position.Nominative, false) :
                             "<не задан сотрудник отдела управления проектами>", "ProjectManagerPosition");

                SetCellValue(businessTrip.RequestTransport.ProjectManager != null ? HelperReports.GetNameShort(businessTrip.RequestTransport.ProjectManager.Name) :
                             "<не задан сотрудник отдела управления проектами>", "ProjectManagerName");

                if (businessTrip.RequestTransport.ProjectManager == null)
                {
                    SetCellYellowColor("ProjectManagerPosition");
                    SetCellYellowColor("ProjectManagerName");
                }

                SetCellValue(businessTrip.RequestTransport.Date != null ? businessTrip.RequestTransport.Date.Value.ToShortDateString() : "<не задана дата выдачи транспорта>", "Date");

                if (businessTrip.RequestTransport.Date == null)
                {
                    SetCellYellowColor("Date");
                }

                SetCellValue(businessTrip.RequestTransport.TimeHour != null ? businessTrip.RequestTransport.TimeHour.ToString() : "<не задано время>", "TimeHour");

                if (businessTrip.RequestTransport.TimeHour == null)
                {
                    SetCellYellowColor("TimeHour");
                }

                SetCellValue(businessTrip.RequestTransport.Address != null ? businessTrip.RequestTransport.Address.ShortAddress : "<не задан адрес>", "Address");

                if (businessTrip.RequestTransport.Address == null)
                {
                    SetCellYellowColor("Address");
                }

                PartyPersonComparer partyPersonComparer = new PartyPersonComparer();
                var persons = businessTrip.Party != null ? (businessTrip.Party.PartyPersons != null ? businessTrip.Party.PartyPersons.ToList() : new List <PartyPerson>()) : new List <PartyPerson>();
                persons.Sort(partyPersonComparer);

                DirectionComparer directionComparer = new DirectionComparer();
                var directions = businessTrip.Directions != null ? businessTrip.Directions : new List <Direction>();
                directions.Sort(directionComparer);

                var responsiblePerson = persons.FirstOrDefault(p => p.IsResponsible);
                SetCellValue(responsiblePerson != null ? HelperReports.GetNameShort(responsiblePerson.Name) :
                             "<не задан ответственный за командировку>", "ContactName");
                SetCellValue(responsiblePerson != null ? (!string.IsNullOrWhiteSpace(responsiblePerson.Person.PhoneNumber) ? responsiblePerson.Person.PhoneNumber : "<не задан телефон ответственного за командировку>") :
                             "<не задан ответственный за командировку>", "ContactPhone");

                if (responsiblePerson == null)
                {
                    SetCellYellowColor("ContactName");
                    SetCellYellowColor("ContactPhone");
                }
                else
                {
                    if (string.IsNullOrWhiteSpace(responsiblePerson.Person.PhoneNumber))
                    {
                        SetCellYellowColor("ContactPhone");
                    }
                }

                if (directions.Count > 0)
                {
                    var countRowInsert = directions.Count != 0 ? directions.Count - 1 : 0;

                    while (countRowInsert != 0)
                    {
                        InsertRow(19);
                        countRowInsert--;
                    }

                    MergeCell(18, 1, 18 + directions.Count - 1, 1);
                    MergeCell(18, 6, 18 + directions.Count - 1, 6);
                    MergeCell(18, 7, 18 + directions.Count - 1, 7);
                    MergeCell(18, 8, 18 + directions.Count - 1, 8);
                    MergeCell(18, 9, 18 + directions.Count - 1, 9);
                    MergeCell(18, 10, 18 + directions.Count - 1, 10);

                    SetBorders(18, 1, 18 + directions.Count - 1, 11);

                    SetCellValue(businessTrip.RequestTransport.Transport != null ? HelperReports.TransformationBeginStr(businessTrip.RequestTransport.Transport.Mark, false) : "", 18, 1);
                    SetCellValue(string.Format("Обеспечение {0} работ", businessTrip.TypeWork != null ? HelperReports.TransformationBeginStr(businessTrip.TypeWork.Name) : ""), 18, 6);
                    SetCellValue(businessTrip.RequestTransport.Mileage != null ? businessTrip.RequestTransport.Mileage.Value.ToString() : "", 18, 7);
                    SetCellValue(businessTrip.RequestTransport.TimeWork != null ? businessTrip.RequestTransport.TimeWork.Value.ToString() : "", 18, 8);
                    SetCellValue(persons.Count.ToString(), 18, 9);
                    SetCellValue(persons.Count != 0 ? HelperReports.GetNameShort(persons.FirstOrDefault().Name) : "", 18, 10);

                    for (int i = 0; i < directions.Count; i++)
                    {
                        SetCellValue(directions[i].DateBeginAsString, i + 18, 2);
                        SetCellValue(directions[i].DateEndAsString, i + 18, 3);
                        SetCellValue(directions[i].Location != null ? directions[i].Location.ShortAddress : "", i + 18, 4);
                        SetCellValue(directions[i].Organization != null ? directions[i].Organization.ShortName : "", i + 18, 5);
                        SetCellValue(string.Format("Об. {0}", directions[i].Reason), i + 18, 11);
                    }
                }
                Visible = true;
            }
            catch (Exception ex)
            {
                _logger.Error(string.Format("{0} | {1} | {2}", DirectoryName, ex.Message, ex.InnerException != null ? (ex.InnerException.InnerException != null ? ex.InnerException.InnerException.Message : "") : ""));
                MessageBox.Show("Ошибка при экспорте служебного задания.", DirectoryName, MessageBoxButtons.OK, MessageBoxIcon.Error);

                Close();
            }
        }