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