コード例 #1
0
        async Task <List <DailyRevenueSheetModel> > ReadAndCalculateDataFromExcelFiles(List <string> _excelFiles)
        {
            var _timer = new MySimpleDurationTimer();

            if (_excelFiles == null || _excelFiles.Count <= 0)
            {
                SetDebugMessage("No Excel Files To Calculate...");
                return(null);
            }

            List <DailyRevenueSheetModel> _revenueSheets = new List <DailyRevenueSheetModel>();

            foreach (var _excelFile in _excelFiles)
            {
                var _revenueSheet = await FindDataFromExcelFile(_excelFile);

                if (_revenueSheet != null)
                {
                    _revenueSheets.Add(_revenueSheet);
                }
                else
                {
                    SetDebugMessage($"Couldn't Add Revenue Sheet {_excelFile}.", false);
                }
            }

            SetRevenueFilesRead(_revenueSheets.Count);
            SetDebugMessage("ReadAndCalculateDataFromExcelFiles Duration:" + _timer.StopWithDuration(), false);
            return(_revenueSheets);
        }
コード例 #2
0
        async Task <List <string> > ReadAllFiles(string _dir, Func <string, bool> _filePathCondition = null)
        {
            var _timer = new MySimpleDurationTimer();

            if (bReadingFiles)
            {
                SetDebugMessage("Cannot Read Files Until Current Folder is Read");
                return(null);
            }
            bReadingFiles = true;

            myReader = new MyFileFinder();
            myReader.OnShowErrorMessage += OnShowErrorMessageFromReaderHandler;

            if (_filePathCondition == null)
            {
                _filePathCondition = new Func <string, bool>(
                    (_fPath) =>
                {
                    FileInfo _info = new FileInfo(_fPath);
                    if (!_info.Exists)
                    {
                        SetDebugMessage($"File {_fPath} cannot be found - file doesn't exist");
                        return(false);
                    }

                    return(_info.Extension == ".xlsx");
                });
            }

            InvokeTimer.CancelInvoke();
            InvokeTimer.InvokeRepeating(100f, UpdateFileStatTextsHandler, TaskScheduler.FromCurrentSynchronizationContext());

            string[] _filesArray = await myReader.GetReadFromDirTask(_dir, _filePathCondition);

            bReadingFiles = false;
            myReader.OnShowErrorMessage -= OnShowErrorMessageFromReaderHandler;

            UpdateFileStatTextsFromReader();

            InvokeTimer.CancelInvoke();
            SetDebugMessage("ReadAllFiles Duration:" + _timer.StopWithDuration(), false);
            return(_filesArray != null?_filesArray.ToList() : null);
        }
コード例 #3
0
        async Task <Dictionary <string, List <DailyRevenueSheetModel> > > PutRevModelsIntoMonthAYearGroups(List <DailyRevenueSheetModel> _revenueSheets)
        {
            var _timer = new MySimpleDurationTimer();
            var _yearAMonthRevGroups = new Dictionary <string, List <DailyRevenueSheetModel> >();

            var _organizedRevenueSheets = from _sheet in _revenueSheets
                                          orderby _sheet.DateByDay ascending
                                          orderby _sheet.DateByMonth descending
                                          orderby _sheet.DateByYear descending
                                          select _sheet;

            //Iterate Over Every Sheet, Creating New MonthAYear Groups As Needed
            foreach (var _revenueSheet in _organizedRevenueSheets)
            {
                string _revenueKey = $"{_revenueSheet.DateByMonth}-{_revenueSheet.DateByYear}";
                if (_yearAMonthRevGroups.ContainsKey(_revenueKey))
                {
                    //Contains Key, Add Rev Model To The List
                    var _revSheetGroupByKey = _yearAMonthRevGroups[_revenueKey];
                    if (_revSheetGroupByKey != null)
                    {
                        _revSheetGroupByKey.Add(_revenueSheet);
                    }
                }
                else
                {
                    //Doesn't Contain Key, Create New List W/Model And Add To Dictionary
                    var _newRevenueGroupFromKey = new List <DailyRevenueSheetModel>();
                    _newRevenueGroupFromKey.Add(_revenueSheet);
                    _yearAMonthRevGroups.Add(_revenueKey, _newRevenueGroupFromKey);
                }
            }
            SetDebugMessage($"Organized Sheet Count: {_organizedRevenueSheets.Count()}", false);
            SetDebugMessage("PutRevModelsIntoMonthAYearGroups Duration:" + _timer.StopWithDuration(), false);
            SetDebugMessage($"Rev MonthAYear Groups Created: {_yearAMonthRevGroups.Count}", false);
            return(_yearAMonthRevGroups);
        }
