public void TestPrintingDropDownFromTheOtherWorksheet() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("printingDropDownFromTheOtherWorksheet.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { targetDocument.CopyVbaInfoFrom(templateDocument); foreach (var index in Enumerable.Range(1, templateDocument.GetWorksheetCount() - 1)) { var worksheet = templateDocument.GetWorksheet(index); var name = templateDocument.GetWorksheetName(index); var innerTemplateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(new ExcelTable(worksheet), logger); var targetWorksheet = targetDocument.AddWorksheet(name); var innerTableBuilder = new TableBuilder(new ExcelTable(targetWorksheet), new TableNavigator(new CellPosition("A1"), logger)); innerTemplateEngine.Render(innerTableBuilder, new {}); } var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new { Type = "Значение 2" }); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); Assert.Fail($"Please manually open file '{path}' and check that dropdown on the first sheet has value 'Значение 2'"); } }
public void MultipleObjectsPrintingTest() { var model = new Document { Buyer = new Organization { Address = "BuyerAddress", Name = "BuyerName" }, Supplier = new Organization { Address = "SupplierAddress", Name = "SupplierName", Inn = 90238192, Kpp = "0832309812" }, Payer = new Organization { Address = "PayerAddress", Name = "PayerAddress" }, DeliveryParty = new Organization { Address = "DeliveryPartyAddress", Name = "DeliveryPartyName" }, Vehicle = new VehicleInfo { NameOfCarrier = "Евкакий", TransportMode = "Agressive", VehicleBrand = "Tauria", VehicleNumber = "A777AB" }, TypeName = "ORDERS" }; var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("complexTemplate.xlsx")), logger); var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger); targetDocument.AddWorksheet("Лист2"); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("B2"), logger), new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, model); target = new ExcelTable(targetDocument.GetWorksheet(1)); tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("A1"), logger), new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, model); var result = targetDocument.CloseAndGetDocumentBytes(); File.WriteAllBytes("output.xlsx", result); templateDocument.Dispose(); targetDocument.Dispose(); }
public void FormControlsPrintingTest() { var model = new { BoolValue1 = false, BoolValue2 = true, String1 = "Value1", Dict = new Dictionary <string, bool> { { "TestKey", false } }, }; var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("formControlsTemplate.xlsx")), logger); var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("A1"), logger), new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, model); var result = targetDocument.CloseAndGetDocumentBytes(); File.WriteAllBytes("output.xlsx", result); templateDocument.Dispose(); targetDocument.Dispose(); }
public void ExcelTablePartExtractionTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var table = new ExcelTable(document.GetWorksheet(0)); var rows = table.GetTablePart(new Rectangle(new CellPosition("B9"), new CellPosition("D11"))) .Cells .Select(row => row.ToArray()) .ToArray(); foreach (var cell in rows.SelectMany(row => row)) { cell.Should().NotBeNull(); } rows[0][0].StringValue.Should().Be("Template:RootTemplate:B10:D11"); (rows[0][1].StringValue + "").Should().BeEmpty(); (rows[0][2].StringValue + "").Should().BeEmpty(); rows[1][0].StringValue.Should().Be("Покупатель:"); rows[1][1].StringValue.Should().Be("Поставщик:"); (rows[1][2].StringValue + "").Should().BeEmpty(); rows[2][0].StringValue.Should().Be("Value:Organization:Buyer"); rows[2][1].StringValue.Should().Be("Value:Organization:Supplier"); rows[2][2].StringValue.Should().Be("Value::TypeName"); }
public void TestPrintingCommentsWithEnabledDataValidationByOtherSheetData() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("commentsAndOtherSheetDataValidations.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { CopySecondaryWorksheets(templateDocument, targetDocument); var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); targetDocument.GetWorksheet(0).SetPrinterSettings(printerSettings); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new {}); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename); Process.Start(filename); Assert.Fail($@"Please check that opened file has: {path} - comment/annotation - D4-D7 has data validation with values from the second worksheet and G4-G7 has data validation with values from K1:K6"); } }
public void TestDataValidationsFromTheOtherWorksheet() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("otherSheetDataValidations.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { targetDocument.CopyVbaInfoFrom(templateDocument); foreach (var index in Enumerable.Range(1, templateDocument.GetWorksheetCount() - 1)) { var worksheet = templateDocument.GetWorksheet(index); var name = templateDocument.GetWorksheetName(index); var innerTemplateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(new ExcelTable(worksheet), logger); var targetWorksheet = targetDocument.AddWorksheet(name); var innerTableBuilder = new TableBuilder(new ExcelTable(targetWorksheet), new TableNavigator(new CellPosition("A1"), logger)); innerTemplateEngine.Render(innerTableBuilder, new {}); } var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new {}); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); Assert.Fail($"Please manually open file '{path}' and check that D4-D7 has data validation with values from the second worksheet and G4-G7 has data validation with values from K1:K6"); } }
public void TestColumnsSwitching() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("columnsSwitching.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new { A = "First", B = true, C = "Third", D = new[] { 1, 2, 3 }, E = "Fifth" }); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); Assert.Fail($"Please manually open file '{path}' and check values:\n" + string.Join("\n", new[] { "C5: First", "D5: <empty>", "E5: Third", "F5: 1", "F6: 2", "F7: 3", "G5: Fifth", "Флажок 1: checked", }) + "\n"); } }
public void TestImportAfterCreate(string extension) { byte[] bytes; using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("importAfterCreate_template.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath($"empty.{extension}")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new { Type = "Значение 2", TestFlag1 = false, TestFlag2 = true }); target.InsertCell(new CellPosition("C16")); bytes = targetDocument.CloseAndGetDocumentBytes(); } var(model, mappingForErrors) = Parse <PriceList>(File.ReadAllBytes(GetFilePath("importAfterCreate_template.xlsx")), bytes); mappingForErrors["TestFlag1"].Should().Be("CheckBoxName1"); mappingForErrors["TestFlag2"].Should().Be("CheckBoxName2"); mappingForErrors["Type"].Should().Be("C3"); model.TestFlag1.Should().BeFalse(); model.TestFlag2.Should().BeTrue(); model.Type.Should().Be("Значение 2"); }
public void NullCellStyleAccessTest() { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var cell = templateDocument.GetWorksheet(0).GetCell(new ExcelCellIndex("A2")); cell.GetStyle(); }
public void CellNumberFormatExtractionTest() { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var cell = templateDocument.GetWorksheet(0).GetCell(new ExcelCellIndex("A1")); var style = cell.GetStyle(); style.NumberingFormat.FormatCode.Should().Be("0.0000"); }
public void CellAlignmentExtractionTest() { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var cell = templateDocument.GetWorksheet(0).GetCell(new ExcelCellIndex("A1")); var style = cell.GetStyle(); style.Alignment.WrapText.Should().BeTrue(); style.Alignment.HorizontalAlignment.Should().Be(ExcelHorizontalAlignment.Left); }
public void CellColorExtractionTest() { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var cell = templateDocument.GetWorksheet(0).GetCell(new ExcelCellIndex("A1")); var style = cell.GetStyle(); style.FillStyle.Color.Red.Should().Be(128); style.FillStyle.Color.Green.Should().Be(129); style.FillStyle.Color.Blue.Should().Be(200); }
public void MergedCellsExtractionEmptyTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger); var worksheet = document.GetWorksheet(0); var cells = worksheet.MergedCells.ToArray(); cells.Length.Should().Be(0); document.Dispose(); }
public void CellBorderFormatExtractionTest() { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var cell = templateDocument.GetWorksheet(0).GetCell(new ExcelCellIndex("A1")); var style = cell.GetStyle(); style.BordersStyle.BottomBorder.BorderType.Should().Be(ExcelBorderType.Double); style.BordersStyle.BottomBorder.Color.Red.Should().Be(255); style.BordersStyle.BottomBorder.Color.Green.Should().Be(0); style.BordersStyle.BottomBorder.Color.Blue.Should().Be(255); }
public void GetCellTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var worksheet = document.GetWorksheet(0); var position = new ExcelCellIndex("B9"); var cell = worksheet.GetCell(position); cell.GetStringValue().Should().Be("Model:Document:B10:C11"); document.Dispose(); }
public void ExcelCellExtractionTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var table = new ExcelTable(document.GetWorksheet(0)); var cell = table.GetCell(new CellPosition("B9")); cell.Should().NotBeNull(); cell.StringValue.Should().Be("Template:RootTemplate:B10:D11"); cell = table.GetCell(new CellPosition("ABCD4234")); cell.Should().BeNull(); }
public void SearchCellsByTextTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var worksheet = document.GetWorksheet(0); var cell = worksheet.SearchCellsByText("Value:String").FirstOrDefault(); cell.Should().NotBeNull(); // ReSharper disable PossibleNullReferenceException cell.GetStringValue().Should().Be("Value:String:Name"); // ReSharper restore PossibleNullReferenceException document.Dispose(); }
public void WorksheetAdditionTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), new ConsoleLog()); document.AddWorksheet("Лист2"); var worksheet = document.GetWorksheet(1); worksheet.Should().NotBeNull(); var result = document.CloseAndGetDocumentBytes(); File.WriteAllBytes("output.xlsx", result); document.Dispose(); }
public void MergedCellsExtractionTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var worksheet = document.GetWorksheet(0); var cells = worksheet.MergedCells.ToArray(); cells.Length.Should().Be(2); cells[0].Item1.CellReference.Should().Be("B32"); cells[0].Item2.CellReference.Should().Be("C35"); cells[1].Item1.CellReference.Should().Be("I2"); cells[1].Item2.CellReference.Should().Be("J3"); document.Dispose(); }
public void CellsInRangeTest() { var document = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger); var worksheet = document.GetWorksheet(0); var upperLeft = new ExcelCellIndex("A22"); var lowerRight = new ExcelCellIndex("A24"); var cells = worksheet.GetSortedCellsInRange(upperLeft, lowerRight).ToArray(); cells.Length.Should().Be(3); cells[1].GetStringValue().Should().Be("Value:String:Name"); cells[2].GetStringValue().Should().Be("Value:String:Address"); document.Dispose(); }
public void TestPrintingCommentsWithSeveralAuthors() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("commentsWithSeveralAuthors.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new {}); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); var templatePath = "file:///" + Path.GetFullPath("ExcelObjectPrinterTests/Files/commentsWithSeveralAuthors.xlsx").Replace("\\", "/"); Assert.Fail($"Please manually open file:\n{path}\nand check that cells has same comments as in\n{templatePath}\n"); } }
private void MakeTest(object model, string templateFileName, Action <ExcelTable> resultValidationFunc = null) { var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath(templateFileName)), logger); var template = new ExcelTable(templateDocument.GetWorksheet(0)); var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("B2"), logger), new Style(template.GetCell(new CellPosition("A1")))); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); templateEngine.Render(tableBuilder, model); var result = targetDocument.CloseAndGetDocumentBytes(); File.WriteAllBytes("output.xlsx", result); resultValidationFunc?.Invoke(target); templateDocument.Dispose(); targetDocument.Dispose(); }
public void TestDataValidations() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("dataValidations.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new { Test = "b" }); var filename = "output.xlsx"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); Assert.Fail($"Please manually open file '{path}' and check that:\n\n" + "Cell C4 has validation with variants abc, cde and lalala\n" + "Cell E6 has validation with variants a, b, c and value b\n"); } }
public void TestPrintingVbaMacros() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("printingVbaMacros.xlsm")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsm")), logger)) { targetDocument.CopyVbaInfoFrom(templateDocument); var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableNavigator = new TableNavigator(new CellPosition("A1"), logger); var tableBuilder = new TableBuilder(target, tableNavigator, new Style(template.GetCell(new CellPosition("A1")))); templateEngine.Render(tableBuilder, new { Type = "123" }); var filename = "output.xlsm"; File.WriteAllBytes(filename, targetDocument.CloseAndGetDocumentBytes()); var path = "file:///" + Path.GetFullPath(filename).Replace("\\", "/"); Assert.Fail($"Please manually open file '{path}' and check that clicking on the right checkbox leads to changes in both checkbox"); } }
public void NonExistentFieldPrintingTest() { var model = new { A = false, B = true, }; using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new SkbKontur.Excel.TemplateEngine.TemplateEngine(template, logger); using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("A1"), logger), new Style(template.GetCell(new CellPosition("A1")))); Action rendering = () => templateEngine.Render(tableBuilder, model); rendering.Should().Throw <InvalidOperationException>(); } } }
public void CellNumberFormatCopyAfterRenderTest() { using (var templateDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("template.xlsx")), logger)) using (var targetDocument = ExcelDocumentFactory.CreateFromTemplate(File.ReadAllBytes(GetFilePath("empty.xlsx")), logger)) { var template = new ExcelTable(templateDocument.GetWorksheet(0)); var templateEngine = new TemplateEngine(template, logger); var target = new ExcelTable(targetDocument.GetWorksheet(0)); var tableBuilder = new TableBuilder(target, new TableNavigator(new CellPosition("A1"), logger), new Style(template.GetCell(new CellPosition("A1")))); // Model is required for printing cells defined by Template:RootTemplate var dummyModel = new { A = 1, B = "asdf" }; templateEngine.Render(tableBuilder, dummyModel); var worksheet = targetDocument.GetWorksheet(0); var customStyle = worksheet.GetCell(new ExcelCellIndex("A1")).GetStyle(); customStyle.NumberingFormat.Code.Should().Be("0.0000"); var standardStyle = worksheet.GetCell(new ExcelCellIndex("A4")).GetStyle(); standardStyle.NumberingFormat.Id.Should().Be(49); standardStyle.NumberingFormat.Code.Should().BeNull(); } }