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);
}
}
private void RenderSubrange(RangeTemplate ownRng, object item, FormulaEvaluator evaluator, TemplateCell cell,
TagsList tags, ref int iCell, ref int row)
{
var start = _buff.NextAddress;
// the child template to which the cell belongs
var formula = ownRng.Source.ReplaceLast("_", ".");
if (evaluator.Evaluate(formula, new Parameter(Name, item)) is IEnumerable value)
{
var valArr = value.Cast <object>().ToArray();
ownRng.Generate(valArr);
if (ownRng.IsHorizontal)
{
int shiftLen = ownRng._colCnt * (valArr.Length - 1);
tags.Where(tag => tag.Cell.Row == cell.Row && tag.Cell.Column > cell.Column)
.ForEach(t =>
{
t.Cell.Column += shiftLen;
t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column);
});
}
else
{
// move current template cell to next (skip subrange)
row += ownRng._rowCnt + 1;
while (_cells[iCell].Row <= row - 1)
{
iCell++;
}
iCell--; // roll back. After it became clear that it was too much, we must go back.
int shiftLen = ownRng._rowCnt * (valArr.Length - 1);
tags.Where(tag => tag.Cell.Row > cell.Row)
.ForEach(t =>
{
t.Cell.Row += shiftLen;
t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column);
});
}
}
var rng = _buff.GetRange(start, _buff.PrevAddress);
var rangeName = ownRng.Name;
var dnr = rng.Worksheet.Workbook.NamedRange(rangeName);
dnr.SetRefersTo(rng);
}
private void RenderSubrange(object item, FormulaEvaluator evaluator, TemplateCell cell, TagsList tags, ref int iCell, ref int row)
{
var start = _buff.NextAddress;
// дочерний шаблон, к которому принадлежит ячейка
var xlCell = _rowRange.Cell(cell.Row, cell.Column);
var ownRng = _subranges.First(r => r._cells.Any(c => c.CellType != TemplateCellType.None && c.XLCell != null && Equals(c.XLCell.Address, xlCell.Address)));
var formula = "{{" + ownRng.Source.ReplaceLast("_", ".") + "}}";
if (evaluator.Evaluate(formula, new Parameter(Name, item)) is IEnumerable value)
{
var valArr = value.Cast <object>().ToArray();
ownRng.Generate(valArr);
if (ownRng.IsHorizontal)
{
iCell += ownRng._colCnt - 1;
int shiftLen = ownRng._colCnt * (valArr.Length - 1);
tags.Where(tag => tag.Cell.Row == cell.Row && tag.Cell.Column > cell.Column)
.ForEach(t =>
{
t.Cell.Column += shiftLen;
t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column);
});
}
else
{
row += ownRng._rowCnt - 1;
while (_cells[iCell].Row <= row + 1)
{
iCell++;
}
int shiftLen = ownRng._rowCnt * (valArr.Length - 1);
tags.Where(tag => tag.Cell.Row > cell.Row)
.ForEach(t =>
{
t.Cell.Row += shiftLen;
t.Cell.XLCell = _rowRange.Cell(t.Cell.Row, t.Cell.Column);
});
}
}
var rng = _buff.GetRange(start, _buff.PrevAddress);
var rangeName = ownRng.Name;
var dnr = rng.Worksheet.Workbook.NamedRange(rangeName);
dnr.SetRefersTo(rng);
}
private void RenderCell(FormulaEvaluator evaluator, TemplateCell cell, params Parameter[] pars)
{
var value = cell.IsCalculated
? evaluator.Evaluate(cell.GetString(), pars)
: cell.CellType == TemplateCellType.Formula ? cell.Formula : cell.Value;
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);
}
}
}
}
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);
}
}
}
}
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);
}
}
}
}
}
}