Example #1
0
        private static void CreateExcelFile(ChromeDriver driver)
        {
            var upcomingEvents = driver.FindElementsByCssSelector(".event.active");

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Games");

            ws.Cell("A1").Value = "Games";
            ws.Cell("B1").Value = "Home";
            ws.Cell("C1").Value = "Draw";
            ws.Cell("D1").Value = "Away";

            for (int i = 0; i < upcomingEvents.Count; i++)
            {
                string eventName = upcomingEvents[i].FindElement(By.ClassName("eventName")).Text;
                string homeOdd = upcomingEvents[i].FindElement(By.ClassName("home")).Text;
                string drawOdd = upcomingEvents[i].FindElement(By.ClassName("draw")).Text;
                string awayOdd = upcomingEvents[i].FindElement(By.ClassName("away")).Text;

                ws.Cell("A" + (i + 2)).Value = eventName;
                ws.Cell("B" + (i + 2)).Value = homeOdd;
                ws.Cell("C" + (i + 2)).Value = drawOdd;
                ws.Cell("D" + (i + 2)).Value = awayOdd;
            }

            // Beautify
            ws.Range("A1:D1").Style.Font.Bold = true;
            ws.Columns().AdjustToContents();

            wb.SaveAs("../../../../Events.xlsx");
        }
        public void InsertingColumnsPreservesFormatting()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");
            IXLColumn column1 = ws.Column(1);
            column1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
            column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
            IXLColumn column2 = ws.Column(2);
            column2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
            column2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);

            column1.InsertColumnsAfter(1);
            column1.InsertColumnsBefore(1);
            column2.InsertColumnsBefore(1);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Xanadu, ws.Column(5).Style.Fill.BackgroundColor);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(2, 1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(2, 5).Style.Fill.BackgroundColor);
        }
        private static void ExportToExcelFile(DataGridView dGV, string filename, string tabName)
        {
            //Creating DataTable
            DataTable dt = new DataTable();

            //Adding the Columns
            foreach (DataGridViewColumn column in dGV.Columns)
            {
                dt.Columns.Add(column.HeaderText, column.ValueType);
            }

            //Adding the Rows
            foreach (DataGridViewRow row in dGV.Rows)
            {
                dt.Rows.Add();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                }
            }

            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt, tabName);
                wb.SaveAs(filename);
            }
        }  
Example #4
0
        // Public
        public void Create(String filePath)
        {
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Outline");

            ws.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Right;
            ws.Columns(2, 6).Group(); // Create an outline (level 1) for columns 2-6
            ws.Columns(2, 4).Group(); // Create an outline (level 2) for columns 2-4
            ws.Column(2).Ungroup(true); // Remove column 2 from all outlines

            ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Bottom;
            ws.Rows(1, 5).Group(); // Create an outline (level 1) for rows 1-5
            ws.Rows(1, 4).Group(); // Create an outline (level 2) for rows 1-4
            ws.Rows(1, 4).Collapse(); // Collapse rows 1-4
            ws.Rows(1, 2).Group(); // Create an outline (level 3) for rows 1-2
            ws.Rows(1, 2).Ungroup(); // Ungroup rows 1-2 from their last outline

            // You can also Collapse/Expand specific outline levels
            //
            // ws.CollapseRows(Int32 outlineLevel)
            // ws.CollapseColumns(Int32 outlineLevel)
            //
            // ws.ExpandRows(Int32 outlineLevel)
            // ws.ExpandColumns(Int32 outlineLevel)

            // And you can also Collapse/Expand ALL outline levels in one shot
            //
            // ws.CollapseRows()
            // ws.CollapseColumns()
            //
            // ws.ExpandRows()
            // ws.ExpandColumns()

            wb.SaveAs(filePath);
        }
