Services/ExportService.cs - master - WorkerTime/WorkerTimeDiplom

Services/ExportService.cs
321 строка14 KB
nikit
Логирование, автосохранение, восстановление пароля
05 июн 2026, 19:24
05 июн 2026, 19:24715eb3d
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;

namespace WorkerTime.Services
{
    public class ExportService
    {
        public void ExportToExcel(ReportService.Timesheet timesheet, string filePath,
            string documentNumber, string documentDate, string okpo)
        {
            string templatePath = Path.Combine(AppContext.BaseDirectory, "template.xlsx");

            //Проверка наличия шаблона и установленного Excel
            if (!File.Exists(templatePath))
            {
                throw new Exception("Файл шаблона template.xlsx не найден в папке с программой.");
            }

            try
            {
                var testExcel = new Excel.Application();
                testExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(testExcel);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                throw new Exception("Microsoft Excel не установлен на данном компьютере. Установите Excel для экспорта табеля.");
            }

            File.Copy(templatePath, filePath, true);

            Excel.Application excel = new Excel.Application();
            excel.Visible = false;
            excel.DisplayAlerts = false;

            try
            {
                Excel.Workbook workbook = excel.Workbooks.Open(filePath);
                Excel.Worksheet sheet1 = workbook.Worksheets[1] as Excel.Worksheet;

                //Хардкод реквизиты организации
                sheet1.Range["A8"].Value = timesheet.OrganizationName;
                sheet1.Range["A10"].Value = timesheet.Department;
                sheet1.Range["AD8"].Value = okpo;
                sheet1.Range["W14"].Value = documentNumber;
                sheet1.Range["Y14"].Value = documentDate;
                sheet1.Range["AB14"].Value = "01." + timesheet.Month.ToString("D2") + "." + timesheet.Year.ToString();
                sheet1.Range["AD14"].Value = timesheet.DaysInMonth.ToString("D2") + "." + timesheet.Month.ToString("D2") + "." + timesheet.Year.ToString();

                int row = 24;
                int num = 1;
                int employeesOnSheet1 = Math.Min(timesheet.Rows.Count, 9);

                FillEmployees(sheet1, timesheet, 0, employeesOnSheet1, row, num);

                //Для заполнения второго листа если сотрудников >9
                if (timesheet.Rows.Count > 9)
                {
                    Excel.Worksheet sheet2 = workbook.Worksheets[2] as Excel.Worksheet;
                    int row2 = 10;
                    int num2 = 10;
                    int remaining = timesheet.Rows.Count - 9;

                    FillEmployees(sheet2, timesheet, 9, remaining, row2, num2);
                }

                workbook.Save();
                workbook.Close();
            }
            finally
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            }
        }

