public virtual void EvaluateValues(IXLRange range, params Parameter[] pars)
{
foreach (var parameter in pars)
{
AddParameter(parameter.Value);
}
range.Worksheet.SuspendEvents();
var innerRanges = range.GetContainingNames().Where(nr => _variables.ContainsKey(nr.Name)).ToArray();
var cells = range.CellsUsed()
.Where(c => !c.HasFormula &&
c.GetString().Contains("{{") &&
!innerRanges.Any(nr => nr.Ranges.Contains(c.AsRange())))
.ToArray();
range.Worksheet.ResumeEvents();
foreach (var cell in cells)
{
string value = cell.GetString();
try
{
if (value.StartsWith("&="))
{
cell.FormulaA1 = _evaluator.Evaluate(value.Substring(2), pars).ToString();
}
else
{
cell.Value = _evaluator.Evaluate(value, pars);
}
}
catch (ParseException ex)
{
Debug.WriteLine("Cell value evaluation exception (range '{1}'): {0}", ex.Message, range.RangeAddress);
}
}
foreach (var nr in innerRanges)
{
if (!_variables.ContainsKey(nr.Name))
{
Debug.WriteLine(string.Format("Range {0} was skipped. Variable with that name was not found.", nr.Name));
continue;
}
var datas = _variables[nr.Name] as IEnumerable;
if (datas == null)
{
continue;
}
var items = datas as object[] ?? datas.Cast <object>().ToArray();
var tplt = RangeTemplate.Parse(nr);
var nrng = nr.Ranges.First();
using (var buff = tplt.Generate(items))
{
var trgtRng = buff.CopyTo(nrng);
nr.SetRefersTo(trgtRng);
tplt.RangeTagsApply(trgtRng, items);
}
// refresh ranges for pivot tables
foreach (var pt in range.Worksheet.Workbook.Worksheets.SelectMany(sh => sh.PivotTables))
{
if (pt.SourceRange.Intersects(nrng))
{
pt.SourceRange = nrng.Offset(-1, 1, nrng.RowCount(), nrng.ColumnCount() - 1);
}
}
}
}
public virtual void EvaluateValues(IXLRange range, params Parameter[] pars)
{
foreach (var parameter in pars)
{
AddParameter(parameter.Value);
}
range.Worksheet.SuspendEvents();
var innerRanges = range.GetContainingNames().Where(nr => _variables.ContainsKey(nr.Name)).ToArray();
var cells = range.CellsUsed()
.Where(c => !c.HasFormula &&
c.GetString().Contains("{{") &&
!innerRanges.Any(nr => nr.Ranges.Contains(c.AsRange())))
.ToArray();
range.Worksheet.ResumeEvents();
foreach (var cell in cells)
{
string value = cell.GetString();
try
{
if (value.StartsWith("&="))
{
cell.FormulaA1 = _evaluator.Evaluate(value.Substring(2), pars).ToString();
}
else
{
cell.Value = _evaluator.Evaluate(value, pars);
}
}
catch (ParseException ex)
{
if (ex.Message == "Unknown identifier 'item'" && pars.Length == 0)
{
var firstCell = cell.Address.RowNumber > 1
? cell.CellAbove().WorksheetRow().FirstCell()
: cell.WorksheetRow().FirstCell();
var msg = "The range does not meet the requirements of the list ranges. For details, see the documentation.";
firstCell.Value = msg;
firstCell.Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(msg, firstCell.AsRange()));
}
cell.Value = ex.Message;
cell.Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(ex.Message, cell.AsRange()));
}
}
foreach (var nr in innerRanges)
{
var datas = _variables[nr.Name] as IEnumerable;
if (datas == null)
{
continue;
}
var items = datas as object[] ?? datas.Cast <object>().ToArray();
var tplt = RangeTemplate.Parse(nr, _errors);
var nrng = nr.Ranges.First();
using (var buff = tplt.Generate(items))
{
var trgtRng = buff.CopyTo(nrng);
nr.SetRefersTo(trgtRng);
tplt.RangeTagsApply(trgtRng, items);
}
// refresh ranges for pivot tables
foreach (var pt in range.Worksheet.Workbook.Worksheets.SelectMany(sh => sh.PivotTables))
{
if (pt.SourceRange.Intersects(nrng))
{
pt.SourceRange = nrng.Offset(-1, 1, nrng.RowCount(), nrng.ColumnCount() - 1);
}
}
}
}
public virtual void EvaluateValues(IXLRange range, params Parameter[] pars)
{
foreach (var parameter in pars)
{
AddParameter(parameter.Value);
}
var innerRanges = range.GetContainingNames()
.Select(BindToVariable)
.Where(nr => nr != null)
.ToArray();
var cells = range.CellsUsed()
.Where(c => !c.HasFormula &&
c.GetString().Contains("{{") &&
!innerRanges.Any(nr => nr.NamedRange.Ranges.Contains(c.AsRange())))
.ToArray();
foreach (var cell in cells)
{
string value = cell.GetString();
try
{
if (value.StartsWith("&="))
{
cell.FormulaA1 = _evaluator.Evaluate(value.Substring(2), pars).ToString();
}
else
{
cell.SetValue(_evaluator.Evaluate(value, pars));
}
}
catch (ParseException ex)
{
if (ex.Message == "Unknown identifier 'item'" && pars.Length == 0)
{
var firstCell = cell.Address.RowNumber > 1
? cell.CellAbove().WorksheetRow().FirstCell()
: cell.WorksheetRow().FirstCell();
var msg = "The range does not meet the requirements of the list ranges. For details, see the documentation.";
firstCell.Value = msg;
firstCell.Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(msg, firstCell.AsRange()));
}
cell.Value = ex.Message;
cell.Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(ex.Message, cell.AsRange()));
}
string EvalString(string str)
{
try
{
return(_evaluator.Evaluate(str, pars).ToString());
}
catch (ParseException ex)
{
_errors.Add(new TemplateError(ex.Message, cell.AsRange()));
return(ex.Message);
}
}
if (cell.HasComment)
{
var comment = EvalString(cell.Comment.Text);
cell.Comment.ClearText();
cell.Comment.AddText(comment);
}
if (cell.HasHyperlink)
{
if (cell.Hyperlink.IsExternal)
{
cell.Hyperlink.ExternalAddress = new Uri(EvalString(cell.Hyperlink.ExternalAddress.ToString()));
}
else
{
cell.Hyperlink.InternalAddress = EvalString(cell.Hyperlink.InternalAddress);
}
}
if (cell.HasRichText)
{
var richText = EvalString(cell.RichText.Text);
cell.RichText.ClearText();
cell.RichText.AddText(richText);
}
}
foreach (var nr in innerRanges)
{
foreach (var rng in nr.NamedRange.Ranges)
{
var growedRange = rng.GrowToMergedRanges();
var items = nr.RangeData as object[] ?? nr.RangeData.Cast <object>().ToArray();
var tplt = RangeTemplate.Parse(nr.NamedRange.Name, growedRange, _errors, _variables);
using (var buff = tplt.Generate(items))
{
var ranges = nr.NamedRange.Ranges;
var trgtRng = buff.CopyTo(growedRange);
ranges.Remove(rng);
ranges.Add(trgtRng);
nr.NamedRange.SetRefersTo(ranges);
tplt.RangeTagsApply(trgtRng, items);
var isOptionsRowEmpty = trgtRng.IsOptionsRowEmpty();
if (isOptionsRowEmpty)
{
trgtRng.LastRow().Delete(XLShiftDeletedCells.ShiftCellsUp);
}
}
// refresh ranges for pivot tables
foreach (var pt in range.Worksheet.Workbook.Worksheets.SelectMany(sh => sh.PivotTables))
{
if (pt.SourceRange.Intersects(growedRange))
{
pt.SourceRange = growedRange.Offset(-1, 1, growedRange.RowCount() + 1, growedRange.ColumnCount() - 1);
}
}
}
}
}