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