C# (CSharp) ClosedXML.Excel XLWorkbook Examples # (CSharp) ClosedXML.Excel XLWorkbook - 30 examples found. These are the top rated real world C# (CSharp) examples of ClosedXML.Excel.XLWorkbook extracted from open source projects. You can rate examples to help us improve the quality of examples. Programming Language: C# (CSharp) Namespace/Package Name: ClosedXML.Excel Class/Type: XLWorkbook (https:/icsharp.hotexamples. com/examples/ClosedXML. Excel/XLWorkbook/-iphp- xIworkbook-class-examples.html) Examples at hotexamples.com: 30 FREQUENTLY USED METHODS Show Facebook Twitter Print Email Pinterest More 7 EXAMPLE #1 af |_| (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xworkbook-class-exampli Show file File: ShiftingRanges.cs (https://esharp.hotexamples.comisite/redirect ?url=https%3A%2F %2Fgithub.com%2Fh Project: hal1932/ClosedXML (https://csharp.hotexamples.com/site/redirect?url-https%3A%2F %2F github.comun public void Create(string FilePath) 2 { B string tempFile = ExampleHelper .GetTempFilePath( FilePath); “ try 4s { 16 new BasicTable().Create(tempFile) ; v var workbook = new XLWorkbook(tenpFile) ; 18 var ws = workbook.Worksheet (1); 19 2 // Get a range object 2 var rngHeaders = ws.Range("B3:F3"); 2 2B // Insert sone rows/columns before the range 2 ws .Row(1).TnsertRowsAbove( 2); 25 ws -Colunn(1) .InsertColumnsBefore(2); 26 a // Change the background color of the headers 28 rngHeaders Style. Fill BackgroundColor = XLColor. LightSalmon; 2 30 ws .Colunns() .AdjustToContents()5 31 32 workbook. SaveAs (FilePath); 33 } 34 Finally 35 t 36 if (File.Exists(tempFile)) 37 { 38 File.Delete(tenpFile); 39 } 40 } at > (hitps://icsharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbook/-/php-xlworkbook-class-exampli Show file File: ExcelOutput.os (https://csharp hotexamples.com/site/redirect?url=https%3A%2F%2F github.com%2FWar Project: Wamadahama/Subnetting-Calculator (https://csharp.hotexamples.comIsite/redirect?url=https%3A%2a8 19 2 a 2 2B Pry 25 26 a 28 29 30 31 32 33 35 36 37 38 39 a 43 45 47 49 50 EXAMPLE #3 Show file //1 Unpacks and writes out the FuliNetwork file, returns the filepath IIL /// /// public string WriteOutexcelDocunent (FullNetwork Network, string FileName) { // Build data table from the Network DataTable Table = new DataTable(); Table.Colunns .Add("Network 1D"); Table.Colunns.Add("First Usable"); Table.Columns.Add("Last Usable"); Table.Colunns .Add("Broadcast Address"); foreach (var subnet in Network. Subnets) t // Add the data to each collumn DataRow Row = Table.NewRow(); Row[@] = subnet. NetworkId; Row[1] = subnet.FirstUsable; Row[2] = subnet.LastUsable; Row[3] = subnet .BroadcastAddress; Table .Rows.Add(Row) ; } string FilePath = FileName + ".x1sx"} XLWorkbook wb = new XLWorkbook(); wb.Worksheets.Add(Table, “Subnet Data"); wb.SaveAs(FilePath) ; return FilePath; if) (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class-examplt File: Utis.cs (https://csharp.hotexamples. com/site/redirect?url=https%3A%2F %2F github.com%2FEnriqueRive Project: EnriqueRivera/InventoryApplication (hitps://esharp.hotexamples.comi/site/redirect?url=https%3A%2F*47 49 50 51 52 53 55 56 57 58 59 61 62 6 6 66 7 68 69 78 n R EXAMPLE #4 Show file private static void ExportToFxcelFile(DataGridView dGv, string filename, stri t /Creating DataTable DataTable dt = new DataTable(); //adding the Columns foreach (DataGridViewColumn column in d6V.columns) t dt.Columns.Add(column.HeaderText, column.ValueType) ; //Adding the Rows foreach (DataGridViewRow row in dGV.Rows) t dt. Rows Add() 5 foreach (DataGridViewCell cell in row.cells) « dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToStrin y y using (XLWorkbook wo = new xLWorkbook()) { wb.Worksheets.Add(dt, tabName) ; wb. SaveAs(filename) ; } o [| (htips://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli File: StyleWorksheet cs (https:/osharp hotexamples. com/site/redirect?uri=https%3A%2F %2F github. com%2Ft Project: hal1932/ClosedXML (hitps://csharp.hotexamples.com/site/rediract?url=https%3A%2F%2F github.comun public void Create(String FilePath) 2 { B var workbook = new XLWorkbook(); 4 var ws = workbook.Worksheets.Add("Style Worksheet"); as 16 ws.Style.Font.Bold = true; v ws.Style.Font.FontColor = XLColor.Red; 18 ws.Style.Fill.BackgroundColor = XLColor.Cyan; 19 2 // The following cells will be bold and red a // because we've specified those attributes to the entire worksheet 2 ws-Cell(1, 1).Value 2B ws.Cell(1, 2).Value m4 3 // Here we'll change the style of a single cell 6 ws.Cell(2, 1).Value = "Default"; a ws.Cell(2, 1).Style = XLWorkbook.Defaultstyle; 2B 29 // Let's play with some rows 30 ws-Row(4).Style = XLWorkbook.DefaultStyle; Et ws-Row(4).Height = 2% 32 ws-Rows(5, 6).Style = XLWorkbook.Defaultstyle; 33 ws-Rows(5, 6)-Height = 20; 34 35 // Let's play with some columns 36 ws.Colunn(4).Style = XLWorkbook.Defaultstyle; 7 ws.Column(4).Width = 5; 38 ws.Colunns(5, 6).Style = XLWorkbook.Defaultstyle; 39 ws.Columns(5, 6).Width = 5; 40 aa workbook. Saveas (FilePath) ; 42 } (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xlworkbook-class-examplt Show file File: InsertingRangesTests.cs (https://esharp.hotexamples. com/site/rediroct?uri=https%3A%2F %2Fgithub.con Project: hal1932/ClosedXML (https:l/csharp.hotexamples.com/site/redirect?url-https%3A%2F %2F github.coma 62 6 65 67 68 69 7” n R B 74 rs 76 n B r7) 80 a1 82 3 85 86 87 EXAMPLE #6 Show file public void InsertingRowsPreservesFormatting() t var wb = new XLWorkbook(); IXLWorksheet ws = wo.Worksheets.Add("Sheet"); IXLRow row = ws-Row(1) 5 rowl.Style. Fill. SetBackgroundColor (XLColor.FrenchLilac); owt Cell (2). Style. Fill .SetBackgroundColor (XLColor.Fulvous) ; IXLRow row2 = ws.Row(2) 3 row2. Style. Fill. SetackgroundColor (XLColor.Xanadu) ; row2. Cell (2) Style. Fill .SetBackgroundColor (XLColor .MacaroniAndCheese) ; owl. InsertRowsBelow(1); Fowl. InsertRowsAbove(1); row2.InsertRowsAbove(1) 5 Assert .AreEqual (ws.Style.Fill.BackgroundColor, ws.Row(1).Style. Fill Backg Assert .AreEqual (XLColor.FrenchLilac, ws.Row(2).Style. Fill BackgroundColor Assert .AreEqual (XLColor.FrenchLilac, ws.Row(3).Style.Fill. BackgroundColor Assert Aretqual (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.8 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.Backg o [| (htips://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli File: InsertingRangesTests.cs (https://osharp.hotexamples.com/site/redirect?uri=https%3A%2F %2F github.con Project: hal1932/ClosedXML (hitps://csharp.hotexamples.com/site/rediract?url=https%3A%2F%2F github.com1s 16 v 18 19 2 a 2 23 Pa 25 26 7 28 29 30 31 32 33 35 36 37 38 39 a EXAMPLE #7 Show file public void InsertingColumnsPreservesFormatting() t var wb = new XLWorkbook(); IXLWorksheet ws = wo.Worksheets.Add("Sheet"); TXLColumn column = ws.Column(1)5 column Style. Fill SetBackgroundColor(XLColor.FrenchLilac) ; colunni .cel1(2).Style. Fill SetBackgroundColor(xLColor .Fulvous) ; TXLColumn column? = ws.Column(2) 5 column2. Style. Fill SetBackgroundColor (XLColor .Xanadu) ; column2.Cel1(2) .Style. Fill. SetBackgroundColor (xLColor .MacaroniAndCheese) colunn.InsertColumnsafter(1); colunn1. InsertColumnsBefore(1)5 column2.InsertColumnsBefore(1); Assert .AreEqual (ws.Style.Fill.BackgroundColor, ws.Column(1).Style.Fill.Ba Assert .AreEqual (XLColor.FrenchLilac, ws.Column(2) .Style.Fill.Backgroundco Assert Areequal (XLColor.FrenchLilac, ws.Colunn(3).Style.Fill.Backgroundco Assert .Aretqual (XLColor.FrenchLilac, ws.Column(4).Style.Fill.Backgroundco Assert .AreEqual(XLColor.Xanadu, ws.Column(5).Style.Fill BackgroundColor) ; Assert -AreEqual (ws.Style.Fill.BackgroundColor, ws.Cell(2, 1).Style.Fill.8 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.Backg o [| (htips://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli File: ExcelDocumentationBuilder.os (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F%2Fgitht, Project: vavavivi/pickles (hitps://esharp.hotexamples. comv/site/redirect?uri=https' 3A%2F%2F github.com%2F7 78 9 80 a1 82 8 85 86, 87 88 29 1 22 93 95 96. 7 98 100 101 102 103 104 165 106 107 108 109 10 a1 EXAMPLE #8 Show file Fi public void Build(GeneralTree features) { if (Log. Isinfonabled) { Log.Info("Writing Excel workbook to {@}", this.configuration.utputro } string spreadsheetPath = this. filesystem.Path.Conbine(this.configuration.| using (var workbook = new XLWorkbook()) { var actionVisitor = new ActionVisitor(node => { var featureDirectoryTreeNode = node as FeatureNode; if (featureDirectoryTreeNode != null) { IXLWorksheet worksheet = workbook . AddWorksheet ( this. excelSheetNaneGenerator .GenerateSheetNane( workbook, FeatureDirectoryTreeNode -Feature)); this.excelFeaturerormatter.Format( worksheet, featureDirectoryTreeNode. Feature); t ys features .AcceptVisitor(actionVisitor); this.excelTableofContentsFormatter.Format (workbook, features); workbook . SaveAs(spreadsheetPath) ; } > oT (htips://esharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbool/-/php-xlworkbook-class-exampli : ExcelHelper.cs (https:/icsharp.hotexamples.com/site/redirect?url=htips%3A%2F%2F github.com%2Fsanc Project: sandeepsajiru/DataDrivenFramework (https://csharp.hotexamples.comIsite/redirect?urI=https%3A%223 | //var wb = new XtWorkbook(northwinddataXsx) ; 24 | public ExcelHelper(string filePath) we) 6 this.filePath = FilePath; 7 workbook = new XLWorkbook(filePath) ; 28 if (workbook Worksheets .Count>®) 29 worksheet = workbook.Worksheet(1) 5 3} EXAMPLE #9 0 [LJ (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xiworkbook-class-exampli Show file File: ProofOfConcept.cs (https://csharp.hotexamples.comIsite/redirect?url=https%3A%2F%2F github.com%2F Project: Balfa/ClosedXmiSandbox (https://esharp.hotexamples.com/sitelredirect?url=https%3A%2F %2Fgithut 58 | public static void Basic() so) « var wb = new XLWorkbook() ; 61 var ws = wb.AddWorksheet ("Worksheet Name") ; 62 ws.Cel1("A1").Value = Data; 6 wb Savehs (MethodBase.GetCurrentMethod().Name + " |} EXAMPLE #10 0 CE) (htips://esharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xiworkbook-class-exampl Show file GraphDataGenerator.cs (https:l/csharp-hotexamples.com/site/redirect?url=https%3A%2F %2Fgithub.com Project: lycilph/Projects (nttps://csharphotexamples.com/site/redirect?uri=https%3A%2F%2F github.com%2F88 a1 82 B 85 86, a7 88 89 a1 92 93 95 96, 7 98 99 100 101 102 103 104 105 106 107 168 109 110 m1 nz 3 EXAMPLE #11 Show file private void WriteCategoriesByMonth(XLWorkbook workbook) t var ws = workbook. Addworksheet("Categorization (by month)"); var all_posts = profile.AggregatedPosts().Order8y(p => p.date); var first_date = all_posts.First().bate; var last_date = all_posts.Last().Date; var all_categories = Category.Flatten(profile.RootCategory) ; var posts_by category = all_categories. ToDictionary(category => category, ws Row(1).Cel1(1).SetValue("Date"); for (int i = @; i < all_categories.Ccount; i++) { ws-Row(1).Cell(i + 2).SetValue(all_categories[i].Nane) ; 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) t ws .Row(current_row).Cel1(1).SetValue(current_date.ToShortbateString() for (int j = t 3 J < all_categories.count; 3+) var total = posts_by_category[all_categories[j]].Where(p => p.Dat ws Row(current_row).Cell(j + 2).SetValue(total); current_row++; current_date = current_date.AddMonths(1); oT (https://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli File: WhenAddingATableToAWorksheet os (https://esharp.hotexamples.com/site/redirect?uri=https%3A%2F%: Project: testpulse/pickles (https://csharp hotexamples. com/site/redirect?url-https%3A%2F%2F github.com%216 v 18 19 20 a 2 2B 24 23 6 7 28 29 30 31 32 33 35 36 7 38 39 a 3 EXAMPLE #12 Show file public void ThenTableaddedsuccessfully() { var excelTableFormatter = Container .ResolvecexcelTableFornatter>(); var table = new Table(); table.HeaderRow = new TableRow("Var1", “Var2", “Var3", "Vara"); table.DataRous new List(new[] {new TableRow: ", "4"), new Table using (var workbook = new XLWorkbook()) t IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1"); int row = 6; excelTableFormatter.Format(worksheet, table, ref row); Check. That (worksheet .Cel1("D6").Value) .TsEqualTo("Vari"); Check. That (worksheet .Cel1("E6").Value) .TsEqualTo(“Var2"); Check. That (worksheet .Cell("F6").Value).TsEqualTo(“Var3"); Check. That (worksheet .Cel ("66"). Value) .TsEqualTo("vara"); Check. That (worksheet .Cel1("D7").Value) .TsEqualTo(1.8); Check. That (worksheet .Cel("E7").Value) .TsEqualTo(2.8); Check. That (worksheet .Cel1("F7"). Value) .TsEqualTo(3.@)3 Check. That (worksheet .Cel1("67").Value).TsEqualTo(4.@); Check. That (worksheet .Cel("D8").Value).TsEqualTo(5.@); Check. That (worksheet .Cel("E8").Value).TsEqualTo(6.@); Check. That (worksheet .Cel1("F8"). Value) .TsEqualTo(7.0)5 Check. That (worksheet .Cel ("G8") .Value).TsEqualTo(8.8); Check. That (row) .TsEqualTo(9); oL [| (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xIworkbook-class-examplt File: IsTests.cs (hitps:/csharp.hotexamples.com/site/redirect?url=hitps%3A%2F%2F github.com%2Fhal1 932 Project: hal1932/ClosedXML (https://csharp.hotexamples.com/site/redirect?u htlps%3A%2F%2Fgithub.com15 | public void IsBlank_false() we) ¢ v var ws = new XLWorkbook().AddWorksheet ("Sheet"); 18 ws.Cel1("A1").Value = " 19 var actual = ws.Evaluate("=IsBlank(A1)"); 20 Assert AreEqual(false, actual); a} } EXAMPLE #13 0 CEJ (hitps://esharp.hotexamples.com/examples/ClosedXML.Excel/XLWorkbool/-/php-xlworkbook-class-exampli Show file Fi ExcelParser.cs (https://esharp.hotexamples.comisite/redirect?url=https%3A%2F %2Fgithub.com%2F Dus Project: DustinH/CsvHelper.Excel (https://csharp.hotexamples.comlsitelredirect?url=https*%3A%2F%2F github 63 | ///

