private void RenderCell(FormulaEvaluator evaluator, TemplateCell cell, params Parameter[] pars)
{
object value;
try
{
value = cell.IsCalculated
? evaluator.Evaluate(cell.GetString(), pars)
: cell.CellType == TemplateCellType.Formula ? cell.Formula : cell.Value;
}
catch (ParseException ex)
{
_buff.WriteValue(ex.Message, cell.Style);
_buff.GetCell(_buff.PrevAddress.RowNumber, _buff.PrevAddress.ColumnNumber).Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(ex.Message, cell.XLCell.AsRange()));
return;
}
if (cell.CellType == TemplateCellType.Formula)
{
var r1c1 = cell.XLCell.GetFormulaR1C1(value.ToString());
_buff.WriteFormulaR1C1(r1c1, cell.Style);
}
else
{
_buff.WriteValue(value, cell.Style);
}
}
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);
}
}
}
}
private void RenderCell(FormulaEvaluator evaluator, TemplateCell cell, params Parameter[] pars)
{
object value;
try
{
value = cell.IsCalculated
? evaluator.Evaluate(cell.GetString(), pars)
: cell.CellType == TemplateCellType.Formula ? cell.Formula : cell.Value;
}
catch (ParseException ex)
{
_buff.WriteValue(ex.Message, cell.XLCell);
_buff.GetCell(_buff.PrevAddress.RowNumber, _buff.PrevAddress.ColumnNumber).Style.Font.FontColor = XLColor.Red;
_errors.Add(new TemplateError(ex.Message, cell.XLCell.AsRange()));
return;
}
IXLCell xlCell;
if (cell.CellType == TemplateCellType.Formula)
{
var r1c1 = cell.XLCell.GetFormulaR1C1(value.ToString());
xlCell = _buff.WriteFormulaR1C1(r1c1, cell.XLCell);
}
else
{
xlCell = _buff.WriteValue(value, cell.XLCell);
}
string EvalString(string str)
{
try
{
return(evaluator.Evaluate(str, pars).ToString());
}
catch (ParseException ex)
{
_errors.Add(new TemplateError(ex.Message, cell.XLCell.AsRange()));
return(ex.Message);
}
}
if (xlCell.HasComment)
{
var comment = EvalString(xlCell.Comment.Text);
xlCell.Comment.ClearText();
xlCell.Comment.AddText(comment);
}
if (xlCell.HasHyperlink)
{
if (xlCell.Hyperlink.IsExternal)
{
xlCell.Hyperlink.ExternalAddress = new Uri(EvalString(xlCell.Hyperlink.ExternalAddress.ToString()));
}
else
{
xlCell.Hyperlink.InternalAddress = EvalString(xlCell.Hyperlink.InternalAddress);
}
}
if (xlCell.HasRichText)
{
var richText = EvalString(xlCell.RichText.Text);
xlCell.RichText.ClearText();
xlCell.RichText.AddText(richText);
}
}
public virtual void EvaluateValues(IXLRange range, params Parameter[] pars)
{
foreach (var parameter in pars)
{
AddParameter(parameter.Value);
}
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();
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.Ranges)
{
{
if (!(_variables[nr.Name] is IEnumerable datas))
{
continue;
}
var items = datas as object[] ?? datas.Cast <object>().ToArray();
var tplt = RangeTemplate.Parse(nr.Name, rng, _errors, _variables);
using (var buff = tplt.Generate(items))
{
var ranges = nr.Ranges;
var trgtRng = buff.CopyTo(rng);
ranges.Remove(rng);
ranges.Add(trgtRng);
nr.SetRefersTo(ranges);
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(rng))
{
pt.SourceRange = rng.Offset(-1, 1, rng.RowCount() + 1, rng.ColumnCount() - 1);
}
}
}
}
}
}