C# Excel.XLWorkbook类代码示例

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

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

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

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

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

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

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

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