64 | /// Creates a new parser using the given 66 | /// The with the data. 67 | /// The configuration. 68 | public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration) | ¢ XLWorksheet"/> and cell Address .ColumnNun ml y EXAMPLE #14 0 [ [J (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class-examplt Show file File: WhenAddingAFeatureToAWorksheet.cs (https://csharp hotexamples.com/site/redirect?url=https%3A%2F Project: picklesdoc/pickles (hitps://csharp.hotexamples.com/site/redirect?url=hitps%3A%2F%2Fgithub,com%2 63 6 67 68 «9 78 n n B 74 ws 76 7 78 9 a1 82 8 85 86 87 88 89 a1 EXAMPLE #15 Show file public void Then_feature_with_background_is_added_successfully() t var excelFeatureFormatter = Container.Resolve(); var feature = new Feature t Name = "Test Feature", Description = "In order to test this feature,\nAs a developer\nI want to test t ub var background = new Scenario t Name = "Test Background Scenario", Description = "In order to test this background, \nAs a developer\nI want to tes b var given = new Step { NativeKeyword = "Given", Name = “a precondition” } background. Steps = new List(new{] { given }); feature. AddBackground(background) ; using (var workbook = new XlWorkbook()) { IXLWorksheet worksheet = workbook. AddWorksheet ("SHEET"); excelFeatureFormatter Format (worksheet, feature); Check. That (worksheet .Cel1("B4"). Value) . sequal To(background. Name) ; Check. That (worksheet .Ce11("C5"). Value) .TsEqualTo(background.Descripti Check. That (worksheet .Cel1 ("D6"). Value). IsEqualTo(given.Nane); } 0 [| (https:/csharp. hotexamples.comvexamples/ClosedXML.Excel/XLWorkbool’-/php-xlworkbook-class-exampli File: DeleteRows.cs (https://csharp. hotexamples.com/site/redirect? url=https%3A%2F%2F github.com%2Fhalt Project: hal1932/ClosedXML (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F%2Fgithub.com2 B “ 1s 16 yw 18 19 20 a 2 23 2 25 26 7 28 29 30 31 32 33 35 36 37 38 39 a 3 EXAMPLE #16 Show file File: Sportingbet.cs (https:/icsharp.hotexamples.com/site/redirect?u // Public public void Create(String FilePath) { region Create case t 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, ¢).Value = string. Format("R{@}C{1}", Fr, ¢)5 var blueRow = ws.Rows(1, 2); var redRow = ws-Row(5); blueRiow.Style.Fill BackgroundColor = XLColor.Blue; redRow.Style.Fill.BackgroundColor = XLColor.Reds workbook. SaveAs (FilePath) ; > endregion region Remove rows t var workbook = new XLWorkbook (FilePath); var ws = workbook.Worksheets Worksheet ("Delete red rows"); ws.Rows(1, 2).Delete(); workbook -Save(); } endregion } o [| (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class-examplt ittps%3A%2F %2F github.com%2Fstobe Project: stoberov/Betman (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F %2F github.com%27 private static void CreatetxcelFile(ChromeDriver driver) 38 { 39 var upcomingEvents = driver.FindElementsByCssSelector(".event.active"); 40 a var wb = new XLWorkbook(); 42 var ws = wb.Worksheets.Add("Games") ; 3 a ws.Cel1("A1").Value = "Games"; 45 ws-Cel1("B1").Value 46 ws.Cel1("C1").Value = ” ws-Cell("D1").Value = 48 49 for (int i = @; i < upcomingévents.Ccount; i++) 50 { sa string eventName = upcomingEvents[i] .FindElement (By.ClassNane("eventN 52 string homeOdd = upcomingEvents[4].FindElement(By.ClassName("home")) . 53 string drawOdd = upcomingEvents[ i] .FindElement (By.ClassName("draw")) 54 string awayOdd = upcomingévents[ i] .FindElement(By.ClassName("away")) 55 56 ws.Cell("A" + (i + 2)).Value = eventName; s7 ws.Cell("B" + (i + 2)).Value = homeodd; 58 ws.Cell("C" + (i + 2)).Value = drawodd; 59 ws.Cell("D" + (i + 2)).Value = awayOdd; @ + a 2 // Beautify 6 ws. Range("A1:D1").Style.Font.Bold = true; 64 ws .Columns().AdjustToContents(); 6 65 wb.Saveas("../../../../Events.x1sx"); 7 > EXAMPLE #17 4 CL) (https://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli Show file File: Outline.cs (https://csharp.hotexamples.com/site/redirect?urf=https%3A%2F %2F github.com%2Fhal19329 Project: hal1932/ClosedXML (https://csharp.hotexamples. com/site/redirect? urt=https%3A%2F %2F github.comnu 2 B “ 15 16 v 18 19 2 a 2 2B 2 2s 26 a 28 29 30 31 32 33 35 36 37 38 39 a1 43 45 EXAMPLE #18 Show file // Public public void Create(String filePath) { var wb = new XLWorkbook(); var ws = wo.Worksheets.Add("Outline") Ws. OutLine.SunmaryHLocation = XLOut]ineSunmaryHLocation Right ws.Colunns(2, 6).Group(); // Create an outline (level 1) for columns 2-6 ws.Colunns(2, 4).Group()3 // Create an outline (level 2) for columns 2-4 ws.Column(2).Ungroup(true); // Remove column 2 from all outlines ws .OutLine.SunmaryVLocation = XLOut]ineSunmaryVLocation Bottoms 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 “se // ws.CollapseRows(Int32_ out1ineLevel) // ws.CollapseColunns(Int32 outlineLevel) u // ws.ExpandRows(Int32 outlineLevel) // ws. Expandcolunns(Int32 outlineLevel) // nd you can also Collapse/Expand ALL outline levels in one shot “se // ws.CollapseRows() // ws.CoLlapseColunns() “se // ws. ExpandRows() // ws.ExpandColunns() wb. SaveAs (FilePath); > tf) (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class-examplt File: DefiningRanges.cs (https://csharp.hotexamples. comi/site/redirect?ur!=https%3A%2F %2F github.com%2F!Project: hal1932/ClosedXML (hitps://csharp.hotexamples.com/site/rediract?url=https%3A%2F%2Fgithub.com u // Public 2 public void Create(string FilePath) B { “ var workbook = new XLWorkbook(); 5 var ws = workbook Worksheets Add("Defining a Range"); 16 v // With a string 18 var rangel = ws.Range("A1:81"); Et rangel.Cell(1, 1).Value = “ws.Range(\"A1:81\") .Merge()"5 28 rangel.Merge(); a 2 // With two XLAddresses B var range2 = ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address); ma range2.Cell(1, 1).Value = “ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2). 2s range2.Merge(); 6 7 // With two strings 28 var rangeé = ws.Range("A3" 29 range4.Cell(1, 1).Value = "ws.Range(\"A3\", \"B3\").Merge()"5 38 range4.Merge(); 31 32 // With 4 points 33 var rangeS = ws.Range(4, 1, 4, 2); 34 range5.Cell(1, 1).Value = “ws.Range(4, 1, 4, 2).Merge()" 35 rangeS .Merge(); 36 37 ws Colunn("A") .AdjustToContents()5 38 39 workbook. Saveas (FilePath) ; 40 } EXAMPLE #19 4 CE) (htips:/icsharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xlworkbook-class-examplt Show file Fi iabetesMetric.cs (https://osharp. hotexamples.com/site/redirect?url=https%3A%2F%2F github.com%2Fs Project: secgoat/Dashboards (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F %2F github.con52 53 55 56 57 58 59 61 a 63 6s 66 67 68 9 78 n nR B 74 B 76 7 78 9 80 a1 82 8 85 EXAMPLE #20 Show file private void findProvderName() t Point providerLocation = new Point(@, 8); for (int metricNumber = @; metricNumber < 16; metricNumber++)//total nume t if (metricNumber < 10) workbook = workbooks[@] ; else if (metricNumber == 10) workbook = workbooks [1] else if (metricNunber > 10 && metricNunber < 14) workbook = workbooks [2]; else if (metricNunber == 14) workbook = workbooks [3] else if (metricNunber == 15) workbook = workbooks [4]; var sheet = workbook.Worksheet(1); var colRange = sheet.Range("A:A"); foreach (var cell in colRange.Cellsused()) t if (cell.Value != null) { String value = (String)cell.Value; int cellRow = cell Address. RowNunber if (value.Contains(provider)) { providerLocation = new Point(1, cellRow); setMetricbataLocations(providerLocation, metricNumber); break; + } > + } 4D) (https://esharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-examplt File: Exce!TableOfContentsFormatter.cs (https://csharp.hotexamples. comisite/redirect?url=https%3A%2F%2F Project: ppnrao/pickles (https://csharp hotexamples. com/site/redirect?url=https%3A%2F%2Fgithub.com%2FE7 68 cy 78 n n B EXAMPLE #21 Show file Fi public void Format(XLWorkbook workbook, GeneralTree featu t IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", @); int startRow 1b BuildTableofcontents (workbook, tochorksheet, ref startRow, 1, features); ‘LL (htips://esharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook-/php-xlworkbook-class-exampli : ExcelHelper.cs (https:/icsharp.hotexamples.com/site/redirect?7url=https%3A%2F%2F github,com%2Ftaka Project: takajimy/Browsit-NewerButStillOld (nttps://csharp hotexamples.com/site/redirect?url=https%3A%2F%20 public static void exportToFxcel (DataTable table, string tableName, string wo a { 2 // Create the excel file and add worksheet 2B XLWorkbook workBook = new XLWorkbook(); 24 IXLWorksheet workSheet = workBook.Worksheets .Add(workSheetName) ; 3 26 // Hardcode title and contents locations a IXLCell titleCell = workSheet.cell(2, 2)5 2B IXLCell contentsCell = workSheet.cell(3, 2); 29 30 //Pretty-up the title 31 titleCell.Value = tableName; 32 titlecell.style.Font.Bold = true; 33 titleCell.style.Fill. BackgroundColor = XLColor.CornflowerBlue; 34 titleCell.style.Alignment.Horizontal = XLALignmentHorizontalValues..Center 35 36 // Merge cells for title 37 workSheet .Range(titleCell, workSheet.Cell(2, table.Columns.Count + 1)).Me 38 39 // Insert table contents, and adjust for content width 40 contentsCell. InsertTable(table) ; a1 workSheet .Columns().AdjustToContents(1, 75); 42 a // Create a new response and flush it to a memory stream a System.Web.HttpResponse response = System.Web.HttpContext .Current.Respons 45 response.Clear(); 46, response. AddHeader("Content-Disposition", “attachment; filename=" + fileN a7 using (MemoryStream stream = new MemoryStream()) 48 t 49 workBook . SaveAs (stream) ; 50 stream.WriteTo(response.OutputStream) ; 51 stream.Close(); 52 } 53 response.End(); sa , EXAMPLE #22 4 CE) (htips://esharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbool-/php-xlworkbook-class-exampli Show file Fi : CopyContentsTests.cs (https://csharp. hotexamples.com/site/redirect?url=https%3A%2F%2Fgithub.com% Project: hal1932/ClosedXML (https:l/csharp.hotexamples.com/site/redirect?url-https%3A%2F %2F github.com88 a1 82 3 85 86, a7 88 89 a1 92 93 95 96 7 98 99 100 101 102 103 104 105 EXAMPLE #23, Show file Fi public void TestRowCopyContents() t var workbook = new XLWorkbook(); TXLWorksheet originalSheet = workbook.Worksheets .Add( "original! TXLWorksheet copyRowSheet = workbook.Worksheets.Add(""copy row"); TXLWorksheet copyRowAsRangeSheet = workbook.Worksheets.Add("copy row as TXLWorksheet copyRangeSheet = workbook.Worksheets .Add(""copy range"); originalSheet .cell("A2").SetValue(“test value"); originalsheet .Range("A2:£2") .Merge(); t IXLRange originalRange = originalSheet.Range("A2:£2"); IXLRange destinationRange = copyRangeSheet .Range("A2:E2"); original Range . CopyTo(destinationRange); + CopyRowAsRange(originalSheet, 2, copyRowAsRangeSheet, 3); t IXLRow originalRow = originalSheet.Row(2); TXLRow destinationRow = copyRowSheet .Row(2); copyRowSheet .cell("62").Value = “must be removed after cop} originalRow .CopyTo(destinationRow); + TestHelper .SaveWorkbook(workbook, @"Misc\CopyRowContents.x1sx") 5 } ‘LL ) (https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class-examplt WhenAddingAScenarioToAWorksheet.cs (https://esharp.hotexamples.com/site/redirect?url=https%3A%2 Project: eduaquiles/pickles (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F %2F github.com%39 public void ThenSingleScenariowithstepsAddedsuccessfully() 40 { a var excelScenarioFornatter = Container. ResolvecExcelScenarioFormatter>(); 2 var scenario = new Scenario 4“ { on Name = “Test Feature", 4s Description = 46 "In order to test this feature,\nAs a develope a7 hs 48 var given = new Step {NativeKeyword = "Given", Name = "a precondition"); 49 var when = new Step (NativeKeyword = “When”, Name = “an event occurs"}; 50 var then = new Step {NativeKeyword = "Then", Name postcondition"}; 51 scenario.Steps = new List(new[] (given, when, then}); 52 53 using (var workbook = new XLWorkbook()) 54 { 55 IXLWorksheet worksheet = workbook. AddWorksheet ("SHEET"); 56 int row = 33 37 excelScenarioFormatter.Format (worksheet, scenario, ref row); 58 59 worksheet .Ce11("83"). Value. ShouldEquall(scenario.Name) ; « worksheet .Cel1("C4"). Value. ShouldEquall (scenario.Description) ; 61 row. ShouldEqual (8); 62 > 63 } EXAMPLE #24 4 CE) (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xlworkbook-class-exampli Show file File: Exce|Writer.cs (https://csharp.hotexamples.comIsite/redirect?url=https%3A%2F%2Fgithub,com%2Fjnykit Project: jnykiel/TestLink2Excel (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F %2F github.co 25 public Excelbiriter(string newFilePath) 6 { a if (Istring. IsNul1orempty (newFilePath)) 28 FilePath = newFilePath; 2 30 workbook = new XLWorkbook() ; 31 yEXAMPLE #25 4 | (htips:/icsharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xlworkbook-class-examplt Show file File: CopyingRangesTests.cs (https’//esharp.hotexamples.comisite/redirect?url=https%3A%2F %2F github, com Project: hal1932/ClosedXML (https://csharp.hotexamples.com/site/redirect?urlhttps%3A%2F%2F github.com 54 public void CopyingRows() 55 { 56 var wb = new XLWorkbook(); 57 TXtWorksheet ws = wb.Worksheets .Add("Sheet") ; 58 59 IXLROW row = ws.Row(1)5 @ Prowl .Cell(1) Style. Fill SetBackgroundColor(XLColor.Red) ; 61 Fowl .Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fromargb(1, 1, 1)); 62 Fowl .Cel1(3).Style. Fill SetBackgroundColor(XLColor. Froniitml("#CCCCCC")) ; 6 row1 Cell (4) Style. Fill .SetBackgroundColor(XLColor . Fromindex(26)); 64 row1.Cell(S).Style. Fill .SetBackgroundColor(XLColor. FromKnownColor (KnownCo. 65 Fowl .Ce11(6).Style, Fill SetBackgroundColor(XLColor.FromName("Blue")) 5 66 owl .Cell(7).Style. Fill .SetBackgroundColor(xLColor. FronTheme(XLThemeColor 7 68 ws.Cell(2, 1).Value = rowl; cy ws.Cel1(3, 1).Value = rowt.Row(1, 7); 7 n TXLRow row2 = ws.Row(2) 5 n Assert AreEqual(XLColor.Red, row2.Cel1(1).Style.Fill BackgroundColor) ; B Assert .AreEqual (XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style. Fill. Backer: 1% Assert .AreEqual (XLColor.FromHtml ("#CCCCCC"), row2.Cell(3).Style.Fill Back B Assert .AreEqual (XLColor.FromIndex(26), row2.Cell(4).Style.Fill Background 6 Assert .AreEqual (XLColor.FromknownColor(knownColor .MediumSeaGreen), row2.C n Assert .AreEqual (XLColor.FromName("Blue"), row2.Cell(6).Style. Fill Backgro "8 Assert .AreEqual (XLColor.. FromTheme(XLThemeColor Accent3), row2.Cell(7).Sty 9 80 TXLROW row3 = Ws.Row(3) 5 a1 Assert AreEqual(XLColor.Red, row3.Cell(1).Style. Fill BackgroundColor) ; 2 Assert Aretqual (XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style. Fill. Backgr: 8 Assert .AreEqual (XLColor.FromHtml ("#CCCCCC"), row3.Cel1(3).Style.Fill Back En Assert .AreEqual (XLColor.FromIndex(26), row3.Cel1(4).Style.Fill Background 85 Assert .AreEqual (XLColor .FromknownColor (KnownColor .MediunSeaGreen), row3.C 86 Assert .AreEqual (XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.Backgro 87 Assert .AreEqual (XLColor.FromTheme(XLThemeColor Accent3), row3.Cell(7).Sty 88 yEXAMPLE #26 4 (tJ (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xiworkbook-class-exampl Show file Fi DashboardUpdate.cs (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F %2F github.com%z Project: secgoat/Dashboards (https://csharp.hotexamples.com/site/redirect?7url=https%3A%2F%2Fgithub.con 6 public void _opengxcel(string dashboardFile, String metricsfolder) 66 { 67 //grab all the files in the metrics folder 68 metrics_files = Directory.GetFiles(netricsFolder); 9 7 if (File.Exists("Copy.xlsx")) n { n File. Delete(*Copy xlsx"); B } 7% File.Copy(dashboardFile, “Copy.xlsx"); 7s 76 //try openeing the excel sheets listed in netricsFile n try 78 { 7 “dashboard = new XLWorkbook(dashboardFile) ey a //iterate thorugh all the dashboard sheets, prep then for new data b: 2 /Inetrics reports and insert into those sheets 8 int nunSheets = _dashboard.Worksheets count; cy for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++) 5 { 86 var sheet = _dashboard. Worksheet (sheetNum) a7 prepDashboardsheets (sheet); 88 ? 89 _dashboard.Save(); Ey 1 } 92 catch (Exception ex) 9% { 4 Console.writeLine(ex.Nessage); 95 } 96 > EXAMPLE #27 1 [ [J(https://csharp hotexamples. com/examples/ClosedXML.Excel/XLWorkbooki-/php-xIworkbook-class-examplt Show file File: WhenAddingATable ToAWorksheet cs (https://esharp.hotexamples.comisite/redirect?url=https%3A%2F%: Project: ppnrao/pickles (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F%2F github.com%2FE as public void ThenTableAddedSuccessfully() 16 t v var excelTableFormatter = Kernel .Get(); 18 var table = new Table(); 19 table.HeaderRow = new TableRow(*Vari", "Var2", “Var3", “Vara"); 20 table.DataRows = 2a new List¢TableRow>(new[] {new TableRow » new Table 2 23 using (var workbook = new XLWorkbook()) 24 { 25 IXLWorksheet worksheet = workbook .AddWorksheet ("SHEET"); 26 int row = 65 2 excelTableFormatter Format (worksheet, table, ref row); 28 29 worksheet .Ce11("D6" ). Value. ShouldEqual (“Var: 30 worksheet .Cel1("E6" ) Value. ShouldEqual ( "Var: 31 worksheet .ce11("F6"). Value. ShouldEqual ( "Var: 32 worksheet .Ce11("G6"). Value. ShouldEqual ("vara"); 33 worksheet .Cel1("D7").Value.ShouldEqual (1.8); 34 worksheet .Cel1("€7").Value.ShouldEqual (2.0); 35 worksheet .ce11("F7").Value.ShouldEqual (3.0); 36 worksheet .Cel1("67").Value.ShouldEqual (4.0) ; 37 worksheet .Ce11("D8").Value.ShouldEqual (5.0); 38 worksheet .Cell("E8").Value.ShouldEqual (6.0); 39 worksheet .cel1("F8").Value.ShouldEqual (7.0); rr) worksheet .Ce11("68" ).Value.ShouldEqual (8.0); 41 row. ShouldEqual (9); a } 4B } EXAMPLE #28 4 CL) (htips:/icsharp. hotexamples.com/examples/ClosedXML.Excel/XLWorkbool-/php-xlworkbook-class-exampli Show fileFile: HomeController.cs (https://esharp.hotexamples. com/site/redirect?url=https%3A%2F %2F github.com%2Ft Project: hjdrent/MVCExcel (https://esharp.hotexamples.comsite/redirect7uri=https%3A%2F %2F github.com% 28 29 30 31 32 33 35 36 37 38 39 a 43 45 46, 47 49 50 51 52 53 EXAMPLE #29 Show file public ActionResult CreateStandardReport (AutoModel model) t string filename testje.x1sx"; 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"))5 worksheet .ActiveCell.Value = model.AantalWielen; //worksheet .Columns().AdjustToContents(); worksheet .ActiveCell = worksheet .Cell("A1"); try t DownloadExcel (workbook, filename); model.Message = return View(model); + catch (Exception ex) t model.Message = ex.Message; return View("About", model) ; o_L) (https://csharp. hotexamples.comvexamples/ClosedXML.Excel/XLWorkbool’-/php-xlworkbook-class-exampli File: RangeUsedTests.cs (https://csharp.hotexamples.com/site/redirect?uri=https%3A%2F%2F github.com%2t Project: hal1932/ClosedXML (https://csharp.hotexamples.com/site/redirect?url=https%3A%2F%2Fgithub.comB public void CanGetNamedFromanother () “ { 1s var wb = new XLWorkbook(); 16 var ws1 = wo.Worksheets.Add("Sheet1"); v ws1.Cel1("A1") .SetValue(1) .AddToNamed("valuei"); 18 Ft Assert.AreEqual(1, w.Cell ("value") .GetValue()); 20 Assert .AreEqual(1, wb.Range("value1").FirstCell().GetValuecint>()); a 2 Assert .AreEqual(1, ws1.Cell(“valuet").GetValuecint>()); 2B Assert .AreEqual(1, wsi.Range("value1").FirstCell().GetValuecint>()); 24 3 var ws2 = wb.Worksheets .Add(""Sheet2") ; 6 a ws2.Cell("A1") . SetFormulaAt("=value1") .AddToNamed("value2”); 28 2» Assert.AreEqual(1, wb.Cell(“value2").GetValuecint>()); 30 Assert.AreEqual (1, wb.Range("value2").FirstCell().GetValuecint>()); 31 32 Assert .AreEqual(1, ws2.Cell(“valuel") GetValuecint>()); 3 Assert .AreEqual (1, ws2-Range("valuel").FirstCell().GetValuecint>()); 34 35 Assert AreEqual(1, ws2.Cell(“value2").Getvaluecint>()); 36 Assert AreEqual(1, ws2.Range(“value2").FirstCell().GetValuecint>()); 37 } EXAMPLE #30 ¢_[_) (htips://csharp. hotexamples.com/examples/ClosedXML. Excel/XLWorkbook/-/php-xlworkbook-class-examplt Show file Fi FlashScores.cs (https://csharp. hotexamples.com/site/redirect? url=https%3A%2F%2F github.com%2Fstot Project: stoberov/Betman (https://csharp-hotexamples.com/site/redirect?url=https%3A%2F %2F github.com%225 26 a 28 29 30 31 32 33 35 36 37 38 39 a 43 45 47 49 50 51 52 53 private static void CreateExcelFile(ChromeDriver driver) t var homeTeams = driver.FindElements(ay.ClassName("team-home")) ; var awayTeams = driver.FindElements(By.ClassName("team-away")); var scores = driver. FindElements (By.ClassNane("score")); var wb = new XLWorkbook(); var ws = wo.Worksheets.Add( Scores”); ws.Cel1("A1"). Value ws.Cell("B1"). Value ws.Cell("C1").Value for (int i = @; i < homeTeams.Count; i++) t string homeTean = homeTeams[i].Text; string score = scores[i].Text; string awayTeam = awayTeans[i].Text; ws.Cel1("A" + (i + 2)).Value = homeTeam; ws.Cel1("B" + (i + 2)).Value = score; ws.Cell("C" + (i + 2)).Value = awayTeam; // Beautify ws .Range("A1:C1").Style.Font.Bold = true; ws Colunns().AdjustToContents()5 wo.Saveas("../../../../FlashScore.x1sx") Top Classes (https://csharp hotexamples. com/site/trends?type=csharp%7Cc) | Documentation (https:/icsharpdoc.hotexamples.com/docimap) | Privacy Policy ({site/privacy) PHP (nttps://hotexamples.comy) | C# (CSharp) (https://csharp.hotexamples.com/) | Java (https:ljava.hotexamples.com/) | Golang (https://golang.hotexamples.com/) | C++ (Cp) (https://epp-hotexamples.com/) | Python (https://python.hotexamples.com/) | JavaScript (hitps:/avascripthotexamples.com/) | TypeScript (hitps://typescript hotexamples.com/) EN (https:l/csharp.hotexamples.com/examplesiClosedXML.Excel/XLWorkbook!-/php-xiworkbook-class- examples.htm)) | RU (https://esharp.hotexamples.com/rulexamples/ClosedXML.Excel/XLWorkbook/-/php- xlworkbook-class-examples.html) | DE(https:l/csharp.hotexamples. com/de/examples/ClosedXML.Excel/XLWorkbook/-/php-xlworkbook-class- ‘examples.html) | FR (https://csharp.hotexamples.comfr/examples/ClosedXML.Excel/XLWorkbooki-/php- xiworkbook-class-examples.html) | ES (https://osharp.hotexamples. com/es/examples/ClosedXML.Excel/XLWorkbooki-/php-xiworkbook-class- ‘examples.html) | PT (https://csharp.hotexamples.com/pt/examples/ClosedXMLExcel/XLWorkbook/-/php- xlworkbook-class-examples. html) | IT (https:vicsharp.hotexamples. com/itlexamples/ClosedXML.Excel/XLWorkbook/-/php-xworkbook-class- ‘examples.html) | JP (https://esharp. hotexamples.com/jp/examples/ClosedXML.Excel/XLWorkbooki-/php- xlworkbook-class-examples.html) | ZH (https://csharp hotexamples. com/zh/examples/ClosedXML.Excel/XLWorkbook/-/php-xiworkbook-class- examples htm!) [Al (www liveinternet.ru/click)