public DailyRevenueSheetModel(string DateCellValue, string DateCellAddress, int RoomCountCellValue, string RoomCountCellAddress, string dateWForwardSlashesStrictFormatted, string SheetFileName = "", string SheetParentFolder = "") { this.DateCellValue = DateCellValue; this.DateCellAddress = DateCellAddress; this.RoomCountCellValue = RoomCountCellValue; this.RoomCountCellAddress = RoomCountCellAddress; this._myMonthAYearGroupUtility = new MyMonthAYearGroupUtility(dateWForwardSlashesStrictFormatted); this.SheetFileName = SheetFileName; this.SheetParentFolder = SheetParentFolder; }
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); } }