예제 #1
0
        private static Excel.Range _range; // current range of curent excel

        #endregion Fields

        #region Methods

        public static WorkSchedule GetWorkHours(string filePath, string workerName)
        {
            var xlApp = new Excel.Application();
            var xlWorkBook = xlApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Item[1];

            _range = xlWorkSheet.UsedRange;

            var name = workerName.Trim();
            var counter = 0;

            var workSchedule = new WorkSchedule
            {
                Name = name,
                //StartDateTime = Convert.ToDateTime(GetCellValue(4, 2)),
                //EndDateTime = Convert.ToDateTime(GetCellValue(4, 14))
                StartDateTime = DateTime.ParseExact(GetCellValue(4, 2), "d.M.yyyy", CultureInfo.InvariantCulture),
                EndDateTime = DateTime.ParseExact(GetCellValue(22, 2), "d.M.yyyy", CultureInfo.InvariantCulture)
            };

            for (var i = 1; i <= _range.Rows.Count; i++)
            {
                for (var j = 1; j <= _range.Columns.Count; j++)
                {
                    var cell = GetCellValue(i, j);

                    #region Old Discount
                    //if (cell == name)
                    //{
                    //    counter++;
                    //    string[] start = null, end = null;
                    //    var cellTimes = GetCellValue(i - 1, j);
                    //    if (!string.IsNullOrEmpty(cellTimes) && cellTimes.Contains(":"))
                    //    {
                    //        start = cellTimes.Split(':');
                    //    }

                    //    cellTimes = GetCellValue(i - 1, j + 1);
                    //    if (!string.IsNullOrEmpty(cellTimes) && cellTimes.Contains(":"))
                    //    {
                    //        end = cellTimes.Split(':');
                    //    }

                    //    var date = Convert.ToDateTime(GetCellValue(4, j));
                    //    if (start != null && end != null)
                    //    {
                    //        var reminder = new Reminder
                    //        {
                    //            DayDesc = GetCellValue(3, j),
                    //            Start = date.AddHours(int.Parse(start[0])).AddMinutes(int.Parse(start[1])),
                    //            End = date.AddHours(int.Parse(end[0])).AddMinutes(int.Parse(end[1])),
                    //            JobName = GetCellValue(i - 1, 1),
                    //        };

                    //        // check if end time in next day
                    //        if ((reminder.End - reminder.Start).TotalHours < 0)
                    //        {
                    //            reminder.End = reminder.End.AddDays(1);
                    //        }

                    //        workSchedule.Reminders.Add(reminder);
                    //    }
                    //}
                    #endregion

                    #region CheckPoint
                    if (cell != null && cell.Contains(name))
                    {
                        counter++;
                        int[] start = null, end = null;
                        var cellTimes = GetCellValue(i, 3);
                        if (!string.IsNullOrEmpty(cellTimes))
                        {
                            cellTimes = cellTimes.Trim();

                            if (cellTimes.Equals("בוקר"))
                            {
                                start = new [] {07 ,00};
                                end = new [] {15 ,00};
                            }

                            if(cellTimes.Equals("צהריים"))
                            {
                                start = new[] { 15, 00 };
                                end = new[] { 23, 00 };
                            }

                            if(cellTimes.Equals("לילה"))
                            {
                                start = new[] {23, 00 };
                                end = new[] { 07, 00 };
                            }
                        }
                        var date = TryGetDate(i, 2);
                        if (start.IsAny() && end.IsAny())
                        {
                            var reminder = new Reminder
                            {
                                DayDesc = TryGetDayDesc(i, 2),
                                Start = date.AddHours(start[0]).AddMinutes(start[1]),
                                End = date.AddHours(end[0]).AddMinutes(end[1]),
                                JobName = GetCellValue(2, j)
                            };

                            // check if end time in next day
                            if ((reminder.End - reminder.Start).TotalHours < 0)
                            {
                                reminder.End = reminder.End.AddDays(1);
                            }

                            workSchedule.Reminders.Add(reminder);
                        }
                    }
                    #endregion
                }
            }

            workSchedule.Reminders = workSchedule.Reminders.OrderBy(r => r.Start).ToList();

            //MessageBox.Show(text);

            xlWorkBook.Close(true);
            xlApp.Quit();

            ReleaseObject(xlWorkSheet);
            ReleaseObject(xlWorkBook);
            ReleaseObject(xlApp);

            if (counter > 0)
            {
                var json = JsonConvert.SerializeObject(workSchedule, Formatting.Indented);
                var fileName =
                    $@"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\{
                        workSchedule.StartDateTime.ToString("dd.MM.yy")}-{workSchedule.EndDateTime.ToString("dd.MM.yy")
                        }_{name}.txt";

                if (Utils.IsMishaPc())
                {
                    File.WriteAllText(fileName, json);
                }

                return workSchedule;
            }
            else
            {
                return null;
            }
        }
예제 #2
0
        private void btnScan_Click(object sender, EventArgs e)
        {
            if (FormIsValid())
            {
                if (Utils.IsMishaPc())
                {
                    var filePath = @"C:\Users\Misha\Desktop\04.12.16-10.12.16_קוסטיה.txt";
                    if (File.Exists(filePath))
                    {
                        var json = File.ReadAllText(filePath);
                        _workSchedule = JsonConvert.DeserializeObject<WorkSchedule>(json);
                        PrintSchedule();
                        PrintSummary();
                        btnAddEvents.Enabled = true;
                        return;
                    }
                }
                btnScan.Enabled = false;
                _workSchedule = ExcelApi.GetWorkHours(tbUploadFile.Text, tbWorkerName.Text);

                if (_workSchedule.IsNotEmptyObject())
                {
                    MessageBox.Show(@"Success to analize Work Schedule.\nPlease check it well.");
                    PrintSchedule();
                    PrintSummary();
                    btnAddEvents.Enabled = true;
                    btnScan.Enabled = true;
                }
                else
                {
                    MessageBox.Show(@"Cann't find worker: " + tbWorkerName.Text);
                }
            }
        }