コード例 #1
0
        public async static void PeriodDataExporter(DateTime startDate, DateTime endDate, string path)
        {
            string startDateStr = startDate.ToString("MM_dd_yyyy");
            string endDateStr   = endDate.ToString("MM_dd_yyyy");

            string filePath = path + $"\\{ startDateStr }-{ endDateStr }.xlsx";


            FileInfo newFile = new FileInfo(filePath);

            if (newFile.Exists)
            {
                try
                {
                    newFile.Delete();
                }
                catch
                {
                    MessageBox.Show("Нээлттэй эксэл файлаа хаана уу?");
                    return;
                }
                newFile = new FileInfo(filePath);
            }
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add($"{startDateStr}-{endDateStr}");

                worksheet.Cells[1, 1].Value = "Өдөр";
                worksheet.Cells[1, 2].Value = "Тасаг";
                worksheet.Cells[1, 3].Value = "Нэр";
                worksheet.Cells[1, 4].Value = "Ирсэн цаг";
                worksheet.Cells[1, 5].Value = "Явсан цаг";
                worksheet.Cells[1, 6].Value = "Ажилласан цаг";
                worksheet.Cells[1, 7].Value = "Ажил дээрээ байгаа эсэх";


                var attendanceList = await ApiProcessor.LoadAttendanceSheet();

                int size = attendanceList.Count();

                var branchList = await ApiProcessor.LoadBranches();

                var staffList = await ApiProcessor.LoadStaffs();

                Dictionary <int, string> staffNameMapper     = new Dictionary <int, string>();
                Dictionary <int, string> branchNameMapper    = new Dictionary <int, string>();
                Dictionary <int, double> staffWorkHourMapper = new Dictionary <int, double>();

                int staffSize  = staffList.Count();
                int branchSize = branchList.Count();

                for (int i = 0; i < staffSize; i++)
                {
                    staffNameMapper[staffList[i].id] = staffList[i].fullName;
                }

                for (int i = 0; i < branchSize; i++)
                {
                    branchNameMapper[branchList[i].id] = branchList[i].name;
                }
                int left       = 0;
                int right      = size - 1;
                int startIndex = 0;
                while (left <= right)
                {
                    int mid     = left + (right - left) / 2;
                    var midDate = DateTime.Parse(attendanceList[mid].date);
                    if (midDate.CompareTo(startDate) == 0)
                    {
                        startIndex = mid;
                        break;
                    }
                    if (midDate.CompareTo(startDate) > 0)
                    {
                        right = mid - 1;
                    }
                    else if (midDate.CompareTo(startDate) < 0)
                    {
                        left       = mid + 1;
                        startIndex = left;
                    }
                }
                for (int i = startIndex; i > 0; i--)
                {
                    if (startIndex == size)
                    {
                        break;
                    }
                    var currentDate = DateTime.Parse(attendanceList[i].date);
                    var prevDate    = DateTime.Parse(attendanceList[i - 1].date);
                    if (currentDate.CompareTo(prevDate) > 0)
                    {
                        startIndex = i;
                        break;
                    }

                    if (i == 1)
                    {
                        startIndex = 0;
                    }
                }
                List <AttendanceModel> outList = new List <AttendanceModel>();

                for (int i = startIndex; i < size; i++)
                {
                    outList.Add(attendanceList[i]);
                    DateTime currentDate = DateTime.Parse(attendanceList[i].date);
                    if (currentDate.CompareTo(endDate) > 0)
                    {
                        break;
                    }
                }

                int k = 0;
                for (int i = startIndex; i < size; i++)
                {
                    DateTime currentDate    = DateTime.Parse(attendanceList[i].date);
                    string   currentDateStr = currentDate.ToString();

                    if (currentDate.CompareTo(endDate) > 0)
                    {
                        break;
                    }


                    worksheet.Cells[$"A{ k + 2 }"].Value = attendanceList[i].date;

                    if (branchNameMapper.ContainsKey(attendanceList[i].branch_id))
                    {
                        worksheet.Cells[$"B{ k + 2 }"].Value = branchNameMapper[attendanceList[i].branch_id];
                    }
                    else
                    {
                        worksheet.Cells[$"B{ k + 2 }"].Value = attendanceList[i].branch_id;
                    }

                    if (staffNameMapper.ContainsKey(attendanceList[i].staff_id))
                    {
                        worksheet.Cells[$"C{ k + 2 }"].Value = staffNameMapper[attendanceList[i].staff_id];
                    }
                    else
                    {
                        worksheet.Cells[$"C{ k + 2 }"].Value = attendanceList[i].staff_id;
                    }


                    worksheet.Cells[$"D{ k + 2 }"].Value = attendanceList[i].arriveTime;
                    worksheet.Cells[$"E{ k + 2 }"].Value = attendanceList[i].leaveTime;
                    worksheet.Cells[$"F{ k + 2 }"].Value = attendanceList[i].officeHours;
                    worksheet.Cells[$"G{ k + 2 }"].Value = attendanceList[i].atOffice;

                    if (staffWorkHourMapper.ContainsKey(attendanceList[i].staff_id))
                    {
                        staffWorkHourMapper[attendanceList[i].staff_id] += attendanceList[i].officeHours;
                    }
                    else
                    {
                        staffWorkHourMapper[attendanceList[i].staff_id] = attendanceList[i].officeHours;
                    }

                    k++;
                }

                //Analysis part
                ExcelWorksheet analyze = package.Workbook.Worksheets.Add($"Тайлан");
                analyze.Cells[1, 1].Value = "Тасаг";
                analyze.Cells[1, 2].Value = "Нэр";
                analyze.Cells[1, 3].Value = "Сонгосон хугацаанд ажилласан цаг";

                comparer          cmp     = new comparer();
                List <StaffModel> display = staffList.OrderBy(s => branchNameMapper[s.branch_id]).ThenBy(s => s.firstName).ToList();

                for (int i = 0; i < staffSize; i++)
                {
                    analyze.Cells[$"A{ i + 2 }"].Value = branchNameMapper[display[i].branch_id];
                    analyze.Cells[$"B{ i + 2 }"].Value = staffNameMapper[display[i].id];
                    analyze.Cells[$"C{ i + 2 }"].Value = staffWorkHourMapper[display[i].id];
                }

                package.Save();
                MessageBox.Show("Хөрвүүлж дууслаа!");
                OpenFile(filePath);
            }
        }
