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); }
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); }
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); }
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); } }