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 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 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 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 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 override void Emit(TextWriter outputWriter) { if (TableNavigator.Select("rc:DefaultValues/rc:Value", VulcanPackage.VulcanConfig.NamespaceManager).Count == 0) { outputWriter.Flush(); return; } StringBuilder columnBuilder = new StringBuilder(); bool containsIdentities = _tableHelper.KeyColumnType == KeyColumnType.Identity; foreach (XPathNavigator nav in TableNavigator.Select("rc:Columns/rc:Column", VulcanPackage.VulcanConfig.NamespaceManager)) { /* Build Column List */ string columnName = nav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value; columnBuilder.AppendFormat( "[{0}],", columnName ); } columnBuilder.Remove(columnBuilder.Length - 1, 1); if (containsIdentities) { outputWriter.Write("\n"); outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture, "\nSET IDENTITY_INSERT {0} ON\n", TableName)); } TemplateEmitter te = new TemplateEmitter("InsertDefaultValues", VulcanPackage, null); outputWriter.Write("\n"); foreach (XPathNavigator nav in TableNavigator.Select("rc:DefaultValues/rc:Value", VulcanPackage.VulcanConfig.NamespaceManager)) { te.SetParameters(TableName, columnBuilder.ToString(), nav.Value); te.Emit(outputWriter); outputWriter.Write("\n");; } if (containsIdentities) { outputWriter.Write(String.Format(System.Globalization.CultureInfo.InvariantCulture, "\nSET IDENTITY_INSERT {0} OFF", TableName)); } outputWriter.Write("\nGO\n"); outputWriter.Flush(); }
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"); } }
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 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 override void Emit(TextWriter outputWriter) { TemplateEmitter te = new TemplateEmitter("CreateIndex", VulcanPackage, null); foreach (XPathNavigator nav in TableNavigator.Select("rc:Indexes/rc:Index", VulcanPackage.VulcanConfig.NamespaceManager)) { string indexName = nav.SelectSingleNode("@Name") == null ? null : nav.SelectSingleNode("@Name").Value; string unique = nav.SelectSingleNode("@Unique", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "UNIQUE" : ""; string clustered = nav.SelectSingleNode("@Clustered", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "CLUSTERED" : "NONCLUSTERED"; string dropExisting = nav.SelectSingleNode("@DropExisting", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "DROP_EXISTING = ON" : "DROP_EXISTING = OFF"; string ignoreDupKey = nav.SelectSingleNode("@IgnoreDupKey", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "IGNORE_DUP_KEY = ON" : "IGNORE_DUP_KEY = OFF"; string online = nav.SelectSingleNode("@Online", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "ONLINE = ON" : "ONLINE = OFF"; string padIndex = nav.SelectSingleNode("@PadIndex", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "PAD_INDEX = ON" : "PAD_INDEX = OFF"; string sortInTempdb = nav.SelectSingleNode("@SortInTempdb", VulcanPackage.VulcanConfig.NamespaceManager).ValueAsBoolean ? "SORT_IN_TEMPDB = ON" : "SORT_IN_TEMPDB = OFF"; StringBuilder columnsBuilder = new StringBuilder(); StringBuilder indexNameBuilder = new StringBuilder("IX_"); indexNameBuilder.Append(TableName); outputWriter.Write("\n"); foreach (XPathNavigator columnNav in nav.Select("rc:Column", VulcanPackage.VulcanConfig.NamespaceManager)) { // This should use a Column Emitter which has callbacks and such... //string componentName = nav.SelectSingleNode("@Name", VulcanPackage.NamespaceManager).Value; string name = columnNav.SelectSingleNode("@Name", VulcanPackage.VulcanConfig.NamespaceManager).Value; string sortOrder = columnNav.SelectSingleNode("@SortOrder", VulcanPackage.VulcanConfig.NamespaceManager).Value; columnsBuilder.AppendFormat( "[{0}] {1},\n", name, sortOrder ); indexNameBuilder.AppendFormat("_{0}", name); } columnsBuilder.Replace(",", "", columnsBuilder.Length - 2, 1); // Remove any dots which happen to show up in the name indexNameBuilder.Replace(".", "_"); if (!string.IsNullOrEmpty(indexName)) { indexNameBuilder = new StringBuilder(indexName); // Throw out the old name :) We got ourselves a better one. } te.SetParameters( unique, clustered, indexNameBuilder.ToString(), TableName, columnsBuilder.ToString(), String.Format(System.Globalization.CultureInfo.InvariantCulture, "{0},\n{1},\n{2},\n{3},\n{4}", padIndex, sortInTempdb, dropExisting, ignoreDupKey, online) ); te.Emit(outputWriter); outputWriter.Flush(); } }