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();
}
}
