public void TestGetNearestNamedPanelTest()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange = ws.Range(2, 1, 3, 4);
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
IXLNamedRange childOfChildNamedRange = ws.NamedRange("ChildOfChild");
IExcelPanel childOfChildPanel = new ExcelNamedPanel(childOfChildNamedRange, excelReport, templateProcessor);
IExcelPanel childPanel = new ExcelPanel(childRange, excelReport, templateProcessor);
IExcelPanel parentPanel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor);
MethodInfo method = typeof(ExcelNamedPanel).GetMethod("GetNearestNamedParent", BindingFlags.Instance | BindingFlags.NonPublic);
Assert.IsNull(method.Invoke(childOfChildPanel, null));
childOfChildPanel.Parent = childPanel;
Assert.IsNull(method.Invoke(childOfChildPanel, null));
childPanel.Parent = parentPanel;
Assert.AreSame(parentPanel, method.Invoke(childOfChildPanel, null));
}
public static IXLNamedRange CopyNamedRange(IXLNamedRange namedRange, IXLCell cell, string name)
{
IXLRange newRange = CopyRange(namedRange.Ranges.ElementAt(0), cell);
newRange.AddToNamed(name, XLScope.Worksheet);
return(cell.Worksheet.NamedRange(name));
}
public void NamedRange1()
{
var wb = new XLWorkbook();
IXLNamedRange range = wb.NamedRange("ABC");
Assert.IsNull(range);
}
public void TestCreateSimplePanel()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(ws.Cell(1, 1), ws.Cell(2, 2));
range.AddToNamed("s_Test", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("s_Test");
var report = new object();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var parseSettings = new PanelParsingSettings
{
SimplePanelPrefix = "s",
PanelPrefixSeparator = "_",
};
var factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
var panel = (ExcelPanel)factory.Create(namedRange, new Dictionary <string, string>
{
[nameof(ExcelPanel.Type)] = PanelType.Horizontal.ToString(),
[nameof(ExcelPanel.ShiftType)] = ShiftType.Row.ToString(),
[nameof(ExcelPanel.RenderPriority)] = "5",
[nameof(ExcelPanel.BeforeRenderMethodName)] = "BeforeRenderMethodName",
[nameof(ExcelPanel.AfterRenderMethodName)] = "AfterRenderMethodName",
});
Assert.AreEqual(PanelType.Horizontal, panel.Type);
Assert.AreEqual(ShiftType.Row, panel.ShiftType);
Assert.AreEqual(5, panel.RenderPriority);
Assert.AreEqual("BeforeRenderMethodName", panel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethodName", panel.AfterRenderMethodName);
Assert.AreEqual(0, panel.Children.Count);
Assert.IsNull(panel.Parent);
Assert.AreEqual(namedRange.Ranges.First(), panel.Range);
Assert.AreSame(report, panel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(templateProcessor, panel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
namedRange.Delete();
range.AddToNamed("SS--Test", XLScope.Workbook);
namedRange = wb.NamedRange("SS--Test");
parseSettings.SimplePanelPrefix = "ss";
parseSettings.PanelPrefixSeparator = "--";
factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
panel = (ExcelPanel)factory.Create(namedRange, null);
Assert.IsInstanceOf <ExcelPanel>(panel);
Assert.AreEqual(PanelType.Vertical, panel.Type);
Assert.AreEqual(ShiftType.Cells, panel.ShiftType);
Assert.AreEqual(0, panel.RenderPriority);
Assert.AreEqual(0, panel.Children.Count);
Assert.IsNull(panel.BeforeRenderMethodName);
Assert.IsNull(panel.AfterRenderMethodName);
Assert.IsNull(panel.Parent);
Assert.AreEqual(namedRange.Ranges.First(), panel.Range);
Assert.AreSame(report, panel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(templateProcessor, panel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
}
public void NamedRange3()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
IXLNamedRange range = wb.NamedRange("Sheet1!Result");
Assert.IsNull(range);
}
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange)
{
IXLNamedRange range = workbook.NamedRange(namedRange);
if (range == null)
{
throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook");
}
int count = range.Ranges.First().Cells().Count();
//if solo then don't return collection.
if (count.Equals(1))
{
IXLCell cell = range.Ranges.First().Cells().First();
switch (cell.DataType)
{
case XLDataType.DateTime:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDateTime().ToOADate() }
});
case XLDataType.Number:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDouble() }
});
case XLDataType.Text:
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, (double)parsed.Value }
});
case ExcelDataType.Text:
string[,] array = new string[1, 1];
array[0, 0] = textValue;
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Text, array }
});
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
}
}
IXLTable table = range.Ranges.First().AsTable();
return(ReadTable(table));
}
public void TestRemoveAllNamesRecursive()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IExcelNamedPanel parentPanel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor);
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IExcelNamedPanel childPanel1 = new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor);
childPanel1.Parent = parentPanel;
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
IExcelPanel childPanel2 = new ExcelPanel(childRange2, excelReport, templateProcessor);
childPanel2.Parent = parentPanel;
parentPanel.Children = new List <IExcelPanel> {
childPanel1, childPanel2
};
IXLRange childOfChild1Range = ws.Range(1, 1, 1, 4);
childOfChild1Range.AddToNamed("ChildOfChild1", XLScope.Worksheet);
IXLNamedRange namedChildOfChild1RangeRange = ws.NamedRange("ChildOfChild1");
IExcelNamedPanel childOfChild1Panel = new ExcelNamedPanel(namedChildOfChild1RangeRange, excelReport, templateProcessor);
childOfChild1Panel.Parent = childPanel1;
childPanel1.Children = new List <IExcelPanel> {
childOfChild1Panel
};
IXLRange childOfChild2Range = ws.Range(3, 1, 3, 4);
childOfChild2Range.AddToNamed("ChildOfChild2", XLScope.Worksheet);
IXLNamedRange namedChildOfChild2RangeRange = ws.NamedRange("ChildOfChild2");
IExcelNamedPanel childOfChild2Panel = new ExcelNamedPanel(namedChildOfChild2RangeRange, excelReport, templateProcessor);
childOfChild2Panel.Parent = childPanel2;
childPanel2.Children = new List <IExcelPanel> {
childOfChild2Panel
};
ExcelNamedPanel.RemoveAllNamesRecursive(parentPanel);
Assert.AreEqual(0, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public ExcelDataSourcePanel(string dataSourceTemplate, IXLNamedRange namedRange, object report, ITemplateProcessor templateProcessor)
: base(namedRange, report, templateProcessor)
{
if (string.IsNullOrWhiteSpace(dataSourceTemplate))
{
throw new ArgumentException(ArgumentHelper.EmptyStringParamMessage, nameof(dataSourceTemplate));
}
_dataSourceTemplate = dataSourceTemplate;
}
public static IXLNamedRange MoveNamedRange(IXLNamedRange namedRange, IXLCell cell)
{
string name = namedRange.Name;
IXLRange newRange = MoveRange(namedRange.Ranges.ElementAt(0), cell);
namedRange.Delete();
newRange.AddToNamed(name, XLScope.Worksheet);
return(cell.Worksheet.NamedRange(name));
}
public Boolean TryGetValue(String name, out IXLNamedRange range)
{
if (_namedRanges.TryGetValue(name, out range))
{
return(true);
}
range = Workbook.NamedRange(name);
return(range != null);
}
public void NamedRange2()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue(1).AddToNamed("Result", XLScope.Worksheet);
IXLNamedRange range = wb.NamedRange("Sheet1!Result");
Assert.IsNotNull(range);
Assert.AreEqual(1, range.Ranges.Count);
Assert.AreEqual(1, range.Ranges.Cells().Count());
Assert.AreEqual(1, range.Ranges.First().FirstCell().GetValue <Int32>());
}
internal RangeTemplate(IXLNamedRange range, TempSheetBuffer buff)
{
_rowRange = range.Ranges.First();
_cells = new TemplateCells(this);
_tagsEvaluator = new TagsEvaluator();
var wb = _rowRange.Worksheet.Workbook;
_buff = buff;
_tags = new TagsList();
_rangeTags = new TagsList();
Name = range.Name;
Source = range.Name;
wb.NamedRanges.Add(range.Name + "_tpl", range.Ranges);
}
internal RangeTemplate(IXLNamedRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary <string, object> globalVariables)
{
_rowRange = range.Ranges.First();
_cells = new TemplateCells(this);
_tagsEvaluator = new TagsEvaluator();
var wb = _rowRange.Worksheet.Workbook;
_buff = buff;
_errors = errors;
_globalVariables = globalVariables;
_tags = new TagsList(_errors);
_rangeTags = new TagsList(_errors);
Name = range.Name;
Source = range.Name;
wb.NamedRanges.Add(range.Name + "_tpl", range.Ranges);
}
public static void AreNamedRangesEquals(IXLNamedRanges expected, IXLNamedRanges actual)
{
Assert.AreEqual(expected.Count(), actual.Count(), "Worksheet named ranges count failed");
foreach (IXLNamedRange expectedNamedRange in expected)
{
IXLNamedRange actualNamedRange = actual.NamedRange(expectedNamedRange.Name);
Assert.AreEqual(expectedNamedRange.Comment, actualNamedRange.Comment, $"Named range {expectedNamedRange.Name} comment failed");
Assert.AreEqual(expectedNamedRange.Ranges.Count, actualNamedRange.Ranges.Count, $"Named range {expectedNamedRange.Name} ranges count failed");
for (int i = 0; i < expectedNamedRange.Ranges.Count; i++)
{
IXLRange expectedRange = expectedNamedRange.Ranges.ElementAt(i);
IXLRange actualRange = actualNamedRange.Ranges.ElementAt(i);
Assert.AreEqual(expectedRange.FirstCell().Address, actualRange.FirstCell().Address, $"Named range {expectedNamedRange.Name} range {i + 1} first cell address failed");
Assert.AreEqual(expectedRange.LastCell().Address, actualRange.LastCell().Address, $"Named range {expectedNamedRange.Name} range {i + 1} last cell address failed");
}
}
}
public void TestCreateDataSourcePanel()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(ws.Cell(1, 1), ws.Cell(2, 2));
range.AddToNamed("d_Test", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("d_Test");
var report = new object();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var parseSettings = new PanelParsingSettings
{
DataSourcePanelPrefix = "d",
PanelPrefixSeparator = "_",
};
var factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
var panel = (ExcelDataSourcePanel)factory.Create(namedRange, new Dictionary <string, string>
{
[nameof(ExcelDataSourcePanel.Type)] = PanelType.Horizontal.ToString(),
[nameof(ExcelDataSourcePanel.ShiftType)] = ShiftType.Row.ToString(),
[nameof(ExcelDataSourcePanel.RenderPriority)] = "5",
[nameof(ExcelDataSourcePanel.BeforeRenderMethodName)] = "BeforeRenderMethodName",
[nameof(ExcelDataSourcePanel.AfterRenderMethodName)] = "AfterRenderMethodName",
[nameof(ExcelDataSourcePanel.BeforeDataItemRenderMethodName)] = "BeforeDataItemRenderMethodName",
[nameof(ExcelDataSourcePanel.AfterDataItemRenderMethodName)] = "AfterDataItemRenderMethodName",
["DataSource"] = "DS",
});
Assert.AreEqual(PanelType.Horizontal, panel.Type);
Assert.AreEqual(ShiftType.Row, panel.ShiftType);
Assert.AreEqual(5, panel.RenderPriority);
Assert.AreEqual("BeforeRenderMethodName", panel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethodName", panel.AfterRenderMethodName);
Assert.AreEqual("BeforeDataItemRenderMethodName", panel.BeforeDataItemRenderMethodName);
Assert.AreEqual("AfterDataItemRenderMethodName", panel.AfterDataItemRenderMethodName);
Assert.AreEqual(0, panel.Children.Count);
Assert.IsNull(panel.Parent);
Assert.AreEqual(namedRange.Ranges.First(), panel.Range);
Assert.AreSame("DS", panel.GetType().GetField("_dataSourceTemplate", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(report, panel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
Assert.AreSame(templateProcessor, panel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(panel));
ExceptionAssert.Throws <InvalidOperationException>(() => factory.Create(namedRange, null), "Data source panel must have the property \"DataSource\"");
}
public void TestCopyIfDataIsSet()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 2, 4);
range.AddToNamed("DataPanel", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("DataPanel");
object[] data = { 1, "One" };
var panel = new ExcelDataSourcePanel(data, namedRange, excelReport, templateProcessor)
{
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod",
AfterRenderMethodName = "AfterRenderMethod",
BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName",
GroupBy = "2,4",
};
ExcelDataSourcePanel copiedPanel = (ExcelDataSourcePanel)panel.Copy(ws.Cell(5, 5));
Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.IsNull(copiedPanel.GetType().GetField("_dataSourceTemplate", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(data, copiedPanel.GetType().GetField("_data", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 8), copiedPanel.Range.LastCell());
Assert.AreEqual(10, copiedPanel.RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType);
Assert.AreEqual("BeforeRenderMethod", copiedPanel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod", copiedPanel.AfterRenderMethodName);
Assert.AreEqual("BeforeDataItemRenderMethodName", copiedPanel.BeforeDataItemRenderMethodName);
Assert.AreEqual("AfterDataItemRenderMethodName", copiedPanel.AfterDataItemRenderMethodName);
Assert.AreEqual("2,4", copiedPanel.GroupBy);
Assert.IsNull(copiedPanel.Parent);
//wb.SaveAs("test.xlsx");
}
private BoundRange BindToVariable(IXLNamedRange namedRange)
{
if (_variables.TryGetValue(namedRange.Name, out var variableValue) &&
variableValue is IEnumerable data1)
{
return(new BoundRange(namedRange, data1));
}
var expression = "{{" + namedRange.Name.Replace("_", ".") + "}}";
if (_evaluator.TryEvaluate(expression, out var res) &&
res is IEnumerable data2)
{
return(new BoundRange(namedRange, data2));
}
return(null);
}
public void TestCreateUnsupportedPanelType()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(ws.Cell(1, 1), ws.Cell(2, 2));
range.AddToNamed("b_Test", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("b_Test");
var report = new object();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var parseSettings = new PanelParsingSettings {
PanelPrefixSeparator = "_"
};
var factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
ExceptionAssert.Throws <NotSupportedException>(() => factory.Create(namedRange, null), "Panel type with prefix \"b\" is not supported");
}
public void TestCreatePanelWithBadName()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(ws.Cell(1, 1), ws.Cell(2, 2));
range.AddToNamed("b_Test", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("b_Test");
var report = new object();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var parseSettings = new PanelParsingSettings {
PanelPrefixSeparator = "-"
};
var factory = new ExcelPanelFactory(report, templateProcessor, parseSettings);
ExceptionAssert.Throws <InvalidOperationException>(() => factory.Create(namedRange, null), "Panel name \"b_Test\" does not contain prefix separator \"-\"");
}
public override void RecalculateRangeRelativeParentRecursive()
{
if (Parent == null)
{
return;
}
string name = _namedRange.Name;
IXLRange range = Parent.Range.Range(
_coordsRelativeParent.FirstCell.RowNum,
_coordsRelativeParent.FirstCell.ColNum,
_coordsRelativeParent.LastCell.RowNum,
_coordsRelativeParent.LastCell.ColNum);
_namedRange.Delete();
range.AddToNamed(name, XLScope.Worksheet);
_namedRange = range.Worksheet.NamedRange(name);
MoveChildren();
}
public IExcelPanel Create(IXLNamedRange namedRange, IDictionary <string, string> properties)
{
_namedRange = namedRange ?? throw new ArgumentNullException(nameof(namedRange), ArgumentHelper.NullParamMessage);
_properties = properties ?? new Dictionary <string, string>(0);
IExcelPanel panel;
int prefixIndex = namedRange.Name.IndexOf(_panelParsingSettings.PanelPrefixSeparator, StringComparison.CurrentCultureIgnoreCase);
if (prefixIndex == -1)
{
throw new InvalidOperationException($"Panel name \"{namedRange.Name}\" does not contain prefix separator \"{_panelParsingSettings.PanelPrefixSeparator}\"");
}
string prefix = namedRange.Name.Substring(0, prefixIndex);
if (prefix.Equals(_panelParsingSettings.SimplePanelPrefix, StringComparison.CurrentCultureIgnoreCase))
{
panel = CreateSimplePanel();
}
else if (prefix.Equals(_panelParsingSettings.DataSourcePanelPrefix, StringComparison.CurrentCultureIgnoreCase))
{
panel = CreateDataSourcePanel();
}
else if (prefix.Equals(_panelParsingSettings.DynamicDataSourcePanelPrefix, StringComparison.CurrentCultureIgnoreCase))
{
panel = CreateDynamicPanel();
}
else if (prefix.Equals(_panelParsingSettings.TotalsPanelPrefix, StringComparison.CurrentCultureIgnoreCase))
{
panel = CreateTotalsPanel();
}
else
{
throw new NotSupportedException($"Panel type with prefix \"{prefix}\" is not supported");
}
FillPanelProperties(panel);
return(panel);
}
public void TestCopyWithName()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange1 = ws.Range(1, 1, 2, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IXLRange childRange2 = ws.Range(3, 1, 3, 4);
IXLRange childOfChildRange1 = ws.Range(1, 1, 1, 4);
childOfChildRange1.AddToNamed("ChildOfChild1", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange1 = ws.NamedRange("ChildOfChild1");
IXLRange childOfChildRange2 = ws.Range(3, 1, 3, 4);
childOfChildRange2.AddToNamed("ChildOfChild2", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange2 = ws.NamedRange("ChildOfChild2");
var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange1, excelReport, templateProcessor)
}
},
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange2, excelReport, templateProcessor)
}
}
}
};
IExcelNamedPanel copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied");
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, "Copied_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.IsInstanceOf <ExcelPanel>(copiedPanel.Children.Last());
Assert.IsNotInstanceOf <ExcelNamedPanel>(copiedPanel.Children.Last());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, "Copied_Child_ChildOfChild1"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, "ChildOfChild2_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
copiedPanel = panel.Copy(ws.Cell(5, 5), "Copied2", false);
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, "Copied2"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count);
ExceptionAssert.Throws <ArgumentNullException>(() => panel.Copy(null, "Copied"));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), null));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), string.Empty));
ExceptionAssert.Throws <ArgumentException>(() => panel.Copy(ws.Cell(5, 5), " "));
//wb.SaveAs("test.xlsx");
}
public NamedRange(IXLNamedRange range)
{
InternalNamedRange = range;
}
public Boolean TryGetValue(String name, out IXLNamedRange range)
{
if (_namedRanges.TryGetValue(name, out range)) return true;
range = Workbook.NamedRange(name);
return range != null;
}
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);
}
private bool TryGetNamedRange(string identifier, IXLWorksheet worksheet, out IXLNamedRange namedRange)
{
return(worksheet.NamedRanges.TryGetValue(identifier, out namedRange) ||
worksheet.Workbook.NamedRanges.TryGetValue(identifier, out namedRange));
}
public void TestDelete()
{
// Deleting with moving cells up
XLWorkbook wb = InitWorkBookForDeleteRangeTest();
IXLWorksheet ws = wb.Worksheet("Test");
IXLNamedRange parentRange = ws.NamedRange("Parent");
IXLNamedRange childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
var panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
}
};
panel.Delete();
IXLCell rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
IXLCell rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
IXLCell belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
IXLCell belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
IXLCell rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
IXLCell rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
IXLCell aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
IXLCell aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
IXLCell leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
IXLCell leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving the row up
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
ShiftType = ShiftType.Row,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(leftCell1);
Assert.IsNull(rightCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(6, 6));
Assert.AreEqual(belowCell2, ws.Cell(6, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell2, ws.Cell(6, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving cells left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
Type = PanelType.Horizontal
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting with moving the column left
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.IsNull(aboveCell1);
Assert.IsNull(belowCell1);
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell2, ws.Cell(10, 5));
Assert.AreEqual(rightCell1, ws.Cell(7, 5));
Assert.AreEqual(rightCell2, ws.Cell(5, 5));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
// Deleting without any shift
wb = InitWorkBookForDeleteRangeTest();
ws = wb.Worksheet("Test");
parentRange = ws.NamedRange("Parent");
childRange = ws.NamedRange("Child");
Assert.AreEqual(2, ws.NamedRanges.Count());
panel = new ExcelNamedPanel(parentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel> {
new ExcelNamedPanel(childRange, excelReport, templateProcessor)
},
ShiftType = ShiftType.NoShift,
};
panel.Delete();
rangeStartCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeStart");
rangeEndCell = ws.Cells().SingleOrDefault(c => c.Value.ToString() == "RangeEnd");
belowCell1 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_1");
belowCell2 = ws.Cells().Single(c => c.Value.ToString() == "BelowCell_2");
rightCell1 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_1");
rightCell2 = ws.Cells().Single(c => c.Value.ToString() == "RightCell_2");
aboveCell1 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_1");
aboveCell2 = ws.Cells().Single(c => c.Value.ToString() == "AboveCell_2");
leftCell1 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_1");
leftCell2 = ws.Cells().Single(c => c.Value.ToString() == "LeftCell_2");
Assert.IsNull(rangeStartCell);
Assert.IsNull(rangeEndCell);
Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).FirstCell().Style.Border.TopBorder);
Assert.AreEqual(XLBorderStyleValues.None, parentRange.Ranges.ElementAt(0).Style.Border.BottomBorder);
Assert.AreEqual(8, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(belowCell1, ws.Cell(10, 6));
Assert.AreEqual(belowCell2, ws.Cell(10, 8));
Assert.AreEqual(rightCell1, ws.Cell(7, 8));
Assert.AreEqual(rightCell2, ws.Cell(5, 8));
Assert.AreEqual(aboveCell1, ws.Cell(5, 6));
Assert.AreEqual(aboveCell2, ws.Cell(5, 4));
Assert.AreEqual(leftCell1, ws.Cell(7, 4));
Assert.AreEqual(leftCell2, ws.Cell(10, 4));
Assert.AreEqual(0, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
public ExcelDataSourcePanel(object data, IXLNamedRange namedRange, object report, ITemplateProcessor templateProcessor) : base(namedRange, report, templateProcessor)
{
_data = data ?? throw new ArgumentNullException(nameof(data), ArgumentHelper.NullParamMessage);
_isDataReceivedDirectly = true;
}
public void TestCopy()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
IXLNamedRange namedRange = ws.NamedRange("Parent");
IXLRange childRange = ws.Range(2, 1, 3, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("Child");
IXLRange childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
IXLNamedRange namedChildOfChildRange = ws.NamedRange("ChildOfChild");
var panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", namedChildOfChildRange, excelReport, templateProcessor)
{
RenderPriority = 30,
Type = PanelType.Horizontal,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod3",
AfterRenderMethodName = "AfterRenderMethod3",
BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName",
GroupBy = "2,4",
}
},
RenderPriority = 20,
ShiftType = ShiftType.Row,
BeforeRenderMethodName = "BeforeRenderMethod2",
AfterRenderMethodName = "AfterRenderMethod2",
}
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod1",
AfterRenderMethodName = "AfterRenderMethod1",
};
IExcelNamedPanel copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreEqual(10, copiedPanel.RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType);
Assert.AreEqual("BeforeRenderMethod1", copiedPanel.BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod1", copiedPanel.AfterRenderMethodName);
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreEqual(20, copiedPanel.Children.First().RenderPriority);
Assert.AreEqual(PanelType.Vertical, copiedPanel.Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod2", copiedPanel.Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod2", copiedPanel.Children.First().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.AreEqual(1, copiedPanel.Children.First().Children.Count);
Assert.AreSame(excelReport, copiedPanel.Children.First().Children.First().GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.AreSame(templateProcessor, copiedPanel.Children.First().Children.First().GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel.Children.First().Children.First()));
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First().Children.First()).Name, @"Parent_[0-9a-f]{32}_Child_ChildOfChild"));
Assert.IsInstanceOf <ExcelDataSourcePanel>(copiedPanel.Children.First().Children.First());
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.First().Children.First().Range.LastCell());
Assert.AreEqual(30, copiedPanel.Children.First().Children.First().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.First().Children.First().Type);
Assert.AreEqual(ShiftType.Row, copiedPanel.Children.First().Children.First().ShiftType);
Assert.AreEqual("BeforeRenderMethod3", copiedPanel.Children.First().Children.First().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod3", copiedPanel.Children.First().Children.First().AfterRenderMethodName);
Assert.AreEqual("BeforeDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).BeforeDataItemRenderMethodName);
Assert.AreEqual("AfterDataItemRenderMethodName", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).AfterDataItemRenderMethodName);
Assert.AreEqual("2,4", ((ExcelDataSourcePanel)copiedPanel.Children.First().Children.First()).GroupBy);
Assert.AreSame(copiedPanel.Children.First(), copiedPanel.Children.First().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 20, 20), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
IXLRange childRange1 = ws.Range(1, 1, 1, 4);
childRange1.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
IXLRange childRange2 = ws.Range(2, 1, 3, 4);
childOfChildRange = ws.Range(3, 1, 3, 4);
childOfChildRange.AddToNamed("ChildOfChild", XLScope.Worksheet);
namedChildOfChildRange = ws.NamedRange("ChildOfChild");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor),
new ExcelPanel(childRange2, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelNamedPanel(namedChildOfChildRange, excelReport, templateProcessor)
},
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod",
AfterRenderMethodName = "AfterRenderMethod",
},
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.AreSame(globalParent, copiedPanel.Parent);
Assert.AreEqual(2, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
Assert.IsInstanceOf <ExcelPanel>(copiedPanel.Children.Last());
Assert.IsNotInstanceOf <ExcelNamedPanel>(copiedPanel.Children.Last());
Assert.AreEqual(ws.Cell(6, 5), copiedPanel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Range.LastCell());
Assert.AreEqual(10, copiedPanel.Children.Last().RenderPriority);
Assert.AreEqual(PanelType.Horizontal, copiedPanel.Children.Last().Type);
Assert.AreEqual(ShiftType.NoShift, copiedPanel.Children.Last().ShiftType);
Assert.AreEqual("BeforeRenderMethod", copiedPanel.Children.Last().BeforeRenderMethodName);
Assert.AreEqual("AfterRenderMethod", copiedPanel.Children.Last().AfterRenderMethodName);
Assert.AreSame(copiedPanel, copiedPanel.Children.Last().Parent);
Assert.AreEqual(1, copiedPanel.Children.Last().Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.Last().Children.First()).Name, @"ChildOfChild_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(7, 5), copiedPanel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Children.Last().Children.First().Range.LastCell());
Assert.AreSame(copiedPanel.Children.Last(), copiedPanel.Children.Last().Children.First().Parent);
namedRange.Delete();
namedChildRange.Delete();
namedChildOfChildRange.Delete();
copiedPanel.Delete();
copiedPanel.Children.First().Delete();
copiedPanel.Children.Last().Children.First().Delete();
globalParent = new ExcelPanel(ws.Range(1, 1, 7, 7), excelReport, templateProcessor);
range = ws.Range(1, 1, 3, 4);
range.AddToNamed("Parent", XLScope.Worksheet);
namedRange = ws.NamedRange("Parent");
childRange = ws.Range(1, 1, 1, 4);
childRange.AddToNamed("Child", XLScope.Worksheet);
namedChildRange = ws.NamedRange("Child");
panel = new ExcelNamedPanel(namedRange, excelReport, templateProcessor)
{
Parent = globalParent,
Children = new List <IExcelPanel> {
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
},
};
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5));
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(1, copiedPanel.Children.Count);
Assert.IsTrue(Regex.IsMatch(((IExcelNamedPanel)copiedPanel.Children.First()).Name, @"Parent_[0-9a-f]{32}_Child"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(5, 8), copiedPanel.Children.First().Range.LastCell());
Assert.AreSame(copiedPanel, copiedPanel.Children.First().Parent);
copiedPanel = (IExcelNamedPanel)panel.Copy(ws.Cell(5, 5), false);
Assert.IsTrue(Regex.IsMatch(copiedPanel.Name, @"Parent_[0-9a-f]{32}"));
Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 8), copiedPanel.Range.LastCell());
Assert.IsNull(copiedPanel.Parent);
Assert.AreEqual(0, copiedPanel.Children.Count);
ExceptionAssert.Throws <ArgumentNullException>(() => panel.Copy(null));
//wb.SaveAs("test.xlsx");
}
private static bool GetContainingRanges(IXLNamedRange x, IXLRange xlRange)
{
return(x.Ranges.Select(GrowToMergedRanges)
.Where(r => r.Worksheet.Position == xlRange.Worksheet.Position && !r.Equals(xlRange))
.Any(xlRange.Contains));
}
public void TestMove()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
var excelReport = Substitute.For <object>();
var templateProcessor = Substitute.For <ITemplateProcessor>();
IXLRange range = ws.Range(1, 1, 4, 5);
range.AddToNamed("parentRange", XLScope.Worksheet);
IXLNamedRange namedParentRange = ws.NamedRange("parentRange");
IXLRange childRange1 = ws.Range(1, 1, 2, 5);
IXLRange childRange2 = ws.Range(3, 1, 4, 5);
childRange2.AddToNamed("childRange2", XLScope.Worksheet);
IXLNamedRange namedChildRange = ws.NamedRange("childRange2");
IXLRange childOfChildRange1 = ws.Range(2, 1, 2, 5);
childOfChildRange1.AddToNamed("childOfChildRange1", XLScope.Worksheet);
IXLNamedRange childOfChildNamedRange = ws.NamedRange("childOfChildRange1");
IXLRange childOfChildRange2 = ws.Range(4, 1, 4, 5);
var panel = new ExcelNamedPanel(namedParentRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childRange1, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelDataSourcePanel("fn:DataSource:Method()", childOfChildNamedRange, excelReport, templateProcessor)
}
},
new ExcelNamedPanel(namedChildRange, excelReport, templateProcessor)
{
Children = new List <IExcelPanel>
{
new ExcelPanel(childOfChildRange2, excelReport, templateProcessor)
}
},
}
};
IExcelPanel globalParent = new ExcelPanel(ws.Range(1, 1, 8, 10), excelReport, templateProcessor);
panel.Children.First().Children.First().Parent = panel.Children.First();
panel.Children.Last().Children.First().Parent = panel.Children.Last();
panel.Children.ToList().ForEach(c => c.Parent = panel);
panel.Parent = globalParent;
panel.Move(ws.Cell(5, 6));
Assert.AreEqual(ws.Cell(5, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Range.LastCell());
Assert.AreEqual("parentRange", ((IExcelNamedPanel)panel).Name);
Assert.AreSame(globalParent, panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(5, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(7, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(6, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(8, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
panel.Move(ws.Cell(6, 6));
Assert.AreEqual(ws.Cell(6, 6), panel.Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Range.LastCell());
Assert.IsNull(panel.Parent);
Assert.AreEqual(2, panel.Children.Count());
Assert.AreEqual(ws.Cell(6, 6), panel.Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.First());
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(ws.Cell(8, 6), panel.Children.Last().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Range.LastCell());
Assert.AreEqual("childRange2", ((IExcelNamedPanel)panel.Children.Last()).Name);
Assert.AreSame(panel, panel.Children.First().Parent);
Assert.AreEqual(1, panel.Children.First().Children.Count());
Assert.AreEqual(ws.Cell(7, 6), panel.Children.First().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(7, 10), panel.Children.First().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelDataSourcePanel>(panel.Children.First().Children.First());
Assert.AreEqual("childOfChildRange1", ((IExcelNamedPanel)panel.Children.First().Children.First()).Name);
Assert.AreSame(panel.Children.First(), panel.Children.First().Children.First().Parent);
Assert.AreEqual(1, panel.Children.Last().Children.Count());
Assert.AreEqual(ws.Cell(9, 6), panel.Children.Last().Children.First().Range.FirstCell());
Assert.AreEqual(ws.Cell(9, 10), panel.Children.Last().Children.First().Range.LastCell());
Assert.IsInstanceOf <ExcelPanel>(panel.Children.Last().Children.First());
Assert.IsNotInstanceOf <IExcelNamedPanel>(panel.Children.Last().Children.First());
Assert.AreSame(panel.Children.Last(), panel.Children.Last().Children.First().Parent);
Assert.AreEqual(3, ws.NamedRanges.Count());
//wb.SaveAs("test.xlsx");
}
