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