Example #5
0
        public void TestRowCopyContents()
        {
            var workbook = new XLWorkbook();
            IXLWorksheet originalSheet = workbook.Worksheets.Add("original");
            IXLWorksheet copyRowSheet = workbook.Worksheets.Add("copy row");
            IXLWorksheet copyRowAsRangeSheet = workbook.Worksheets.Add("copy row as range");
            IXLWorksheet copyRangeSheet = workbook.Worksheets.Add("copy range");

            originalSheet.Cell("A2").SetValue("test value");
            originalSheet.Range("A2:E2").Merge();

            {
                IXLRange originalRange = originalSheet.Range("A2:E2");
                IXLRange destinationRange = copyRangeSheet.Range("A2:E2");

                originalRange.CopyTo(destinationRange);
            }
            CopyRowAsRange(originalSheet, 2, copyRowAsRangeSheet, 3);
            {
                IXLRow originalRow = originalSheet.Row(2);
                IXLRow destinationRow = copyRowSheet.Row(2);
                copyRowSheet.Cell("G2").Value = "must be removed after copy";
                originalRow.CopyTo(destinationRow);
            }
            TestHelper.SaveWorkbook(workbook, @"Misc\CopyRowContents.xlsx");
        }
        public void InsertingRowsPreservesFormatting()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");
            IXLRow row1 = ws.Row(1);
            row1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
            row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
            IXLRow row2 = ws.Row(2);
            row2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
            row2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);

            row1.InsertRowsBelow(1);
            row1.InsertRowsAbove(1);
            row2.InsertRowsAbove(1);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Row(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Xanadu, ws.Row(5).Style.Fill.BackgroundColor);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(1, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(3, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor);
        }
        /// Unpacks and writes out the FullNetwork file, returns the filepath 
        /// </summary>
        /// <param name="Network"></param>
        /// <param name="Path"></param>
        public string WriteOutExcelDocument(FullNetwork Network, string FileName)
        {
            // Build data table from the Network
            DataTable Table = new DataTable();
            Table.Columns.Add("Network ID");
            Table.Columns.Add("First Usable");
            Table.Columns.Add("Last Usable");
            Table.Columns.Add("Broadcast Address");

            foreach (var subnet in Network.Subnets)
            {
                // Add the data to each collumn
                DataRow Row = Table.NewRow();

                Row[0] = subnet.NetworkId;
                Row[1] = subnet.FirstUsable;
                Row[2] = subnet.LastUsable;
                Row[3] = subnet.BroadcastAddress;
                Table.Rows.Add(Row);
            }

            string FilePath = FileName + ".xlsx";

            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(Table, "Subnet Data");
            wb.SaveAs(FilePath);

            return FilePath;
        }
        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);
            }
        }
Example #9
0
        // Public
        public void Create(String filePath)
        {
            #region Create case
            {
                var workbook = new XLWorkbook();
                var ws = workbook.Worksheets.Add("Delete red rows");

                // Put a value in a few cells
                foreach (var r in Enumerable.Range(1, 5))
                    foreach (var c in Enumerable.Range(1, 5))
                        ws.Cell(r, c).Value = string.Format("R{0}C{1}", r, c);

                var blueRow = ws.Rows(1, 2);
                var redRow = ws.Row(5);

                blueRow.Style.Fill.BackgroundColor = XLColor.Blue;

                redRow.Style.Fill.BackgroundColor = XLColor.Red;
                workbook.SaveAs(filePath);
            }
            #endregion

            #region Remove rows
            {
                var workbook = new XLWorkbook(filePath);
                var ws = workbook.Worksheets.Worksheet("Delete red rows");

                ws.Rows(1, 2).Delete();
                workbook.Save();
            }
            #endregion
        }
Example #10
0
        private static void CreateExcelFile(ChromeDriver driver)
        {
            var homeTeams = driver.FindElements(By.ClassName("team-home"));
            var awayTeams = driver.FindElements(By.ClassName("team-away"));
            var scores = driver.FindElements(By.ClassName("score"));

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Scores");

            ws.Cell("A1").Value = "Home Team";
            ws.Cell("B1").Value = "Score";
            ws.Cell("C1").Value = "Away Team";

            for (int i = 0; i < homeTeams.Count; i++)
            {
                string homeTeam = homeTeams[i].Text;
                string score = scores[i].Text;
                string awayTeam = awayTeams[i].Text;

                ws.Cell("A" + (i + 2)).Value = homeTeam;
                ws.Cell("B" + (i + 2)).Value = score;
                ws.Cell("C" + (i + 2)).Value = awayTeam;
            }

            // Beautify
            ws.Range("A1:C1").Style.Font.Bold = true;
            ws.Columns().AdjustToContents();

            wb.SaveAs("../../../../FlashScore.xlsx");
        }
Example #11
0
        public void Create(string filePath)
        {
            string tempFile = ExampleHelper.GetTempFilePath(filePath);
            try
            {
                new BasicTable().Create(tempFile);
                var workbook = new XLWorkbook(tempFile);
                var ws = workbook.Worksheet(1);

                // Get a range object
                var rngHeaders = ws.Range("B3:F3");

                // Insert some rows/columns before the range
                ws.Row(1).InsertRowsAbove(2);
                ws.Column(1).InsertColumnsBefore(2);

                // Change the background color of the headers
                rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon;

                ws.Columns().AdjustToContents();

                workbook.SaveAs(filePath);
            }
            finally
            {
                if (File.Exists(tempFile))
                {
                    File.Delete(tempFile);
                }
            }
        }
        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);
            }
        }
