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); }
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())); }
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); }
public void TestColumnLetterLookup() { var columnLetters = new List <String>(); for (int c = 1; c <= XLHelper.MaxColumnNumber; c++) { var columnLetter = NaiveGetColumnLetterFromNumber(c); columnLetters.Add(columnLetter); Assert.AreEqual(columnLetter, XLHelper.GetColumnLetterFromNumber(c)); } foreach (var cl in columnLetters) { var columnNumber = NaiveGetColumnNumberFromLetter(cl); Assert.AreEqual(columnNumber, XLHelper.GetColumnNumberFromLetter(cl)); } }
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); }
private static void CheckColumnNumber(int column) { Assert.AreEqual(column, XLHelper.GetColumnNumberFromLetter(XLHelper.GetColumnLetterFromNumber(column))); }