public void Cell3() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result"); IXLCell cell = wb.Cell("Sheet1!Result"); Assert.IsNotNull(cell); Assert.AreEqual(1, cell.GetValue<Int32>()); }
public void Then_feature_with_background_is_added_successfully() { var excelFeatureFormatter = Container.Resolve<ExcelFeatureFormatter>(); var feature = new Feature { Name = "Test Feature", Description = "In order to test this feature,\nAs a developer\nI want to test this feature", }; var background = new Scenario { Name = "Test Background Scenario", Description = "In order to test this background,\nAs a developer\nI want to test this background" }; var given = new Step { NativeKeyword = "Given", Name = "a precondition" }; background.Steps = new List<Step>(new[] { given }); feature.AddBackground(background); using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); excelFeatureFormatter.Format(worksheet, feature); Check.That(worksheet.Cell("B4").Value).IsEqualTo(background.Name); Check.That(worksheet.Cell("C5").Value).IsEqualTo(background.Description); Check.That(worksheet.Cell("D6").Value).IsEqualTo(given.Name); } }
public void ThenStepCommentsAreAddedSuccessfully() { var excelStepFormatter = Container.Resolve<ExcelStepFormatter>(); var step = new Step { NativeKeyword = "Given", Name = "I have some precondition", Comments = new List<Comment>() { new Comment() { Text = "# A comment", Type = CommentType.StepComment } } }; using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 5; excelStepFormatter.Format(worksheet, step, ref row); Check.That(worksheet.Cell("C5").Value).IsEqualTo(step.Comments.First().Text); Check.That(worksheet.Cell("C6").Value).IsEqualTo(step.NativeKeyword); Check.That(worksheet.Cell("D6").Value).IsEqualTo(step.Name); } }
public void InsideBorderTest() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); IXLRange range = ws.Range("B2:D4"); SetupBorders(range); range.Style.Border.InsideBorder = XLBorderStyleValues.Thin; range.Style.Border.InsideBorderColor = XLColor.Red; IXLCell center = range.Cell(2, 2); Assert.AreEqual(XLColor.Red, center.Style.Border.TopBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.BottomBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.LeftBorderColor); Assert.AreEqual(XLColor.Red, center.Style.Border.RightBorderColor); Assert.AreEqual(XLBorderStyleValues.None, range.FirstRow().Cell(1).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstRow().Cell(2).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.FirstRow().Cell(3).Style.Border.TopBorder); Assert.AreEqual(XLBorderStyleValues.None, range.LastRow().Cell(1).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.LastRow().Cell(2).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.LastRow().Cell(3).Style.Border.BottomBorder); Assert.AreEqual(XLBorderStyleValues.None, range.FirstColumn().Cell(1).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.FirstColumn().Cell(2).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.FirstColumn().Cell(3).Style.Border.LeftBorder); Assert.AreEqual(XLBorderStyleValues.None, range.LastColumn().Cell(1).Style.Border.RightBorder); Assert.AreEqual(XLBorderStyleValues.Thick, range.LastColumn().Cell(2).Style.Border.RightBorder); Assert.AreEqual(XLBorderStyleValues.Double, range.LastColumn().Cell(3).Style.Border.RightBorder); }
public static void Basic() { var wb = new XLWorkbook(); var ws = wb.AddWorksheet("Worksheet Name"); ws.Cell("A1").Value = Data; wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx"); }
public void Format(XLWorkbook workbook, Tree features) { IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0); int startRow = 1; this.BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features); }
private void WriteCategoriesByMonth(XLWorkbook workbook) { var ws = workbook.AddWorksheet("Categorization (by month)"); var all_posts = profile.AggregatedPosts().OrderBy(p => p.Date); var first_date = all_posts.First().Date; var last_date = all_posts.Last().Date; var all_categories = Category.Flatten(profile.RootCategory); var posts_by_category = all_categories.ToDictionary(category => category, category => category.AggregatePosts().Distinct().ToList()); ws.Row(1).Cell(1).SetValue("Date"); for (int i = 0; i < all_categories.Count; i++) { ws.Row(1).Cell(i + 2).SetValue(all_categories[i].Name); } var current_date = new DateTime(first_date.Year, first_date.Month, 1); last_date = new DateTime(last_date.Year, last_date.Month, 1).AddMonths(1); var current_row = 2; while (current_date != last_date) { ws.Row(current_row).Cell(1).SetValue(current_date.ToShortDateString()); for (int j = 0; j < all_categories.Count; j++) { var total = posts_by_category[all_categories[j]].Where(p => p.Date.Year == current_date.Year && p.Date.Month == current_date.Month).Sum(p => p.Value); ws.Row(current_row).Cell(j + 2).SetValue(total); } current_row++; current_date = current_date.AddMonths(1); } }
public void Build(Tree features) { if (Log.IsInfoEnabled) { Log.Info("Writing Excel workbook to {0}", this.configuration.OutputFolder.FullName); } string spreadsheetPath = this.fileSystem.Path.Combine(this.configuration.OutputFolder.FullName, "features.xlsx"); using (var workbook = new XLWorkbook()) { foreach (var node in features) { var featureDirectoryTreeNode = node as FeatureNode; if (featureDirectoryTreeNode != null) { IXLWorksheet worksheet = workbook.AddWorksheet( this.excelSheetNameGenerator.GenerateSheetName( workbook, featureDirectoryTreeNode .Feature)); this.excelFeatureFormatter.Format( worksheet, featureDirectoryTreeNode.Feature); } } this.excelTableOfContentsFormatter.Format(workbook, features); workbook.SaveAs(spreadsheetPath); } }
public void ThenTableAddedSuccessfully() { var excelTableFormatter = Container.Resolve<ExcelTableFormatter>(); var table = new Table(); table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4"); table.DataRows = new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")}); using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 6; excelTableFormatter.Format(worksheet, table, ref row); Check.That(worksheet.Cell("D6").Value).IsEqualTo("Var1"); Check.That(worksheet.Cell("E6").Value).IsEqualTo("Var2"); Check.That(worksheet.Cell("F6").Value).IsEqualTo("Var3"); Check.That(worksheet.Cell("G6").Value).IsEqualTo("Var4"); Check.That(worksheet.Cell("D7").Value).IsEqualTo(1.0); Check.That(worksheet.Cell("E7").Value).IsEqualTo(2.0); Check.That(worksheet.Cell("F7").Value).IsEqualTo(3.0); Check.That(worksheet.Cell("G7").Value).IsEqualTo(4.0); Check.That(worksheet.Cell("D8").Value).IsEqualTo(5.0); Check.That(worksheet.Cell("E8").Value).IsEqualTo(6.0); Check.That(worksheet.Cell("F8").Value).IsEqualTo(7.0); Check.That(worksheet.Cell("G8").Value).IsEqualTo(8.0); Check.That(row).IsEqualTo(9); } }
public void Create(String filePath) { var wb = new XLWorkbook(); var wsActiveCell = wb.AddWorksheet("Set Active Cell"); wsActiveCell.Cell("B2").SetActive(); var wsSelectRowsColumns = wb.AddWorksheet("Select Rows and Columns"); wsSelectRowsColumns.Rows("2, 4-5").Select(); wsSelectRowsColumns.Columns("2, 4-5").Select(); var wsSelectMisc = wb.AddWorksheet("Select Misc"); wsSelectMisc.Cell("B2").Select(); wsSelectMisc.Range("D2:E2").Select(); wsSelectMisc.Ranges("C3, D4:E5").Select(); wb.SaveAs(filePath); }
public void CopyConditionalFormatsCount() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().AddConditionalFormat().WhenContains("1").Fill.SetBackgroundColor(XLColor.Blue); IXLWorksheet ws2 = ws.CopyTo("Sheet2"); Assert.AreEqual(1, ws2.ConditionalFormats.Count()); }
public void ThenCanAddTableOfContentsWorksheetSuccessfully() { var excelTableOfContentsFormatter = new ExcelTableOfContentsFormatter(); using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); excelTableOfContentsFormatter.Format(workbook, null); } }
public void CopyColumn() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Test").Style.Font.SetBold(); ws.FirstColumn().CopyTo(ws.Column(2)); Assert.IsTrue(ws.Cell("B1").Style.Font.Bold); }
public void DefaultColorIndex64isTransparentWhite() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); XLColor color = ws.FirstCell().Style.Fill.BackgroundColor; Assert.AreEqual(XLColorType.Indexed, color.ColorType); Assert.AreEqual(64, color.Indexed); Assert.AreEqual(Color.Transparent, color.Color); }
public void Cells2() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1).AddToNamed("Result", XLScope.Worksheet); IXLCells cells = wb.Cells("Sheet1!Result, ABC"); Assert.IsNotNull(cells); Assert.AreEqual(1, cells.Count()); Assert.AreEqual(1, cells.First().GetValue<Int32>()); }
/// <summary> /// Creates a new Excel File Writer with one worksheet (the default worksheet) in the workbook. /// </summary> public ExcelFileWriter( bool createDefaultWorksheet = true ) { workbook = new XLWorkbook(); AutofitOnSave = true; if( createDefaultWorksheet ) { var newWorkSheet = new ExcelWorksheet( workbook.AddWorksheet( "Sheet1" ) ); namesToWorksheets.Add( newWorkSheet.Name, newWorkSheet ); DefaultWorksheet = newWorkSheet; } }
public void CanSaveTableCreatedFromSingleRow() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Title"); ws.Range("A1").CreateTable(); using (var ms = new MemoryStream()) wb.SaveAs(ms); }
public void CreatingATableFromHeadersPushCellsBelow() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().SetValue("Title") .CellBelow().SetValue("X"); ws.Range("A1").CreateTable(); Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); Assert.AreEqual(ws.Cell("A3").GetString(), "X"); }
public static void UseStylePerRange() { var wb = new XLWorkbook(); var ws = wb.AddWorksheet("Worksheet Name"); ws.Cell("A1").Value = Data; // loop through cells in first col and make each one SAME colour var range = ws.Range("A1:A10"); range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#FF9")); wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx"); }
public void CopyConditionalFormatsRelative() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.Cell("A1").Value = "1"; ws.Cell("B1").Value = "1"; ws.Cell("A1").AddConditionalFormat().WhenEquals("=B1").Fill.SetBackgroundColor(XLColor.Blue); ws.Cell("A2").Value = ws.Cell("A1"); Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B1" && v.Value.IsFormula))); Assert.IsTrue(ws.ConditionalFormats.Any(cf => cf.Values.Any(v => v.Value.Value == "B2" && v.Value.IsFormula))); }
public static void ColWidth() { var wb = new XLWorkbook(); var ws = wb.AddWorksheet("Worksheet Name"); ws.Cell("A1").Value = Data; var cols = ws.Columns(1,3); cols.Width = 44; wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx"); }
public void CanSaveTableCreatedFromEmptyDataTable() { var dt = new DataTable("sheet1"); dt.Columns.Add("col1", typeof (string)); dt.Columns.Add("col2", typeof (double)); var wb = new XLWorkbook(); wb.AddWorksheet(dt); using (var ms = new MemoryStream()) wb.SaveAs(ms); }
public void ThenCanShortenLongMultipleDuplicatedNameSuccessfully() { var excelSheetNameGenerator = Container.Resolve<ExcelSheetNameGenerator>(); var feature1 = new Feature {Name = "This is a really really long feature name that needs to be shortened A"}; var feature2 = new Feature {Name = "This is a really really long feature name that needs to be shortened B"}; var feature3 = new Feature {Name = "This is a really really long feature name that needs to be shortened C"}; string name1; string name2; string name3; using (var wb = new XLWorkbook()) { name1 = excelSheetNameGenerator.GenerateSheetName(wb, feature1); wb.AddWorksheet(name1); name2 = excelSheetNameGenerator.GenerateSheetName(wb, feature2); wb.AddWorksheet(name2); name3 = excelSheetNameGenerator.GenerateSheetName(wb, feature3); } name3.ShouldEqual("THISISAREALLYREALLYLONGFEATU(2)"); }
public void Validation_2() { var wb = new XLWorkbook(); IXLWorksheet ws = wb.Worksheets.Add("Sheet1"); ws.Cell("A1").SetValue("A"); ws.Cell("B1").SetDataValidation().Custom("Sheet1!A1"); IXLWorksheet ws2 = wb.AddWorksheet("Sheet2"); ws2.Cell("A1").SetValue("B"); ws.Cell("B1").CopyTo(ws2.Cell("B1")); Assert.AreEqual("Sheet1!A1", ws2.Cell("B1").DataValidation.Value); }
public static void BackgroundColor() { var wb = new XLWorkbook(); var ws = wb.AddWorksheet("Worksheet Name"); ws.Cell("A1").Value = Data; var subHeaders = ws.Ranges("A2:C2,A6:C6,A8:C8"); subHeaders.Style .Fill.SetBackgroundColor(XLColor.Almond); subHeaders.Style.Font.SetFontColor(XLColor.AliceBlue); wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx"); }
public void RowSerializer_Exports_Into_ClosedXml() { var config = new ExampleClassMap(); var data = TestData.GenerateExampleClasses(); using var stream = new MemoryStream(); var workbook = new ClosedXML.Excel.XLWorkbook(); var worksheet = workbook.AddWorksheet("Sheet1"); worksheet.Cell(1, 1).InsertData(new[] { config.GetHeader() }); worksheet.Cell(2, 1).InsertData(ExportSerializer.RowSerializer(data, config).Select(e => e.Data)); ResultData.VerifyExcel(worksheet); }
private void WriteAccountsSheet(XLWorkbook workbook) { var accounts = profile.Accounts; var ws = workbook.AddWorksheet("Accounts"); ws.Row(1).Cell(1).SetValue("Name"); ws.Row(1).Cell(2).SetValue("# of posts"); for (int i = 0; i < accounts.Count; i++) { ws.Row(i + 2).Cell(1).SetValue(accounts[i].Name); ws.Row(i + 2).Cell(2).SetValue(accounts[i].Posts.Count); } }
public static void BorderStyleColor() { var wb = new XLWorkbook(); var ws = wb.AddWorksheet("Worksheet Name"); ws.Cell("A1").Value = Data; var subHeaders = ws.Ranges("A2:C2,A6:C6,A8:C8"); subHeaders.Style .Border.SetBottomBorder(XLBorderStyleValues.Double) .Border.SetBottomBorderColor(XLColor.Aquamarine); wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx"); }
private void btnFullMetal_Click(object sender, EventArgs e) { string strKeyWord = "毒蜥级"; string strPath = Application.StartupPath + "\\EXCEL\\" + strKeyWord + "制造方案"; Excel.XLWorkbook xFile = new Excel.XLWorkbook(); Excel.IXLWorksheet xLSheet = xFile.AddWorksheet(); int nRow = 1; int nCol = 1; OutputBluePrintResult(xLSheet, strKeyWord, ref nRow, ref nCol); xFile.SaveAs(strPath + ".xlsx"); MessageBox.Show("计算完成,路径:" + strPath); }
public void ThenTableAddedSuccessfully() { var excelTableFormatter = Kernel.Get<ExcelTableFormatter>(); var table = new Table(); table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4"); table.DataRows = new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")}); using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 6; excelTableFormatter.Format(worksheet, table, ref row); worksheet.Cell("D6").Value.ShouldEqual("Var1"); worksheet.Cell("E6").Value.ShouldEqual("Var2"); worksheet.Cell("F6").Value.ShouldEqual("Var3"); worksheet.Cell("G6").Value.ShouldEqual("Var4"); worksheet.Cell("D7").Value.ShouldEqual(1.0); worksheet.Cell("E7").Value.ShouldEqual(2.0); worksheet.Cell("F7").Value.ShouldEqual(3.0); worksheet.Cell("G7").Value.ShouldEqual(4.0); worksheet.Cell("D8").Value.ShouldEqual(5.0); worksheet.Cell("E8").Value.ShouldEqual(6.0); worksheet.Cell("F8").Value.ShouldEqual(7.0); worksheet.Cell("G8").Value.ShouldEqual(8.0); row.ShouldEqual(9); } }
public void Format(XLWorkbook workbook, GeneralTree<IDirectoryTreeNode> features) { IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0); int startRow = 1; BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features); }
public void ThenSingleScenarioWithStepsAddedSuccessfully() { var excelScenarioFormatter = Container.Resolve<ExcelScenarioFormatter>(); var scenario = new Scenario { Name = "Test Feature", Description = "In order to test this feature,\nAs a developer\nI want to test this feature" }; var given = new Step {NativeKeyword = "Given", Name = "a precondition"}; var when = new Step {NativeKeyword = "When", Name = "an event occurs"}; var then = new Step {NativeKeyword = "Then", Name = "a postcondition"}; scenario.Steps = new List<Step>(new[] {given, when, then}); using (var workbook = new XLWorkbook()) { IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 3; excelScenarioFormatter.Format(worksheet, scenario, ref row); worksheet.Cell("B3").Value.ShouldEqual(scenario.Name); worksheet.Cell("C4").Value.ShouldEqual(scenario.Description); row.ShouldEqual(8); } }