        private void FillEmployees(Excel.Worksheet sheet, ReportService.Timesheet t,
            int startIndex, int count, int startRow, int startNum)
        {
            int row = startRow;
            int num = startNum;
            string[] cols = { "", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE" };

            //Коды рабочих дней
            HashSet<string> workCodes = new HashSet<string> { "Я", "ОТ", "ОД", "С", "Н", "РП", "ВМ", "У", "К", "ПК", "ПМ" };

            for (int i = 0; i < count; i++)
            {
                ReportService.TimesheetRow emp = t.Rows[startIndex + i];

                sheet.Range["A" + row.ToString()].Value = num.ToString();
                sheet.Range["B" + row.ToString()].Value = emp.FullName;
                sheet.Range["C" + row.ToString()].Value = emp.PersonnelNumber;

                //Дни 1-15 коды
                for (int d = 0; d < 15 && d < t.DaysInMonth; d++)
                {
                    sheet.Range[cols[d + 4] + row.ToString()].Value = emp.DayCodes[d];
                }

                //Дни 1-15 часы
                for (int d = 0; d < 15 && d < t.DaysInMonth; d++)
                {
                    double hours;
                    if (double.TryParse(emp.DayHours[d], out hours))
                    {
                        sheet.Range[cols[d + 4] + (row + 1).ToString()].Value = hours;
                    }
                    else if (!string.IsNullOrEmpty(emp.DayHours[d]))
                    {
                        sheet.Range[cols[d + 4] + (row + 1).ToString()].Value = emp.DayHours[d];
                    }
                }

                //Дни 16-31 коды
                for (int d = 15; d < 31 && d < t.DaysInMonth; d++)
                {
                    sheet.Range[cols[(d - 15) + 4] + (row + 2).ToString()].Value = emp.DayCodes[d];
                }

                //Дни 16-31 часы
                for (int d = 15; d < 31 && d < t.DaysInMonth; d++)
                {
                    double hours;
                    if (double.TryParse(emp.DayHours[d], out hours))
                    {
                        sheet.Range[cols[(d - 15) + 4] + (row + 3).ToString()].Value = hours;
                    }
                    else if (!string.IsNullOrEmpty(emp.DayHours[d]))
                    {
                        sheet.Range[cols[(d - 15) + 4] + (row + 3).ToString()].Value = emp.DayHours[d];
                    }
                }

                //Итоги дни 1-15
                int daysFirstHalf = 0;
                int hoursFirstHalf = 0;
                for (int d = 0; d < 15 && d < t.DaysInMonth; d++)
                {
                    if (workCodes.Contains(emp.DayCodes[d]))
                    {
                        daysFirstHalf++;
                        int hours;
                        if (!string.IsNullOrEmpty(emp.DayHours[d]) && int.TryParse(emp.DayHours[d], out hours))
                        {
                            hoursFirstHalf = hoursFirstHalf + hours;
                        }
                    }
                }
                sheet.Range["T" + row.ToString()].Value = daysFirstHalf;
                sheet.Range["T" + (row + 1).ToString()].Value = hoursFirstHalf;

                //Итоги дни 16-31
                int daysSecondHalf = 0;
                int hoursSecondHalf = 0;
                for (int d = 15; d < t.DaysInMonth; d++)
                {
                    if (workCodes.Contains(emp.DayCodes[d]))
                    {
                        daysSecondHalf++;
                        int hours;
                        if (!string.IsNullOrEmpty(emp.DayHours[d]) && int.TryParse(emp.DayHours[d], out hours))
                        {
                            hoursSecondHalf = hoursSecondHalf + hours;
                        }
                    }
                }
                sheet.Range["T" + (row + 2).ToString()].Value = daysSecondHalf;
                sheet.Range["T" + (row + 3).ToString()].Value = hoursSecondHalf;

                //Итоги весь месяц (посчитаны в ReportService)
                sheet.Range["U" + row.ToString()].Value = emp.TotalDays;
                sheet.Range["U" + (row + 2).ToString()].Value = emp.TotalHours;

                //Заполнение неявок
                FillAbsences(sheet, row, emp, t.DaysInMonth);

                //Следующий сотрудник
                row = row + 4;
                num++;
            }
        }

        private void FillAbsences(Excel.Worksheet sheet, int row, ReportService.TimesheetRow emp, int daysInMonth)
        {
            // Собираем все неявки: код -> общее количество дней и часов
            Dictionary<string, int> absenceDays = new Dictionary<string, int>();
            Dictionary<string, int> absenceHours = new Dictionary<string, int>();

            for (int d = 0; d < daysInMonth; d++)
            {
                string code = emp.DayCodes[d];
                if (string.IsNullOrEmpty(code)) continue;
                if (code == "Я" || code == "В" || code == "X" || code == "С" || code == "Н" || code == "РП" || code == "ВМ") continue;

                int hours = 0;
                if (!string.IsNullOrEmpty(emp.DayHours[d]))
                {
                    int.TryParse(emp.DayHours[d], out hours);
                }

                if (!absenceDays.ContainsKey(code))
                {
                    absenceDays[code] = 0;
                    absenceHours[code] = 0;
                }

                absenceDays[code] = absenceDays[code] + 1;
                absenceHours[code] = absenceHours[code] + hours;
            }

            // Разделяем неявки на первую и вторую половину месяца
            List<string> firstHalfCodes = new List<string>();
            List<int> firstHalfDays = new List<int>();
            List<int> firstHalfHours = new List<int>();

            List<string> secondHalfCodes = new List<string>();
            List<int> secondHalfDays = new List<int>();
            List<int> secondHalfHours = new List<int>();

            foreach (string code in absenceDays.Keys)
            {
                // Подсчёт для первой половины
                int days15 = 0;
                int hours15 = 0;
                for (int d = 0; d < 15 && d < daysInMonth; d++)
                {
                    if (emp.DayCodes[d] == code)
                    {
                        days15++;
                        int h;
                        if (!string.IsNullOrEmpty(emp.DayHours[d]) && int.TryParse(emp.DayHours[d], out h))
                        {
                            hours15 = hours15 + h;
                        }
                    }
                }

                // Подсчёт для второй половины
                int days31 = 0;
                int hours31 = 0;
                for (int d = 15; d < daysInMonth; d++)
                {
                    if (emp.DayCodes[d] == code)
                    {
                        days31++;
                        int h;
                        if (!string.IsNullOrEmpty(emp.DayHours[d]) && int.TryParse(emp.DayHours[d], out h))
                        {
                            hours31 = hours31 + h;
                        }
                    }
                }

                if (days15 > 0 || hours15 > 0)
                {
                    firstHalfCodes.Add(code);
                    firstHalfDays.Add(days15);
                    firstHalfHours.Add(hours15);
                }
                if (days31 > 0 || hours31 > 0)
                {
                    secondHalfCodes.Add(code);
                    secondHalfDays.Add(days31);
                    secondHalfHours.Add(hours31);
                }
            }

            // Записываем неявки первой половины в ячейки
            // Первая причина: AB24 + AC24
            if (firstHalfCodes.Count > 0)
            {
                sheet.Range["AB" + row.ToString()].Value = firstHalfCodes[0];
                sheet.Range["AC" + row.ToString()].Value = firstHalfDays[0].ToString() + "(" + firstHalfHours[0].ToString() + ")";
            }
            // Вторая причина: AD24 + AE24
            if (firstHalfCodes.Count > 1)
            {
                sheet.Range["AD" + row.ToString()].Value = firstHalfCodes[1];
                sheet.Range["AE" + row.ToString()].Value = firstHalfDays[1].ToString() + "(" + firstHalfHours[1].ToString() + ")";
            }
            // Третья причина: AB25 + AC25
            if (firstHalfCodes.Count > 2)
            {
                sheet.Range["AB" + (row + 1).ToString()].Value = firstHalfCodes[2];
                sheet.Range["AC" + (row + 1).ToString()].Value = firstHalfDays[2].ToString() + "(" + firstHalfHours[2].ToString() + ")";
            }
            // Четвёртая причина: AD25 + AE25
            if (firstHalfCodes.Count > 3)
            {
                sheet.Range["AD" + (row + 1).ToString()].Value = firstHalfCodes[3];
                sheet.Range["AE" + (row + 1).ToString()].Value = firstHalfDays[3].ToString() + "(" + firstHalfHours[3].ToString() + ")";
            }

            // Записываем неявки второй половины
            if (secondHalfCodes.Count > 0)
            {
                sheet.Range["AB" + (row + 2).ToString()].Value = secondHalfCodes[0];
                sheet.Range["AC" + (row + 2).ToString()].Value = secondHalfDays[0].ToString() + "(" + secondHalfHours[0].ToString() + ")";
            }
            if (secondHalfCodes.Count > 1)
            {
                sheet.Range["AD" + (row + 2).ToString()].Value = secondHalfCodes[1];
                sheet.Range["AE" + (row + 2).ToString()].Value = secondHalfDays[1].ToString() + "(" + secondHalfHours[1].ToString() + ")";
            }
            if (secondHalfCodes.Count > 2)
            {
                sheet.Range["AB" + (row + 3).ToString()].Value = secondHalfCodes[2];
                sheet.Range["AC" + (row + 3).ToString()].Value = secondHalfDays[2].ToString() + "(" + secondHalfHours[2].ToString() + ")";
            }
            if (secondHalfCodes.Count > 3)
            {
                sheet.Range["AD" + (row + 3).ToString()].Value = secondHalfCodes[3];
                sheet.Range["AE" + (row + 3).ToString()].Value = secondHalfDays[3].ToString() + "(" + secondHalfHours[3].ToString() + ")";
            }
        }
    }
}