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