C# XLWorkbook.AddWorksheet方法代码示例

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

        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 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 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 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 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, Tree features)
        {
            IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0);

            int startRow = 1;
            this.BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features);
        }

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

        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 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 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 ThenCanAddTableOfContentsWorksheetSuccessfully()
 {
     var excelTableOfContentsFormatter = new ExcelTableOfContentsFormatter();
     using (var workbook = new XLWorkbook())
     {
         IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
         excelTableOfContentsFormatter.Format(workbook, null);
     }
 }