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