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);
}
public void MDetem()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(2).CellRight().SetValue(4);
ws.Cell("A2").SetValue(3).CellRight().SetValue(5);
Object actual;
ws.Cell("A5").FormulaA1 = "MDeterm(A1:B2)";
actual = ws.Cell("A5").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double)actual));
ws.Cell("A6").FormulaA1 = "Sum(A5)";
actual = ws.Cell("A6").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double)actual));
ws.Cell("A7").FormulaA1 = "Sum(MDeterm(A1:B2))";
actual = ws.Cell("A7").Value;
Assert.IsTrue(XLHelper.AreEqual(-2.0, (double)actual));
}
public void MInverse()
{
IXLWorksheet ws = new XLWorkbook().AddWorksheet("Sheet1");
ws.Cell("A1").SetValue(1).CellRight().SetValue(2).CellRight().SetValue(1);
ws.Cell("A2").SetValue(3).CellRight().SetValue(4).CellRight().SetValue(-1);
ws.Cell("A3").SetValue(0).CellRight().SetValue(2).CellRight().SetValue(0);
Object actual;
ws.Cell("A5").FormulaA1 = "MInverse(A1:C3)";
actual = ws.Cell("A5").Value;
Assert.IsTrue(XLHelper.AreEqual(0.25, (double)actual));
ws.Cell("A6").FormulaA1 = "Sum(A5)";
actual = ws.Cell("A6").Value;
Assert.IsTrue(XLHelper.AreEqual(0.25, (double)actual));
ws.Cell("A7").FormulaA1 = "Sum(MInverse(A1:C3))";
actual = ws.Cell("A7").Value;
Assert.IsTrue(XLHelper.AreEqual(0.5, (double)actual));
}
public static void SaveTable(IXLWorksheet sheet, DataTable dataTable, string start, bool showColumnHeaders)
{
if (showColumnHeaders)
{
int colStart = XLHelper.GetColumnNumberFromAddress(start);
var rowPos = 0;
while (start[rowPos] > '9')
{
rowPos++;
}
int rowStart = int.Parse(start.Substring(rowPos));
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sheet.Cell(rowStart, colStart + i).Value = dataTable.Columns[i].ColumnName;
}
sheet.Cell(rowStart + 1, colStart).InsertData(dataTable.AsEnumerable());
}
else
{
sheet.Cell(start).InsertData(dataTable.AsEnumerable());
}
}
public MessageResult ProcessAjaxTask(XLHelper xlHelper, AjaxTaskDto taskDto)
{
if (taskDto == null)
{
return(CreateDelayBadResult("task should not null"));
}
var xlTaskItem = AjaxTaskDto.ConvertToXLTaskItem(taskDto);
if (lastAjaxTaskUrl == xlTaskItem.Url)
{
var message = string.Format("Escape processed same task: {0}", xlTaskItem.FileName);
Console.WriteLine();
Console.WriteLine(message);
return(CreateDelayBadResult(message));
}
string existMessage;
var exist = CheckExist(xlTaskItem, out existMessage);
if (exist)
{
Console.WriteLine();
Console.WriteLine(existMessage);
return(CreateDelayBadResult(existMessage));
}
lastAjaxTaskUrl = xlTaskItem.Url;
return(ProcessTask(xlHelper, xlTaskItem));
}
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());
}
/// <summary>
/// Apply shading to every other data row
/// </summary>
/// <param name="worksheet">The worksheet</param>
/// <param name="startTableDataRow">The table start row</param>
/// <param name="endTableDataRow">The table end row</param>
/// <param name="lastColumn">The last column</param>
private void ApplyShadingEveryOtherRow(ref IXLWorksheet worksheet, int startTableDataRow, int endTableDataRow, int lastColumn)
{
string rangeBegin = XLHelper.GetColumnLetterFromNumber(1) + startTableDataRow;
string rangeEnd = XLHelper.GetColumnLetterFromNumber(lastColumn) + endTableDataRow;
IXLRange range = worksheet.Range(rangeBegin, rangeEnd);
range.AddConditionalFormat().WhenIsTrue("=mod(row(),2)=0").Fill.SetBackgroundColor(XLColor.LightGray);
}
/// <summary>
/// Apply shading to the section
/// </summary>
/// <param name="worksheet">The worksheet.</param>
/// <param name="startSectionRow">The start section row</param>
/// <param name="endSectionRow">The end section row</param>
private void ApplyFormattingToSection(ref IXLWorksheet worksheet, int startSectionRow, int endSectionRow)
{
string rangeBegin = XLHelper.GetColumnLetterFromNumber(1) + startSectionRow;
string rangeEnd = XLHelper.GetColumnLetterFromNumber(_NUMBER_OF_COLUMNS_WIDE) + endSectionRow;
IXLRange range = worksheet.Range(rangeBegin, rangeEnd);
range.Style.Fill.SetBackgroundColor(XLColor.LightGray);
}
/// <summary>
/// Create a new row
/// </summary>
/// <param name="worksheet">The worksheet to create the </param>
/// <param name="currentRow"></param>
/// <param name="lastColumn"></param>
/// <returns></returns>
private IXLRange CreateRow(ref IXLWorksheet worksheet, ref int currentRow, int lastColumn)
{
string rangeBegin = XLHelper.GetColumnLetterFromNumber(1) + currentRow;
string rangeEnd = XLHelper.GetColumnLetterFromNumber(lastColumn) + currentRow;
IXLRange range = worksheet.Range(rangeBegin, rangeEnd);
currentRow++;
return(range);
}
/// <summary>
/// Create a row
/// </summary>
/// <param name="sheet">The sheet</param>
/// <param name="currentRow">The current row</param>
/// <returns>A IXLRange</returns>
private IXLRange CreateRow(ref IXLWorksheet sheet, ref int currentRow)
{
string rangeBegin = XLHelper.GetColumnLetterFromNumber(1) + currentRow;
string rangeEnd = XLHelper.GetColumnLetterFromNumber(_NUMBER_OF_COLUMNS_WIDE) + currentRow;
IXLRange range = sheet.Range(rangeBegin, rangeEnd);
currentRow++;
return(range);
}
internal static String GetHeader(MemberInfo mi)
{
var attribute = GetXLColumnAttribute(mi);
if (attribute == null)
{
return(null);
}
return(XLHelper.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header);
}
private static double AddCount(object value)
{
var strVal = value as String;
if (value != null && (strVal == null || !XLHelper.IsNullOrWhiteSpace(strVal)))
{
return(1);
}
return(0);
}
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 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 void IsValidColumnTest()
{
Assert.AreEqual(false, XLHelper.IsValidColumn(""));
Assert.AreEqual(false, XLHelper.IsValidColumn("1"));
Assert.AreEqual(false, XLHelper.IsValidColumn("A1"));
Assert.AreEqual(false, XLHelper.IsValidColumn("AA1"));
Assert.AreEqual(true, XLHelper.IsValidColumn("A"));
Assert.AreEqual(true, XLHelper.IsValidColumn("AA"));
Assert.AreEqual(true, XLHelper.IsValidColumn("AAA"));
Assert.AreEqual(true, XLHelper.IsValidColumn("Z"));
Assert.AreEqual(true, XLHelper.IsValidColumn("ZZ"));
Assert.AreEqual(true, XLHelper.IsValidColumn("XFD"));
Assert.AreEqual(false, XLHelper.IsValidColumn("ZAA"));
Assert.AreEqual(false, XLHelper.IsValidColumn("XZA"));
Assert.AreEqual(false, XLHelper.IsValidColumn("XFZ"));
}
public void ValidA1Addresses()
{
Assert.IsTrue(XLHelper.IsValidA1Address("A1"));
Assert.IsTrue(XLHelper.IsValidA1Address("A" + XLHelper.MaxRowNumber));
Assert.IsTrue(XLHelper.IsValidA1Address("Z1"));
Assert.IsTrue(XLHelper.IsValidA1Address("Z" + XLHelper.MaxRowNumber));
Assert.IsTrue(XLHelper.IsValidA1Address("AA1"));
Assert.IsTrue(XLHelper.IsValidA1Address("AA" + XLHelper.MaxRowNumber));
Assert.IsTrue(XLHelper.IsValidA1Address("ZZ1"));
Assert.IsTrue(XLHelper.IsValidA1Address("ZZ" + XLHelper.MaxRowNumber));
Assert.IsTrue(XLHelper.IsValidA1Address("AAA1"));
Assert.IsTrue(XLHelper.IsValidA1Address("AAA" + XLHelper.MaxRowNumber));
Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + "1"));
Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + XLHelper.MaxRowNumber));
}
/// <summary>
/// Gets the column letter of a given column number.
/// </summary>
/// <param name="columnNumber">The column number to translate into a column letter.</param>
/// <param name="trimToAllowed">if set to <c>true</c> the column letter will be restricted to the allowed range.</param>
/// <returns></returns>
private static string NaiveGetColumnLetterFromNumber(int columnNumber, bool trimToAllowed = false)
{
if (trimToAllowed)
{
columnNumber = XLHelper.TrimColumnNumber(columnNumber);
}
columnNumber--; // Adjust for start on column 1
if (columnNumber <= 25)
{
return(letters[columnNumber]);
}
var firstPart = (columnNumber) / 26;
var remainder = ((columnNumber) % 26) + 1;
return(NaiveGetColumnLetterFromNumber(firstPart) + NaiveGetColumnLetterFromNumber(remainder));
}
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 void Process()
{
if (!File.Exists("log.txt"))
{
Console.WriteLine("log.txt file not exist!");
Console.Read();
return;
}
var links = ParseFileLinks("log.txt");
var xlTaskItems = CreateXlTaskQueues(links);
//Download(xlTaskItems);
var xlHelper = new XLHelper();
xlHelper.Init();
ProcessQueues(xlHelper, xlTaskItems);
Console.WriteLine("work complete!");
MyCommonHelper.TryChangeFileName("log.txt", string.Format("log_{0}.txt", DateTime.Now.ToString("yyyyMMdd-HHmmss")));
}
public void InvalidA1Addresses()
{
Assert.IsFalse(XLHelper.IsValidA1Address(""));
Assert.IsFalse(XLHelper.IsValidA1Address("A"));
Assert.IsFalse(XLHelper.IsValidA1Address("a"));
Assert.IsFalse(XLHelper.IsValidA1Address("1"));
Assert.IsFalse(XLHelper.IsValidA1Address("-1"));
Assert.IsFalse(XLHelper.IsValidA1Address("AAAA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("XFG1"));
Assert.IsFalse(XLHelper.IsValidA1Address("@A1"));
Assert.IsFalse(XLHelper.IsValidA1Address("@AA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("@AAA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("[A1"));
Assert.IsFalse(XLHelper.IsValidA1Address("[AA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("[AAA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("{A1"));
Assert.IsFalse(XLHelper.IsValidA1Address("{AA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("{AAA1"));
Assert.IsFalse(XLHelper.IsValidA1Address("A1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("AA1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("AAA1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("A1["));
Assert.IsFalse(XLHelper.IsValidA1Address("AA1["));
Assert.IsFalse(XLHelper.IsValidA1Address("AAA1["));
Assert.IsFalse(XLHelper.IsValidA1Address("A1{"));
Assert.IsFalse(XLHelper.IsValidA1Address("AA1{"));
Assert.IsFalse(XLHelper.IsValidA1Address("AAA1{"));
Assert.IsFalse(XLHelper.IsValidA1Address("@A1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("@AA1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("@AAA1@"));
Assert.IsFalse(XLHelper.IsValidA1Address("[A1["));
Assert.IsFalse(XLHelper.IsValidA1Address("[AA1["));
Assert.IsFalse(XLHelper.IsValidA1Address("[AAA1["));
Assert.IsFalse(XLHelper.IsValidA1Address("{A1{"));
Assert.IsFalse(XLHelper.IsValidA1Address("{AA1{"));
Assert.IsFalse(XLHelper.IsValidA1Address("{AAA1{"));
}
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);
}
// ** implementation
private object GetValue(IXLCell cell)
{
if (_evaluating)
{
throw new Exception("Circular Reference");
}
try
{
_evaluating = true;
var f = cell.FormulaA1;
if (XLHelper.IsNullOrWhiteSpace(f))
{
return(cell.Value);
}
else
{
return(new XLCalcEngine(cell.Worksheet).Evaluate(f));
}
}
finally
{
_evaluating = false;
}
}
private void ValidateSpanText(string span_text, SolidColorBrush background, Color highlight)
{
if (string.IsNullOrEmpty(span_text)) // Nothing entered
{
background.Color = Colors.White;
_highlight_worker.ClearHighlightColor(highlight);
}
else if (XLHelper.IsValidRangeAddress(span_text)) // Valid Span range entered
{
background.Color = Colors.LightGreen; // Invalid Span range entered
var sheet = _workbook.Worksheet(SelectedSheetIndex + 1);
var range = sheet.Range(span_text);
_highlight_worker.SetOrUpdateHighlightColor(highlight, // ToDo will break if span is reversed
range.FirstRow().RowNumber(), range.LastRow().RowNumber(),
range.FirstColumn().ColumnNumber(), range.LastColumn().ColumnNumber());
}
else
{
background.Color = Colors.LightPink;
_highlight_worker.ClearHighlightColor(highlight);
}
}
// ** implementation
private object GetValue(IXLCell cell)
{
if (_evaluating || (cell as XLCell).IsEvaluating)
{
throw new InvalidOperationException($"Circular Reference occured during evaluation. Cell: {cell.Address.ToString(XLReferenceStyle.Default, true)}");
}
try
{
_evaluating = true;
var f = cell.FormulaA1;
if (XLHelper.IsNullOrWhiteSpace(f))
{
return(cell.Value);
}
else
{
return(new XLCalcEngine(cell.Worksheet).Evaluate(f));
}
}
finally
{
_evaluating = false;
}
}
public void Yearfrac_2_base4()
{
Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2013\",4)");
Assert.IsTrue(XLHelper.AreEqual(5.24722222222222, (double)actual));
}
public void Yearfrac_1_base3()
{
Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",3)");
Assert.IsTrue(XLHelper.AreEqual(0.24657534246575341, (double)actual));
}
public void Yearfrac_1_base1()
{
Object actual = XLWorkbook.EvaluateExpr("Yearfrac(\"1/1/2008\", \"3/31/2008\",1)");
Assert.IsTrue(XLHelper.AreEqual(0.24590163934426229, (double)actual));
}
public void TimeValue2()
{
Object actual = XLWorkbook.EvaluateExpr("TimeValue(\"22-Aug-2008 6:35 AM\")");
Assert.IsTrue(XLHelper.AreEqual(0.27430555555555558, (double)actual));
}
public void TimeValue1()
{
Object actual = XLWorkbook.EvaluateExpr("TimeValue(\"2:24 AM\")");
Assert.IsTrue(XLHelper.AreEqual(0.1, (double)actual));
}
public override object GetExternalObject(string identifier)
{
if (identifier.Contains("!") && _wb != null)
{
var referencedSheetNames = identifier.Split(':')
.Select(part =>
{
if (part.Contains("!"))
{
return(part.Substring(0, part.IndexOf('!')).ToLower());
}
else
{
return(null);
}
})
.Where(sheet => sheet != null)
.Distinct();
if (!referencedSheetNames.Any())
{
return(GetCellRangeReference(_ws.Range(identifier)));
}
else if (referencedSheetNames.Count() > 1)
{
throw new ArgumentOutOfRangeException(referencedSheetNames.Last(), "Cross worksheet references may references no more than 1 other worksheet");
}
else
{
if (!_wb.TryGetWorksheet(referencedSheetNames.Single(), out IXLWorksheet worksheet))
{
throw new ArgumentOutOfRangeException(referencedSheetNames.Single(), "The required worksheet cannot be found");
}
identifier = identifier.ToLower().Replace(string.Format("{0}!", worksheet.Name.ToLower()), "");
return(GetCellRangeReference(worksheet.Range(identifier)));
}
}
else if (_ws != null)
{
if (TryGetNamedRange(identifier, _ws, out IXLNamedRange namedRange))
{
var references = (namedRange as XLNamedRange).RangeList.Select(r =>
XLHelper.IsValidRangeAddress(r)
? GetCellRangeReference(_ws.Workbook.Range(r))
: new XLCalcEngine(_ws).Evaluate(r.ToString())
);
if (references.Count() == 1)
{
return(references.Single());
}
return(references);
}
return(GetCellRangeReference(_ws.Range(identifier)));
}
else
{
return(identifier);
}
}
