public void TestRenderEmptyDataSet()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 2, 6);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:Id}";
ws.Cell(2, 3).Value = "{di:Name}";
ws.Cell(2, 4).Value = "{di:IsVip}";
ws.Cell(2, 5).Value = "{di:Description}";
ws.Cell(2, 6).Value = "{di:Type}";
var panel = new ExcelDataSourcePanel("m:DataProvider:GetEmptyDataSet()", ws.NamedRange("TestRange"), report, report.TemplateProcessor);
panel.Render();
Assert.IsNull(panel.ResultRange);
Assert.AreEqual(0, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestDynamicPanelBeforeRenderEvent()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 4, 2);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{Headers}";
ws.Cell(3, 2).Value = "{Data}";
ws.Cell(4, 2).Value = "{Totals}";
var panel = new ExcelDataSourceDynamicPanel("m:DataProvider:GetAllCustomersDataSet()", ws.NamedRange("TestRange"), report, report.TemplateProcessor)
{
BeforeRenderMethodName = "TestExcelDynamicPaneBeforeRender",
};
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(3, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("CanceledHeaders", ws.Cell(2, 2).Value);
Assert.AreEqual("CanceledData", ws.Cell(3, 2).Value);
Assert.AreEqual("CanceledTotals", ws.Cell(4, 2).Value);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestPanelRenderEvents()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 1, 2);
ws.Cell(1, 1).Value = "{p:StrParam}";
ws.Cell(1, 2).Value = "{p:IntParam}";
var panel = new ExcelPanel(range, report, report.TemplateProcessor)
{
BeforeRenderMethodName = "TestExcelPanelBeforeRender",
AfterRenderMethodName = "TestExcelPanelAfterRender",
};
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(2, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.IsTrue((bool)ws.Cell(1, 1).Value);
Assert.AreEqual(11d, ws.Cell(1, 2).Value);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestCellsUsedWithoutFormulas()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Test");
ws.Cell(70, 100).Value = "Value";
ws.Cell(10, 15).Value = "Value2";
ws.Cell(10, 15).Active = true;
ws.Cell(10, 20).FormulaA1 = "=ROW()";
ws.Cell(10, 30).FormulaR1C1 = "=COLUMN()";
ws.Cell(20, 30).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Cell(25, 30).Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
ws.Cell(25, 30).FormulaA1 = "=A1+B2";
Assert.AreEqual(5, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual(6, ws.CellsUsed(XLCellsUsedOptions.All).Count());
Assert.AreEqual(2, ws.CellsUsedWithoutFormulas().Count());
Assert.AreEqual(3, ws.CellsUsedWithoutFormulas(XLCellsUsedOptions.All).Count());
Assert.AreEqual(2, ws.CellsUsed(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count());
Assert.AreEqual(1, ws.CellsUsedWithoutFormulas(c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count());
Assert.AreEqual(3, ws.CellsUsed(XLCellsUsedOptions.All, c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count());
Assert.AreEqual(2, ws.CellsUsedWithoutFormulas(XLCellsUsedOptions.All, c => c.Active || c.Style.Border.TopBorder == XLBorderStyleValues.Thin).Count());
}
public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual)
{
if (expected == actual)
{
return;
}
Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed");
IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All);
IXLCell actualFirstCellUsed = actual.FirstCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed");
IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All);
IXLCell actualLastCellUsed = actual.LastCellUsed(XLCellsUsedOptions.All);
Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed");
IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed);
foreach (IXLCell expectedCell in range.Cells())
{
IXLCell actualCell = actual.Cell(expectedCell.Address);
if (expectedCell.HasFormula)
{
Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed.");
//// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match
//Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed.");
Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed.");
}
else
{
Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed.");
}
Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed.");
Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed.");
AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed.");
AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed.");
AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed.");
AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed.");
}
AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges);
AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges);
ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed.");
}
public void TestRenderEmptyDictionary()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range1 = ws.Range(2, 2, 4, 2);
range1.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{Headers}";
ws.Cell(3, 2).Value = "{Data}";
ws.Cell(4, 2).Value = "{Totals}";
IXLRange range2 = ws.Range(7, 2, 9, 2);
range2.AddToNamed("TestRange2", XLScope.Worksheet);
ws.Cell(7, 2).Value = "{Headers}";
ws.Cell(8, 2).Value = "{Data}";
ws.Cell(9, 2).Value = "{Totals}";
IDictionary <string, object> data1 = new Dictionary <string, object>();
var panel1 = new ExcelDataSourceDynamicPanel(data1, ws.NamedRange("TestRange"), report, report.TemplateProcessor);
panel1.Render();
Assert.AreEqual(ws.Range(2, 2, 3, 3), panel1.ResultRange);
IEnumerable <KeyValuePair <string, object> > data2 = new List <KeyValuePair <string, object> >();
var panel2 = new ExcelDataSourceDynamicPanel(data2, ws.NamedRange("TestRange2"), report, report.TemplateProcessor);
panel2.Render();
Assert.AreEqual(ws.Range(6, 2, 7, 3), panel2.ResultRange);
Assert.AreEqual(4, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("Key", ws.Cell(2, 2).Value);
Assert.AreEqual("Value", ws.Cell(2, 3).Value);
Assert.AreEqual("Key", ws.Cell(6, 2).Value);
Assert.AreEqual("Value", ws.Cell(6, 3).Value);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestRenderNullItem()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range1 = ws.Range(2, 2, 4, 2);
range1.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{Headers}";
ws.Cell(3, 2).Value = "{Data}";
ws.Cell(4, 2).Value = "{Totals}";
var panel = new ExcelDataSourceDynamicPanel("m:DataProvider:GetNullItem()", ws.NamedRange("TestRange"), report, report.TemplateProcessor);
panel.Render();
Assert.IsNull(panel.ResultRange);
Assert.AreEqual(0, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
}
public void TestRenderEmptyDictionaryEnumerable()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 4, 2);
range.AddToNamed("TestRange1", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{Headers}";
ws.Cell(3, 2).Value = "{Data}";
ws.Cell(4, 2).Value = "{Totals}";
var panel = new ExcelDataSourceDynamicPanel(new List <IDictionary <string, decimal> >(), ws.NamedRange("TestRange1"), report, report.TemplateProcessor);
panel.Render();
Assert.IsNull(panel.ResultRange);
Assert.AreEqual(0, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
//report.Workbook.SaveAs("test.xlsx");
}
public void TestCancelPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(2, 2, 2, 2);
range.AddToNamed("TestRange", XLScope.Worksheet);
ws.Cell(2, 2).Value = "{di:di}";
var panel = new ExcelDataSourcePanel(new[] { 1, 2, 3, 4 }, ws.NamedRange("TestRange"), report, report.TemplateProcessor)
{
BeforeRenderMethodName = "CancelPanelRender",
};
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(1, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("{di:di}", ws.Cell(2, 2).Value);
//report.Workbook.SaveAs("test.xlsx");
}
public static XLWorkbook GetExcel(TableModel[] models)
{
XLWorkbook wb = new XLWorkbook();
IXLWorksheet sheet = wb.Worksheets.Add("Sheet1");
// ЗАГОЛОВКИ
IXLRange range = sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "I"));
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#fcecdc"));
// жирный шрифт
range.Style.Font.SetBold();
// выравнивание
range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
range.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// ширина заголовков
double factor = 1.145; // множитель (чтобы ширина была как у оригинального excel)
sheet.Column("A").Width = 24.13 * factor;
sheet.Column("B").Width = 33.63 * factor;
sheet.Column("C").Width = 11.88 * factor;
sheet.Column("D").Width = 9.25 * factor;
sheet.Column("E").Width = 10.5 * factor;
sheet.Column("F").Width = 52.75 * factor;
sheet.Column("G").Width = 8.75 * factor;
sheet.Column("H").Width = 8.75 * factor;
sheet.Column("I").Width = 33.25 * factor;
// подписи у заголовков и объединение ячеек для заголовков
sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "A"))
.Merge().Value = "Component type";
sheet.Range(sheet.Cell(1, "B"), sheet.Cell(2, "B"))
.Merge().Value = "Name";
sheet.Range(sheet.Cell(1, "C"), sheet.Cell(2, "C"))
.Merge().Value = "Nominal";
sheet.Range(sheet.Cell(1, "D"), sheet.Cell(2, "D"))
.Merge().Value = "Deviation";
sheet.Range(sheet.Cell(1, "E"), sheet.Cell(2, "E"))
.Merge().Value = "Case type";
sheet.Range(sheet.Cell(1, "F"), sheet.Cell(2, "F"))
.Merge().Value = "Comment";
sheet.Range(sheet.Cell(1, "G"), sheet.Cell(1, "H"))
.Merge().Value = "quantity";
sheet.Cell(2, "G").Value = "00";
sheet.Cell(2, "H").Value = "01";
sheet.Range(sheet.Cell(1, "I"), sheet.Cell(2, "I"))
.Merge().Value = "Remark:";
// границы у заголовков
XLBorderStyleValues styleBorder = XLBorderStyleValues.Medium;
range.Style.Border.SetOutsideBorder(styleBorder);
range.Style.Border.SetInsideBorder(styleBorder);
#region ЛОГИКА ВЫВОДА ДАННЫХ
int currentRow = 3;
IXLCells cellRange;
for (int i = 0; i < models.Length; i++)
{
TableModel tm = models[i];
#region ЛОГИКА ОТДЕЛЕНИЯ ГРУПП КОМПОНЕНТОВ
// чтобы не выйти за пределы массива
if (i > 0 && tm.ComponentType != models[i - 1].ComponentType)
{
range = sheet.Range(sheet.Cell(currentRow, "A"), sheet.Cell(currentRow, "i")).Merge();
// границы
range.Style.Border.SetOutsideBorder(XLBorderStyleValues.Medium);
// заливка
range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#f8f4f4"));
currentRow++;
}
#endregion
cellRange = sheet.Row(currentRow).Cells("A", "I");
// выравнивание текста
cellRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
cellRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
// границы
styleBorder = XLBorderStyleValues.Thin;
cellRange.Style.Border.SetInsideBorder(styleBorder);
cellRange.Style.Border.SetOutsideBorder(styleBorder);
sheet.Cell(currentRow, "A").Value = tm.ComponentType;
sheet.Cell(currentRow, "B").Value = tm.Name;
sheet.Cell(currentRow, "C").Value = tm.Nominal;
sheet.Cell(currentRow, "D").Value = tm.Deviation;
sheet.Cell(currentRow, "E").SetValue(tm.CaseType);
sheet.Cell(currentRow, "F").Value = tm.Comment;
sheet.Cell(currentRow, "G").Value = tm.Quanity.ZeroZero;
sheet.Cell(currentRow, "H").Value = tm.Quanity.ZeroOne;
currentRow++;
}
#endregion
// СТИЛИ ДЛЯ ВСЕХ ЯЧЕЕК
cellRange = sheet.CellsUsed();
// шрифт
cellRange.Style.Font.SetFontName("Arial Cyr");
cellRange.Style.Font.SetFontSize(9);
return(wb);
}
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");
}
protected bool WorksheetsAreEqual(IXLWorksheet expected, IXLWorksheet actual, out IList <string> messages)
{
messages = new List <string>();
if (expected.Name != actual.Name)
{
messages.Add("Worksheet names differ");
}
if (expected.RangeUsed()?.RangeAddress?.ToString() != actual.RangeUsed()?.RangeAddress?.ToString())
{
messages.Add("Used ranges differ");
}
if (expected.Style.ToString() != actual.Style.ToString())
{
messages.Add("Worksheet styles differ");
}
if (!expected.PageSetup.RowBreaks.All(actual.PageSetup.RowBreaks.Contains) ||
expected.PageSetup.RowBreaks.Count != actual.PageSetup.RowBreaks.Count)
{
messages.Add("PageBreaks differ");
}
if (expected.PageSetup.PagesTall != actual.PageSetup.PagesTall)
{
messages.Add("PagesTall differ");
}
if (expected.PageSetup.PagesWide != actual.PageSetup.PagesWide)
{
messages.Add("PagesWide differ");
}
if (expected.PageSetup.PageOrientation != actual.PageSetup.PageOrientation)
{
messages.Add("PageOrientation differ");
}
if (expected.PageSetup.PageOrder != actual.PageSetup.PageOrder)
{
messages.Add("PageOrder differ");
}
var usedCells = expected.CellsUsed(XLCellsUsedOptions.All).Select(c => c.Address)
.Concat(actual.CellsUsed(XLCellsUsedOptions.All).Select(c => c.Address))
.Distinct();
foreach (var address in usedCells)
{
var expectedCell = expected.Cell(address);
var actualCell = actual.Cell(address);
bool cellsAreEqual = true;
if (actualCell.Value?.ToString() != expectedCell.Value?.ToString())
{
messages.Add($"Cell values are not equal starting from {address}");
cellsAreEqual = false;
}
if (!string.Equals(actualCell.FormulaA1, expectedCell.FormulaA1, StringComparison.InvariantCultureIgnoreCase))
{
messages.Add($"Cell formulae are not equal starting from {address}");
cellsAreEqual = false;
}
if (!expectedCell.HasFormula && actualCell.DataType != expectedCell.DataType)
{
messages.Add($"Cell data types are not equal starting from {address}");
cellsAreEqual = false;
}
if (expectedCell.Style.ToString() != actualCell.Style.ToString())
{
messages.Add($"Cell style are not equal starting from {address}");
cellsAreEqual = false;
}
if (!cellsAreEqual)
{
break; // we don't need thousands of messages
}
}
if (expected.MergedRanges.Count() != actual.MergedRanges.Count())
{
messages.Add("Merged ranges counts differ");
}
else
{
var expectedRanges = expected.MergedRanges
.OrderBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ToList();
var actualRanges = actual.MergedRanges
.OrderBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ToList();
for (int i = 0; i < expectedRanges.Count(); i++)
{
var expectedMr = expectedRanges.ElementAt(i);
var actualMr = actualRanges.ElementAt(i);
if (expectedMr.RangeAddress.ToString() != actualMr.RangeAddress.ToString())
{
messages.Add($"Merged ranges differ starting from {expectedMr.RangeAddress}");
break;
}
}
}
if (expected.ConditionalFormats.Count() != actual.ConditionalFormats.Count())
{
messages.Add("Conditional format counts differ");
}
else
{
var expectedFormats = expected.ConditionalFormats
.OrderBy(r => r.Range.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.Range.RangeAddress.FirstAddress.RowNumber)
.ToList();
var actualFormats = actual.ConditionalFormats
.OrderBy(r => r.Range.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.Range.RangeAddress.FirstAddress.RowNumber)
.ToList();
for (int i = 0; i < expectedFormats.Count(); i++)
{
var expectedCf = expectedFormats.ElementAt(i);
var actualCf = actualFormats.ElementAt(i);
if (expectedCf.Range.RangeAddress.ToString() != actualCf.Range.RangeAddress.ToString())
{
messages.Add($"Conditional formats actual {actualCf.Range.RangeAddress}, but expected {expectedCf.Range.RangeAddress}.");
}
if (expectedCf.Style.ToString() != actualCf.Style.ToString())
{
messages.Add($"Conditional formats at {actualCf.Range.RangeAddress} have different styles");
}
}
}
return(!messages.Any());
}
public void TestHierarchicalPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range1 = ws.Range(1, 1, 10, 8);
ws.Cell(1, 1).Value = "Panel1: {p:IntParam}";
ws.Cell(10, 8).Value = "Panel1: {p:IntParam}";
var panel1 = new ExcelPanel(range1, report, report.TemplateProcessor);
IXLRange range2 = ws.Range(3, 1, 8, 2);
ws.Cell(3, 1).Value = "Panel2: {p:IntParam}";
var panel2 = new ExcelPanel(range2, report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range3 = ws.Range(1, 3, 6, 5);
ws.Cell(1, 3).Value = "Panel3: {p:IntParam}";
range3.AddToNamed("NamedPanel1");
var panel3 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel1"), report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range4 = ws.Range(5, 6, 9, 8);
ws.Cell(5, 6).Value = "Panel4: {p:IntParam}";
range4.AddToNamed("NamedPanel2", XLScope.Worksheet);
var panel4 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel2"), report, report.TemplateProcessor)
{
Parent = panel1
};
IXLRange range5 = ws.Range(4, 1, 5, 2);
ws.Cell(4, 1).Value = "Panel5: {p:IntParam}";
var panel5 = new ExcelPanel(range5, report, report.TemplateProcessor)
{
Parent = panel2
};
IXLRange range6 = ws.Range(6, 1, 8, 2);
ws.Cell(6, 1).Value = "Panel6: {p:IntParam}";
range6.AddToNamed("NamedPanel3");
var panel6 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel3"), report, report.TemplateProcessor)
{
Parent = panel2
};
IXLRange range7 = ws.Range(6, 1, 6, 2);
ws.Cell(6, 2).Value = "Panel7: {p:IntParam}";
var panel7 = new ExcelPanel(range7, report, report.TemplateProcessor)
{
Parent = panel6
};
IXLRange range8 = ws.Range(7, 1, 7, 2);
ws.Cell(7, 2).Value = "Panel8: {p:IntParam}";
range8.AddToNamed("NamedPanel4", XLScope.Worksheet);
var panel8 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel4"), report, report.TemplateProcessor)
{
Parent = panel6
};
IXLRange range9 = ws.Range(1, 3, 6, 5);
ws.Cell(6, 5).Value = "Panel9: {p:IntParam}";
range9.AddToNamed("NamedPanel5", XLScope.Worksheet);
var panel9 =
new ExcelNamedPanel(ws.NamedRange("NamedPanel5"), report, report.TemplateProcessor)
{
Parent = panel3
};
IXLRange range10 = ws.Range(3, 3, 4, 5);
ws.Cell(4, 5).Value = "Panel10: {p:IntParam}";
var panel10 = new ExcelPanel(range10, report, report.TemplateProcessor)
{
Parent = panel9
};
IXLRange range11 = ws.Range(5, 6, 9, 8);
ws.Cell(6, 6).Value = "Panel11: {p:IntParam}";
var panel11 = new ExcelPanel(range11, report, report.TemplateProcessor)
{
Parent = panel4
};
IXLRange range12 = ws.Range(8, 6, 9, 8);
ws.Cell(9, 8).Value = "Panel12: {p:IntParam}";
range12.AddToNamed("NamedPanel6");
var panel12 =
new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel6"), report, report.TemplateProcessor)
{
Parent = panel11
};
panel1.Children = new[] { panel2, panel3, panel4 };
panel2.Children = new[] { panel5, panel6 };
panel3.Children = new[] { panel9 };
panel6.Children = new[] { panel7, panel8 };
panel4.Children = new[] { panel11 };
panel9.Children = new[] { panel10 };
panel11.Children = new[] { panel12 };
ws.Cell(11, 8).Value = "Outside panel: {p:IntParam}";
panel1.Render();
Assert.AreEqual(range1, panel1.ResultRange);
Assert.AreEqual(14, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("Panel1: 10", ws.Cell(1, 1).Value);
Assert.AreEqual("Panel1: 10", ws.Cell(10, 8).Value);
Assert.AreEqual("Panel2: 10", ws.Cell(3, 1).Value);
Assert.AreEqual("Panel3: 10", ws.Cell(1, 3).Value);
Assert.AreEqual("Panel4: 10", ws.Cell(5, 6).Value);
Assert.AreEqual("Panel5: 10", ws.Cell(4, 1).Value);
Assert.AreEqual("Panel6: 10", ws.Cell(6, 1).Value);
Assert.AreEqual("Panel7: 10", ws.Cell(6, 2).Value);
Assert.AreEqual("Panel8: 10", ws.Cell(7, 2).Value);
Assert.AreEqual("Panel9: 10", ws.Cell(6, 5).Value);
Assert.AreEqual("Panel10: 10", ws.Cell(4, 5).Value);
Assert.AreEqual("Panel11: 10", ws.Cell(6, 6).Value);
Assert.AreEqual("Panel12: 10", ws.Cell(9, 8).Value);
Assert.AreEqual("Outside panel: {p:IntParam}", ws.Cell(11, 8).Value);
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestNamedPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 4, 5);
range.AddToNamed("NamedPanel");
ws.Cell(1, 1).Value = "{p:StrParam}";
ws.Cell(1, 2).Value = "{p:IntParam}";
ws.Cell(1, 3).Value = "{p:DateParam}";
ws.Cell(1, 4).Value = "{p:BoolParam}";
ws.Cell(1, 5).Value = "{p:TimeSpanParam}";
ws.Cell(2, 1).Value = "{ p:StrParam }";
ws.Cell(2, 2).Value = "Plain text";
ws.Cell(2, 3).Value = "{Plain text}";
ws.Cell(2, 4).Value = " { m:Format ( p:DateParam ) } ";
ws.Cell(2, 5).Value = "''{m:Format(p:DateParam)}";
ws.Cell(3, 1).Value = "Int: { p:IntParam }. Str: {p:ComplexTypeParam.StrParam}. FormattedDate: {m:Format(p:DateParam)}";
ws.Cell(3, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
ws.Cell(3, 3).Value = "''{m:Format(m:AddDays(p:DateParam, 5), ddMMyyyy)}";
ws.Cell(3, 4).Value = "''{m:Format(m:AddDays(p:DateParam, -2), dd.MM.yyyy)}";
ws.Cell(3, 5).Value = "''{m:Format(m:AddDays(p:DateParam, [int]-3), \"dd.MM.yyyy HH:mm:ss\")}";
ws.Cell(4, 1).Value = "{m:TestReport:Counter()}";
ws.Cell(4, 2).Value = "{ m:TestReport : Counter ( ) }";
ws.Cell(4, 3).Value = "{m:Counter()}";
ws.Cell(5, 1).Value = "{p:StrParam}";
ws.Cell(5, 2).Value = "{m:Counter()}";
ws.Cell(6, 1).Value = "Plain text outside range";
var panel = new ExcelNamedPanel(ws.Workbook.NamedRange("NamedPanel"), report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(21, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("String parameter", ws.Cell(1, 1).Value);
Assert.AreEqual(10d, ws.Cell(1, 2).Value);
Assert.AreEqual(new DateTime(2017, 10, 25), ws.Cell(1, 3).Value);
Assert.AreEqual(true, ws.Cell(1, 4).Value);
Assert.AreEqual(TimeSpan.FromHours(20), ws.Cell(1, 5).Value);
Assert.AreEqual("String parameter", ws.Cell(2, 1).Value);
Assert.AreEqual("Plain text", ws.Cell(2, 2).Value);
Assert.AreEqual("{Plain text}", ws.Cell(2, 3).Value);
Assert.AreEqual(20171025d, ws.Cell(2, 4).Value);
Assert.AreEqual("20171025", ws.Cell(2, 5).Value);
Assert.AreEqual("Int: 10. Str: Complex type string parameter. FormattedDate: 20171025", ws.Cell(3, 1).Value);
Assert.AreEqual("0001-01-12", ws.Cell(3, 2).Value);
Assert.AreEqual("30102017", ws.Cell(3, 3).Value);
Assert.AreEqual("23.10.2017", ws.Cell(3, 4).Value);
Assert.AreEqual("22.10.2017 00:00:00", ws.Cell(3, 5).Value);
Assert.AreEqual(1d, ws.Cell(4, 1).Value);
Assert.AreEqual(2d, ws.Cell(4, 2).Value);
Assert.AreEqual(3d, ws.Cell(4, 3).Value);
Assert.IsTrue(ws.Cell(4, 4).IsEmpty());
Assert.IsTrue(ws.Cell(4, 5).IsEmpty());
Assert.AreEqual("{p:StrParam}", ws.Cell(5, 1).Value);
Assert.AreEqual("{m:Counter()}", ws.Cell(5, 2).Value);
Assert.AreEqual("Plain text outside range", ws.Cell(6, 1).Value);
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
IXLRange range2 = ws.Range(8, 1, 11, 5);
range2.AddToNamed("NamedPanel2", XLScope.Worksheet);
ws.Cell(8, 1).Value = "{p:StrParam}";
ws.Cell(8, 2).Value = "{p:IntParam}";
ws.Cell(8, 3).Value = "{p:DateParam}";
ws.Cell(8, 4).Value = "{p:BoolParam}";
ws.Cell(8, 5).Value = "{p:TimeSpanParam}";
ws.Cell(9, 1).Value = "{ p:StrParam }";
ws.Cell(9, 2).Value = "Plain text";
ws.Cell(9, 3).Value = "{Plain text}";
ws.Cell(9, 4).Value = " { m:Format ( p:DateParam ) } ";
ws.Cell(9, 5).Value = "''{m:Format(p:DateParam)}";
ws.Cell(10, 1).Value = "Int: { p:IntParam }. Str: {p:ComplexTypeParam.StrParam}. FormattedDate: {m:Format(p:DateParam)}";
ws.Cell(10, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
ws.Cell(10, 3).Value = "''{m:Format(m:AddDays(p:DateParam, 5), ddMMyyyy)}";
ws.Cell(10, 4).Value = "''{m:Format(m:AddDays(p:DateParam, -2), dd.MM.yyyy)}";
ws.Cell(10, 5).Value = "''{m:Format(m:AddDays(p:DateParam, [int]-3), \"dd.MM.yyyy HH:mm:ss\")}";
ws.Cell(11, 1).Value = "{m:TestReport:Counter()}";
ws.Cell(11, 2).Value = "{ m:TestReport : Counter ( ) }";
ws.Cell(11, 3).Value = "{m:Counter()}";
ws.Cell(12, 1).Value = "{p:StrParam}";
ws.Cell(12, 2).Value = "{m:Counter()}";
ws.Cell(13, 1).Value = "Plain text outside range";
panel = new ExcelNamedPanel(ws.NamedRange("NamedPanel2"), report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range2, panel.ResultRange);
Assert.AreEqual(42, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("String parameter", ws.Cell(8, 1).Value);
Assert.AreEqual(10d, ws.Cell(8, 2).Value);
Assert.AreEqual(new DateTime(2017, 10, 25), ws.Cell(8, 3).Value);
Assert.AreEqual(true, ws.Cell(8, 4).Value);
Assert.AreEqual(TimeSpan.FromHours(20), ws.Cell(8, 5).Value);
Assert.AreEqual("String parameter", ws.Cell(9, 1).Value);
Assert.AreEqual("Plain text", ws.Cell(9, 2).Value);
Assert.AreEqual("{Plain text}", ws.Cell(9, 3).Value);
Assert.AreEqual(20171025d, ws.Cell(9, 4).Value);
Assert.AreEqual("20171025", ws.Cell(9, 5).Value);
Assert.AreEqual("Int: 10. Str: Complex type string parameter. FormattedDate: 20171025", ws.Cell(10, 1).Value);
Assert.AreEqual("0001-01-12", ws.Cell(10, 2).Value);
Assert.AreEqual("30102017", ws.Cell(10, 3).Value);
Assert.AreEqual("23.10.2017", ws.Cell(10, 4).Value);
Assert.AreEqual("22.10.2017 00:00:00", ws.Cell(10, 5).Value);
Assert.AreEqual(4d, ws.Cell(11, 1).Value);
Assert.AreEqual(5d, ws.Cell(11, 2).Value);
Assert.AreEqual(6d, ws.Cell(11, 3).Value);
Assert.IsTrue(ws.Cell(11, 4).IsEmpty());
Assert.IsTrue(ws.Cell(11, 5).IsEmpty());
Assert.AreEqual("{p:StrParam}", ws.Cell(12, 1).Value);
Assert.AreEqual("{m:Counter()}", ws.Cell(12, 2).Value);
Assert.AreEqual("Plain text outside range", ws.Cell(13, 1).Value);
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
public void TestPanelRender()
{
var report = new TestReport();
IXLWorksheet ws = report.Workbook.AddWorksheet("Test");
IXLRange range = ws.Range(1, 1, 5, 5);
ws.Cell(1, 1).Value = "{p:StrParam}";
ws.Cell(1, 2).Value = "{p:IntParam}";
ws.Cell(1, 3).Value = "{p:DateParam}";
ws.Cell(1, 4).Value = "{P:BoolParam}";
ws.Cell(1, 5).Value = "{p:TimeSpanParam}";
ws.Cell(2, 1).Value = " { p:StrParam } ";
ws.Cell(2, 2).Value = "Plain text";
ws.Cell(2, 3).Value = "{Plain text}";
ws.Cell(2, 4).Value = " { m:Format ( p:DateParam ) } ";
ws.Cell(2, 5).Value = "''{m:Format(p:DateParam)}";
ws.Cell(3, 1).Value = "Int: { p:IntParam }. Str: {p:ComplexTypeParam.StrParam}. FormattedDate: {M:Format(p:DateParam)}. NullProp: {p:NullProp}";
ws.Cell(3, 2).Value = "''{m:Format(m:DateTime:AddDays(p:ComplexTypeParam.IntParam), \"yyyy-MM-dd\")}";
ws.Cell(3, 3).Value = "''{sf:Format(m:AddDays(p:DateParam, 5), ddMMyyyy)}";
ws.Cell(3, 4).Value = "''{m:Format(m:AddDays(p:DateParam, -2), dd.MM.yyyy)}";
ws.Cell(3, 5).Value = "''{sf:Format(m:AddDays(p:DateParam, [int]-3), \"dd.MM.yyyy HH:mm:ss\")}";
ws.Cell(4, 1).Value = "{m:TestReport:Counter()}";
ws.Cell(4, 2).Value = "{ m:TestReport : Counter ( ) }";
ws.Cell(4, 3).Value = "{m:Counter()}";
ws.Cell(4, 4).FormulaA1 = "=$B$1+A$4";
ws.Cell(5, 1).Value = "{p:ExpandoObj.StrProp}";
ws.Cell(5, 2).Value = "{p:ExpandoObj.DecimalProp}";
ws.Cell(5, 3).Value = "{p:NullProp}";
ws.Cell(6, 1).Value = "{p:StrParam}";
ws.Cell(6, 2).Value = "{m:Counter()}";
ws.Cell(7, 1).Value = "Plain text outside range";
var panel = new ExcelPanel(range, report, report.TemplateProcessor);
panel.Render();
Assert.AreEqual(range, panel.ResultRange);
Assert.AreEqual(24, ws.CellsUsed(XLCellsUsedOptions.Contents).Count());
Assert.AreEqual("String parameter", ws.Cell(1, 1).Value);
Assert.AreEqual(10d, ws.Cell(1, 2).Value);
Assert.AreEqual(new DateTime(2017, 10, 25), ws.Cell(1, 3).Value);
Assert.AreEqual(true, ws.Cell(1, 4).Value);
Assert.AreEqual(TimeSpan.FromHours(20), ws.Cell(1, 5).Value);
Assert.AreEqual(" String parameter ", ws.Cell(2, 1).Value);
Assert.AreEqual("Plain text", ws.Cell(2, 2).Value);
Assert.AreEqual("{Plain text}", ws.Cell(2, 3).Value);
Assert.AreEqual(20171025d, ws.Cell(2, 4).Value);
Assert.AreEqual("20171025", ws.Cell(2, 5).Value);
Assert.AreEqual("Int: 10. Str: Complex type string parameter. FormattedDate: 20171025. NullProp: ", ws.Cell(3, 1).Value);
Assert.AreEqual("0001-01-12", ws.Cell(3, 2).Value);
Assert.AreEqual("30102017", ws.Cell(3, 3).Value);
Assert.AreEqual("23.10.2017", ws.Cell(3, 4).Value);
Assert.AreEqual("22.10.2017 00:00:00", ws.Cell(3, 5).Value);
Assert.AreEqual(1d, ws.Cell(4, 1).Value);
Assert.AreEqual(2d, ws.Cell(4, 2).Value);
Assert.AreEqual(3d, ws.Cell(4, 3).Value);
Assert.AreEqual(11d, ws.Cell(4, 4).Value);
Assert.IsTrue(ws.Cell(4, 5).IsEmpty());
Assert.AreEqual("ExpandoStr", ws.Cell(5, 1).Value);
Assert.AreEqual(5.56d, ws.Cell(5, 2).Value);
Assert.IsTrue(ws.Cell(5, 3).IsEmpty());
Assert.AreEqual("{p:StrParam}", ws.Cell(6, 1).Value);
Assert.AreEqual("{m:Counter()}", ws.Cell(6, 2).Value);
Assert.AreEqual("Plain text outside range", ws.Cell(7, 1).Value);
Assert.AreEqual(0, ws.NamedRanges.Count());
Assert.AreEqual(0, ws.Workbook.NamedRanges.Count());
Assert.AreEqual(1, ws.Workbook.Worksheets.Count);
//report.Workbook.SaveAs("test.xlsx");
}
protected bool WorksheetsAreEqual(IXLWorksheet expected, IXLWorksheet actual, out IList <string> messages)
{
messages = new List <string>();
if (expected.Name != actual.Name)
{
messages.Add("Worksheet names differ");
}
if (expected.RangeUsed().RangeAddress.ToString() != actual.RangeUsed().RangeAddress.ToString())
{
messages.Add("Used ranges differ");
}
if (expected.Style.ToString() != actual.Style.ToString())
{
messages.Add("Worksheet styles differ");
}
foreach (var expectedCell in expected.CellsUsed())
{
var actualCell = actual.Cell(expectedCell.Address);
bool cellsAreEqual = true;
if (actualCell.Value?.ToString() != expectedCell.Value?.ToString())
{
messages.Add($"Cell values are not equal starting from {actualCell.Address}");
cellsAreEqual = false;
}
if (!string.Equals(actualCell.FormulaA1, expectedCell.FormulaA1, StringComparison.InvariantCultureIgnoreCase))
{
messages.Add($"Cell formulae are not equal starting from {actualCell.Address}");
cellsAreEqual = false;
}
if (actualCell.DataType != expectedCell.DataType)
{
messages.Add($"Cell data types are not equal starting from {actualCell.Address}");
cellsAreEqual = false;
}
if (expectedCell.Style.ToString() != actualCell.Style.ToString())
{
messages.Add($"Cell style are not equal starting from {actualCell.Address}");
cellsAreEqual = false;
}
if (!cellsAreEqual)
{
break; // we don't need thousands of messages
}
}
if (expected.MergedRanges.Count() != actual.MergedRanges.Count())
{
messages.Add("Merged ranges counts differ");
}
else
{
var expectedRanges = expected.MergedRanges
.OrderBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ToList();
var actualRanges = actual.MergedRanges
.OrderBy(r => r.RangeAddress.FirstAddress.ColumnNumber)
.ThenBy(r => r.RangeAddress.FirstAddress.RowNumber)
.ToList();
for (int i = 0; i < expectedRanges.Count(); i++)
{
var expectedMr = expectedRanges.ElementAt(i);
var actualMr = actualRanges.ElementAt(i);
if (expectedMr.RangeAddress.ToString() != actualMr.RangeAddress.ToString())
{
messages.Add($"Merged ranges differ starting from {expectedMr.RangeAddress}");
break;
}
}
}
if (expected.ConditionalFormats.Count() != actual.ConditionalFormats.Count())
{
messages.Add("Conditional format counts differ");
}
for (int i = 0; i < expected.ConditionalFormats.Count(); i++)
{
var expectedCf = expected.ConditionalFormats.ElementAt(i);
var actualCf = actual.ConditionalFormats.ElementAt(i);
if (expectedCf.Range.RangeAddress.ToString() != actualCf.Range.RangeAddress.ToString())
{
messages.Add($"Conditional formats at index {i} have different ranges");
}
if (expectedCf.Style.ToString() != actualCf.Style.ToString())
{
messages.Add($"Conditional formats at index {i} have different styles");
}
}
return(!messages.Any());
}