public void TestParseTotalCellsErrors()
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 1);
range.AddToNamed("Test", XLScope.Worksheet);
var templateProcessor = Substitute.For <ITemplateProcessor>();
templateProcessor.LeftTemplateBorder.Returns("<");
templateProcessor.RightTemplateBorder.Returns(">");
templateProcessor.MemberLabelSeparator.Returns("-");
templateProcessor.DataItemMemberLabel.Returns("d");
var report = new TestReport
{
TemplateProcessor = templateProcessor,
Workbook = wb
};
var panel = new ExcelTotalsPanel("Stub", ws.NamedRange("Test"), report, report.TemplateProcessor);
MethodInfo method = panel.GetType().GetMethod("ParseTotalCells", BindingFlags.Instance | BindingFlags.NonPublic);
ws.Cell(1, 1).Value = "<Sum(d-Val, fn1, fn2, fn3)>";
ExceptionAssert.ThrowsBaseException <InvalidOperationException>(() => method.Invoke(panel, null), "Aggregation function must have at least one but no more than 3 parameters");
}
public void TestAggregationPostOperation()
{
IList <Test> data = GetTestData();
var totalPanel = new ExcelTotalsPanel(data, Substitute.For <IXLNamedRange>(), new TestReportForAggregation(), new TestReport().TemplateProcessor);
IEnumerator enumerator = EnumeratorFactory.Create(data);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:TestColumn2")
{
PostProcessFunction = "PostSumOperation"
},
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:TestColumn3")
{
PostProcessFunction = "PostMinOperation"
},
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Custom, "di:TestColumn2")
{
CustomFunc = "CustomAggregation",
PostProcessFunction = "PostCustomAggregation",
},
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(22.033.ToString("F3"), totalCells[0].Result);
Assert.AreEqual("ABC", totalCells[1].Result);
Assert.AreEqual(24, totalCells[2].Result);
}
public void TestDoAggregationWithBadData()
{
IList <Test> data = GetTestData();
var totalPanel = new ExcelTotalsPanel(data, Substitute.For <IXLNamedRange>(), Substitute.For <object>(), new TestReport().TemplateProcessor);
IEnumerator enumerator = EnumeratorFactory.Create(data);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:TestColumn4"),
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
ExceptionAssert.ThrowsBaseException <RuntimeBinderException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }));
enumerator.Reset();
totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:BadColumn"),
};
ExceptionAssert.ThrowsBaseException <InvalidOperationException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
"For Min and Max aggregation functions data items must implement IComparable interface");
enumerator.Reset();
totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc((AggregateFunction)6, "di:TestColumn1"),
};
ExceptionAssert.ThrowsBaseException <NotSupportedException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
"Unsupportable aggregation function");
}
public void TestPanelWithNoData()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 5);
range.AddToNamed("Test", XLScope.Worksheet);
ws.Cell(1, 1).Value = "Plain text";
ws.Cell(1, 2).Value = "{Sum(di:Sum)}";
ws.Cell(1, 3).Value = "{ Custom(DI:Sum, CustomAggregation, PostAggregation) }";
ws.Cell(1, 4).Value = "{Min(di:Sum)}";
ws.Cell(1, 5).Value = "Text1 {count(di:Name)} Text2 {avg(di:Sum, , PostAggregationRound)} Text3 {Max(di:Sum)}";
var panel = new ExcelTotalsPanel("m:DataProvider:GetEmptyIEnumerable()", ws.NamedRange("Test"), report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(TotalsPanelRenderTest),
nameof(TestPanelWithNoData)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestSimplePanelExpansion()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange simplePanelRange = ws.Range(1, 1, 3, 5);
var simplePanel = new ExcelPanel(simplePanelRange, report, report.TemplateProcessor);
IXLRange dataPanelRange = ws.Range(2, 2, 2, 5);
dataPanelRange.AddToNamed("d_Data", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Name}";
ws.Cell(2, 3).Value = "{di:Date}";
ws.Cell(2, 4).Value = "{di:Sex}";
ws.Cell(2, 5).Value = "{di:Sum}";
var dataPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("d_Data"), report, report.TemplateProcessor)
{
Parent = simplePanel,
};
IXLRange totalsPanelRange = ws.Range(3, 2, 3, 5);
totalsPanelRange.AddToNamed("t_Totals", XLScope.Worksheet);
ws.Cell(3, 2).Value = "{Max(di:Name)}";
ws.Cell(3, 3).Value = "{Min(di:Date)}";
ws.Cell(3, 4).Value = "{Max(di:Sex)}";
ws.Cell(3, 5).Value = "{Sum(di:Sum)}";
var totalsPanel = new ExcelTotalsPanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("t_Totals"), report, report.TemplateProcessor)
{
Parent = simplePanel,
};
simplePanel.Children = new[] { dataPanel, totalsPanel };
simplePanel.Render();
Assert.AreEqual(ws.Range(1, 1, 5, 5), simplePanel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(PanelRenderTest),
nameof(TestSimplePanelExpansion)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
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 ExcelTotalsPanel(data, namedRange, excelReport, templateProcessor)
{
RenderPriority = 10,
Type = PanelType.Horizontal,
ShiftType = ShiftType.NoShift,
BeforeRenderMethodName = "BeforeRenderMethod",
AfterRenderMethodName = "AfterRenderMethod",
BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
AfterDataItemRenderMethodName = "AfterDataItemRenderMethodName",
GroupBy = "2,4",
};
ExcelTotalsPanel copiedPanel = (ExcelTotalsPanel)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");
}
public void TestPanelRenderWithParentContext()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange parentRange = ws.Range(2, 2, 3, 5);
parentRange.AddToNamed("ParentRange", XLScope.Worksheet);
IXLRange child1 = ws.Range(2, 2, 2, 5);
child1.AddToNamed("ChildRange1", XLScope.Worksheet);
IXLRange child2 = ws.Range(3, 2, 3, 5);
child2.AddToNamed("ChildRange2", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Field1}";
ws.Cell(2, 3).Value = "{di:Field2}";
ws.Cell(3, 2).Value = "{Count(di:Field1)}";
ws.Cell(3, 3).Value = "{Max(di:Field2)}";
ws.Cell(3, 4).Value = "{Max(di:parent:Sum)}";
ws.Cell(3, 5).Value = "{di:parent:Name}";
var parentPanel = new ExcelDataSourcePanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("ParentRange"), report, report.TemplateProcessor);
var childPanel1 = new ExcelDataSourcePanel("m:DataProvider:GetChildIEnumerable(di:Name)", ws.NamedRange("ChildRange1"), report, report.TemplateProcessor)
{
Parent = parentPanel,
};
var childPanel2 = new ExcelTotalsPanel("m:DataProvider:GetChildIEnumerable(di:Name)", ws.NamedRange("ChildRange2"), report, report.TemplateProcessor)
{
Parent = parentPanel
};
parentPanel.Children = new[] { childPanel1, childPanel2 };
parentPanel.Render();
Assert.AreEqual(ws.Range(2, 2, 9, 5), parentPanel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(TotalsPanelRenderTest),
nameof(TestPanelRenderWithParentContext)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestDoAggregationWithIntData()
{
int[] data = new DataProvider().GetIntData();
var totalPanel = new ExcelTotalsPanel(data, Substitute.For <IXLNamedRange>(), Substitute.For <object>(), new TestReport().TemplateProcessor);
IEnumerator enumerator = EnumeratorFactory.Create(data);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:di"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Avg, "di:di"),
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(55, totalCells[0].Result);
Assert.AreEqual(5.5, totalCells[1].Result);
}
public void TestCustomAggregation()
{
IList <Test> data = GetTestData();
var totalPanel = new ExcelTotalsPanel(data, Substitute.For <IXLNamedRange>(), new TestReportForAggregation(), new TestReport().TemplateProcessor);
IEnumerator enumerator = EnumeratorFactory.Create(data);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Custom, "di:TestColumn2")
{
CustomFunc = "CustomAggregation",
},
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(24.18125m, totalCells.First().Result);
enumerator.Reset();
totalCells.First().CustomFunc = null;
ExceptionAssert.ThrowsBaseException <InvalidOperationException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
"The custom type of aggregation is specified in the template but custom function is missing");
enumerator.Reset();
totalCells.First().CustomFunc = string.Empty;
ExceptionAssert.ThrowsBaseException <InvalidOperationException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
"The custom type of aggregation is specified in the template but custom function is missing");
enumerator.Reset();
totalCells.First().CustomFunc = " ";
ExceptionAssert.ThrowsBaseException <InvalidOperationException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
"The custom type of aggregation is specified in the template but custom function is missing");
enumerator.Reset();
totalCells.First().CustomFunc = "BadMethod";
ExceptionAssert.ThrowsBaseException <MethodNotFoundException>(() => method.Invoke(totalPanel, new object[] { enumerator, totalCells, null }),
$"Cannot find public instance method \"BadMethod\" in type \"{nameof(TestReportForAggregation)}\"");
}
public void TestPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 12);
range.AddToNamed("Test", XLScope.Worksheet);
ws.Cell(1, 1).Value = "Plain text";
ws.Cell(1, 2).Value = "{Sum(di:Sum)}";
ws.Cell(1, 3).Value = "{ Custom(DI:Sum, CustomAggregation, PostAggregation) }";
ws.Cell(1, 4).Value = "{Min(di:Sum)}";
ws.Cell(1, 5).Value = "Text1 {count(di:Name)} Text2 {avg(di:Sum, , PostAggregationRound)} {p:StrParam} {Max(di:Sum)} {Max(di:Sum)}";
ws.Cell(1, 6).Value = "{Mix(di:Sum)}";
ws.Cell(1, 7).FormulaA1 = "=SUM(B1:D1)";
ws.Cell(1, 8).FormulaA1 = "=ROW()";
ws.Cell(1, 9).Value = "{sf:Format(p:DateParam, yyyyMMdd)}";
ws.Cell(1, 10).Value = "{p:IntParam}";
ws.Cell(1, 11).Value = "{sf:Format(m:TestClassForTotals:Round(Min(di:Sum), 1), #,,0.0000)}";
ws.Cell(1, 12).Value = "Aggregation: {m:TestClassForTotals:Meth(avg(di:Sum, , PostAggregationRound), Max( di : Sum ))}. Date: {sf:Format(p:DateParam, dd.MM.yyyy)}";
ws.Cell(1, 13).Value = "{Sum(di:Sum)}";
var panel = new ExcelTotalsPanel("m:DataProvider:GetIEnumerable()", ws.NamedRange("Test"), report, report.TemplateProcessor)
{
BeforeRenderMethodName = "TestExcelTotalsPanelBeforeRender",
AfterRenderMethodName = "TestExcelTotalsPanelAfterRender",
};
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
ExcelAssert.AreWorkbooksContentEquals(TestHelper.GetExpectedWorkbook(nameof(TotalsPanelRenderTest),
nameof(TestPanelRender)), ws.Workbook);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestMakePanelsHierarchy()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
IXLRange panel1Range = ws.Range(1, 1, 4, 4);
IXLRange panel2Range = ws.Range(1, 1, 2, 4);
panel2Range.AddToNamed("Panel2", XLScope.Worksheet);
IXLRange panel3Range = ws.Range(2, 1, 2, 4);
panel3Range.AddToNamed("Panel3", XLScope.Workbook);
IXLRange panel4Range = ws.Range(5, 1, 6, 5);
IXLRange panel5Range = ws.Range(6, 1, 6, 5);
panel5Range.AddToNamed("Panel5", XLScope.Worksheet);
IXLRange panel6Range = ws.Range(3, 1, 4, 4);
IXLRange panel7Range = ws.Range(10, 10, 10, 10);
IXLRange panel8Range = ws.Range(8, 9, 9, 10);
panel8Range.AddToNamed("Panel8", XLScope.Worksheet);
var panel1 = new ExcelPanel(panel1Range, new object(), Substitute.For <ITemplateProcessor>());
var panel2 = new ExcelDataSourcePanel("Stub", ws.NamedRange("Panel2"), new object(),
Substitute.For <ITemplateProcessor>());
var panel3 = new ExcelDataSourcePanel("Stub", wb.NamedRange("Panel3"), new object(),
Substitute.For <ITemplateProcessor>());
var panel4 = new ExcelPanel(panel4Range, new object(), Substitute.For <ITemplateProcessor>());
var panel5 = new ExcelDataSourceDynamicPanel("Stub", ws.NamedRange("Panel5"), new object(),
Substitute.For <ITemplateProcessor>());
var panel6 = new ExcelPanel(panel6Range, new object(), Substitute.For <ITemplateProcessor>());
var panel7 = new ExcelPanel(panel7Range, new object(), Substitute.For <ITemplateProcessor>());
var panel8 = new ExcelTotalsPanel("Stub", ws.NamedRange("Panel8"), new object(),
Substitute.For <ITemplateProcessor>());
IDictionary <string, (IExcelPanel, string)> panelsFlatView = new Dictionary <string, (IExcelPanel, string)>
{
public void TestDoAggregationWithEmptyData()
{
var data = new List <Test>();
var totalPanel = new ExcelTotalsPanel(data, Substitute.For <IXLNamedRange>(), Substitute.For <object>(), Substitute.For <ITemplateProcessor>());
IEnumerator enumerator = EnumeratorFactory.Create(data);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Count, "TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Avg, "TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Max, "TestColumn1"),
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(0, totalCells[0].Result);
Assert.AreEqual(0, totalCells[1].Result);
Assert.AreEqual(0, totalCells[2].Result);
Assert.IsNull(totalCells[3].Result);
Assert.IsNull(totalCells[4].Result);
}
public void TestParseTotalCells()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 7);
range.AddToNamed("Test", XLScope.Worksheet);
ws.Cell(1, 1).Value = "Plain text";
ws.Cell(1, 2).Value = "{Sum(di:Amount)}";
ws.Cell(1, 3).Value = "{ Custom(DI:Amount, CustomFunc) }";
ws.Cell(1, 4).Value = "{Min(di:Value, CustomFunc, PostFunc)}";
ws.Cell(1, 5).Value = "Text {count(di:Number)} {p:Text} {AVG( di:Value, , PostFunc )} {Text} Text {Max(di:Val)} {Min(val)}";
ws.Cell(1, 6).Value = "{Mix(di:Amount)}";
ws.Cell(1, 7).Value = "Text {Plain Text} Sum(di:Count) {sf:Format(Sum(di:Amount,,PostAggregation), #,,0.00)} {p:Text} {Max(di:Count)} {m:Meth(1, Avg( di : Value ), Min(di:Amount, CustomAggregation, PostAggregation), \"Str\"} {sv:RenderDate} m:Meth2(Avg(di:Value))";
ws.Cell(1, 8).Value = "{Sum(di:Amount)}";
var panel = new ExcelTotalsPanel("Stub", ws.NamedRange("Test"), report, report.TemplateProcessor);
MethodInfo method = panel.GetType().GetMethod("ParseTotalCells", BindingFlags.Instance | BindingFlags.NonPublic);
var result = (IDictionary <IXLCell, IList <ExcelTotalsPanel.ParsedAggregationFunc> >)method.Invoke(panel, null);
Assert.AreEqual(5, result.Count);
Assert.AreEqual("Plain text", ws.Cell(1, 1).Value);
Assert.AreEqual("{Mix(di:Amount)}", ws.Cell(1, 6).Value);
Assert.AreEqual("{Sum(di:Amount)}", ws.Cell(1, 8).Value);
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 2).Value.ToString(), @"{di:AggFunc_[0-9a-f]{32}}"));
Assert.AreEqual(1, result[ws.Cell(1, 2)].Count);
Assert.AreEqual(AggregateFunction.Sum, result[ws.Cell(1, 2)].First().AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 2)].First().ColumnName);
Assert.IsNull(result[ws.Cell(1, 2)].First().CustomFunc);
Assert.IsNull(result[ws.Cell(1, 2)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 2)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 2)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 3).Value.ToString(), @"{ di:AggFunc_[0-9a-f]{32} }"));
Assert.AreEqual(1, result[ws.Cell(1, 3)].Count);
Assert.AreEqual(AggregateFunction.Custom, result[ws.Cell(1, 3)].First().AggregateFunction);
Assert.AreEqual("DI:Amount", result[ws.Cell(1, 3)].First().ColumnName);
Assert.AreEqual("CustomFunc", result[ws.Cell(1, 3)].First().CustomFunc);
Assert.IsNull(result[ws.Cell(1, 3)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 3)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 3)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 4).Value.ToString(), @"{di:AggFunc_[0-9a-f]{32}}"));
Assert.AreEqual(1, result[ws.Cell(1, 4)].Count);
Assert.AreEqual(AggregateFunction.Min, result[ws.Cell(1, 4)].First().AggregateFunction);
Assert.AreEqual("di:Value", result[ws.Cell(1, 4)].First().ColumnName);
Assert.AreEqual("CustomFunc", result[ws.Cell(1, 4)].First().CustomFunc);
Assert.AreEqual("PostFunc", result[ws.Cell(1, 4)].First().PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 4)].First().Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 4)].First().UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 5).Value.ToString(), @"Text {di:AggFunc_[0-9a-f]{32}} {p:Text} {di:AggFunc_[0-9a-f]{32}} {Text} Text {di:AggFunc_[0-9a-f]{32}} {Min\(val\)}"));
Assert.AreEqual(3, result[ws.Cell(1, 5)].Count);
Assert.AreEqual(AggregateFunction.Count, result[ws.Cell(1, 5)][0].AggregateFunction);
Assert.AreEqual("di:Number", result[ws.Cell(1, 5)][0].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][0].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 5)][0].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][0].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][0].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Avg, result[ws.Cell(1, 5)][1].AggregateFunction);
Assert.AreEqual("di:Value", result[ws.Cell(1, 5)][1].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][1].CustomFunc);
Assert.AreEqual("PostFunc", result[ws.Cell(1, 5)][1].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][1].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][1].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Max, result[ws.Cell(1, 5)][2].AggregateFunction);
Assert.AreEqual("di:Val", result[ws.Cell(1, 5)][2].ColumnName);
Assert.IsNull(result[ws.Cell(1, 5)][2].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 5)][2].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 5)][2].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 5)][2].UniqueName, out _));
Assert.IsTrue(Regex.IsMatch(ws.Cell(1, 7).Value.ToString(), @"Text {Plain Text} Sum\(di:Count\) {sf:Format\(di:AggFunc_[0-9a-f]{32}, #,,0.00\)} {p:Text} {di:AggFunc_[0-9a-f]{32}} {m:Meth\(1, di:AggFunc_[0-9a-f]{32}, di:AggFunc_[0-9a-f]{32}, ""Str""} {sv:RenderDate} m:Meth2\(Avg\(di:Value\)\)"));
Assert.AreEqual(4, result[ws.Cell(1, 7)].Count);
Assert.AreEqual(AggregateFunction.Sum, result[ws.Cell(1, 7)][0].AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 7)][0].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][0].CustomFunc);
Assert.AreEqual("PostAggregation", result[ws.Cell(1, 7)][0].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][0].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][0].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Max, result[ws.Cell(1, 7)][1].AggregateFunction);
Assert.AreEqual("di:Count", result[ws.Cell(1, 7)][1].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][1].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 7)][1].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][1].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][1].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Avg, result[ws.Cell(1, 7)][2].AggregateFunction);
Assert.AreEqual("di : Value", result[ws.Cell(1, 7)][2].ColumnName);
Assert.IsNull(result[ws.Cell(1, 7)][2].CustomFunc);
Assert.IsNull(result[ws.Cell(1, 7)][2].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][2].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][2].UniqueName, out _));
Assert.AreEqual(AggregateFunction.Min, result[ws.Cell(1, 7)][3].AggregateFunction);
Assert.AreEqual("di:Amount", result[ws.Cell(1, 7)][3].ColumnName);
Assert.AreEqual("CustomAggregation", result[ws.Cell(1, 7)][3].CustomFunc);
Assert.AreEqual("PostAggregation", result[ws.Cell(1, 7)][3].PostProcessFunction);
Assert.IsNull(result[ws.Cell(1, 7)][3].Result);
Assert.IsTrue(Guid.TryParse(result[ws.Cell(1, 7)][3].UniqueName, out _));
}
public void TestDoAggregation()
{
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("TestColumn1", typeof(int)));
dataTable.Columns.Add(new DataColumn("TestColumn2", typeof(decimal)));
dataTable.Columns.Add(new DataColumn("TestColumn3", typeof(string)));
dataTable.Columns.Add(new DataColumn("TestColumn4", typeof(bool)));
dataTable.Rows.Add(3, 20.7m, "abc", false);
dataTable.Rows.Add(1, 10.5m, "jkl", true);
dataTable.Rows.Add(null, null, null, null);
dataTable.Rows.Add(2, 30.9m, "def", false);
var totalPanel = new ExcelTotalsPanel(dataTable, Substitute.For <IXLNamedRange>(), Substitute.For <object>(), new TestReport().TemplateProcessor);
IEnumerator enumerator = EnumeratorFactory.Create(dataTable);
IList <ExcelTotalsPanel.ParsedAggregationFunc> totalCells = new List <ExcelTotalsPanel.ParsedAggregationFunc>
{
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:TestColumn2"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:TestColumn3"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Count, "di:TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Count, "di:TestColumn3"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Avg, "di:TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Avg, "di:TestColumn2"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Max, "di:TestColumn1"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:TestColumn2"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Max, "di:TestColumn2"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:TestColumn3"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Max, "di:TestColumn3"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Min, "di:TestColumn4"),
new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Max, "di:TestColumn4"),
};
MethodInfo method = totalPanel.GetType().GetMethod("DoAggregation", BindingFlags.Instance | BindingFlags.NonPublic);
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(6, totalCells[0].Result);
Assert.AreEqual(62.1m, totalCells[1].Result);
Assert.AreEqual("abcjkldef", totalCells[2].Result);
Assert.AreEqual(4, totalCells[3].Result);
Assert.AreEqual(4, totalCells[4].Result);
Assert.AreEqual((double)6 / 4, totalCells[5].Result);
Assert.AreEqual(62.1 / 4, totalCells[6].Result);
Assert.AreEqual(1, totalCells[7].Result);
Assert.AreEqual(3, totalCells[8].Result);
Assert.AreEqual(10.5m, totalCells[9].Result);
Assert.AreEqual(30.9m, totalCells[10].Result);
Assert.AreEqual("abc", totalCells[11].Result);
Assert.AreEqual("jkl", totalCells[12].Result);
Assert.AreEqual(false, totalCells[13].Result);
Assert.AreEqual(true, totalCells[14].Result);
// Reset all results before next test
foreach (ExcelTotalsPanel.ParsedAggregationFunc totalCell in totalCells)
{
totalCell.Result = null;
}
IList <Test> data = GetTestData();
enumerator = EnumeratorFactory.Create(data);
totalCells.Add(new ExcelTotalsPanel.ParsedAggregationFunc(AggregateFunction.Sum, "di:Result.Amount"));
method.Invoke(totalPanel, new object[] { enumerator, totalCells, null });
Assert.AreEqual(6, totalCells[0].Result);
Assert.AreEqual(62.1m, totalCells[1].Result);
Assert.AreEqual("abcjkldef", totalCells[2].Result);
Assert.AreEqual(4, totalCells[3].Result);
Assert.AreEqual(4, totalCells[4].Result);
Assert.AreEqual((double)6 / 4, totalCells[5].Result);
Assert.AreEqual(62.1 / 4, totalCells[6].Result);
Assert.AreEqual(1, totalCells[7].Result);
Assert.AreEqual(3, totalCells[8].Result);
Assert.AreEqual(10.5m, totalCells[9].Result);
Assert.AreEqual(30.9m, totalCells[10].Result);
Assert.AreEqual("abc", totalCells[11].Result);
Assert.AreEqual("jkl", totalCells[12].Result);
Assert.AreEqual(false, totalCells[13].Result);
Assert.AreEqual(true, totalCells[14].Result);
Assert.AreEqual(410.59m, totalCells[15].Result);
}
// Rendering speed test
//[Test]
public void TestPanelRenderSpeedWithHierarchy()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 4, 6);
range.AddToNamed("ParentRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Name}";
ws.Cell(2, 3).Value = "{di:Date}";
ws.Cell(2, 4).Value = "{di:Sum}";
ws.Cell(2, 5).Value = "{di:Contacts.Phone}";
ws.Cell(2, 6).Value = "{di:Contacts.Fax}";
IXLRange child = ws.Range(3, 2, 3, 6);
child.AddToNamed("ChildRange", XLScope.Worksheet);
ws.Cell(3, 2).Value = "{di:Field1}";
ws.Cell(3, 3).Value = "{di:Field2}";
IXLRange total = ws.Range(4, 2, 4, 6);
total.AddToNamed("TotalRange", XLScope.Worksheet);
ws.Cell(4, 5).Value = "{Max(di:Field1)}";
ws.Cell(4, 6).Value = "{Min(di:Field2)}";
const int dataCount = 100;
IList <TestItem> data = new List <TestItem>(dataCount);
for (int i = 0; i < dataCount; i++)
{
data.Add(new TestItem($"Name_{i}", DateTime.Now.AddHours(1), i + 1, new Contacts($"Phone_{i}", $"Fax_{i}")));
}
var parentPanel = new ExcelDataSourcePanel(data, ws.NamedRange("ParentRange"), report, report.TemplateProcessor)
{
//ShiftType = ShiftType.Row,
//ShiftType = ShiftType.NoShift,
};
//var childPanel = new ExcelDataSourcePanel("m:DataProvider:GetChildrenProportionally(di:di)", ws.NamedRange("ChildRange"), report, report.TemplateProcessor)
var childPanel = new ExcelDataSourcePanel("m:DataProvider:GetChildrenRandom(10, 20)", ws.NamedRange("ChildRange"), report, report.TemplateProcessor)
{
//ShiftType = ShiftType.Row,
//ShiftType = ShiftType.NoShift,
Parent = parentPanel,
};
//var totalPanel = new ExcelTotalsPanel("m:DataProvider:GetChildrenProportionally(di:di)", ws.NamedRange("TotalRange"), report, report.TemplateProcessor)
var totalPanel = new ExcelTotalsPanel("m:DataProvider:GetChildrenRandom(10, 20)", ws.NamedRange("TotalRange"), report, report.TemplateProcessor)
{
//ShiftType = ShiftType.Row,
//ShiftType = ShiftType.NoShift,
Parent = parentPanel,
};
parentPanel.Children.Add(childPanel);
parentPanel.Children.Add(totalPanel);
Stopwatch sw = Stopwatch.StartNew();
parentPanel.Render();
sw.Stop();
//Stopwatch sw2 = Stopwatch.StartNew();
//report.Workbook.SaveAs("test.xlsx");
//sw2.Stop();
}