コード例 #2
0
        public async static void AllDataExporter(string path)
        {
            string   filePath = path + @"\allTimeAttendance.xlsx";
            FileInfo newFile  = new FileInfo(filePath);

            if (newFile.Exists)
            {
                try
                {
                    newFile.Delete();
                }
                catch
                {
                    MessageBox.Show("Нээлттэй эксэл файлаа хаана уу?");
                    return;
                }
                newFile = new FileInfo(filePath);
            }
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                // adding new worksheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Бүх ирц");

                // adding headers
                worksheet.Cells[1, 1].Value = "Өдөр";
                worksheet.Cells[1, 2].Value = "Тасаг";
                worksheet.Cells[1, 3].Value = "Нэр";
                worksheet.Cells[1, 4].Value = "Ирсэн цаг";
                worksheet.Cells[1, 5].Value = "Явсан цаг";
                worksheet.Cells[1, 6].Value = "Ажилласан цаг";
                worksheet.Cells[1, 7].Value = "Ажил дээрээ байгаа эсэх";


                var attendanceList = await ApiProcessor.LoadAttendanceSheet();

                int size = attendanceList.Count();

                var branchList = await ApiProcessor.LoadBranches();

                var staffList = await ApiProcessor.LoadStaffs();

                Dictionary <int, string> staffNameMapper     = new Dictionary <int, string>();
                Dictionary <int, string> branchNameMapper    = new Dictionary <int, string>();
                Dictionary <int, double> staffWorkHourMapper = new Dictionary <int, double>();

                int staffSize  = staffList.Count();
                int branchSize = branchList.Count();

                for (int i = 0; i < staffSize; i++)
                {
                    staffNameMapper[staffList[i].id] = staffList[i].fullName;
                }

                for (int i = 0; i < branchSize; i++)
                {
                    branchNameMapper[branchList[i].id] = branchList[i].name;
                }

                for (int i = 0; i < size; i++)
                {
                    worksheet.Cells[$"A{ i + 2 }"].Value = attendanceList[i].date;

                    if (branchNameMapper.ContainsKey(attendanceList[i].branch_id))
                    {
                        worksheet.Cells[$"B{ i + 2 }"].Value = branchNameMapper[attendanceList[i].branch_id];
                    }
                    else
                    {
                        worksheet.Cells[$"B{ i + 2 }"].Value = attendanceList[i].branch_id;
                    }

                    if (staffNameMapper.ContainsKey(attendanceList[i].staff_id))
                    {
                        worksheet.Cells[$"C{ i + 2 }"].Value = staffNameMapper[attendanceList[i].staff_id];
                    }
                    else
                    {
                        worksheet.Cells[$"C{ i + 2 }"].Value = attendanceList[i].staff_id;
                    }


                    worksheet.Cells[$"D{ i + 2 }"].Value = attendanceList[i].arriveTime;
                    worksheet.Cells[$"E{ i + 2 }"].Value = attendanceList[i].leaveTime;
                    worksheet.Cells[$"F{ i + 2 }"].Value = attendanceList[i].officeHours;
                    worksheet.Cells[$"G{ i + 2 }"].Value = attendanceList[i].atOffice;

                    if (staffWorkHourMapper.ContainsKey(attendanceList[i].staff_id))
                    {
                        staffWorkHourMapper[attendanceList[i].staff_id] += attendanceList[i].officeHours;
                    }
                    else
                    {
                        staffWorkHourMapper[attendanceList[i].staff_id] = attendanceList[i].officeHours;
                    }
                }
                //Analysis part
                ExcelWorksheet analyze = package.Workbook.Worksheets.Add($"Тайлан");
                analyze.Cells[1, 1].Value = "Тасаг";
                analyze.Cells[1, 2].Value = "Нэр";
                analyze.Cells[1, 3].Value = "Сонгосон хугацаанд ажилласан цаг";

                comparer          cmp     = new comparer();
                List <StaffModel> display = staffList.OrderBy(s => branchNameMapper[s.branch_id]).ThenBy(s => s.firstName).ToList();

                for (int i = 0; i < staffSize; i++)
                {
                    analyze.Cells[$"A{ i + 2 }"].Value = branchNameMapper[display[i].branch_id];
                    analyze.Cells[$"B{ i + 2 }"].Value = staffNameMapper[display[i].id];
                    analyze.Cells[$"C{ i + 2 }"].Value = staffWorkHourMapper[display[i].id];
                }

                package.Save();
                MessageBox.Show("Хөрвүүлж дууслаа!");
                OpenFile(filePath);
            }
        }