コード例 #4
0
        async Task WriteModelsToOutputSheet(Dictionary <string, List <DailyRevenueSheetModel> > _modelsGroupedIntoMonthAYear, FileInfo _outputSheetInfo, int _revModelCount)
        {
            try
            {
                var _timer     = new MySimpleDurationTimer();
                var _colorUtil = new SimpleColorUtility();
                int _revModelPlusGroupsCount =
                    _revModelCount + _modelsGroupedIntoMonthAYear.Count + 2;
                using (var _package = new ExcelPackage(_outputSheetInfo))
                {
                    var firstSheet = _package.Workbook.Worksheets.First();
                    if (firstSheet != null)
                    {
                        //Clear And Insert Rows As Needed
                        firstSheet.Cells.Clear();
                        int _insertRowIndexRef = 2;
                        if (firstSheet.Cells.Rows < _revModelPlusGroupsCount + _insertRowIndexRef)
                        {
                            SetDebugMessage("Inserting Rows into output sheet...", false);
                            firstSheet.InsertRow(_insertRowIndexRef, _revModelPlusGroupsCount);
                        }

                        //Add Headers
                        firstSheet.Cells[1, 1].Value                     = "Date";
                        firstSheet.Cells[1, 1].Style.Font.Italic         = true;
                        firstSheet.Cells[1, 1].Style.Font.Size           = 14.0f;
                        firstSheet.Cells[1, 1].Style.Font.UnderLine      = true;
                        firstSheet.Cells[1, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        firstSheet.Cells[1, 2].Value                     = "Room Count";
                        firstSheet.Cells[1, 2].Style.Font.Italic         = true;
                        firstSheet.Cells[1, 2].Style.Font.Size           = 14.0f;
                        firstSheet.Cells[1, 2].Style.Font.UnderLine      = true;
                        firstSheet.Cells[1, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        //Iterate Through Groups And Write To Sheet
                        int _myI = 2;
                        foreach (var(_revGroupKey, _revenueSheets) in _modelsGroupedIntoMonthAYear)
                        {
                            //Only Add Space After The First Group Finishes
                            if (_myI > 2)
                            {
                                //Add Empty Space After Every Group
                                firstSheet.Cells[_myI, 1, _myI, 8].Clear();
                                //Iterate At The Beginning of Each Group After Adding Space
                                _myI++;
                            }
                            //Set Random Color And Beginning Iterative Count
                            int _beginningRange = _myI;
                            var _ramColor       = _colorUtil.GetRandomColor();
                            //Monthly Room Count And DateByMonthAYear
                            int          _monthlyRoomCount = 0;
                            EDateByMonth _myDateByMonth    = EDateByMonth.Undecided;
                            EDateByYear  _myDateByYear     = EDateByYear.Undecided;
                            //Used To Add , Separator To Average Monthly Count Formula
                            List <string> _monthlyCountStrList = new List <string>();
                            //Iterate Through Revenue Sheets
                            foreach (var _revenueModel in _revenueSheets)
                            {
                                //Add Room Count Value To Monthly Average String List For Formula
                                _monthlyCountStrList.Add(_revenueModel.RoomCountCellValue.ToString());
                                //Figure Out Which MonthAYear We're On If Undecided
                                if (_myDateByMonth == EDateByMonth.Undecided)
                                {
                                    _myDateByMonth = _revenueModel.DateByMonth;
                                }
                                if (_myDateByYear == EDateByYear.Undecided)
                                {
                                    _myDateByYear = _revenueModel.DateByYear;
                                }
                                //Add All Room Counts To Monthly Room Count
                                _monthlyRoomCount += _revenueModel.RoomCountCellValue;
                                //Date Cell
                                firstSheet.Cells[_myI, 1].Value = _revenueModel.DateCellValue;
                                firstSheet.Cells[_myI, 1].Style.Font.UnderLine      = true;
                                firstSheet.Cells[_myI, 1].Style.Font.Bold           = true;
                                firstSheet.Cells[_myI, 1].Style.Font.Size           = 16.0f;
                                firstSheet.Cells[_myI, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                //Room Count Cell
                                firstSheet.Cells[_myI, 2].Value = _revenueModel.RoomCountCellValue;
                                firstSheet.Cells[_myI, 2].Style.Font.UnderLine      = true;
                                firstSheet.Cells[_myI, 2].Style.Font.Bold           = true;
                                firstSheet.Cells[_myI, 2].Style.Font.Size           = 18.0f;
                                firstSheet.Cells[_myI, 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                //Debugging Month, Year and Date Grouping
                                firstSheet.Cells[_myI, 3].Value                     = _revenueModel.DateByMonth.ToString();
                                firstSheet.Cells[_myI, 3].Style.Font.Size           = 11.0f;
                                firstSheet.Cells[_myI, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                firstSheet.Cells[_myI, 4].Value                     = _revenueModel.DateByYear.ToString();
                                firstSheet.Cells[_myI, 4].Style.Font.Size           = 11.0f;
                                firstSheet.Cells[_myI, 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                firstSheet.Cells[_myI, 5].Value                     = _revenueModel.DateByDay;
                                firstSheet.Cells[_myI, 5].Style.Font.Size           = 11.0f;
                                firstSheet.Cells[_myI, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                //Also Debugging FileName And Parent Directory
                                firstSheet.Cells[_myI, 6].Value                     = _revenueModel.SheetFileName;
                                firstSheet.Cells[_myI, 6].Style.Font.Size           = 11.0f;
                                firstSheet.Cells[_myI, 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                firstSheet.Cells[_myI, 7].Value                     = _revenueModel.SheetParentFolder;
                                firstSheet.Cells[_myI, 7].Style.Font.Size           = 11.0f;
                                firstSheet.Cells[_myI, 7].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                //Iterate After Each Model
                                _myI++;
                            }
                            //Only Show Monthly Room Count if it's Greater than 0
                            //And There's More Than 5 Revenue Sheets In The Monthly Group
                            if (_monthlyRoomCount > 0 && _revenueSheets.Count > 6)
                            {
                                //Only Add Monthly Average If String List Has The Counts
                                if (_monthlyCountStrList.Count > 0)
                                {
                                    //Create Average Formula By Concatting Formula With Room Counts And Close For Valid Entry
                                    string _monthlyAverageFormula = string.Concat("=AVERAGE(",
                                                                                  string.Join(",", _monthlyCountStrList), ")");
                                    //Monthly Average Room Count Header
                                    firstSheet.Cells[_myI - 5, 8].Value = "Monthly Average Count";
                                    firstSheet.Cells[_myI - 5, 8].Style.Font.UnderLine      = true;
                                    firstSheet.Cells[_myI - 5, 8].Style.Font.Italic         = true;
                                    firstSheet.Cells[_myI - 5, 8].Style.Font.Size           = 14.0f;
                                    firstSheet.Cells[_myI - 5, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                    //Monthly Average Room Count
                                    firstSheet.Cells[_myI - 4, 8].Formula = _monthlyAverageFormula;
                                    firstSheet.Cells[_myI - 4, 8].Style.Font.UnderLine      = true;
                                    firstSheet.Cells[_myI - 4, 8].Style.Font.Bold           = true;
                                    firstSheet.Cells[_myI - 4, 8].Style.Font.Size           = 18.0f;
                                    firstSheet.Cells[_myI - 4, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                }
                                //If Month Is Missing Days, Then Add Missing Notifier.
                                if (MyMonthAYearGroupUtility.IsMonthMissingDays(_myDateByMonth, _myDateByYear, _revenueSheets.Count))
                                {
                                    firstSheet.Cells[_myI - 3, 8].Value = "Month Missing Days.";
                                    firstSheet.Cells[_myI - 3, 8].Style.Font.UnderLine      = true;
                                    firstSheet.Cells[_myI - 3, 8].Style.Font.Italic         = true;
                                    firstSheet.Cells[_myI - 3, 8].Style.Font.Size           = 14.0f;
                                    firstSheet.Cells[_myI - 3, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                }
                                //Monthly Count Header On Row-Day Before Last, And Before Iteration
                                firstSheet.Cells[_myI - 2, 8].Value = "Monthly Count";
                                firstSheet.Cells[_myI - 2, 8].Style.Font.UnderLine      = true;
                                firstSheet.Cells[_myI - 2, 8].Style.Font.Italic         = true;
                                firstSheet.Cells[_myI - 2, 8].Style.Font.Size           = 14.0f;
                                firstSheet.Cells[_myI - 2, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                                //Show Monthly Room Count on Last Row-Day Before Iteration
                                firstSheet.Cells[_myI - 1, 8].Value = _monthlyRoomCount;
                                firstSheet.Cells[_myI - 1, 8].Style.Font.UnderLine      = true;
                                firstSheet.Cells[_myI - 1, 8].Style.Font.Bold           = true;
                                firstSheet.Cells[_myI - 1, 8].Style.Font.Size           = 18.0f;
                                firstSheet.Cells[_myI - 1, 8].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                            }
                            //Set Ending Iterative Count And Fill Background W/Random Color
                            int _endRange = _myI;
                            firstSheet.Cells[_beginningRange, 1, _endRange, 8].Style.Fill.SetBackground(_ramColor);
                        }
                        //AutoFit Columns And Save To Sheet
                        firstSheet.Column(1).AutoFit();
                        firstSheet.Column(2).AutoFit();
                        firstSheet.Column(3).AutoFit();
                        firstSheet.Column(4).AutoFit();
                        firstSheet.Column(5).AutoFit();
                        firstSheet.Column(6).AutoFit();
                        firstSheet.Column(7).AutoFit();
                        firstSheet.Column(8).AutoFit();
                        _package.SaveAs(_outputSheetInfo);
                    }
                }

                SetDebugMessage("WriteModelsToOutputSheet Duration:" + _timer.StopWithDuration(), false);
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR: " + ex.Message);
                SetDebugMessage("ERROR: " + ex.Message);
            }
        }