private async Task <string> GetPhoneTimeCellFill(IXLWorksheet worksheet, ExcelCell excelCell)
{
IXLRow sheetRow = await Task.Run(() => worksheet.Row(excelCell.Row));
IXLCell cell = await Task.Run(() => sheetRow.Cell(XLHelper.GetColumnNumberFromLetter(excelCell.Column)));
return(cell.Style.Fill.ToString());
}
public async Task <List <string> > GetNamesAsync(string excelPath, ExcelCell groupByNameCell)
{
List <string> managerNames = new List <string>();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - Creating a new file stream to extract names from source Excel at { excelPath }");
}
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XLWorkbook excel = new XLWorkbook(fs);
int workSheetCount = excel.Worksheets.Count;
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - workSheetCount = { workSheetCount }");
}
IXLWorksheet worksheet = await Task.Run(() => excel.Worksheet(workSheetCount));
if (_log.IsDebugEnabled)
{
_log.Debug($"Created Worksheet");
}
string nameColumnHeader = worksheet.Row(groupByNameCell.Row)
.Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetNamesAsync - nameColumnHeader = { nameColumnHeader }");
}
IXLRows rows = await Task.Run(() => worksheet.RowsUsed());
foreach (IXLRow row in rows)
{
string cellValue = row.Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString().Trim();
if (!(string.IsNullOrEmpty(cellValue) || cellValue == nameColumnHeader))
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetManagerNamesAsync - Adding { cellValue } to manager list");
}
managerNames.Add(cellValue);
}
}
}
return(await Task.Run(() => managerNames.Distinct().ToList()));
}
public async Task <string> GetGroupByNameAsync(string excelPath, ExcelCell groupByNameCell)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetGroupByNameAsync - Creating a new file stream to extract names from source Excel at { excelPath }");
}
string groupName;
using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XLWorkbook excel = new XLWorkbook(fs);
int workSheetCount = excel.Worksheets.Count;
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetGroupByNameAsync - workSheetCount = { workSheetCount }");
}
IXLWorksheet worksheet = excel.Worksheet(workSheetCount);
groupName = worksheet.Row(groupByNameCell.Row).Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString();
}
return(groupName);
}
public async Task <List <AgentStartStops> > GetAgentStartStopListAsync(string excelPath, string columnName, string agentNameColumn, string twelveAmColumn, ExcelCell groupByNameCell, ExcelCell phoneColorKeyCell)
{
List <AgentStartStops> startStopList = new List <AgentStartStops>();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopListAsync - Creating Filestream for working excel at {excelPath}");
}
using (FileStream stream = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XLWorkbook excel = new XLWorkbook(stream);
int workSheetCount = excel.Worksheets.Count;
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopListAsync - workSheetCount = { workSheetCount }");
}
IXLWorksheet lastWorksheet = await Task.Run(() => excel.Worksheet(workSheetCount));
List <int> teamRows = await GetTeamRowsAsync(lastWorksheet, columnName, groupByNameCell);
await SetWorksheetDateAsync(lastWorksheet);
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopListAsync - WorksheetDay = {WorksheetDay.ToShortDateString()}");
}
List <Task <AgentStartStops> > tasks = new List <Task <AgentStartStops> >();
foreach (int row in teamRows)
{
tasks.Add(GetAgentStartStopFromRowAsync(lastWorksheet, row, agentNameColumn, twelveAmColumn, phoneColorKeyCell));
}
AgentStartStops[] results = await Task.WhenAll(tasks);
foreach (AgentStartStops result in results)
{
startStopList.Add(result);
}
}
return(startStopList);
}
private async Task <AgentStartStops> GetAgentStartStopFromRowAsync(IXLWorksheet worksheet, int rowNumber, string agentNameColumn, string twelveAmColumn, ExcelCell phoneColorKeyCell)
{
AgentStartStops agentStartStop = new AgentStartStops();
List <int> phoneTimeColumns = new List <int>();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Creating row object from worksheet and rowNumber { rowNumber }");
}
IXLRow row = await Task.Run(() => worksheet.Row(rowNumber));
agentStartStop.AgentName = row.Cell(XLHelper.GetColumnNumberFromLetter(agentNameColumn)).Value.ToString();
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Setting AgentName = { agentStartStop.AgentName }");
}
int twelveAmColumnInt = XLHelper.GetColumnNumberFromLetter(twelveAmColumn);
for (int i = twelveAmColumnInt; i <= twelveAmColumnInt + 23; i++)
{
if (row.Cell(i).Style.Fill.ToString() == await GetPhoneTimeCellFill(worksheet, phoneColorKeyCell))
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Adding {i} to phoneTimeColumns List<int>");
}
phoneTimeColumns.Add(i);
}
}
List <Task <StartStop> > tasks = new List <Task <StartStop> >();
foreach (int column in phoneTimeColumns)
{
tasks.Add(GetStartStopByCellPositionAsync(column - twelveAmColumnInt));
}
StartStop[] results = await Task.WhenAll(tasks);
foreach (StartStop startStop in results)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetAgentStartStopFromRowAsync - Adding start:{ startStop.Start } and stop: { startStop.Stop } to agentStartStop.StartStopList");
}
agentStartStop.StartStopList.Add(startStop);
}
return(agentStartStop);
}
private async Task <List <int> > GetTeamRowsAsync(IXLWorksheet worksheet, string columnName, ExcelCell groupByNameCell)
{
List <int> teamRows = new List <int>();
IXLRows col = await Task.Run(() => worksheet.RowsUsed());
foreach (IXLRow row in col)
{
int columnNumber = XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column);
if (!int.TryParse(await Task.Run(() => Regex.Replace(row.Cell(columnNumber).Address.ToString(), "[^0-9.]", "")), out int currentRowAddress))
{
throw new InvalidCastException("Unable to parse row int from cell address resturned from Excel");
}
if (row.Cell(columnNumber).Value.ToString().Trim() == columnName)
{
if (_log.IsDebugEnabled)
{
_log.Debug($"ExcelReader.GetTeamRowsAsync - Adding { currentRowAddress } to the teamRows list");
}
teamRows.Add(currentRowAddress);
}
}
return(teamRows);
}