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);
}
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 static RangeTemplate Parse(IXLNamedRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary <string, object> globalVariables)
{
var prng = range.Ranges.First();
var result = new RangeTemplate(range, buff,
prng.RowCount(), prng.ColumnCount(), errors, globalVariables);
var innerRanges = GetInnerRanges(prng).ToArray();
var sheet = prng.Worksheet;
for (int iRow = 1; iRow <= result._rowCnt; iRow++)
{
for (int iColumn = 1; iColumn <= result._colCnt; iColumn++)
{
var xlCell = prng.Cell(iRow, iColumn);
if (innerRanges.Any(x => x.Ranges.Cells().Contains(xlCell)))
{
xlCell = null;
}
result._cells.Add(iRow, iColumn, xlCell);
}
if (iRow != result._rowCnt)
{
result._cells.AddNewRow();
}
}
result._mergedRanges = sheet.MergedRanges.Where(x => prng.Contains(x) && !innerRanges.Any(nr => nr.Ranges.Any(r => r.Contains(x)))).ToArray();
sheet.MergedRanges.RemoveAll(result._mergedRanges.Contains);
result._condFormats = sheet.ConditionalFormats
.Where(f => prng.Contains(f.Range) && !innerRanges.Any(ir => ir.Ranges.Contains(f.Range)))
.ToArray();
if (result._rowCnt > 1)
{
// Exclude special row
result._rowCnt--;
result._rowRange = prng.Offset(0, 0, result._rowCnt, result._colCnt);
result._optionsRow = prng.LastRow();
result._optionsRowIsEmpty = !result._optionsRow.CellsUsed(XLCellsUsedOptions.AllContents | XLCellsUsedOptions.MergedRanges).Any();
result._totalsCondFormats = sheet.ConditionalFormats
.Where(f => result._optionsRow.Contains(f.Range) && !innerRanges.Any(ir => ir.Ranges.Contains(f.Range)))
.ToArray();
var rs = prng.RangeAddress.FirstAddress.RowNumber;
result._condFormats = result._condFormats.Where(x => x.Range.RangeAddress.FirstAddress.RowNumber - rs + 1 <= result._rowCnt).ToArray();
}
else
{
result._totalsCondFormats = new IXLConditionalFormat[0];
}
result._subranges = innerRanges.Select(rng =>
{
var tpl = Parse(rng, buff, errors, globalVariables);
tpl._buff = result._buff;
tpl._isSubrange = true;
tpl._globalVariables = globalVariables;
return(tpl);
}).ToArray();
result.ParseTags(prng);
if (result._rangeOption != null)
{
var source = result._rangeOption.GetParameter("source");
if (!string.IsNullOrEmpty(source))
{
result.Source = source;
}
}
return(result);
}
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 static RangeTemplate Parse(string name, IXLRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary <string, object> globalVariables)
{
var result = new RangeTemplate(name, range, buff,
range.RowCount(), range.ColumnCount(), errors, globalVariables);
var innerRanges = GetInnerRanges(range).ToArray();
var sheet = range.Worksheet;
for (int iRow = 1; iRow <= result._rowCnt; iRow++)
{
for (int iColumn = 1; iColumn <= result._colCnt; iColumn++)
{
var xlCell = range.Cell(iRow, iColumn);
if (innerRanges.Any(x => x.Ranges.Cells().Contains(xlCell)))
{
xlCell = null;
}
result._cells.Add(iRow, iColumn, xlCell);
}
if (iRow != result._rowCnt)
{
result._cells.AddNewRow();
}
}
result._mergedRanges = sheet.MergedRanges.Where(x => range.Contains(x) && !innerRanges.Any(nr => nr.Ranges.Any(r => r.Contains(x)))).ToArray();
sheet.MergedRanges.RemoveAll(result._mergedRanges.Contains);
result.ParseTags(range);
if (result._rowCnt > 1 && !result.IsHorizontal)
{
// Exclude special row
result._rowCnt--;
result._rowRange = range.Offset(0, 0, result._rowCnt, result._colCnt);
result._optionsRow = range.LastRow();
result._optionsRowIsEmpty = !result._optionsRow.CellsUsed(XLCellsUsedOptions.AllContents | XLCellsUsedOptions.MergedRanges).Any();
}
result._subranges = innerRanges.SelectMany(nrng => nrng.Ranges,
(nr, rng) =>
{
var tpl = Parse(nr.Name, rng, buff, errors, globalVariables);
tpl._buff = result._buff;
tpl._isSubrange = true;
tpl._globalVariables = globalVariables;
return(tpl);
}).ToArray();
if (result._rangeOption != null)
{
var source = result._rangeOption.GetParameter("source");
if (!string.IsNullOrEmpty(source))
{
result.Source = source;
}
}
return(result);
}
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);
}
}
}
}
}
public TemplateCells(RangeTemplate template)
{
Template = template;
}
