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