public void SingleCell() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); ws.Cell(1, 1).Value = "Hello World!"; wb.NamedRanges.Add("SingleCell", "Sheet1!$A$1"); IXLRange range = wb.Range("SingleCell"); Assert.AreEqual(1, range.CellsUsed().Count()); Assert.AreEqual("Hello World!", range.CellsUsed().Single().GetString()); }
public void ParseTags(IXLRange range, string rangeName) { var innerRanges = range.GetContainingNames().Where(nr => _variables.ContainsKey(nr.Name)).ToArray(); var cells = from c in range.CellsUsed() let value = c.GetString() where !c.HasFormula && (value.StartsWith("<<") || value.EndsWith(">>")) && !innerRanges.Any(nr => { using (var r = nr.Ranges) using (var cr = c.AsRange()) return(r.Contains(cr)); }) select c; if (!_tags.ContainsKey(rangeName)) { _tags.Add(rangeName, new TagsList()); } foreach (var cell in cells) { string value = cell.GetString(); OptionTag[] tags; string newValue; var templateCell = new TemplateCell(cell.Address.RowNumber, cell.Address.ColumnNumber, cell); if (value.StartsWith("&=")) { tags = _tagsEvaluator.Parse(value.Substring(2), range, templateCell, out newValue); cell.FormulaA1 = newValue; } else { tags = _tagsEvaluator.Parse(value, range, templateCell, out newValue); cell.Value = newValue; } _tags[rangeName].AddRange(tags); } }
public void TableRange() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); IXLRangeColumn rangeColumn = ws.Column(1).Column(1, 4); rangeColumn.Cell(1).Value = "FName"; rangeColumn.Cell(2).Value = "John"; rangeColumn.Cell(3).Value = "Hank"; rangeColumn.Cell(4).Value = "Dagny"; IXLTable table = rangeColumn.CreateTable(); wb.NamedRanges.Add("FNameColumn", String.Format("{0}[{1}]", table.Name, "FName")); IXLRange namedRange = wb.Range("FNameColumn"); Assert.AreEqual(3, namedRange.Cells().Count()); Assert.IsTrue( namedRange.CellsUsed().Select(cell => cell.GetString()).SequenceEqual(new[] { "John", "Hank", "Dagny" })); }
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); } } } }
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); } } } } }