### Array Path Example: Addition with Broadcasting Source: https://github.com/closedxml/closedxml/blob/develop/docs/concepts/function-evaluation.md Shows how array addition is handled with broadcasting when array dimensions do not match, including the final result with potential errors. ```default {1,2,3} + {1,2} - original {1,2,3} + {1,2, #N/A} - broadcasted {2, 4, #N/A } - final result ``` -------------------------------- ### Setup Pivot Table Data Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Initializes data and creates a pivot table. This code is required before applying pivot table styles. ```csharp private record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); pt.Theme = XLPivotTableTheme.None; pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Install ClosedXML using .NET CLI Source: https://github.com/closedxml/closedxml/blob/develop/docs/installation.md Use the 'dotnet add package' command to install the latest version of ClosedXML into your project. ```batch C:\source> dotnet add package ClosedXML ``` -------------------------------- ### Setup Pivot Table Data and Structure Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Initializes data, creates a workbook and worksheet, inserts data into a table, and adds a basic pivot table. This is a prerequisite for applying styles. ```csharp public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Install ClosedXML via .NET CLI Source: https://github.com/closedxml/closedxml/blob/develop/docs/index.md Use the .NET CLI to add the ClosedXML package to your project. ```batch C:\source> dotnet add package ClosedXML ``` -------------------------------- ### Create a new workbook and worksheet Source: https://github.com/closedxml/closedxml/wiki/Using-Rich-Text Initializes a new Excel workbook and adds a worksheet to it. This is the starting point for any Excel manipulation. ```csharp var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Rich Text"); ``` -------------------------------- ### Array Path Example: SIGN function Source: https://github.com/closedxml/closedxml/blob/develop/docs/concepts/function-evaluation.md Demonstrates the array path evaluation for the SIGN function, showing the original input, broadcasting, function calls for each element, and the final result. ```default SIGN({"5"}) -- original { SIGN("5") } - broadcasted { SIGN(5) } -- Function call for each element { 1 } -- Final result ``` -------------------------------- ### Configure Page Setup for a Worksheet Source: https://github.com/closedxml/closedxml/wiki/Pages-Tab Set page orientation to landscape, adjust scaling to 80%, and specify legal paper size with custom DPI. ```csharp var workbook = new XLWorkbook(); var ws1 = workbook.Worksheets.Add("Page Setup - Page1"); ws1.PageSetup.PageOrientation = XLPageOrientation.Landscape; ws1.PageSetup.AdjustTo(80); ws1.PageSetup.PaperSize = XLPaperSize.LegalPaper; ws1.PageSetup.VerticalDpi = 600; ws1.PageSetup.HorizontalDpi = 600; ``` -------------------------------- ### Classic XmlReader Hand-coded Parser Example Source: https://github.com/closedxml/closedxml/blob/develop/ClosedXML.IO.CodeGen/README.md This example demonstrates a classic hand-coded parser using XmlReader. It requires explicit schema validation setup and manual element checking. This approach lacks the built-in validation guarantees of a CodeGen parser. ```csharp // Classic XmlReader hand-coded parser. No explicit validation, requires to // supply schema to XmlReader and set XmlReaderSettings.ValidationType to // ValidationType.Schema. while (reader.Read()) { if (reader.IsStartElement()) { if (reader.Name == "numFmts") // Do something } else if (reader.Name == "fonts") // Do something else } else if (reader.Name == "fills") { // ... } } } ``` -------------------------------- ### Setup Data and Pivot Table Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Initializes a dataset of pastries and creates a basic pivot table structure in ClosedXML. This code is a prerequisite for applying pivot table styles. ```csharp public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); ptSheet.SetTabActive(); pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Create Workbook and Worksheet Source: https://github.com/closedxml/closedxml/wiki/Workbook-Properties Initializes a new Excel workbook and adds a worksheet. This is the starting point for manipulating workbook properties. ```csharp var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Workbook Properties"); ``` -------------------------------- ### Setup Pivot Table Data Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Sets up a sample data table for a pivot table. This includes defining a record, creating an array of records, and inserting it into an Excel worksheet as a table. A pivot table is then created on this data. ```csharp public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Formula Representation Example Source: https://github.com/closedxml/closedxml/wiki/Calc-Engine Illustrates the conversion of a simple formula to RC notation for stateless representation and sharing across cells. ```diff - Not yet finished ``` -------------------------------- ### Install ClosedXML via NuGet Package Manager Source: https://github.com/closedxml/closedxml/blob/develop/README.md Use this command in the Package Manager Console to install the ClosedXML library into your .NET project. ```powershell PM> Install-Package ClosedXML ``` -------------------------------- ### Install ClosedXML with Prerelease using Package Manager Console Source: https://github.com/closedxml/closedxml/blob/develop/docs/installation.md When encountering issues with prerelease dependencies like SixLabors.Fonts, use the '-IncludePrerelease' switch in the Package Manager Console to ensure all necessary packages are installed correctly. ```batch PM> Install-Package ClosedXML -Version 0.97.0 -Verbose -IncludePrerelease ``` -------------------------------- ### Specific Function Signature Example Source: https://github.com/closedxml/closedxml/wiki/Calc-Engine An example of a specific function signature that avoids generic conversions. The conversion from the generic signature is handled by an adapter function. ```csharp AnyValue Cos(double number) ``` -------------------------------- ### Blank to Number Conversion Example Source: https://github.com/closedxml/closedxml/wiki/Formula-Types Shows how a blank cell is converted to a number, specifically resulting in 1.0 when used in a mathematical context like COS. ```csharp COS(A1) ``` -------------------------------- ### Create Excel File with Hello World in C# Source: https://github.com/closedxml/closedxml/wiki/Hello-World This snippet shows the fundamental process of creating a new Excel workbook, adding a worksheet, writing text to a specific cell, and saving the workbook. Ensure ClosedXML is installed in your project. ```csharp var workbook = new XLWorkbook(); var worksheet = workbook.Worksheets.Add("Sample Sheet"); worksheet.Cell("A1").Value = "Hello World!"; workbook.SaveAs("HelloWorld.xlsx"); ``` -------------------------------- ### Blank to Logical Conversion Example Source: https://github.com/closedxml/closedxml/wiki/Formula-Types Demonstrates converting a blank cell to a logical value using the IF function. A blank cell evaluates to false in a logical context. ```csharp IF(A1,"blank is true", "false") ``` -------------------------------- ### Setup Data and Pivot Table for Styling Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Defines a data structure for pastries and creates a ClosedXML workbook, worksheet, and table. It then adds a pivot table to a new worksheet based on this data. ```csharp public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "Botswana", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "Botswana", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "Botswana", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); ptSheet.SetTabActive(); pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Configure Page Setup and Save Workbook Source: https://github.com/closedxml/closedxml/wiki/Pages-Tab Set page orientation to portrait, fit content to a 2x2 page grid, use letter paper size, custom DPI, and set the first page number. The workbook is then saved. ```csharp var ws2 = workbook.Worksheets.Add("Page Setup - Page2"); ws2.PageSetup.PageOrientation = XLPageOrientation.Portrait; ws2.PageSetup.FitToPages(2, 2); // Alternatively you can use // ws2.PageSetup.PagesTall = # // and/or ws2.PageSetup.PagesWide = # ws2.PageSetup.PaperSize = XLPaperSize.LetterPaper; ws2.PageSetup.VerticalDpi = 600; ws2.PageSetup.HorizontalDpi = 600; ws2.PageSetup.FirstPageNumber = 5; workbook.SaveAs("PageTab.xlsx"); ``` -------------------------------- ### Blank to Text Conversion Example Source: https://github.com/closedxml/closedxml/wiki/Formula-Types Illustrates converting a blank cell to a text value. An empty string is treated as text, not blank. ```csharp IF(A1="",1,2) ``` -------------------------------- ### PROPER Function Argument Type Suggestion Source: https://github.com/closedxml/closedxml/wiki/Calc-Engine This example suggests the argument type for the PROPER function, which likely accepts a string or a reference. ```csharp OneOf ``` -------------------------------- ### Array Path Example: MOD function Source: https://github.com/closedxml/closedxml/blob/develop/docs/concepts/function-evaluation.md Illustrates the array path evaluation for the MOD function with multi-dimensional array arguments. Shows original input, broadcasting, function calls for each element, and the final result. ```default MOD(A1:A2, {1;3;5}) -- original, A1:A2 contains values 4 and 5. MOD({4,5; 4,5; 4,5}, {1,1;3,3;5,5}) - broadcasted { MOD(4, 1), MOD(5, 1); MOD(4, 3), MOD(5, 3); MOD(4, 5), MOD(5, 5); } - Function call for each element { 0, 0; 1, 2; 4, 0 } - Final result ``` -------------------------------- ### Setup Pivot Table Data Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-Styles Initializes a ClosedXML workbook, adds a worksheet, inserts data into a table, and creates a pivot table from that data. This sets up the basic structure for applying pivot table styles. ```csharp public record Pastry(string Name, string Country, int NumberOfOrders, double Quality, string Month, DateTime? BakeDate); var pastries = new[] { new Pastry("Croissant", "India", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), new Pastry("Croissant", "Niger", 250, 50.42, "May", new DateTime(2016, 05, 03)), new Pastry("Croissant", "Botswana", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), new Pastry("Doughnut", "France", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), new Pastry("Doughnut", "Botswana", 225, 70, "May", new DateTime(2016, 05, 24)), new Pastry("Doughnut", "Botswana", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), new Pastry("Bearclaw", "India", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), new Pastry("Bearclaw", "India", 184, 33.33, "May", new DateTime(2016, 05, 20)), new Pastry("Bearclaw", "France", 124, 25, "Jun", new DateTime(2017, 06, 05)), new Pastry("Danish", "India", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), new Pastry("Danish", "France", 190, 60, "May", new DateTime(2017, 05, 08)), new Pastry("Danish", "France", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), new Pastry("Scone", "India", 135, 0, "Apr", new DateTime(2017, 04, 22)), new Pastry("SconE", "Botswana", 122, 5.19, "May", new DateTime(2017, 05, 03)), new Pastry("SCONE", "France", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("PastrySalesData"); var table = ws.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); IXLWorksheet ptSheet = wb.Worksheets.Add("pivot"); IXLPivotTable pt = ptSheet.PivotTables.Add("pivot", ptSheet.Cell(1, 1), table.AsRange()); pt.SetRowHeaderCaption("Pastry sales"); ``` -------------------------------- ### Generated ParseGradientFill Method Example Source: https://github.com/closedxml/closedxml/blob/develop/ClosedXML.IO.CodeGen/README.md This generated method demonstrates how CodeGen handles sequences of complex types by storing them in a list and then passing them to a hook method. It shows the integration of generated parsing logic with hand-coded hooks. ```csharp private void ParseGradientFill(string elementName) { // Other attributes omitted for brevity var type = _reader.GetOptionalEnum("type") ?? XLGradientType.Linear; // Because generator knows that ParseGradientStop should return value and that it can contain a sequence here, it stores values in a list var stop = new List<(double Value, XLColor Color)>(); while (_reader.TryOpen("stop", _ns)) { stop.Add(ParseGradientStop("stop")); } _reader.Close(elementName, _ns); // Extracted valkus are supplied to the partial hook. The ParseGradientFill doesn't have a hook and thus doesn't return a value. OnGradientFillParsed(stop, type, degree, left, right, top, bottom); } ``` -------------------------------- ### Create a Sample DataSet Source: https://github.com/closedxml/closedxml/wiki/Adding-DataSet This helper method creates a DataSet containing multiple DataTables, each with predefined columns and sample data. This is used to demonstrate adding a DataSet to an Excel file. ```csharp private DataSet GetDataSet() { var ds = new DataSet(); ds.Tables.Add(GetTable("Patients")); ds.Tables.Add(GetTable("Employees")); ds.Tables.Add(GetTable("Information")); return ds; } private DataTable GetTable(String tableName) { DataTable table = new DataTable(); table.TableName = tableName; table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); table.Rows.Add(25, "Indocin", "David", DateTime.Now); table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); return table; } ``` -------------------------------- ### Create and Save an Excel Workbook Source: https://github.com/closedxml/closedxml/blob/develop/docs/index.md Demonstrates creating a new workbook, adding a worksheet, setting cell values and formulas, and saving the file. ```csharp using var workbook = new XLWorkbook(); var worksheet = workbook.AddWorksheet("Sample Sheet"); worksheet.Cell("A1").Value = "Hello World!"; worksheet.Cell("A2").FormulaA1 = "MID(A1, 7, 5)"; workbook.SaveAs("HelloWorld.xlsx"); ``` -------------------------------- ### Get Formula Result in C# Source: https://github.com/closedxml/closedxml/wiki/How-do-I-get-the-result-of-a-formula Access the Value property of a worksheet cell to retrieve the evaluated result of a formula. Ensure the cell contains a formula before attempting to get its value. ```csharp var result = worksheet.Cell("A1").Value; ``` -------------------------------- ### Initialize Workbook and Worksheet Source: https://github.com/closedxml/closedxml/wiki/Data-Validation Sets up a new workbook and adds a worksheet for data validation. ```csharp var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Data Validation"); ``` -------------------------------- ### Accessing and Styling Column Cells in ClosedXML Source: https://github.com/closedxml/closedxml/wiki/Column-Cells Demonstrates how to get a column from a worksheet and style its cells using direct cell access, cell ranges specified by strings, and cell ranges specified by row and column numbers. Also shows how to get a column from a range and perform similar styling operations. ```csharp var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("Column Cells"); var columnFromWorksheet = ws.Column(1); columnFromWorksheet.Cell(1).Style.Fill.BackgroundColor = XLColor.Red; columnFromWorksheet.Cells("2").Style.Fill.BackgroundColor = XLColor.Blue; columnFromWorksheet.Cells("3,5:6").Style.Fill.BackgroundColor = XLColor.Red; columnFromWorksheet.Cells(8, 9).Style.Fill.BackgroundColor = XLColor.Blue; var columnFromRange = ws.Range("B1:B9").FirstColumn(); columnFromRange.Cell(1).Style.Fill.BackgroundColor = XLColor.Red; columnFromRange.Cells("2").Style.Fill.BackgroundColor = XLColor.Blue; columnFromRange.Cells("3,5:6").Style.Fill.BackgroundColor = XLColor.Red; columnFromRange.Cells(8, 9).Style.Fill.BackgroundColor = XLColor.Blue; workbook.SaveAs("ColumnCells.xlsx"); ``` -------------------------------- ### Example: Applying and Modifying Default Styles Source: https://github.com/closedxml/closedxml/wiki/Using-Default-Styles Demonstrates how to access default styles, modify them (though changes to static defaults are discarded), create a workbook, add worksheets with default styles, and then override default row heights for specific worksheets within the workbook. ```csharp // The static default values are read-only so even if // you try to change a referenced type, the changes will be discarded. var style = XLWorkbook.DefaultStyle; style.Border.DiagonalUp = true; style.Border.DiagonalDown = true; style.Border.DiagonalBorder = XLBorderStyleValues.Thick; style.Border.DiagonalBorderColor = XLColor.Red; // Create our workbook var workbook = new XLWorkbook(); // This worksheet will have the default style, row height, column width, and page setup var ws1 = workbook.Worksheets.Add("Default Style"); // Change the default row height for all new worksheets in this workbook workbook.RowHeight = 30; var ws2 = workbook.Worksheets.Add("Tall Rows"); // Create a worksheet and change the default row height var ws3 = workbook.Worksheets.Add("Short Rows"); ws3.RowHeight = 7.5; workbook.SaveAs("DefaultStyles.xlsx"); ``` -------------------------------- ### List NuGet Sources via CLI Source: https://github.com/closedxml/closedxml/wiki/Development-Builds View the currently registered NuGet sources to verify the local source has been added and is enabled. ```powershell dotnet nuget list source ``` -------------------------------- ### Create a New Workbook Source: https://github.com/closedxml/closedxml/wiki/Basic-Table Initializes a new Excel workbook. This is the first step before adding any content. ```csharp var wb = new XLWorkbook(); ``` -------------------------------- ### Create and Fill Worksheet Source: https://github.com/closedxml/closedxml/wiki/Inserting-Columns Initializes a new workbook, adds a worksheet, colors all columns, and fills a 5x5 grid with 'X'. ```csharp var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("Inserting Columns"); // Color the entire spreadsheet using columns ws.Columns().Style.Fill.BackgroundColor = XLColor.LightCyan; // 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 = "X"; ``` -------------------------------- ### Helper Method: AddTestColumn Source: https://github.com/closedxml/closedxml/wiki/Sorting-Data A helper method to populate a worksheet column with test data, including values and background colors. This is used in sorting examples. ```csharp private void AddTestColumn(IXLWorksheet ws) { ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); ws.Cell("A2").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.DarkTurquoise); ws.Cell("A3").SetValue("a").Style.Fill.SetBackgroundColor(XLColor.BurlyWood); ws.Cell("A4").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkGray); ws.Cell("A5").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DarkSalmon); } ``` -------------------------------- ### List Hyperlinks in C# Source: https://github.com/closedxml/closedxml/wiki/Using-Hyperlinks Provides examples of how to retrieve all hyperlinks present in a worksheet or within a specific range of cells. This is useful for auditing or processing existing hyperlinks. ```csharp // List all hyperlinks in a worksheet: var hyperlinksInWorksheet = ws.Hyperlinks; // List all hyperlinks in a range: var hyperlinksInRange = ws.Range("A1:A3").Hyperlinks; ``` -------------------------------- ### Create and Save an Excel Workbook Source: https://github.com/closedxml/closedxml/blob/develop/README.md This snippet demonstrates how to create a new Excel workbook, add a worksheet, populate cells with text and formulas, and save the workbook to a file. Ensure you have the ClosedXML library referenced in your project. ```csharp using (var workbook = new XLWorkbook()) { var worksheet = workbook.Worksheets.Add("Sample Sheet"); worksheet.Cell("A1").Value = "Hello World!"; worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)"; workbook.SaveAs("HelloWorld.xlsx"); } ``` -------------------------------- ### Set Formulas with A1 and R1C1 Notation Source: https://github.com/closedxml/closedxml/wiki/Using-Formulas Demonstrates setting formulas using both A1 and R1C1 notation. The equal sign is optional when setting formulas. Shows how to retrieve formula strings and calculated values. ```csharp var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Formulas"); ws.Cell(1, 1).Value = "Num1"; ws.Cell(1, 2).Value = "Num2"; ws.Cell(1, 3).Value = "Total"; ws.Cell(1, 4).Value = "cell.FormulaA1"; ws.Cell(1, 5).Value = "cell.FormulaR1C1"; ws.Cell(1, 6).Value = "cell.Value"; ws.Cell(1, 7).Value = "Are Equal?"; ws.Cell(2, 1).Value = 1; ws.Cell(2, 2).Value = 2; var cellWithFormulaA1 = ws.Cell(2, 3); // Use A1 notation cellWithFormulaA1.FormulaA1 = "=A2+$B$2"; // The equal sign (=) in a formula is optional ws.Cell(2, 4).Value = cellWithFormulaA1.FormulaA1; ws.Cell(2, 5).Value = cellWithFormulaA1.FormulaR1C1; ws.Cell(2, 6).Value = cellWithFormulaA1.Value; ws.Cell(3, 1).Value = 1; ws.Cell(3, 2).Value = 2; var cellWithFormulaR1C1 = ws.Cell(3, 3); // Use R1C1 notation cellWithFormulaR1C1.FormulaR1C1 = "RC[-2]+R3C2"; // The equal sign (=) in a formula is optional ws.Cell(3, 4).Value = cellWithFormulaR1C1.FormulaA1; ws.Cell(3, 5).Value = cellWithFormulaR1C1.FormulaR1C1; ws.Cell(3, 6).Value = cellWithFormulaR1C1.Value; ws.Cell(4, 1).Value = "A"; ws.Cell(4, 2).Value = "B"; var cellWithStringFormula = ws.Cell(4, 3); // Use R1C1 notation cellWithStringFormula.FormulaR1C1 = "=\"Test\" & RC[-2] & \"R3C2\""; ws.Cell(4, 4).Value = cellWithStringFormula.FormulaA1; ws.Cell(4, 5).Value = cellWithStringFormula.FormulaR1C1; ws.Cell(4, 6).Value = cellWithStringFormula.Value; // Setting the formula of a range var rngData = ws.Range(2, 1, 4, 7); rngData.LastColumn().FormulaR1C1 = "=IF(RC[-3]=RC[-1],\"Yes\", \"No\")"; // Using an array formula: // Just put the formula between curly braces ws.Cell("A6").Value = "Array Formula: "; ws.Cell("B6").FormulaA1 = "{A2+A3}"; ws.Range(1, 1, 1, 7).Style.Fill.BackgroundColor = XLColor.Cyan; ws.Range(1, 1, 1, 7).Style.Font.Bold = true; ws.Columns().AdjustToContents(); // You can also change the reference notation: wb.ReferenceStyle = XLReferenceStyle.R1C1; // And the workbook calculation mode: wb.CalculateMode = XLCalculateMode.Auto; wb.SaveAs("Formulas.xlsx"); ``` -------------------------------- ### Get First/Last Cell, Row, Column in a Range Source: https://github.com/closedxml/closedxml/wiki/Simplifying-your-life Use these methods to easily retrieve the boundaries of a range without manual calculation. They are applicable to any range object. ```csharp range.FirstCell() range.FirstCellUsed() range.FirstColumn() range.FirstColumnUsed() range.FirstRow() range.FirstRowUsed() range.LastCell() range.LastCellUsed() range.LastColumn() range.LastColumnUsed() range.LastRow() range.LastRowUsed() ``` -------------------------------- ### Create and Fill Worksheet Source: https://github.com/closedxml/closedxml/wiki/Inserting-Rows Initializes a new workbook and worksheet, colors the entire sheet, and populates a 5x5 grid with 'X'. ```csharp var workbook = new XLWorkbook(); var ws = workbook.Worksheets.Add("Inserting Rows"); // Color the entire spreadsheet using rows ws.Rows().Style.Fill.BackgroundColor = XLColor.LightCyan; // 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 = "X"; ``` -------------------------------- ### Set Fallback Font in ClosedXML Source: https://github.com/closedxml/closedxml/blob/develop/docs/tips/missing-font.md Configure ClosedXML to use a specific font as a fallback when the requested font is not found. This is done by setting the `DefaultGraphicEngine` with the name of an installed font. ```csharp // "Fallback font name" will likely be something like "DejaVu Sans" or "Tahoma" // It is not a path to font file, but a font name. LoadOptions.DefaultGraphicEngine = new DefaultGraphicEngine("Fallback font name"); ``` -------------------------------- ### Apply Icon Set Conditional Formatting Source: https://github.com/closedxml/closedxml/wiki/Conditional-Formatting Applies an icon set to a range of cells based on defined values and operators. This example uses a three-traffic-light icon set. ```csharp var workbook = new XLWorkbook(); var ws = workbook.AddWorksheet("Sheet1"); ws.FirstCell().SetValue(1) .CellBelow().SetValue(1) .CellBelow().SetValue(2) .CellBelow().SetValue(3) .CellBelow().SetValue(4); ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 0, XLCFContentType.Number) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 2, XLCFContentType.Number) .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, 3, XLCFContentType.Number); ``` -------------------------------- ### Retrieve Cached Cell Value Source: https://github.com/closedxml/closedxml/blob/develop/docs/concepts/formula-calculation.md Access IXLCell.CachedValue to get the stored value of a cell without triggering recalculation. This is preferred for performance when the latest value is not strictly required. ```C# var cachedValue = cell.CachedValue; ``` -------------------------------- ### Custom Comparison Filters Source: https://github.com/closedxml/closedxml/blob/develop/docs/features/autofilter.md Demonstrates how to apply custom comparison filters such as EqualOrLessThan, GreaterThan, and NotEqualTo. It also shows how to combine filters using logical AND and OR conditions with methods like Between and NotBetween. ```APIDOC ## Custom Comparison Filters ### Description Allows users to define specific comparison criteria for filtering data. This includes operators like GreaterThan, EqualOrLessThan, EqualTo, NotEqualTo, EqualOrGreaterThan, and LessThan. Custom filters can also be combined using logical AND or OR connectors, with convenience methods like `Between` and `NotBetween` available for dual comparisons. ### Methods - `IXLFilterColumn.GreaterThan(value, reapply)` - `IXLFilterColumn.EqualOrGreaterThan(value, reapply)` - `IXLFilterColumn.EqualTo(value, reapply)` - `IXLFilterColumn.NotEqualTo(value, reapply)` - `IXLFilterColumn.EqualOrLessThan(value, reapply)` - `IXLFilterColumn.LessThan(value, reapply)` - `IXLFilterColumn.Between(minValue, maxValue, reapply)` - `IXLFilterColumn.NotBetween(minValue, maxValue, reapply)` ### Example 1: Single Comparison Filter ```csharp using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("AutoFilter"); var range = ws.Cell("A1").InsertData(new object[] { "Number", 2, 14, 4, 5, 8, -5, }); var autoFilter = range.SetAutoFilter(); autoFilter.Column(1).EqualOrLessThan(5); wb.SaveAs("autofilter-filter-custom-compare-example.xlsx"); ``` ### Example 2: Combined Comparison Filters ```csharp using var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("AutoFilter"); var gradesRange = ws.Cell("A1").InsertData(new[] { "Grade", "F", "C", "C", "N/A", "A", "D", }); // Add filters for failing grade. Skip immedate reapply for the first filter. gradesRange.SetAutoFilter().Column(1).GreaterThan("C", false).And.NotEqualTo("N/A"); // Sort the filtered list ws.AutoFilter.Sort(1, XLSortOrder.Ascending); wb.SaveAs("autofilter-filter-custom-connector-example.xlsx"); ``` ``` -------------------------------- ### Style Comment Fill and Lines in C# Source: https://github.com/closedxml/closedxml/wiki/Comments-Style-Colors-and-Lines Demonstrates how to add text to a comment, apply formatting like bold and font color, and then style the comment's fill color, line color, and line style. All comments are then made visible. ```csharp var wb = new XLWorkbook(); var ws = wb.Worksheets.Add("Colors and Lines"); ws.Cell("A2").Comment .AddText("Now ") .AddText("THIS").SetBold().SetFontColor(XLColor.Red) .AddText(" is colorful!"); ws.Cell("A2").Comment.Style .ColorsAndLines.SetFillColor(XLColor.RichCarmine) .ColorsAndLines.SetFillTransparency(0.25) // 25% opaque .ColorsAndLines.SetLineColor(XLColor.Blue) .ColorsAndLines.SetLineTransparency(0.75) // 75% opaque .ColorsAndLines.SetLineDash(XLDashStyle.LongDash) .ColorsAndLines.SetLineStyle(XLLineStyle.ThickBetweenThin) .ColorsAndLines.SetLineWeight(7.5); // Set all comments to visible ws.CellsUsed(true, c => c.HasComment).ForEach(c => c.Comment.SetVisible()); wb.SaveAs("CommentsColorsAndLines.xlsx"); ``` -------------------------------- ### Apply Formula-Based Conditional Formatting Source: https://github.com/closedxml/closedxml/wiki/Conditional-Formatting Applies conditional formatting based on a specified Excel formula. For formulas starting with an equals sign, enclose the entire formula string in quotes. ```csharp .AddConditionalFormat().WhenEquals("=B1") ``` ```csharp .AddConditionalFormat().WhenEquals("\"=Hello\"") ``` -------------------------------- ### Mock Up Pastry Sales Data Source: https://github.com/closedxml/closedxml/wiki/Pivot-Table-example Creates a list of Pastry objects to be used as the source data for the pivot table. This data represents monthly sales figures for different pastries. ```csharp var pastries = new List { new Pastry("Croissant", 150, "Apr"), new Pastry("Croissant", 250, "May"), new Pastry("Croissant", 134, "June"), new Pastry("Doughnut", 250, "Apr"), new Pastry("Doughnut", 225, "May"), new Pastry("Doughnut", 210, "June"), new Pastry("Bearclaw", 134, "Apr"), new Pastry("Bearclaw", 184, "May"), new Pastry("Bearclaw", 124, "June"), new Pastry("Danish", 394, "Apr"), new Pastry("Danish", 190, "May"), new Pastry("Danish", 221, "June"), new Pastry("Scone", 135, "Apr"), new Pastry("Scone", 122, "May"), new Pastry("Scone", 243, "June") }; ```