Example #13
0
        public ActionResult CreateStandardReport(AutoModel model)
        {
            string filename = "testje.xlsx";

            XLWorkbook workbook = new XLWorkbook();
            IXLWorksheet worksheet = workbook.Worksheets.Add("Scheet");

            worksheet.ActiveCell = worksheet.Cell(string.Format("A1"));
            worksheet.ActiveCell.Value = model.Naam;
            worksheet.ActiveCell = worksheet.Cell(string.Format("B1"));
            worksheet.ActiveCell.Value = model.AantalWielen;

            //worksheet.Columns().AdjustToContents();
            worksheet.ActiveCell = worksheet.Cell("A1");

            try
            {
                DownloadExcel(workbook, filename);
                model.Message = "Succes!";
                return View(model);
            }
            catch (Exception ex)
            {
                model.Message = ex.Message;
                return View("About", model);
            }
        }
        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 exportToExcel(DataTable table, string tableName, string workSheetName, string fileName)
        {
            // Create the excel file and add worksheet
            XLWorkbook workBook = new XLWorkbook();
            IXLWorksheet workSheet = workBook.Worksheets.Add(workSheetName);

            // Hardcode title and contents locations
            IXLCell titleCell = workSheet.Cell(2, 2);
            IXLCell contentsCell = workSheet.Cell(3, 2);

            //Pretty-up the title
            titleCell.Value = tableName;
            titleCell.Style.Font.Bold = true;
            titleCell.Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
            titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

            // Merge cells for title
            workSheet.Range(titleCell, workSheet.Cell(2, table.Columns.Count + 1)).Merge();

            // Insert table contents, and adjust for content width
            contentsCell.InsertTable(table);
            workSheet.Columns().AdjustToContents(1, 75);

            // Create a new response and flush it to a memory stream
            System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx;");
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.SaveAs(stream);
                stream.WriteTo(response.OutputStream);
                stream.Close();
            }
            response.End();
        }
Example #16
0
        public void CopyingRows()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");

            IXLRow row1 = ws.Row(1);
            row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
            row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
            row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
            row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
            row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
            row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
            row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));

            ws.Cell(2, 1).Value = row1;
            ws.Cell(3, 1).Value = row1.Row(1, 7);

            IXLRow row2 = ws.Row(2);
            Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);

            IXLRow row3 = ws.Row(3);
            Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
        }
Example #17
0
        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);
            }
        }
Example #18
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("Style Worksheet");

            ws.Style.Font.Bold = true;
            ws.Style.Font.FontColor = XLColor.Red;
            ws.Style.Fill.BackgroundColor = XLColor.Cyan;

            // The following cells will be bold and red
            // because we've specified those attributes to the entire worksheet
            ws.Cell(1, 1).Value = "Test";
            ws.Cell(1, 2).Value = "Case";

            // Here we'll change the style of a single cell
            ws.Cell(2, 1).Value = "Default";
            ws.Cell(2, 1).Style = XLWorkbook.DefaultStyle;

            // Let's play with some rows
            ws.Row(4).Style = XLWorkbook.DefaultStyle;
            ws.Row(4).Height = 20;
            ws.Rows(5, 6).Style = XLWorkbook.DefaultStyle;
            ws.Rows(5, 6).Height = 20;

            // Let's play with some columns
            ws.Column(4).Style = XLWorkbook.DefaultStyle;
            ws.Column(4).Width = 5;
            ws.Columns(5, 6).Style = XLWorkbook.DefaultStyle;
            ws.Columns(5, 6).Width = 5;

            workbook.SaveAs(filePath);
        }
Example #19
0
        // Public
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("Defining a Range");

            // With a string
            var range1 = ws.Range("A1:B1");
            range1.Cell(1, 1).Value = "ws.Range(\"A1:B1\").Merge()";
            range1.Merge();

            // With two XLAddresses
            var range2 = ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address);
            range2.Cell(1, 1).Value = "ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address).Merge()";
            range2.Merge();

            // With two strings
            var range4 = ws.Range("A3", "B3");
            range4.Cell(1, 1).Value = "ws.Range(\"A3\", \"B3\").Merge()";
            range4.Merge();

            // With 4 points
            var range5 = ws.Range(4, 1, 4, 2);
            range5.Cell(1, 1).Value = "ws.Range(4, 1, 4, 2).Merge()";
            range5.Merge();

            ws.Column("A").AdjustToContents();

            workbook.SaveAs(filePath);
        }
        public void Build(GeneralTree<INode> 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())
            {
                var actionVisitor = new ActionVisitor<INode>(node =>
                {
                    var featureDirectoryTreeNode =
                        node as FeatureNode;
                    if (featureDirectoryTreeNode != null)
                    {
                        IXLWorksheet worksheet =
                            workbook.AddWorksheet(
                                this.excelSheetNameGenerator.GenerateSheetName(
                                    workbook,
                                    featureDirectoryTreeNode
                                        .Feature));
                        this.excelFeatureFormatter.Format(
                            worksheet,
                            featureDirectoryTreeNode.Feature);
                    }
                });

                features.AcceptVisitor(actionVisitor);

                this.excelTableOfContentsFormatter.Format(workbook, features);

                workbook.SaveAs(spreadsheetPath);
            }
        }
Example #21
0
 public void IsBlank_false()
 {
     var ws = new XLWorkbook().AddWorksheet("Sheet");
     ws.Cell("A1").Value = " ";
     var actual = ws.Evaluate("=IsBlank(A1)");
     Assert.AreEqual(false, actual);
 }
        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 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);
            }
        }
 //var wb = new XLWorkbook(northwinddataXlsx);
 public ExcelHelper(string filePath)
 {
     this.filePath = filePath;
     workbook = new XLWorkbook(filePath);
     if(workbook.Worksheets.Count>0)
         worksheet = workbook.Worksheet(1);
 }
Example #25
0
 /// <summary>
 /// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
 /// </summary>
 /// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
 /// <param name="configuration">The configuration.</param>
 public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
 {
     workbook = worksheet.Workbook;
     this.worksheet = worksheet;
     this.configuration = configuration;
     FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
 }
Example #26
0
 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");
 }
Example #27
-1
        private void findProvderName()
        {
            Point providerLocation = new Point(0, 0);
            for (int metricNumber = 0; metricNumber < 16; metricNumber++)//total numebr of metrics = 15
            {
                if (metricNumber < 10)
                    workbook = workbooks[0];
                else if (metricNumber == 10)
                    workbook = workbooks[1];
                else if (metricNumber > 10 && metricNumber < 14)
                    workbook = workbooks[2];
                else if (metricNumber == 14)
                    workbook = workbooks[3];
                else if (metricNumber == 15)
                    workbook = workbooks[4];

                var sheet = workbook.Worksheet(1);
                var colRange = sheet.Range("A:A");
                foreach (var cell in colRange.CellsUsed())
                {
                    if (cell.Value != null)
                    {
                        String value = (String)cell.Value;
                        int cellRow = cell.Address.RowNumber;
                        if (value.Contains(provider))
                        {
                            providerLocation = new Point(1, cellRow);
                            setMetricDataLocations(providerLocation, metricNumber);
                            break;
                        }
                    }
                }
            }
        }
Example #28
-1
        public ExcelWriter(string newFilePath)
        {
            if (!string.IsNullOrEmpty(newFilePath))
                filePath = newFilePath;

            workbook = new XLWorkbook();
        }
Example #29
-1
        public void _openExcel(String dashboardFile, String metricsFolder)
        {
            //grab all the files in the metrics folder
            metrics_files = Directory.GetFiles(metricsFolder);

            if (File.Exists("Copy.xlsx"))
            {
                File.Delete("Copy.xlsx");
            }
            File.Copy(dashboardFile, "Copy.xlsx");

            //try openeing the excel sheets listed in metricsFile
            try
            {
                _dashboard = new XLWorkbook(dashboardFile);

                //iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then  extrract data from
                //metrics reports and insert into those sheets
                int numSheets = _dashboard.Worksheets.Count;
                for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                    {
                        var sheet = _dashboard.Worksheet(sheetNum);
                        prepDashboardSheets(sheet);
                     }
                _dashboard.Save();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
Example #30
-6
        public void CanGetNamedFromAnother()
        {
            var wb = new XLWorkbook();
            var ws1 = wb.Worksheets.Add("Sheet1");
            ws1.Cell("A1").SetValue(1).AddToNamed("value1");

            Assert.AreEqual(1, wb.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws1.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue<int>());

            var ws2 = wb.Worksheets.Add("Sheet2");

            ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2");

            Assert.AreEqual(1, wb.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue<int>());
        }