# SharpCell > A free (MIT) Excel formula engine for .NET 8 and later: it reads .xlsx files, recalculates them, saves the results back and evaluates formulas, including dynamic arrays, LET/LAMBDA and tables with structured references. Results are checked against workbooks calculated by Microsoft Excel. Packages: `SharpCell` (the engine, no dependencies) and `SharpCell.Xlsx` (reads .xlsx and saves values back). Namespaces: `SharpCell`, `SharpCell.Xlsx`. Call `Workbook.Recalculate()` after changing cells; values do not update on assignment. Excel errors are values, not exceptions. A `Workbook` is not thread-safe. --- # SharpCell SharpCell is a free (MIT) Excel formula engine for .NET 8 and later: it reads .xlsx files, recalculates them, saves the results back and evaluates formulas, including dynamic arrays and LET/LAMBDA. ## Correctness Results are compared with workbooks calculated by Microsoft Excel. The [compatibility report](compatibility.md) lists every function, its status and how many Excel-calculated cells it matches. ## Packages | Package | What it does | |---|---| | `SharpCell` | The engine: parser, values, recalculation and functions. No dependencies. | | `SharpCell.Xlsx` | Reads .xlsx files into a SharpCell workbook and saves recalculated values back. | Install from NuGet: ```bash dotnet add package SharpCell.Xlsx ``` ## A first example ```csharp snippet=build-in-code var workbook = new Workbook(); Worksheet sheet = workbook.AddSheet("Sheet1"); sheet["A1"].Value = 2; sheet["A2"].Formula = "=A1*3"; workbook.Recalculate(); CellValue result = sheet["A2"].Value; // 6 ``` ## Next - [Getting started](getting-started.md): install, read a file, recalculate, read values. - [Formulas and values](guides/formulas.md): syntax, references, dynamic arrays, names and LAMBDA. - [Cells, ranges and dates](guides/cells.md): going through cells, blocks of values, dates, defined names. - [Recalculation](guides/recalculation.md): when values update, changed cells, precedents, threads, templates. - [Custom functions](guides/custom-functions.md): functions written in C# that formulas call. - [Reading .xlsx files](guides/xlsx.md): what the reader loads and what it skips. - [Saving .xlsx files](guides/saving.md): writing recalculated values back into a template. - [Functions](guides/functions.md): function status, criteria, dates. - [What SharpCell does not do](guides/limits.md): every known gap. - [API reference](api/index.md): every public type and member. - [For AI coding agents](agents.md): llms.txt and rules for your agent. --- # Getting started Install SharpCell, read an .xlsx file, recalculate it and read the results, step by step through the console sample. ## Requirements .NET 8 or .NET 10. ## Install ```bash dotnet add package SharpCell.Xlsx ``` `SharpCell.Xlsx` brings `SharpCell` with it. Use `dotnet add package SharpCell` alone if you build workbooks in code and never read files. Versions before 1.0 may change the API between minor versions. ## The sample [samples/SharpCell.Sample](../samples/SharpCell.Sample/README.md) is a console app built around `budget.xlsx`, one sheet named `Budget`: | Cell | Content | |---|---| | A2:B5 | Rent 1200, Food 450, Transport 120, Internet 40 (monthly) | | C2:C5 | Yearly amounts, `=B2*12` and so on | | B6 | `=SUM(B2:B5)`, the monthly total | | F1 | Income, 3000 | | F2 | `=F1-B6`, what is left | ### Namespaces ```csharp snippet=sample-usings using SharpCell; using SharpCell.Xlsx; ``` ### Load and recalculate `XlsxReader.Load` reads sheets, values and formulas. Cells with formulas start out holding the results Excel saved in the file, if any. `Recalculate` calculates every formula with SharpCell and replaces those results. ```csharp snippet=sample-load // Read the workbook: sheets, values, formulas and any results Excel saved with them. Workbook workbook = XlsxReader.Load(path); Worksheet budget = workbook["Budget"]; // Calculate every formula now instead of relying on saved results. workbook.Recalculate(); ``` ### Read values A cell's `Value` is a `CellValue`. Check its `Kind` before calling `AsNumber`, `AsText` or another `As` method: each one throws `InvalidOperationException` when the value is of another kind. Excel errors such as `#DIV/0!` are values of kind `Error`, not exceptions. ```csharp snippet=sample-read CellValue total = budget["B6"].Value; // =SUM(B2:B5) output.WriteLine($"Total per month: {Format(total)}"); output.WriteLine($"Left after expenses: {Format(budget["F2"].Value)}"); ``` The sample formats values with this helper: ```csharp snippet=sample-format // A CellValue can be a number, text, logical, error, array or empty; check Kind before As*. private static string Format(CellValue value) => value.Kind switch { CellValueKind.Number => value.AsNumber().ToString(CultureInfo.InvariantCulture), CellValueKind.Error => value.AsError().ToText(), // "#DIV/0!", "#N/A", ... _ => value.ToString(), }; ``` ### Change an input Assigning a value does not update the formulas that depend on it. Call `Recalculate` after a batch of changes: it calculates only the cells that are out of date. ```csharp snippet=sample-change // Change an input. Dependent formulas are updated by the next Recalculate, not before. budget["B2"].Value = 1350; workbook.Recalculate(); output.WriteLine("After rent change to 1350:"); output.WriteLine($"Total per month: {Format(budget["B6"].Value)}"); output.WriteLine($"Left after expenses: {Format(budget["F2"].Value)}"); ``` ### Formulas without a cell `Workbook.Evaluate` calculates a formula that lives in no cell. It can read the workbook's sheets. A formula that returns several values gives a `CellValue` of kind `Array`; its text form is `{1;2;3}`. ```csharp snippet=sample-evaluate // Evaluate formulas that live in no cell. They can read the workbook's sheets. CellValue food = workbook.Evaluate("=XLOOKUP(\"Food\", Budget!A2:A5, Budget!B2:B5)"); CellValue average = workbook.Evaluate("=LET(items, Budget!B2:B5, SUM(items) / COUNT(items))"); CellValue sequence = workbook.Evaluate("=SEQUENCE(3)"); // a dynamic array: {1;2;3} output.WriteLine($"Food per month: {Format(food)}"); output.WriteLine($"Average expense: {Format(average)}"); output.WriteLine($"Sequence: {sequence}"); ``` ## Run it ```bash dotnet run --project samples/SharpCell.Sample -f net10.0 ``` ```text Total per month: 1810 Left after expenses: 1190 After rent change to 1350: Total per month: 1960 Left after expenses: 1040 Food per month: 450 Average expense: 490 Sequence: {1;2;3} ``` ## Next - [Formulas and values](guides/formulas.md) - [Recalculation](guides/recalculation.md) - [Reading .xlsx files](guides/xlsx.md) --- # Formulas and values How to write formulas, what a cell value can be, and how dynamic arrays, names and LAMBDA work. ## Syntax Write formulas the way Excel stores them in a file: English function names, a comma between arguments and a dot as the decimal separator. The leading `=` is optional; `Cell.Formula` adds it if missing. Localized function names such as `СУММ` or `SOMME` are not recognized. References: | Form | Meaning | |---|---| | `A1`, `$A$1` | A cell, relative or absolute | | `A1:C3`, `A:A`, `1:1` | A range, whole columns, whole rows | | `'Sheet name'!A1` | A cell on another sheet | | `Sheet1:Sheet3!A1` | The same cell on a run of sheets | | `TaxRate` | A defined name | | `C1#` | The whole range a dynamic array in C1 spilled into | | `@A1:A9` | Implicit intersection: the cell of the range in the formula's row or column | ## Build a workbook in code ```csharp snippet=build-in-code var workbook = new Workbook(); Worksheet sheet = workbook.AddSheet("Sheet1"); sheet["A1"].Value = 2; sheet["A2"].Formula = "=A1*3"; workbook.Recalculate(); CellValue result = sheet["A2"].Value; // 6 ``` ## Reading values A `CellValue` is one of: `Number`, `Text`, `Boolean`, `Error`, `Array`, `Empty`, `Missing` or `Lambda`. Dates and times are numbers: serial days since the workbook's date system epoch. Errors such as `#DIV/0!` are values, not exceptions. ```csharp snippet=read-values static string Describe(CellValue value) => value.Kind switch { CellValueKind.Number => value.AsNumber().ToString(CultureInfo.InvariantCulture), CellValueKind.Text => value.AsText(), CellValueKind.Boolean => value.AsBoolean() ? "TRUE" : "FALSE", CellValueKind.Error => value.AsError().ToText(), // "#DIV/0!", "#N/A", ... CellValueKind.Array => $"array of {value.AsArray().GetLength(0)} x {value.AsArray().GetLength(1)}", CellValueKind.Empty => "(empty)", _ => value.ToString(), }; ``` The `As` methods throw `InvalidOperationException` when the value is of another kind. Every member is listed in the [CellValue reference](../api/SharpCell.CellValue.md). ## Dynamic arrays A formula that returns several values spills into the cells below and to the right. The spilled cells hold values but no formula: their `Formula` is `null`. ```csharp snippet=spill sheet["C1"].Formula = "=SEQUENCE(3)"; // spills into C1:C3 sheet["D1"].Formula = "=SUM(C1#)"; // C1# is the whole spilled range workbook.Recalculate(); // sheet["C3"].Value is 3 and sheet["D1"].Value is 6 ``` When a cell in the way holds something, the anchor shows `#SPILL!` and the other cells stay as they are: ```csharp snippet=spill-blocked sheet["C2"].Value = "in the way"; workbook.Recalculate(); // sheet["C1"].Value is #SPILL!; clear C2 and recalculate to spill again ``` ## Names and LAMBDA `DefineName` gives a formula a name. A name whose formula is a `LAMBDA` can be called like a function. ```csharp snippet=names-lambda workbook.DefineName("TaxRate", "=0.2"); workbook.DefineName("WithTax", "=LAMBDA(amount, amount * (1 + TaxRate))"); CellValue gross = workbook.Evaluate("=WithTax(100)"); // 120 ``` A function's name alone is the function as a value, wherever a LAMBDA is expected: in `MAP`, `REDUCE`, `SCAN`, `BYROW`, `BYCOL`, `MAKEARRAY` and as the value of a `LET` name. It takes its arguments as a call by name would. Custom functions can be passed the same way. ```csharp snippet=function-values sheet["D1"].Formula = "=BYROW(A1:B2, SUM)"; // 3 and 7: SUM is passed as a function sheet["E1"].Formula = "=LET(size, LEN, size(\"abc\"))"; // 3 workbook.Recalculate(); ``` ## Formulas outside cells ```csharp snippet=evaluate CellValue sum = workbook.Evaluate("=SUM(1, 2, 3)"); // 6, no sheet needed ``` ## Errors in formula text Text that is not a valid formula throws `FormulaParseException`, which says where parsing stopped. The cell is left unchanged. ```csharp snippet=parse-error string? problem = null; try { sheet["A1"].Formula = "=SUM(1,"; } catch (FormulaParseException e) { problem = $"at position {e.Position}: {e.Message}"; } ``` A formula read from a file that SharpCell cannot handle does not throw. Once calculated, it is `#NAME?` and listed in `Workbook.Diagnostics` as `UnsupportedFormula`. See [Reading .xlsx files](xlsx.md). --- # Cells, ranges and dates Going through the cells of a sheet, reading and writing blocks of values, dates, number formats and the text Excel shows, and the defined names of a workbook. ## Going through cells ```csharp snippet=cells-enumerate foreach (Cell cell in sheet.Cells) // non-empty cells, row by row { if (cell.Formula is not null) lines.Add($"{cell.Address}: {cell.Formula} = {cell.Value}"); } CellRange? used = sheet.UsedRange; // A1:F6 for the sample budget; null for an empty sheet ``` `Worksheet.Cells` visits only cells that hold something: a constant, a formula, or a value a dynamic array spilled into them. Empty cells cost nothing, so a sheet with a cell in `XFD1048576` is as quick to walk as any other. `CellRange.Cells` does the same for part of a sheet. While enumerating you may change the value or formula of the cells you get. Adding or removing a cell, including setting a value to `CellValue.Empty` or a recalculation that changes a spill, makes the next step throw `InvalidOperationException`, as changing a .NET collection during a `foreach` does. ## Ranges `Worksheet.Range` takes an A1 address on that sheet: `A1:C10`, a single cell `B2`, whole columns `A:B` or whole rows `2:3`. A `CellRange` is a handle, like a `Cell`: making one allocates nothing. Its `ToString()` includes the sheet, as in `'My Sheet'!A1:C10`. ```csharp snippet=cells-values CellRange amounts = sheet.Range("B2:B5"); CellValue[,] before = amounts.GetValues(); // [row, column] from B2: 1200, 450, 120, 40 amounts.SetValues(new CellValue[,] { { 1300 }, { 500 }, { 150 }, { 50 } }); workbook.Recalculate(); // sheet["B6"].Value is now 2000 ``` `GetValues` returns every cell of the range, empty ones as `CellValue.Empty`, indexed `[row, column]` from the top-left cell. A whole column has 1,048,576 cells and the array has as many elements; for sparse data use `UsedRange` or `Cells`. `SetValues` writes the way assigning `Cell.Value` does: formulas are replaced and `CellValue.Empty` clears a cell. It checks everything first (the array's size, values a cell cannot hold, cells inside an array formula other than its top-left cell, even when the range covers the whole array) and writes nothing when a check fails. As with any change, formulas see the new values after `Workbook.Recalculate`. ## Dates Excel stores a date as a number: days counted from the workbook's date system, with the time of day as the fraction. A `CellValue` does not know which date system its workbook uses, so the conversions take it: ```csharp snippet=cells-dates sheet["A1"].Value = CellValue.DateTime(new DateTime(2026, 3, 15), workbook.DateSystem); sheet["A2"].Formula = "=EOMONTH(A1, 0)"; workbook.Recalculate(); DateTime monthEnd = sheet["A2"].Value.AsDateTime(workbook.DateSystem); // 2026-03-31 ``` `AsDateTime` throws `InvalidOperationException` for a value that is not a number, like the other `As` methods, and `ArgumentOutOfRangeException` for a number that is no date: a negative one, one past 9999-12-31, or 60 in the 1900 date system, which Excel shows as the non-existent 29 February 1900. The time is rounded to the millisecond. `CellValue.DateTime` refuses dates before the date system starts. ## Number formats and display text ```csharp snippet=cells-text sheet["A1"].Value = 1234.5; sheet["A1"].NumberFormat = "#,##0.00"; // as read from the file, or set here string shown = sheet["A1"].Text; // "1,234.50" ``` `Cell.NumberFormat` is the cell's format code, such as `0.00`, `#,##0` or `yyyy-mm-dd`, and `General` when it has none. `XlsxReader` reads it from the file's cell styles, Excel's built-in formats included. `Cell.Text` is the value as Excel shows it through that format, with the workbook's culture for separators and month names: dates come out as dates, percentages with `%`, and `General` the way Excel's default column shows a number. | Value | `Text` | |---|---| | Number | formatted by the cell's format; a date format on a number that is no date gives `#######` | | Text | the format's text section (`"Name: "@`), or the text itself | | Logical | `TRUE` or `FALSE` | | Error | `#DIV/0!`, `#N/A` and so on | | Empty | an empty string | Column width plays no part, so a long number is never cut to `####`. A format is no content: it does not block a spill and is not listed by `Cells`. `XlsxWriter` saves formats with the cells. ## Typing into cells ```csharp snippet=cells-input sheet["A1"].Input = "12.5%"; // 0.125, and the format 0.00% sheet["A2"].Input = "10/11/2026"; // a date in the workbook's culture, and its short date format sheet["A3"].Input = "=A1*2"; // a formula sheet["A4"].Input = "'0123"; // text, kept as typed string edit = sheet["A1"].Input; // "12.5%", as Excel shows it to edit ``` `Cell.Input` is what a person types into a cell in Excel. Setting it does what typing and pressing Enter does: `=` starts a formula, `'` keeps the rest as text, `TRUE`, `FALSE` and error literals are those values, and numbers, percentages, currency, grouped digits, dates and times are read in `Workbook.Culture` by the same rules as text meeting arithmetic and `DATEVALUE`; anything else stays text, and an empty string clears the cell. A cell in the General format takes a format from what was typed: `12%` gives `0%`, `1,250.50` gives `#,##0.00`, `$5` a currency format, a date the culture's short date, a time `h:mm` with seconds and AM/PM when they were typed. A cell with a format of its own keeps it. Getting `Input` gives the text Excel shows to edit the cell: the formula, a number in full, a percentage as one, a date or time as the culture writes it, and text with an apostrophe in front when it would read as something else. Typed back, it gives the same content. A format taken from typing is saved with the cell by `XlsxWriter`. The formats Excel gives typed currency and dates are not yet compared with Excel's. ## Styles ```csharp snippet=cells-styles CellStyle header = sheet["A1"].Style with // the workbook's default until the cell has its own { Font = sheet["A1"].Style.Font with { Bold = true }, Fill = CellColor.FromRgb(0xD9E1F2), BottomBorder = new CellBorder(CellBorderStyle.Thin), HorizontalAlignment = CellHorizontalAlignment.Center, }; sheet["A1"].Style = header; bool bold = sheet["A1"].Style.Font.Bold; // true string font = sheet["B1"].Style.Font.Name; // "Calibri", the default ``` `Cell.Style` is how the cell looks in Excel: its `CellFont` (name, size in points, bold, italic, underline, strikethrough, colour), a fill colour, the four edges of its border and its alignment, wrapping and indent. A cell without a style of its own has `Workbook.DefaultStyle`, which `XlsxReader` takes from the file's Normal style; setting `Style` to null gives it back. Styles are records: they compare by value, and `with` makes a changed copy. `XlsxReader` turns theme and indexed colours into RGB with the file's theme and palette. Only solid fills are read; patterns and gradients read as no fill. Styles of whole rows and columns (for cells that hold nothing), conditional formats, rotated text, shrink to fit and diagonal borders are not read. A style plays no part in calculation and is no content. ## Column widths, row heights and merged cells ```csharp snippet=cells-geometry sheet.SetColumnWidth(2, 20); // column B, in the units .xlsx files use sheet.SetRowHeight(1, 30); // row 1, in points sheet.SetColumnHidden(3, true); // column C sheet.Merge("A1:D1"); double? width = sheet.ColumnWidth(1); // null: column A has the default width string merged = sheet.MergedAreas[0].Address; // "A1:D1" ``` Column widths are in the units .xlsx files use: characters of the widest digit of the default font, padding included, so Excel's standard 8.43 characters of Calibri 11 are 9.140625 here. Row heights are in points. Null means the sheet's default, `Worksheet.DefaultColumnWidth` and `Worksheet.DefaultRowHeight`, which are null too when the file gives none. Converting either to pixels depends on the font and the screen and is left to the application. Hidden columns change no result, as in Excel; hidden rows do (see [Tables and hidden rows](tables.md)). `Merge` keeps the values of the cells it covers, where Excel's Merge command would clear all but the top-left one. ## Frozen panes and gridlines ```csharp snippet=cells-view sheet.FrozenRows = 1; // the header row stays in place while the rest scrolls sheet.FrozenColumns = 1; // and so does column A sheet.ShowGridlines = false; // as View > Gridlines turned off in Excel ``` `FrozenRows` and `FrozenColumns` are the rows at the top and the columns on the left that Excel's Freeze Panes keeps in place; `ShowGridlines` is whether Excel draws gridlines on the sheet. `XlsxReader` takes them from the sheet's first view. A plain split that is not frozen reads as no frozen panes. None of them changes a result. `XlsxWriter` saves styles, sizes, merged cells, frozen panes and gridlines. A new style starts from the cell's style in the file, so what SharpCell does not read (pattern fills, rotated text) stays. ## Printing ```csharp snippet=cells-printing sheet.PageOrientation = PageOrientation.Landscape; sheet.PageMargins = PageMargins.Narrow with { Top = 1 }; sheet.PrintArea = "A1:E7"; // only these cells print sheet.PrintTitleRows = "1:1"; // the header row repeats on every page sheet.AddRowPageBreak(5); // a new page starts at row 5 ``` These are the settings of Excel's Page Layout tab. `PageOrientation` is portrait unless set otherwise, and `PageMargins` are Excel's Normal margins; `PageMargins.Wide` and `PageMargins.Narrow` are its other presets, and `with` changes single margins, in inches. `PrintArea` is one range or several separated by commas, each printed on pages of its own; null prints everything. `PrintTitleRows` and `PrintTitleColumns` repeat rows at the top and columns at the left of every page. `AddRowPageBreak` and `AddColumnPageBreak` start a new page at a row or column, as Insert Page Break does; `RowPageBreaks` and `ColumnPageBreaks` list them. The print area moves with its cells when rows, columns or cells are inserted or deleted, as the defined name Excel keeps it in does; titles and page breaks move with whole rows and columns. `XlsxReader` reads all of them and `XlsxWriter` saves them; the file's other page setup (paper size, scaling, headers and footers) stays as it was. ## Inserting and deleting rows and columns ```csharp snippet=insert-rows sheet.InsertRows(3); // a new row 3; rows 3 and below move down sheet["A3"].Value = "Travel"; sheet["B3"].Value = 300; workbook.Recalculate(); // B7 is =SUM(B2:B6) now and includes the new row sheet.DeleteColumns(5); // column E goes; formulas that read it get #REF! ``` `InsertRows`, `DeleteRows`, `InsertColumns` and `DeleteColumns` work like Excel's commands. Cells, styles, number formats, row heights, column widths, hidden rows and columns, merged areas and tables move, and every reference to the moved cells follows them: in formulas of every sheet and in defined names. A range the new rows are inserted into grows (`SUM(B2:B5)` becomes `SUM(B2:B6)`), and so do merged areas and tables; inserting at a range's first row moves it instead. A range that loses some of its rows shrinks, a reference to a deleted cell becomes `#REF!`, and a table whose rows are all deleted is removed. Whole-column references such as `A:A` do not change when rows move. References through several sheets (`Sheet1:Sheet3!A1`) and text given to `INDIRECT` do not change either, as in Excel. Every formula is calculated again at the next `Workbook.Recalculate`. An edit that would push content past the last row or column, split an array formula (Ctrl+Shift+Enter), or remove a table's header or totals row without the whole table throws `InvalidOperationException` and changes nothing. Inserting or deleting columns inside a table throws `NotSupportedException`, and so does any edit of a sheet that has, or is named by, a formula SharpCell cannot read (such as a link to another workbook): its references could not follow. Formulas that name a table whose rows were all deleted get `#REF!`. Each edit goes through every formula of the workbook, so insert or delete many rows with one call (`InsertRows(5, 100)`) rather than one at a time. When the workbook is saved, conditional formats, data validation, pictures, comments, charts and pivot table sources in the file move with the cells too (see [Saving .xlsx files](saving.md)). ## Inserting and deleting cells ```csharp snippet=insert-cells sheet.Range("A1").Insert(InsertShift.Down); // A1 and A2 move to A2 and A3; column B stays workbook.Recalculate(); // C1 is =SUM(A2:A3) now sheet.Range("A1:A2").Delete(DeleteShift.Up); // A3 moves up to A1; a formula reading A2 gets #REF! ``` `CellRange.Insert` and `CellRange.Delete` work like Excel's Insert and Delete with "Shift cells down", "Shift cells right", "Shift cells up" and "Shift cells left": only the cells in the range's columns (or rows) move, with their styles, number formats, links, data validation, merged areas and tables, and references to them follow as they do for rows. A reference to a range that only some of the moved cells are in stays as it was (`SUM(A1:C5)` does not change when cells of column A move), and a data validation rule over such a range is split. Row heights and column widths stay. A range of whole rows or whole columns inserts or deletes them, as `InsertRows` does. Cells that would move apart from the rest of a merged area, a table or an array formula throw `InvalidOperationException` and change nothing, as Excel refuses them. When the workbook is saved, the file's conditional formats, comments and other parts move with the cells; pictures and charts stay where they are. ## Copying cells ```csharp snippet=cells-copy sheet["B1"].Formula = "=A1*$A$1"; sheet.Range("B1").CopyTo(sheet.Range("B2:B3")); // B2 is =A2*$A$1, B3 is =A3*$A$1 sheet.Range("A1:B3").CopyTo(sheet.Range("D1")); // values, formulas, formats, styles, links, validation workbook.Recalculate(); ``` `CellRange.CopyTo` copies cells as copying and pasting them in Excel does: values, formulas, number formats, styles, hyperlinks, notes and data validation. Relative references in a formula keep their distance from its cell and absolute ones (`$A$1`) stay; a reference that would point past the sheet's edge becomes `#REF!`. Data validation formulas keep their distance from each cell in the same way. Empty cells of the range clear the cells they land on, their validation and hyperlinks too. A destination that is a whole number of times as tall and as wide as the range is filled by repeating it, so one cell copied to `B2:B3` fills both; any other destination gets one copy at its top-left cell. The range and the destination may overlap, and the destination may be in another workbook, where formulas refer to that workbook's sheets and names. Merged cells, column widths and row heights are not copied. A range holding part of an array formula (Ctrl+Shift+Enter) or a formula SharpCell cannot read throws `NotSupportedException`; nothing changes. ## Conditional formats ```csharp snippet=cells-conditional-formats sheet.AddConditionalFormat("B2:B6", ConditionalFormat.CellValue( ConditionalOperator.GreaterThan, "=500", ConditionalStyle.LightRedFill)); sheet.AddConditionalFormat("B2:B6", ConditionalFormat.Top(1, new ConditionalStyle { Bold = true })); workbook.Recalculate(); foreach (var cell in sheet.Range("B2:B6").Cells) { if (cell.ConditionalFormats.Count > 0) // the rules that apply now Console.WriteLine($"{cell.Address}: {cell.DisplayStyle.Fill}"); // as Excel shows it } ``` `ConditionalFormat` has a factory for each of Excel's rules: `CellValue` (greater than, between and the others), `Expression` (a formula true for the cell), `Top` and `Bottom` (items or percent), `AboveAverage` and `BelowAverage`, `DuplicateValues` and `UniqueValues`, `ContainsText`, `NotContainsText`, `BeginsWith`, `EndsWith`, `Blanks`, `NoBlanks`, `Errors`, `NoErrors` and `DateOccurring`. Formulas are written for the range's top-left cell, as in Excel's dialog, and relative references move from cell to cell. A `ConditionalStyle` sets only what the rule changes: font style and colour, fill, borders and number format; `LightRedFill`, `YellowFill` and `GreenFill` are Excel's presets. Rules added first win where several set the same thing, and `StopIfTrue` (`rule with { StopIfTrue = true }`) skips the rules below for cells it applies to. SharpCell works out which cells a rule applies to as Excel does. `Cell.ConditionalFormats` lists the rules that apply to a cell, from the values of the last `Recalculate` (rule formulas are calculated when read), and `Cell.DisplayStyle` is the cell's style with them put over it, Excel's DisplayFormat. An empty cell compares as 0 or as empty text; top, bottom and average rules count only numbers, and duplicates compare text ignoring case and leave empty cells out. Colour scales, data bars and icon sets are read from files and kept, and their cells are listed as applying to numbers, but SharpCell does not work out their colours and cannot add them. Rules move with their cells when rows, columns or cells are inserted or deleted, and `CopyTo` copies them. `ClearConditionalFormats` takes a range out of every rule, or removes them all. ## Notes ```csharp snippet=cells-notes sheet["B2"].Note = new Note("Checked against the invoice", "Ann"); sheet["B3"].Note = new Note("Shown all the time") { IsVisible = true }; foreach (var (cell, note) in sheet.Notes) Console.WriteLine($"{cell.Address}: {note.Text} ({note.Author})"); sheet["B3"].Note = null; // removes it ``` A `Note` is what Excel's Insert Note puts on a cell (a comment, before Excel 365): a text, who wrote it, and whether it is shown all the time (`IsVisible`) or only while the pointer rests on the cell. Notes move with their cells when rows, columns or cells are inserted or deleted, and `CopyTo` copies them. Excel starts the text of a note it makes with the author's name and a colon; SharpCell reads that line as part of the text and writes only the text it is given. `XlsxReader` reads the notes of a file and `XlsxWriter` saves them. A note that did not change keeps its formatted text and its box as they were; a new or changed one is written as plain text in a box beside its cell, where Excel puts a new note. Threaded comments, Excel's newer comments with replies, are not notes: a file's own are kept as they are, and SharpCell does not make them. ## Defined names ```csharp snippet=cells-names foreach (DefinedName name in workbook.DefinedNames) lines.Add($"{name.Name} {name.Formula} {name.Scope?.Name ?? "(workbook)"}"); // TaxRate =0.2 (workbook) ``` `Workbook.DefinedNames` lists workbook-wide and sheet-scoped names in the order they were first defined, spelled as they were defined. It is a snapshot: names defined later are not in it. --- # Recalculation When cell values update, what is recalculated, which cells changed, which cells a formula reads, and how to cancel, inspect problems, use threads and templates, and set the culture. ## Values update on Recalculate ```csharp snippet=recalculate-stale sheet["A1"].Value = 5; // sheet["A2"].Value is still 6: formulas are updated by Recalculate, not on assignment workbook.Recalculate(); // sheet["A2"].Value is now 15 ``` `Workbook.Recalculate` calculates the formulas that are out of date: those whose inputs changed since the last calculation, everything that depends on them, and formulas that use a volatile function. It does not recalculate the rest. Volatile functions are recalculated on every `Recalculate`: - `AGGREGATE`, `SUBTOTAL` - `INDIRECT`, `OFFSET` - `NOW`, `TODAY` - `RAND`, `RANDARRAY`, `RANDBETWEEN` `Workbook.Evaluate` calculates the out-of-date cells its formula reads before evaluating it. ## Changed cells `Recalculate` returns the cells whose values calculation changed, for example to send only those to a client: ```csharp snippet=changed-cells sheet["B2"].Value = 1350; RecalculationResult result = workbook.Recalculate(); foreach (Cell cell in result.ChangedCells) // C2, F2, F3, B6, C6: formulas whose value changed sent.Add($"{cell.Worksheet.Name}!{cell.Address} = {cell.Value}"); ``` `ChangedCells` lists formula cells and cells a spill filled, changed or left (those are now empty), in the order of the sheets, then by row and column. It covers everything calculated since the previous `Recalculate`, including cells `Evaluate` calculated in between. Cells you changed yourself are not in it, nor cells that changed and changed back. A new formula is in it when its result is not empty. After a cancelled `Recalculate`, the next one reports what both calculated. The first `Recalculate` after `XlsxReader.Load` lists the cells where SharpCell's result differs from the one Excel saved in the file. ## Precedents and dependents ```csharp snippet=precedents IReadOnlyList inputs = sheet["F2"].Precedents; // Budget!F1, Budget!B6 IReadOnlyList readers = sheet["B2"].Dependents; // Budget!C2, Budget!F3, Budget!B6 ``` `Cell.Precedents` lists the cells and ranges the cell's formula read in its last calculation: references in the formula and in the names it uses, and references built while calculating, by `INDIRECT` or `OFFSET`. A branch of `IF` that was not taken is not included, so the list is what the value actually depends on now. A cell filled by a spill has its anchor as precedent. The formula must be calculated: an out-of-date one throws `InvalidOperationException`. `Cell.Dependents` lists the formula cells that read the cell, alone or within a range, and for a spilling formula also the cells it spilled into. It throws `InvalidOperationException` while any formula of the workbook is out of date. Both list direct links only; follow them for the rest: ```csharp snippet=affected // Every formula an input reaches, directly or through other formulas. static List Affected(Cell input) { var seen = new HashSet(); var found = new List(); var queue = new Queue(input.Dependents); while (queue.Count > 0) { Cell cell = queue.Dequeue(); if (!seen.Add(cell.ToString())) continue; found.Add(cell); foreach (Cell next in cell.Dependents) queue.Enqueue(next); } return found; } ``` ## Iterative calculation A formula that reads its own result, directly or through other cells, is a circular reference. Excel gives its cells 0, and so does SharpCell, unless iterative calculation is on: ```csharp snippet=iterative var workbook = new Workbook { Iteration = new IterationSettings(Enabled: true) }; Worksheet loan = workbook.AddSheet("Loan"); loan["A1"].Value = 1000; // opening balance loan["A2"].Formula = "=A1+A3"; // closing balance loan["A3"].Formula = "=(A1+A2)/2*0.05"; // interest on the average balance: a circular reference workbook.Recalculate(); // loan["A2"].Value is about 1051.28; without iteration A2 and A3 would be 0 ``` With `Workbook.Iteration` enabled, the cells of a circular reference are calculated in passes, each reading the others' latest values, until no value changes by more than `MaxChange` (0.001 by default) or `MaxIterations` passes (100 by default) are done. A cycle that does not settle keeps its last values and gets an `IterationLimitReached` diagnostic on each of its cells. The cells of a circular reference are calculated again on every `Recalculate`, continuing from their values, as in Excel: `=A1+1` in A1 grows by `MaxIterations` each time. A circular reference through a spilling formula is not iterated; it gives 0 and a `CircularReference` diagnostic. `XlsxReader` reads the settings from the file, as Excel saves them. Changing them calculates every formula again. Iterative calculation follows Excel's documented behaviour, checked by SharpCell's own tests: a counter and converging models give Excel's results whatever the order. It has not been compared with results calculated by Excel, because Excel for the web does not support iterative calculation. In a cycle of several cells that does not settle, the values after the last pass depend on the order the cells are calculated in (sheet, then row, then column), which may differ from Excel's. ## Cancellation ```csharp snippet=cancel using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(30)); try { workbook.Recalculate(timeout.Token); } catch (OperationCanceledException) { // The workbook stays usable: the next Recalculate finishes the work. cancelled = true; } ``` Cells finished before the cancellation keep their new values. The rest stay out of date and are calculated by the next `Recalculate`. ## Diagnostics Problems that do not show in the values alone are listed in `Workbook.Diagnostics`: ```csharp snippet=diagnostics sheet["F1"].Formula = "=G1"; sheet["G1"].Formula = "=F1"; workbook.Recalculate(); foreach (CalculationDiagnostic diagnostic in workbook.Diagnostics) { // CircularReference at Sheet1!F1: Circular reference: Sheet1!F1 -> Sheet1!G1 -> Sheet1!F1 lines.Add($"{diagnostic.Kind} at {diagnostic.Sheet?.Name}!{diagnostic.Address}: {diagnostic.Message}"); } ``` | Kind | What happened | What the cell gets | |---|---|---| | `CircularReference` | Cells reference each other in a loop. | 0 in every cell of the loop, as Excel without iterative calculation | | `FunctionFailure` | A function failed unexpectedly. This is a SharpCell bug worth reporting. | `#VALUE!` | | `UnsupportedFormula` | A formula uses something SharpCell cannot evaluate, such as a link to another workbook. | `#NAME?` | | `LimitExceeded` | A result was larger than a limit allows, such as the cells all spills of a workbook may cover together. | An error | | `IterationLimitReached` | With iterative calculation on, a circular reference did not settle within `MaxIterations` passes. | Its last values | | `CustomFunctionFailure` | A [custom function](custom-functions.md) threw an exception. | `#VALUE!` | Diagnostics describe the current state: editing a cell or calculating it without the problem removes its entries. ## Threads ```csharp snippet=threads // A Workbook is not thread-safe. Separate workbooks are independent and can run in parallel. Parallel.ForEach(paths, path => { Workbook workbook = XlsxReader.Load(path); workbook.Recalculate(); totals.Add(workbook["Budget"]["B6"].Value.AsNumber()); }); ``` ## Templates in a service Loading a file means unzipping, parsing XML and formulas, and calculating every formula. `Workbook.Clone` copies a workbook as it is, calculated values and all, without any of that. A service can load a template once and clone it for each request: ```csharp snippet=clone-template // Once, at startup: load and calculate the template. Workbook template = XlsxReader.Load(SampleTests.BudgetPath); template.Recalculate(); // Per request, on any thread: a copy costs no file reading and no full calculation. Parallel.ForEach(amounts, amount => { Workbook workbook = template.Clone(); workbook["Budget"]["B2"].Value = amount; workbook.Recalculate(); // only what depends on B2 results.Add(workbook["Budget"]["F2"].Value.AsNumber()); }); ``` The copy is independent: changing it does not change the template, and the other way round. It keeps the template's custom functions, and `XlsxWriter.Save` writes it into the template's file. Parsed formulas and the file's bytes are shared, not copied. Cloning only reads the template, so any number of threads may clone it at the same time, as long as nothing changes or calculates the template meanwhile. Each copy, like any workbook, is for one thread at a time. ## Culture and dates ```csharp snippet=culture var german = new Workbook { Culture = CultureInfo.GetCultureInfo("de-DE") }; CellValue number = german.Evaluate("=VALUE(\"1,5\")"); // 1.5 ``` `Workbook.Culture` controls text-to-number conversion, number-to-text conversion and text ordering. It defaults to the invariant culture, which behaves like English Excel. The process culture is never read. `Workbook.DateSystem` says whether serial dates count from 1900 or from 1904; the .xlsx reader sets it from the file. In the 1900 system, serial number 60 is the non-existent 29 February 1900, as in Excel. --- # Custom functions Functions written in C# that formulas call by name, in place of VBA functions or add-ins that a workbook expects. ## Adding a function ```csharp snippet=custom-function workbook.Functions.Add("VAT", args => { CellValue amount = args.Number(0); // converted as Excel converts: "100" is 100 if (amount.IsError) return amount; // pass #VALUE! and other errors on return amount.AsNumber() * 0.2; }, new FunctionOptions { MinArguments = 1, MaxArguments = 1, ScalarParameters = [0] }); sheet["A1"].Value = 100; sheet["A2"].Value = 250; sheet["B1"].Formula = "=VAT(A1)"; // 20 sheet["C1"].Formula = "=VAT(A1:A2)"; // a scalar parameter given a range: spills 20 and 50 workbook.Recalculate(); ``` `Workbook.Functions.Add` takes a name, a body and options. Formulas call the function ignoring case, and can pass it as a value where a LAMBDA is expected, as in `MAP(A1:A9, VAT)`. Formulas that were `#NAME?` because the function was missing are calculated again by the next `Recalculate`; `Remove` turns its calls back into `#NAME?`. Each workbook has its own functions, and `Workbook.Clone` copies them. The name has letters, digits, `.` and `_`, and starts with a letter or `_`. The name of a function SharpCell implements is refused: their results are checked against Excel. A name Excel knows but SharpCell does not implement, such as `WEBSERVICE`, can be added. In files, Excel writes calls of add-in functions as `_xll.NAME(...)` and calls of functions it does not know as `_xludf.NAME(...)`; both find the function `NAME`. ## Arguments | Option | Meaning | |---|---| | `MinArguments`, `MaxArguments` | How many arguments a call may have, 0 to 255. A call outside the range is `#VALUE!`, as with Excel's functions. | | `ScalarParameters` | Positions (from 0) of parameters that take one value. Given a range or an array, the function runs once per element and the results spill, as `LEN(A1:A10)` does. | | `IsVolatile` | The function runs on every `Recalculate`, like `NOW`, not only when its arguments change. | `FunctionArguments` gives the body the call's arguments: - `args[i]` is the value: a single cell's value, a range as a `CellValueKind.Array`, an error as an error value (the function decides what to do with it), and `CellValue.Missing` for an argument left out, as the middle one of `F(1,,2)`. - `Number(i)`, `Text(i)` and `Boolean(i)` convert the argument as Excel does and return either the converted value or the error to return, such as `#VALUE!` for `"abc"` read as a number. - `Caller` is the cell whose formula makes the call; null for `Workbook.Evaluate`. - `DateSystem` and `Culture` are the workbook's, for dates and text. A parameter that is not scalar receives a range as an array of its values: ```csharp snippet=custom-function-range workbook.Functions.Add("SUMSQUARES", args => { double total = 0; foreach (CellValue value in args[0].Kind == CellValueKind.Array ? args[0].AsArray() : new[,] { { args[0] } }) { if (value.Kind == CellValueKind.Number) total += value.AsNumber() * value.AsNumber(); } return total; }, new FunctionOptions { MinArguments = 1, MaxArguments = 1 }); sheet["B1"].Formula = "=SUMSQUARES(A1:A3)"; // the range arrives as an array: 25 ``` ## Results and failures The body returns a `CellValue`. An array result spills like any dynamic array. `Missing` and lambda results are `#VALUE!`. An exception thrown by the body makes the cell `#VALUE!` and adds a diagnostic of kind `DiagnosticKind.CustomFunctionFailure`, naming the function and the exception. The rest of the workbook calculates as usual. An `OperationCanceledException` cancels the calculation only when its token was cancelled; otherwise it is a failure like any other exception. ## Rules for the body - **Read cells only through the arguments.** SharpCell records which cells a formula reads, to know what to calculate again. A cell the body reads on its own, such as `args.Caller!.Worksheet["Z1"].Value`, is not recorded: the formula is not updated when it changes. Pass it as an argument, or make the function volatile. - **Do not change the workbook.** Setting cells, defining names, adding sheets or functions, and calling `Recalculate` or `Evaluate` from inside a body throw `InvalidOperationException`, which turns into `#VALUE!` and a diagnostic. - **Keep the arguments to the call.** `FunctionArguments` is valid only while the body runs. An array it gives is a copy, which the body may change. - **Expect extra calls.** When a formula's inputs are out of date, SharpCell may call the body before calculating them, with stand-in values, and throw that result away. A body without side effects does not notice; one that calls a service should cache or tolerate it. - **Stop when cancelled.** `args.CancellationToken` is the token given to `Recalculate`; a slow body can throw `OperationCanceledException` through it to cancel the calculation. - **Clones share the body.** `Workbook.Clone` copies the delegate, not what it captures: a body used by clones on several threads must be thread-safe. A workbook whose formulas use custom functions saves with `XlsxWriter.Save` like any other: the results are calculated by SharpCell, so `KeepUncalculated` is not needed. --- # Reading .xlsx files What `XlsxReader` loads, what it skips, and how saved results and streams behave. It reads .xlsx files and macro-enabled .xlsm and .xltm files; macros are kept for saving and never run. ## What is read - Sheets, including hidden ones, in workbook order. - Constants: numbers, text, logical values and errors. - Formulas: ordinary, shared, legacy array formulas (Ctrl+Shift+Enter) and dynamic arrays. - The results Excel saved with each formula. - Defined names, workbook-wide and sheet-scoped. - Tables, with their column names and header and totals rows. - Hidden rows, and whether a sheet has a filter (see [Tables and hidden rows](tables.md)). - The date system, 1900 or 1904. - Each cell's number format, for `Cell.NumberFormat` and `Cell.Text` (see [Cells, ranges and dates](cells.md#number-formats-and-display-text)). - Each cell's style: font, solid fill, borders, alignment; theme colours as RGB (see [Cells, ranges and dates](cells.md#styles)). - Column widths, row heights, hidden columns and merged cells (see [Cells, ranges and dates](cells.md#column-widths-row-heights-and-merged-cells)). - Frozen panes and whether gridlines show (see [Cells, ranges and dates](cells.md#frozen-panes-and-gridlines)). - Page orientation, margins, print areas, print titles and page breaks (see [Cells, ranges and dates](cells.md#printing)). - Conditional formats with their looks (see [Cells, ranges and dates](cells.md#conditional-formats)). - Notes on cells (see [Cells, ranges and dates](cells.md#notes)). - The iterative calculation settings (see [Iterative calculation](recalculation.md#iterative-calculation)). ## What is not read Pattern and gradient fills, styles of whole rows and columns, charts, pivot tables, threaded comments and macros are skipped. Conditional formats in the extension list (newer data bars and icon sets) stay in the file without being read. | File | Result | |---|---| | .xls, .xlsb, encrypted files | `NotSupportedException` | | A damaged or non-xlsx file | `InvalidDataException` | ## Saved results After `Load`, a cell with a formula holds the result Excel saved in the file. A file written by another program may have no saved results; those cells are empty. `Workbook.Recalculate` calculates every formula with SharpCell and replaces the saved results. A formula SharpCell cannot parse, such as a link to another workbook, does not fail the load. Once calculated, it is `#NAME?` and listed in `Workbook.Diagnostics` as `UnsupportedFormula`. A legacy array formula covers a fixed range. Setting the value or formula of one of its cells other than the top-left one throws `InvalidOperationException`; change the whole array at its top-left cell. ## Streams ```csharp snippet=load-stream using Stream stream = File.OpenRead(path); Workbook workbook = XlsxReader.Load(stream); // the stream is left open ``` The stream does not have to be seekable; a stream that is not is copied into memory first. ## Writing `XlsxWriter` saves a workbook into the file it was read from, keeping what SharpCell does not read; see [Saving .xlsx files](saving.md). --- # Saving .xlsx files `XlsxWriter` saves a workbook as an .xlsx file: one read by `XlsxReader`, with whatever you changed, or one built in code. ```csharp snippet=save-template Workbook workbook = XlsxReader.Load(templatePath); workbook["Budget"]["B2"].Value = 1350; // an input workbook.Recalculate(); // required before saving XlsxWriter.Save(workbook, resultPath); // charts, comments and other cells stay as they were ``` ```csharp snippet=save-new var workbook = new Workbook(); Worksheet sheet = workbook.AddSheet("Report"); sheet["A1"].Value = "Total"; sheet["A1"].Style = sheet["A1"].Style with { Font = sheet["A1"].Style.Font with { Bold = true } }; sheet["B1"].Formula = "=SUM(B2:B4)"; sheet["B2"].Value = 10; sheet["B3"].Value = 20; sheet["B4"].Value = 30; sheet["B1"].NumberFormat = "#,##0.00"; sheet.SetColumnWidth(1, 15); workbook.Recalculate(); XlsxWriter.Save(workbook, path); // a new .xlsx file; Excel shows Total 60.00 ``` ## What is written Everything SharpCell models: - Cell values and formulas, with the results SharpCell calculated. Formulas are written the way Excel stores them, so newer functions such as `XLOOKUP`, `FILTER` or `LET` work when Excel opens the file. - Dynamic arrays, with the area they spill over. A formula that reads a range where one value is expected (`=SUM(A1:A3*2)`) is saved as a dynamic array formula, so Excel does not reduce it as Excel 2016 would. - Cell styles and number formats, column widths, hidden columns, row heights, hidden rows, merged cells, frozen panes, gridlines, the default column width and row height, and whether a sheet is filtered. - Hyperlinks, notes, data validation, conditional formats, sheet and workbook protection, and locked and hidden cells. - Page orientation and margins, print areas, print titles and manual page breaks. - New sheets, defined names, new tables, the date system and the iteration settings. A workbook read from a file keeps everything SharpCell does not model, copied unchanged from the file: charts, pivot tables, threaded comments, the settings of colour scales, data bars and icon sets, pictures, macros, headers and footers, paper size and scaling, pattern fills, and parts SharpCell does not know. A cell that did not change is copied as it was, and a new style starts from the cell's old one, so what SharpCell does not read (rotated text, a pattern fill) stays. Saving a workbook in which nothing changed writes the original file byte for byte. New text goes into the cell itself, not into the shared string table. A new table's header cells are saved holding the column names as text, as Excel requires. When formulas change, the file's calculation chain is left out; Excel builds it again. `#SPILL!` and `#CALC!` are saved the way Excel saves them, so Excel shows them before it recalculates. After rows or columns were inserted or deleted, everything in the file that points at cells follows them, including what SharpCell does not model: filters, pictures and shapes, threaded comments, chart series and pivot table sources. A comment on a deleted cell goes; a picture anchored in deleted rows shrinks to where they were. A pivot table moves with the rows above it, but rows inserted into it or deleted from it make the save fail, as Excel refuses that edit. A sheet holding an Excel data table cannot be saved after its rows or columns moved (`NotSupportedException`). Formulas SharpCell cannot read are kept as the file has them, with the results Excel saved. Saving to a path writes a new file next to the target and then replaces it, so the file the workbook was read from can be the target. ## What cannot be saved | Situation | Exception | |---|---| | A workbook without sheets | `InvalidOperationException` | | A changed cell of an Excel data table, rows or columns moved on a sheet with one, or `#SPILL!`/`#CALC!` typed as a value | `NotSupportedException` | | Rows or columns inserted into or deleted from a pivot table | `NotSupportedException` | | A formula SharpCell cannot read changed so that formulas sharing its text would lose it | `NotSupportedException` | | Formulas not calculated since the last change (or since loading) | `InvalidOperationException` | | Formulas SharpCell cannot calculate | `XlsxWriteException` | Nothing is written when a save is refused. ## Formulas SharpCell cannot calculate A formula that calls a function SharpCell does not know, or links to another workbook, has no trustworthy result. By default `Save` refuses with an `XlsxWriteException`; its `Problems` list the cells. With `new XlsxWriteOptions { KeepUncalculated = true }` those cells keep the result Excel saved, and the file asks Excel to recalculate every formula when it opens it. Until then, programs that read saved results (pandas, openpyxl, `XlsxReader`) see what SharpCell calculated for the cells that read such a formula. ## Memory A workbook read by `XlsxReader` keeps the file's bytes in memory for saving: count the file's size on top of the workbook. --- # Tables and hidden rows An Excel table is a named range with column names. Formulas refer to it with structured references such as `Sales[Units]`; SharpCell evaluates them against the table as it is. ## Tables in code ```csharp snippet=tables-add var workbook = new Workbook(); Worksheet sheet = workbook.AddSheet("Sales"); sheet["A1"].Value = "Region"; sheet["B1"].Value = "Units"; sheet["A2"].Value = "North"; sheet["B2"].Value = 10; sheet["A3"].Value = "South"; sheet["B3"].Value = 20; sheet.AddTable("Sales", "A1:B3"); sheet["D1"].Formula = "=SUM(Sales[Units])"; sheet["C2"].Formula = "=Sales[@Units]*2"; // the table's row on the formula's own row workbook.Recalculate(); ``` `AddTable` takes the column names from the header row as it is when the table is added; a header formula counts by its value, as Excel turns it into text. A single cell, such as `"A1"`, is a one-cell table without a header row. A table does not change afterwards: SharpCell does not resize or rename tables, and editing a header cell does not rename its column. Tables and defined names share one set of names. ## Structured references | Reference | Cells | |---|---| | `Sales[Units]`, `Sales[[#Data],[Units]]` | the data rows of a column | | `Sales`, `Sales[]`, `Sales[#Data]` | all data rows | | `Sales[#All]` | the whole table, header and totals rows included | | `Sales[#Headers]`, `Sales[#Totals]` | the header or totals row; `#REF!` if the table has none | | `Sales[[#Headers],[#Data]]`, `Sales[[#Data],[#Totals]]` | two adjacent parts | | `Sales[[Jan]:[Mar]]` | a range of columns | | `Sales[@Units]`, `Sales[[#This Row],[Units]]` | the data row on the formula's own row; `#VALUE!` on any other row | | `[Units]`, `[@Units]` | `Sales[Units]` and `Sales[@Units]`, written in a formula inside the table | Inside a name, an apostrophe escapes `[`, `]`, `#` and `'`: the column `[est] Q1` is `Sales['[est'] Q1]`. A missing table or column is `#REF!`. A formula inside a table that returns several values is `#SPILL!`, as in Excel. `Workbook.Evaluate` runs a formula that belongs to no cell, so it is in no table and has no row: there `[Units]` is `#REF!` and `Sales[@Units]` is `#VALUE!`, while `Sales[Units]` works. ## Tables from .xlsx files `XlsxReader` reads every table of the file: its name, range, column names and whether it has header and totals rows. `Workbook.Tables` lists them. ## Hidden rows and filters ```csharp snippet=tables-hidden-rows sheet.SetRowHidden(2, true); sheet["B1"].Formula = "=SUBTOTAL(9,A1:A3)"; // counts a row hidden by hand sheet["B2"].Formula = "=SUBTOTAL(109,A1:A3)"; // skips it workbook.Recalculate(); // B1 is 7, B2 is 5 sheet.FilterMode = true; // as if a filter hid the row workbook.Recalculate(); // both are 5 ``` - `SUBTOTAL` with codes 101–111 and `AGGREGATE` with options 1, 3, 5 and 7 skip hidden rows. - `SUBTOTAL` with codes 1–11 counts rows hidden by hand and skips rows a filter hid. A file does not say which is which, so Excel decides by the sheet: on a sheet with a filter that has criteria, every hidden row counts as filtered. `Worksheet.FilterMode` is that flag; the reader sets it from the file. - `AGGREGATE` with options 0, 2, 4 and 6 counts every row. - SharpCell does not apply filter criteria: hidden rows are as the file or your code left them. --- # Functions Which functions exist, how criteria are written, and how dates and regular expressions behave. ## Function status The [compatibility report](../compatibility.md) lists every function with one of three statuses, and the same data is in [compatibility.json](../compatibility.json): | Status | Meaning | |---|---| | `implemented` | Matches Excel on the reference workbooks. | | `known deviation` | Implemented, with a described difference from Excel. | | `not implemented` | The function name evaluates to `#NAME?`. | A function that does not appear in the report evaluates to `#NAME?` as well. ## Criteria `COUNTIF`, `SUMIF`, `AVERAGEIF`, the `*IFS` functions and the `D` database functions take criteria written as in Excel: | Criterion | Matches | |---|---| | `5` or `"=5"` | The number 5 and the text `"5"` | | `">5"`, `"<=5"`, `"<>5"` | Numbers compared with 5. `<` and `>` compare only values of the same type. | | `"<>"` | Any non-empty cell; the text `""` counts as non-empty | | `""` | Empty cells and the text `""` | | `"app*"`, `"?pple"` | Text with wildcards: `*` any run of characters, `?` one character | | `"~*"` | A literal `*`; `~` escapes `*`, `?` and `~` | | `TRUE`, `"true"` | Logical values only | Wildcards apply to text only. In the `D` functions, a plain text criterion matches text that starts with it, as in Excel. `MATCH` (match type 0), `XMATCH` and `XLOOKUP` (match mode 2) accept the same wildcards but none of the operators: `MATCH(">5", A1:A9, 0)` looks for the text `">5"`. ## Dates and times Dates and times are numbers: serial days, with the time of day as the fraction. Text that looks like a date or time, such as `"2024-01-10"` or `"12:00"`, is converted to a number when arithmetic or a function needs one, using `Workbook.Culture`. Two-digit years 00–29 mean 2000–2029. ## Grouping `GROUPBY` and `PIVOTBY` summarize a table the way a pivot table does, and `PERCENTOF` gives a part of a total. The function argument is a function passed by name (`SUM`, `AVERAGE`, `PERCENTOF`), a `LAMBDA`, or several of them side by side with `HSTACK`. A function of two arguments, such as `PERCENTOF` or `LAMBDA(part, all, SUM(part)/SUM(all))`, also gets the values its group is a part of (in `PIVOTBY`, the ones `relative_to` picks): ```csharp snippet=groupby sheet["D1"].Formula = "=GROUPBY(A1:A3, B1:B3, SUM)"; // North 40, South 20, Total 60 sheet["G1"].Formula = "=GROUPBY(A1:A3, B1:B3, HSTACK(SUM, MAX))"; // a header row SUM, MAX, then one column each workbook.Recalculate(); ``` Groups are sorted ascending, empty keys last; totals are labelled `Total`, or `Grand Total` when there are subtotals. The results were compared with Excel for the web cell by cell. In `PIVOTBY`, several functions at once give `#VALUE!`. ## Regular expressions `REGEXTEST`, `REGEXEXTRACT`, `REGEXREPLACE` and match mode 3 of `XLOOKUP` and `XMATCH` use .NET regular expressions, not PCRE2 as Excel does. Most patterns behave the same. `\d` and `\w` also match non-ASCII digits and letters, and PCRE-only syntax such as possessive quantifiers is not available. --- # What SharpCell does not do Every known gap in one list, so you can decide before you start. - **Writing files.** `XlsxWriter` saves what SharpCell models (see [Saving .xlsx files](saving.md)). It cannot add charts, pivot tables, threaded comments, pictures, colour scales, data bars or icon sets, or remove or rename sheets; a file's own are kept as they are. - **Other file formats.** .xls, .xlsb and encrypted files throw `NotSupportedException`. Macros of .xlsm files are kept, never run. - **Tables change only with rows.** Tables grow and shrink with inserted and deleted rows, but cannot be resized, renamed or removed directly, columns cannot be inserted into or deleted from them, and editing a header cell does not rename its column. - **Filters are not applied.** Rows are hidden as the file or your code left them; filter criteria are not evaluated. - **Iterative calculation is not compared with Excel.** It follows Excel's documented behaviour, but Excel for the web cannot calculate it, so no reference workbook pins the order of passes in a cycle of several cells. - **Styles are partly read.** Fonts, solid fills, borders, alignment, number formats and cell protection (locked, hidden) are read and saved; pattern and gradient fills and rotated text are not read (a file's own are kept when it is saved). The colours of colour scales, data bars and icon sets are not worked out. - **Protection is not enforced.** Sheet and workbook protection is read, set and saved, but SharpCell does not enforce it: code can still change a protected sheet or workbook. - **Data validation is stored, not evaluated.** Rules are read, set and saved; nothing checks a value against them. Validations in `extLst` (`x14`) are not modelled, and neither is the `imeMode` attribute: it is lost when a sheet's rules are rewritten, which includes any row or column insert or delete on that sheet. - **Large hyperlinks.** A hyperlink covering more than 10,000 cells, or more than 100,000 linked cells on a sheet, is kept in the file but not read into the model. - **Hyperlink style.** Setting a hyperlink does not apply Excel's blue underlined Hyperlink style; style the cell yourself. - **Localized function names.** Formulas use English names, as in the file format. - **Links to other workbooks.** They evaluate to `#NAME?`. - **Cube, real-time data and web functions.** `CUBE*`, `RTD` and `WEBSERVICE` are not available. - **Concurrent use.** One `Workbook` must not be used from several threads at once. Separate workbooks are independent, and one workbook may be cloned from several threads while nothing changes it (see [Templates in a service](recalculation.md#templates-in-a-service)). Each function's status, and every function that differs from Excel in a described way, is in the [compatibility report](../compatibility.md). --- # Roadmap Where SharpCell is going, in the order the work is planned. Each step ships when its tests pass and its results are checked against Microsoft Excel. Dates are not promised; the project has one maintainer. ## Released | Version | What | |---|---| | 0.1 | The engine: parser, dependency graph, recalculation, dynamic arrays, LET/LAMBDA, reading .xlsx | | 0.2 | Tables and structured references (`Sales[Units]`), hidden rows in `SUBTOTAL` and `AGGREGATE` | | 0.3 | Saving recalculated values back into the file a workbook was read from | | 0.4 | API for applications: walking and writing cells, custom functions, changed cells, cloning templates | | 0.5 | Number formats and `Cell.Text`; iterative calculation of circular references | | 0.6 | Cell styles, column widths, row heights, merged cells, frozen panes and gridlines, read from files | | 0.7 | Saving everything the model holds, workbooks built in code, .xlsm files, inserting and deleting rows and columns; functions as values, `GROUPBY`, `PIVOTBY`, `PERCENTOF` | | 0.8 | `Cell.Input`: a cell as typed in Excel | | 0.9 | Building workbooks: filling sheets from collections and `DataTable`s, hyperlinks, data validation, sheet and workbook protection, locked and hidden cells | | 0.10 | Copying cells as Excel pastes them, formulas, links and data validation following (`CellRange.CopyTo`) | ## Now: 0.11, rules on cells - Done: conditional formatting, read, added and saved. SharpCell also tells which cells a rule applies to and how they look (`Cell.ConditionalFormats`, `Cell.DisplayStyle`), since it calculates the formulas Excel would. Colour scales, data bars and icon sets are kept, not drawn. - Done: notes, read, added and saved. - Done: page setup: orientation, margins, print area, print titles and page breaks. - Done: inserting and deleting cells with Excel's "Shift cells down / right / up / left". - Benchmarks against EPPlus and ClosedXML: loading, recalculating and saving large workbooks. ## Later - Cells that break their data validation, found by calculating the rules (Excel's Circle Invalid Data). - Sorting a range, with formulas moving as Excel moves them. - Fitting column widths to their contents. This needs font metrics, which the engine does not carry; it may come as a separate package. - Resizing and renaming tables, and inserting columns into them. - Removing and renaming sheets. - Rich text in cells. ## Not planned Creating charts, pivot tables, pictures or shapes (a file's own are kept when it is saved), running macros, .xls and .xlsb files, and encrypted files. --- # Using SharpCell from an AI coding agent How to give Claude Code, Cursor, Copilot or another coding agent accurate knowledge of SharpCell. ## Point the agent at llms.txt The documentation is published as plain Markdown next to the HTML. Give your agent one of these: - [llms.txt](llms.txt): a short index of every page with one line each. - [llms-full.txt](llms-full.txt): the guides and the API reference in one file, for agents that read a whole document. - Any page with `.md` instead of `.html` in its address, such as [the API reference](api/index.md). ## Add the rules to your project Paste this into the file your agent reads: `AGENTS.md`, `CLAUDE.md`, `.cursor/rules/sharpcell.mdc` or `.github/copilot-instructions.md`. ```markdown ## SharpCell (Excel formula engine) - Packages: `SharpCell` (engine) and `SharpCell.Xlsx` (reads .xlsx, saves values back). Namespaces `SharpCell`, `SharpCell.Xlsx`. - Load: `Workbook workbook = XlsxReader.Load(path);` Build in code: `new Workbook()`, `AddSheet`, `sheet["A1"].Value`, `sheet["A2"].Formula = "=A1*3"`. - Call `workbook.Recalculate()` after changing values or formulas. Values do not update on assignment. It returns the cells whose values changed (`ChangedCells`). - Walk cells with `sheet.Cells` (non-empty only) or `sheet.Range("A1:C10").GetValues()`; dates convert with `value.AsDateTime(workbook.DateSystem)` and `CellValue.DateTime(date, workbook.DateSystem)`. - `cell.Text` is the value as Excel shows it through its number format (`cell.NumberFormat`, read from the file). - Circular references give 0 unless `workbook.Iteration = new IterationSettings(Enabled: true)`; files carry their own setting. - Custom functions: `workbook.Functions.Add("NAME", args => ..., new FunctionOptions { ... })`; the body reads cells only through its arguments and must not change the workbook. - For a template used per request, load and recalculate it once, then `template.Clone()` per request; cloning from several threads is safe while nothing changes the template. - `workbook.Evaluate("=SUM(1,2)")` evaluates a formula that lives in no cell. - A `CellValue` has a `Kind`: Number, Text, Boolean, Error, Array, Empty, Missing, Lambda. Check `Kind` before `AsNumber()`, `AsText()` and the other `As` methods; they throw on the wrong kind. - Excel errors such as #DIV/0! are values (`Kind == Error`), not exceptions. A formula that cannot be parsed throws `FormulaParseException`. - Formulas use Excel's file syntax: English function names, commas between arguments, dot decimals; the leading `=` is optional. - Dates are numbers (serial days). Text-to-number conversion uses `workbook.Culture`, invariant by default. - A `Workbook` is not thread-safe; use one per thread (clones of one template included). - Call `Workbook.Recalculate()` before `XlsxWriter.Save`. It saves values, formulas, styles, sizes, sheets, names and tables; a workbook read from a file keeps its charts, pivot tables and comments. - Not supported: .xls/.xlsb, encrypted files, localized function names. - Before relying on a function, check its status in compatibility.json: https://ifmelate.github.io/SharpCell/compatibility.json - Docs for agents: https://ifmelate.github.io/SharpCell/llms.txt ``` ## Check a function before using it `compatibility.json` lists every function with its status and how many Excel-calculated cells it matched: ```bash curl -s https://ifmelate.github.io/SharpCell/compatibility.json | jq '.functions[] | select(.name == "XLOOKUP")' ``` ```json {"name": "XLOOKUP", "status": "known deviation", "deviation": "Match mode 3 (regular expressions) uses .NET regular expressions, whose syntax differs from Excel's PCRE2 in details.", "cases": 437, "passed": 437} ``` A function with status `not implemented` evaluates to `#NAME?`. --- # CalculationDiagnostic ```csharp public sealed class CalculationDiagnostic ``` Namespace `SharpCell` · package `SharpCell` Something calculation noticed that is not visible in cell values alone. ## Constructors ### CalculationDiagnostic(DiagnosticKind, Worksheet, string, string) ```csharp public CalculationDiagnostic(DiagnosticKind kind, Worksheet? sheet, string? address, string message) ``` Something calculation noticed that is not visible in cell values alone. ## Properties ### Address ```csharp public string? Address { get; } ``` A1 address of the affected cell; null for [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string). ### Kind ```csharp public DiagnosticKind Kind { get; } ``` What happened. ### Message ```csharp public string Message { get; } ``` A description for people, such as the cells that form a circular reference. ### Sheet ```csharp public Worksheet? Sheet { get; } ``` Sheet of the affected cell; null for [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string). ## Methods ### ToString() ```csharp public override string ToString() ``` The kind, the cell if there is one, and the message on one line. --- # Cell ```csharp public sealed class Cell ``` Namespace `SharpCell` · package `SharpCell` A handle to one cell position. Creating a handle does not create the cell; storage is allocated only when a value or formula is set. ## Properties ### Address ```csharp public string Address { get; } ``` The A1 address without the sheet name, such as `B2`. ### Column ```csharp public int Column { get; } ``` The column number, from 1 (A is 1). ### ConditionalFormats ```csharp public IReadOnlyList ConditionalFormats { get; } ``` The conditional formats that apply to the cell now, highest priority first, as Excel works them out from the cell's value and the values of the rules' ranges: up to the first that applies with [ConditionalFormat.StopIfTrue](SharpCell.ConditionalFormat.md#stopiftrue). Values are those of the last [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken); rule formulas are calculated on each read. ### Dependents ```csharp public IReadOnlyList Dependents { get; } ``` The formula cells that read this cell in their last calculation, alone or in a range; for a cell whose formula spills, also the cells it spilled into. Direct readers only: follow [Cell.Dependents](SharpCell.Cell.md#dependents) again for the cells that read those. In the order of their sheets in the workbook, then by row and column. **Exceptions** - `InvalidOperationException`: Some formula of the workbook is out of date: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first. ### DisplayStyle ```csharp public CellStyle DisplayStyle { get; } ``` The cell's style with the conditional formats that apply to it put over it, as Excel shows the cell (its Range.DisplayFormat): where rules set the same thing, the higher one's wins. A rule's number format is not part of it. ### Formula ```csharp public string? Formula { get; set; } ``` Formula text starting with '=' (added if missing), or null for no formula. The text is parsed on assignment; invalid text throws [FormulaParseException](SharpCell.FormulaParseException.md) and leaves the cell unchanged. **Exceptions** - `InvalidOperationException`: The cell is part of an array formula other than its top-left cell. ### Hyperlink ```csharp public Hyperlink? Hyperlink { get; set; } ``` The cell's hyperlink; null for none, which setting null also gives. The cell's value is what Excel shows: setting a link neither changes the value nor applies the Hyperlink cell style. A link moves with its cell when rows or columns are inserted, and goes with it when they are deleted. ### Input ```csharp public string Input { get; set; } ``` The cell as typed in Excel. Setting it does what typing the text and pressing Enter does: `=` starts a formula; `'` keeps the rest as text; `TRUE`, `FALSE` and error literals such as `#N/A` are those values; numbers, percentages, currency and grouped digits, dates and times are read in the workbook's [Workbook.Culture](SharpCell.Workbook.md#culture), as Excel reads them in arithmetic and DATEVALUE (a date without a year is in the current year); anything else is text; an empty string clears the cell's content. A cell in the General format takes a format from what was typed, as in Excel: `12%` gives `0%`, `1,250.50` gives `#,##0.00`, `$5` a currency format, a date the culture's short date, a time `h:mm` with seconds and AM/PM when they were typed. A cell with a format of its own keeps it. Getting it gives the text Excel shows to edit the cell, which set again gives the same content: the formula, a number in full (a percentage as one, a date or time as the culture writes it), text with an apostrophe in front when it would read as something else. **Exceptions** - `FormulaParseException`: The text starts with `=` but is not a formula; the cell is unchanged. - `InvalidOperationException`: The cell is part of an array formula other than its top-left cell. ### Note ```csharp public Note? Note { get; set; } ``` The cell's note, or null for none, which setting null also gives. A note moves with its cell when rows, columns or cells are inserted, goes with it when they are deleted, and is copied with it by [CellRange.CopyTo](SharpCell.CellRange.md#copyto-cellrange). ### NumberFormat ```csharp public string NumberFormat { get; set; } ``` The number format Excel shows the cell with, as a format code such as `0.00`, `#,##0` or `yyyy-mm-dd`; `General` when the cell has none. Formats are read from .xlsx files and shape [Cell.Text](SharpCell.Cell.md#text); they play no part in calculation. An empty cell can have a format, and a format alone does not make a cell hold something. **Exceptions** - `ArgumentException`: The code is not a number format. ### Precedents ```csharp public IReadOnlyList Precedents { get; } ``` The cells and ranges the cell's formula read in its last calculation: references in the formula, in the defined names it uses, and those built while calculating (`INDIRECT`, `OFFSET`). A branch of `IF` that was not taken is not included. A cell filled by another cell's spill has that cell as its precedent; a constant or empty cell has none. The ranges come in the order of their sheets in the workbook, then by their top-left cell. **Exceptions** - `InvalidOperationException`: The formula is out of date: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first. ### Row ```csharp public int Row { get; } ``` The row number, from 1. ### Style ```csharp public CellStyle Style { get; set; } ``` How the cell looks: font, fill, borders and alignment. A cell without a style of its own has [Workbook.DefaultStyle](SharpCell.Workbook.md#defaultstyle); setting null gives it back. Styles are read from .xlsx files; they play no part in calculation, and a style alone does not make a cell hold something. ### Text ```csharp public string Text { get; } ``` The cell as Excel shows it: the value formatted by [Cell.NumberFormat](SharpCell.Cell.md#numberformat) with the workbook's culture and date system. Column width plays no part, so a number that fits no width is never cut short; a date format on a number that is no date shows `#######`. Errors show as `#DIV/0!` and the like, logical values as `TRUE` and `FALSE`, an empty cell as an empty string. ### Validation ```csharp public DataValidation? Validation { get; } ``` The data validation rule of the cell, or null for none. Its formulas are relative to the first cell of the rule's first range, not to this cell. Rules are set with [Worksheet.AddValidation](SharpCell.Worksheet.md#addvalidation-string-datavalidation). ### Value ```csharp public CellValue Value { get; set; } ``` The cell's [CellValue](SharpCell.CellValue.md): a constant, or the formula's result as of the last calculation (values do not change until [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken)). Setting a value removes the formula. Cells hold scalars only: Missing, arrays and lambdas cannot be stored. **Exceptions** - `InvalidOperationException`: The cell is part of an array formula other than its top-left cell. ### Worksheet ```csharp public Worksheet Worksheet { get; } ``` The sheet the cell belongs to. ## Methods ### ToString() ```csharp public override string ToString() ``` The address with the sheet name, such as `Sheet1!B2`. --- # CellBorder ```csharp public sealed class CellBorder ``` Namespace `SharpCell` · package `SharpCell` One edge of a cell's border. A record: borders compare by value. ## Constructors ### CellBorder(CellBorderStyle, CellColor?) ```csharp public CellBorder(CellBorderStyle Style, CellColor? Color = default) ``` One edge of a cell's border. A record: borders compare by value. **Parameters** - `Style`: The line. - `Color`: The colour; null for automatic, which Excel shows black. ## Properties ### Color ```csharp public CellColor? Color { get; init; } ``` The colour; null for automatic, which Excel shows black. ### Style ```csharp public CellBorderStyle Style { get; init; } ``` The line. --- # CellBorderStyle ```csharp public enum CellBorderStyle ``` Namespace `SharpCell` · package `SharpCell` The lines Excel draws borders with. ## Values | Name | Value | Description | |---|---|---| | `Thin` | 0 | A thin solid line. | | `Medium` | 1 | A solid line of medium weight. | | `Thick` | 2 | A thick solid line. | | `Double` | 3 | Two thin lines. | | `Hair` | 4 | A hairline, thinner than [CellBorderStyle.Thin](SharpCell.CellBorderStyle.md#thin). | | `Dotted` | 5 | A thin line of dots. | | `Dashed` | 6 | A thin dashed line. | | `DashDot` | 7 | A thin line of dashes and dots. | | `DashDotDot` | 8 | A thin line of a dash and two dots. | | `MediumDashed` | 9 | A dashed line of medium weight. | | `MediumDashDot` | 10 | A line of dashes and dots of medium weight. | | `MediumDashDotDot` | 11 | A line of a dash and two dots of medium weight. | | `SlantDashDot` | 12 | A slanted line of dashes and dots of medium weight. | --- # CellColor ```csharp public readonly struct CellColor ``` Namespace `SharpCell` · package `SharpCell` A colour as Excel shows it, in sRGB. Theme and indexed colours of a file are turned into these when it is read; Excel ignores the alpha channel, so there is none. Colours compare by value. ## Constructors ### CellColor(byte, byte, byte) ```csharp public CellColor(byte R, byte G, byte B) ``` A colour as Excel shows it, in sRGB. Theme and indexed colours of a file are turned into these when it is read; Excel ignores the alpha channel, so there is none. Colours compare by value. **Parameters** - `R`: Red, 0 to 255. - `G`: Green, 0 to 255. - `B`: Blue, 0 to 255. ## Properties ### B ```csharp public byte B { get; init; } ``` Blue, 0 to 255. ### G ```csharp public byte G { get; init; } ``` Green, 0 to 255. ### R ```csharp public byte R { get; init; } ``` Red, 0 to 255. ## Methods ### FromRgb(int) ```csharp public static CellColor FromRgb(int rgb) ``` A colour from a `0xRRGGBB` number. ### ToString() ```csharp public override string ToString() ``` The colour as `#RRGGBB`. --- # CellFont ```csharp public sealed class CellFont ``` Namespace `SharpCell` · package `SharpCell` The font of a cell's text. A record: fonts compare by value, and `with` makes a changed copy, such as `font with { Bold = true }`. ## Constructors ### CellFont() ```csharp public CellFont() ``` Calibri 11 in the automatic colour, as [CellFont.Default](SharpCell.CellFont.md#default); set properties in an initializer. ## Properties ### Bold ```csharp public bool Bold { get; init; } ``` Bold text. ### Color ```csharp public CellColor? Color { get; init; } ``` The text colour; null for automatic, which Excel shows black. ### Default ```csharp public static CellFont Default { get; } ``` Calibri 11 in the automatic colour, the font of a new workbook in Excel 2007 to 2021. ### Italic ```csharp public bool Italic { get; init; } ``` Italic text. ### Name ```csharp public string Name { get; init; } ``` The font family, such as `Calibri`. **Exceptions** - `ArgumentException`: The name is empty. ### Size ```csharp public double Size { get; init; } ``` The size in points, 1 to 409 as in Excel. **Exceptions** - `ArgumentOutOfRangeException`: The size is outside 1 to 409. ### Strikethrough ```csharp public bool Strikethrough { get; init; } ``` Text struck through. ### Underline ```csharp public CellUnderline Underline { get; init; } ``` The underline, if any. --- # CellHorizontalAlignment ```csharp public enum CellHorizontalAlignment ``` Namespace `SharpCell` · package `SharpCell` Where a cell's text sits across the cell. ## Values | Name | Value | Description | |---|---|---| | `General` | 0 | Excel's default: numbers right, text left, logical values and errors in the middle. | | `Left` | 1 | Against the left edge, after the indent. | | `Center` | 2 | In the middle. | | `Right` | 3 | Against the right edge, before the indent. | | `Fill` | 4 | The text repeated to fill the cell. | | `Justify` | 5 | Wrapped, each line but the last spread to both edges. | | `CenterContinuous` | 6 | In the middle of this cell and the empty cells to its right. | | `Distributed` | 7 | Each line spread evenly across the cell. | --- # CellRange ```csharp public sealed class CellRange ``` Namespace `SharpCell` · package `SharpCell` A rectangle of cells on one sheet, such as `A1:C10`. Like a [Cell](SharpCell.Cell.md), it is a handle: creating one allocates no cells. ## Properties ### Address ```csharp public string Address { get; } ``` The A1 address without the sheet name: `A1:C10`, or `B2` for one cell. ### Cells ```csharp public IEnumerable Cells { get; } ``` The cells of the range that hold something (a constant, a formula or a value spilled into them), row by row and left to right. Only existing cells are visited, so `A:A` is cheap. Values and formulas of the cells may be changed while enumerating; adding or removing a cell of the sheet makes the next step throw `InvalidOperationException`. ### FirstColumn ```csharp public int FirstColumn { get; } ``` The first column, from 1 (A is 1). ### FirstRow ```csharp public int FirstRow { get; } ``` The first row, from 1. ### LastColumn ```csharp public int LastColumn { get; } ``` The last column, from 1. ### LastRow ```csharp public int LastRow { get; } ``` The last row, from 1. ### Worksheet ```csharp public Worksheet Worksheet { get; } ``` The sheet the range is on. ## Methods ### CopyTo(CellRange) ```csharp public CellRange CopyTo(CellRange destination) ``` Copies the range's cells to another range, as copying and pasting them in Excel does: values, formulas, number formats, styles, hyperlinks, notes, data validation and conditional formats. A formula's relative references keep their distance from its cell, so `=A1*2` in B1 becomes `=A2*2` in B2, and a reference that would point past the sheet's edge becomes `#REF!`. Empty cells of the range clear the cells they are copied to. When the destination is a whole number of times as tall and as wide as the range, the range repeats to fill it; otherwise it is copied once, with its top-left cell at the destination's. The cells are read before any is written, so the ranges may overlap. The destination can be in another workbook: formulas then refer to that workbook's sheets and names. A cell filled by a formula's spill is copied as its value, unless that formula is copied too and spills again. Data validation formulas keep their distance from each cell as cell formulas do. Merged cells, column widths and row heights are not copied. Every formula reading the cells written is calculated again at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Parameters** - `destination`: Where to copy the cells to. **Returns:** The cells written to. **Exceptions** - `ArgumentException`: The copy would not fit on the sheet. - `InvalidOperationException`: A cell written to is part of an array formula other than its top-left cell. - `NotSupportedException`: The range holds part of an array formula (Ctrl+Shift+Enter), or a formula SharpCell cannot read (such as a link to another workbook), in a cell or a data validation rule, whose references could not be moved. ### Delete(DeleteShift) ```csharp public void Delete(DeleteShift shift) ``` Deletes the range's cells, as Excel's Delete with "Shift cells up" or "Shift cells left" does: the cells below (or to the right) in the range's columns (or rows) move into their place. A reference to a deleted cell becomes `#REF!`; otherwise references follow as with [CellRange.Insert](SharpCell.CellRange.md#insert-insertshift). A range of whole rows (or columns) deletes them. **Parameters** - `shift`: Which cells take the place of the deleted ones. **Exceptions** - `ArgumentException`: `shift` is not a defined value. - `InvalidOperationException`: The cells that move would split a merged area, a table or an array formula, or a table would lose its header, totals or data rows. - `NotSupportedException`: A formula on the sheet, or one naming it, cannot be read by SharpCell; or some of a table's columns would be deleted. ### Equals(CellRange) ```csharp public bool Equals(CellRange? other) ``` Whether the other range is on the same sheet with the same corners. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is a range on the same sheet with the same corners. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [CellRange.Equals](SharpCell.CellRange.md#equals-cellrange). ### GetValues() ```csharp public CellValue[,] GetValues() ``` The value of every cell, empty ones included, indexed `[row, column]` from the range's top-left cell. A whole column allocates an element for each of its 1,048,576 cells; use [Worksheet.UsedRange](SharpCell.Worksheet.md#usedrange) or [CellRange.Cells](SharpCell.CellRange.md#cells) for sparse data. **Exceptions** - `InvalidOperationException`: The range has more cells than an array can hold. ### Insert(InsertShift) ```csharp public void Insert(InsertShift shift) ``` Inserts empty cells in place of the range, as Excel's Insert with "Shift cells down" or "Shift cells right" does: only the cells in the range's columns (or rows) move, along with their styles, number formats, hyperlinks, notes, data validation, merged areas and tables, and every reference to them follows. A reference to a range that only some of the moved cells are in stays as it was, as in Excel; data validation over such a range is split. A range of whole rows (or whole columns) inserts rows (or columns), as [Worksheet.InsertRows](SharpCell.Worksheet.md#insertrows-int-int) does. Every formula is calculated again at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Parameters** - `shift`: Where the cells beside the range go. **Exceptions** - `ArgumentException`: `shift` is not a defined value. - `InvalidOperationException`: Content would be pushed off the sheet, or the cells that move would split a merged area, a table or an array formula. - `NotSupportedException`: A formula on the sheet, or one naming it, cannot be read by SharpCell, so its references could not follow; or cells would be inserted inside a table's columns. ### SetValues(CellValue[,]) ```csharp public void SetValues(CellValue[,] values) ``` Sets the value of every cell, as assigning [Cell.Value](SharpCell.Cell.md#value) would: formulas are replaced and [CellValue.Empty](SharpCell.CellValue.md#empty) clears a cell. Everything is checked before the first cell is written, so an exception leaves the sheet unchanged; a cell of an array formula other than its top-left cell is refused even when the range covers the whole array. **Parameters** - `values`: Values indexed `[row, column]`, exactly as large as the range. **Exceptions** - `ArgumentException`: The array's size differs from the range's, or it holds a Missing, array or lambda value. - `InvalidOperationException`: A cell is part of an array formula other than its top-left cell. ### ToString() ```csharp public override string ToString() ``` The address with the sheet name as a formula writes it, such as `Sheet1!A1:C10` or `'My Sheet'!B2`. --- # CellStyle ```csharp public sealed class CellStyle ``` Namespace `SharpCell` · package `SharpCell` How a cell looks in Excel: font, fill, borders, alignment and protection (locked, formula hidden). The number format is not part of it; see [Cell.NumberFormat](SharpCell.Cell.md#numberformat). Styles play no part in calculation. A record: styles compare by value, and `with` makes a changed copy, such as `style with { Fill = CellColor.FromRgb(0xFFFF00) }`. ## Constructors ### CellStyle() ```csharp public CellStyle() ``` A style like [CellStyle.Default](SharpCell.CellStyle.md#default); set properties in an initializer. ## Properties ### BottomBorder ```csharp public CellBorder? BottomBorder { get; init; } ``` The bottom edge of the border; null for none. ### Default ```csharp public static CellStyle Default { get; } ``` The style of a new workbook's cells: [CellFont.Default](SharpCell.CellFont.md#default), no fill, no borders, General alignment at the bottom. ### Fill ```csharp public CellColor? Fill { get; init; } ``` The colour the cell is filled with; null for none. Only solid fills are read from files. ### Font ```csharp public CellFont Font { get; init; } ``` The font. ### FormulaHidden ```csharp public bool FormulaHidden { get; init; } ``` Whether the cell's formula is hidden from the formula bar while its sheet is protected. ### HorizontalAlignment ```csharp public CellHorizontalAlignment HorizontalAlignment { get; init; } ``` Where the text sits across the cell. [CellHorizontalAlignment.General](SharpCell.CellHorizontalAlignment.md#general) is Excel's default: numbers right, text left, logical values and errors in the middle. ### Indent ```csharp public int Indent { get; init; } ``` The indent, in steps of the width of three characters of the default font; 0 to 250. **Exceptions** - `ArgumentOutOfRangeException`: The indent is outside 0 to 250. ### LeftBorder ```csharp public CellBorder? LeftBorder { get; init; } ``` The left edge of the border; null for none. ### Locked ```csharp public bool Locked { get; init; } ``` Whether the cell is locked: it cannot be changed while its sheet is protected (see [Worksheet.Protect](SharpCell.Worksheet.md#protect-string-sheetprotection)). Excel's default is true; set false for input cells. ### RightBorder ```csharp public CellBorder? RightBorder { get; init; } ``` The right edge of the border; null for none. ### TopBorder ```csharp public CellBorder? TopBorder { get; init; } ``` The top edge of the border; null for none. ### VerticalAlignment ```csharp public CellVerticalAlignment VerticalAlignment { get; init; } ``` Where the text sits from top to bottom of the cell; Excel's default is the bottom. ### WrapText ```csharp public bool WrapText { get; init; } ``` Whether text wraps onto more lines inside the cell. --- # CellUnderline ```csharp public enum CellUnderline ``` Namespace `SharpCell` · package `SharpCell` How a font underlines text. ## Values | Name | Value | Description | |---|---|---| | `None` | 0 | No underline. | | `Single` | 1 | One line under the characters. | | `Double` | 2 | Two lines under the characters. | | `SingleAccounting` | 3 | One line across the whole cell, lower than [CellUnderline.Single](SharpCell.CellUnderline.md#single). | | `DoubleAccounting` | 4 | Two lines across the whole cell, lower than [CellUnderline.Double](SharpCell.CellUnderline.md#double). | --- # CellValue ```csharp public readonly struct CellValue ``` Namespace `SharpCell` · package `SharpCell` A value produced by a cell or a formula. `default` is [CellValue.Empty](SharpCell.CellValue.md#empty). Equality is identity (same kind, same payload, ordinal text), not Excel's `=`. ## Properties ### Empty ```csharp public static CellValue Empty { get; } ``` An empty cell. ### False ```csharp public static CellValue False { get; } ``` The logical value FALSE. ### IsError ```csharp public bool IsError { get; } ``` Whether the value is an Excel error. ### Kind ```csharp public CellValueKind Kind { get; } ``` The kind of value; check it before calling one of the `As` methods. ### Missing ```csharp public static CellValue Missing { get; } ``` An omitted function argument, as in `IF(A1,,B1)`. ### True ```csharp public static CellValue True { get; } ``` The logical value TRUE. ## Methods ### Array(CellValue[,]) ```csharp public static CellValue Array(CellValue[,] values) ``` A rectangular array. The array is taken by reference and must not be mutated afterwards. Excel has no empty arrays, so both dimensions must be at least 1. ### AsArray() ```csharp public CellValue[,] AsArray() ``` The array, rows first. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Array](SharpCell.CellValueKind.md#array). ### AsBoolean() ```csharp public bool AsBoolean() ``` The logical value. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Boolean](SharpCell.CellValueKind.md#boolean). ### AsDateTime(DateSystem) ```csharp public DateTime AsDateTime(DateSystem system) ``` The number read as a date and time: Excel stores dates as serial days counted from the workbook's `system` ([Workbook.DateSystem](SharpCell.Workbook.md#datesystem)), the time of day as the fraction, here rounded to the millisecond. The result's kind is `DateTimeKind.Unspecified`, as Excel knows no time zones. **Exceptions** - `InvalidOperationException`: [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Number](SharpCell.CellValueKind.md#number). - `ArgumentOutOfRangeException`: The number is no date: negative, past 9999-12-31, or 60 in the 1900 system, which Excel shows as the non-existent 1900-02-29. ### AsError() ```csharp public ErrorKind AsError() ``` The error. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Error](SharpCell.CellValueKind.md#error). ### AsLambda() ```csharp public LambdaValue AsLambda() ``` The function value. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Lambda](SharpCell.CellValueKind.md#lambda). ### AsNumber() ```csharp public double AsNumber() ``` The number. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Number](SharpCell.CellValueKind.md#number). ### AsText() ```csharp public string AsText() ``` The text. Throws `InvalidOperationException` when [CellValue.Kind](SharpCell.CellValue.md#kind) is not [CellValueKind.Text](SharpCell.CellValueKind.md#text). ### Boolean(bool) ```csharp public static CellValue Boolean(bool value) ``` A logical value. ### DateTime(DateTime, DateSystem) ```csharp public static CellValue DateTime(DateTime value, DateSystem system) ``` A date and time as the serial number Excel stores: days counted from the workbook's `system` ([Workbook.DateSystem](SharpCell.Workbook.md#datesystem)), the time of day as the fraction. **Exceptions** - `ArgumentOutOfRangeException`: The date is before the date system starts: 1899-12-31 in the 1900 system, 1904-01-01 in the 1904 system. ### Equals(CellValue) ```csharp public bool Equals(CellValue other) ``` Whether both values have the same kind and content; arrays are compared element by element. This is not Excel's `=`, which ignores case. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is a [CellValue](SharpCell.CellValue.md) equal to this one. ### Error(ErrorKind) ```csharp public static CellValue Error(ErrorKind kind) ``` An error value. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [CellValue.Equals](SharpCell.CellValue.md#equals-cellvalue). ### Lambda(LambdaValue) ```csharp public static CellValue Lambda(LambdaValue value) ``` A function value. ### Number(double) ```csharp public static CellValue Number(double value) ``` A number. Excel has neither NaN, infinities nor negative zero: non-finite input becomes `#NUM!` and `-0` becomes `0`. ### Text(string) ```csharp public static CellValue Text(string value) ``` A text value. ### ToString() ```csharp public override string ToString() ``` Formats the value the way it would be written as a formula literal. ## Operators ### implicit operator CellValue(bool) ```csharp public static implicit operator CellValue(bool value) ``` A logical value, so `cell.Value = true` works. ### implicit operator CellValue(double) ```csharp public static implicit operator CellValue(double value) ``` A number, so `cell.Value = 42` works. ### implicit operator CellValue(string) ```csharp public static implicit operator CellValue(string value) ``` A text value, so `cell.Value = "text"` works. ### operator !=(CellValue, CellValue) ```csharp public static bool operator !=(CellValue left, CellValue right) ``` Whether the values differ, as [CellValue.Equals](SharpCell.CellValue.md#equals-cellvalue). ### operator ==(CellValue, CellValue) ```csharp public static bool operator ==(CellValue left, CellValue right) ``` Whether the values are equal, as [CellValue.Equals](SharpCell.CellValue.md#equals-cellvalue). --- # CellValueKind ```csharp public enum CellValueKind ``` Namespace `SharpCell` · package `SharpCell` The kind of value a [CellValue](SharpCell.CellValue.md) holds. ## Values | Name | Value | Description | |---|---|---| | `Empty` | 0 | An empty cell. Reads as 0, "" or FALSE depending on what the formula expects. | | `Missing` | 1 | An argument left out of a function call, as in `IF(A1,,B1)`. | | `Number` | 2 | A number. Dates and times are numbers too: serial days since the workbook's date system epoch. | | `Text` | 3 | Text. | | `Boolean` | 4 | TRUE or FALSE. | | `Error` | 5 | An Excel error value such as `#DIV/0!`; errors are values, not exceptions. | | `Array` | 6 | A rectangular array, the result of a dynamic array formula or an array constant. | | `Lambda` | 7 | A function value created by `LAMBDA`. | --- # CellVerticalAlignment ```csharp public enum CellVerticalAlignment ``` Namespace `SharpCell` · package `SharpCell` Where a cell's text sits from top to bottom of the cell. ## Values | Name | Value | Description | |---|---|---| | `Top` | 0 | Against the top edge. | | `Center` | 1 | In the middle. | | `Bottom` | 2 | Against the bottom edge, Excel's default. | | `Justify` | 3 | Lines spread to the top and bottom edges. | | `Distributed` | 4 | Lines spread evenly from top to bottom. | --- # ConditionalFormat ```csharp public sealed class ConditionalFormat ``` Namespace `SharpCell` · package `SharpCell` A conditional format: which cells of its ranges it formats, and how. Make one with a factory such as [ConditionalFormat.CellValue](SharpCell.ConditionalFormat.md#cellvalue-conditionaloperator-string-conditionalstyle-string), [ConditionalFormat.Expression](SharpCell.ConditionalFormat.md#expression-string-conditionalstyle) or [ConditionalFormat.Top](SharpCell.ConditionalFormat.md#top-int-conditionalstyle-bool), change it with `with` (`rule with { StopIfTrue = true }`), and add it with [Worksheet.AddConditionalFormat](SharpCell.Worksheet.md#addconditionalformat-string-conditionalformat). SharpCell works out which cells a rule applies to ([Cell.ConditionalFormats](SharpCell.Cell.md#conditionalformats)) and how a cell then looks ([Cell.DisplayStyle](SharpCell.Cell.md#displaystyle)). ## Properties ### Formula1 ```csharp public string? Formula1 { get; } ``` The formula of a [ConditionalFormatType.CellValue](SharpCell.ConditionalFormatType.md#cellvalue) or [ConditionalFormatType.Expression](SharpCell.ConditionalFormatType.md#expression) rule, starting with `=`, relative to the first cell of the rule's first range; null for other rules. ### Formula2 ```csharp public string? Formula2 { get; } ``` The second formula of a between rule; null for others. ### Operator ```csharp public ConditionalOperator Operator { get; } ``` How a [ConditionalFormatType.CellValue](SharpCell.ConditionalFormatType.md#cellvalue) rule compares; [ConditionalOperator.Between](SharpCell.ConditionalOperator.md#between) for other rules. ### OrEqual ```csharp public bool OrEqual { get; } ``` Whether an above or below average rule also formats values equal to the average. ### Percent ```csharp public bool Percent { get; } ``` Whether [ConditionalFormat.Rank](SharpCell.ConditionalFormat.md#rank) is a percentage. ### Period ```csharp public TimePeriod Period { get; } ``` The period of a [ConditionalFormatType.TimePeriod](SharpCell.ConditionalFormatType.md#timeperiod) rule. ### Rank ```csharp public int Rank { get; } ``` How many values (or what percentage of them) a top or bottom rule formats; 0 for others. ### StandardDeviations ```csharp public int StandardDeviations { get; } ``` How many standard deviations above or below the average a value must be; 0 for the average itself. ### StopIfTrue ```csharp public bool StopIfTrue { get; init; } ``` Whether lower rules are skipped for a cell this rule applies to, as Excel's Stop If True does. ### Style ```csharp public ConditionalStyle? Style { get; init; } ``` How matching cells look; null for colour scales, data bars and icon sets, which colour each cell themselves. ### Text ```csharp public string? Text { get; } ``` The text a text rule looks for; null for others. ### Type ```csharp public ConditionalFormatType Type { get; } ``` What the rule looks at. ## Methods ### AboveAverage(ConditionalStyle, bool, int) ```csharp public static ConditionalFormat AboveAverage(ConditionalStyle style, bool orEqual = false, int standardDeviations = 0) ``` Numbers above the average of the ranges, or by `standardDeviations` standard deviations above it. **Exceptions** - `ArgumentOutOfRangeException`: The standard deviations are outside 0 to 3, as in Excel. ### BeginsWith(string, ConditionalStyle) ```csharp public static ConditionalFormat BeginsWith(string text, ConditionalStyle style) ``` Cells whose text begins with `text`, ignoring case. **Exceptions** - `ArgumentException`: The text is empty. ### BelowAverage(ConditionalStyle, bool, int) ```csharp public static ConditionalFormat BelowAverage(ConditionalStyle style, bool orEqual = false, int standardDeviations = 0) ``` Numbers below the average of the ranges, or by `standardDeviations` standard deviations below it. **Exceptions** - `ArgumentOutOfRangeException`: The standard deviations are outside 0 to 3, as in Excel. ### Blanks(ConditionalStyle) ```csharp public static ConditionalFormat Blanks(ConditionalStyle style) ``` Empty cells and cells holding only spaces. ### Bottom(int, ConditionalStyle, bool) ```csharp public static ConditionalFormat Bottom(int rank, ConditionalStyle style, bool percent = false) ``` The `rank` lowest numbers of the ranges, or the lowest `rank` percent of them. **Exceptions** - `ArgumentOutOfRangeException`: The rank is outside 1 to 1000, or a percentage outside 1 to 100. ### CellValue(ConditionalOperator, string, ConditionalStyle, string) ```csharp public static ConditionalFormat CellValue(ConditionalOperator comparison, string formula1, ConditionalStyle style, string? formula2 = null) ``` Cells whose value compares with the formulas, such as greater than `=100` or between `=$B$1` and `=$B$2`. **Parameters** - `comparison`: How the value compares. - `formula1`: The formula compared with, starting with `=`, relative to the range's first cell. - `style`: How matching cells look. - `formula2`: The second formula, for [ConditionalOperator.Between](SharpCell.ConditionalOperator.md#between) and [ConditionalOperator.NotBetween](SharpCell.ConditionalOperator.md#notbetween). **Exceptions** - `ArgumentException`: A formula is missing or empty, or a second formula is given to a comparison that takes one. ### ContainsText(string, ConditionalStyle) ```csharp public static ConditionalFormat ContainsText(string text, ConditionalStyle style) ``` Cells whose text contains `text`, ignoring case. **Exceptions** - `ArgumentException`: The text is empty. ### DateOccurring(TimePeriod, ConditionalStyle) ```csharp public static ConditionalFormat DateOccurring(TimePeriod period, ConditionalStyle style) ``` Dates in a period around today, as Excel's A Date Occurring. **Exceptions** - `ArgumentOutOfRangeException`: The period is not a defined value. ### DuplicateValues(ConditionalStyle) ```csharp public static ConditionalFormat DuplicateValues(ConditionalStyle style) ``` Values that appear more than once in the ranges; text ignoring case. ### EndsWith(string, ConditionalStyle) ```csharp public static ConditionalFormat EndsWith(string text, ConditionalStyle style) ``` Cells whose text ends with `text`, ignoring case. **Exceptions** - `ArgumentException`: The text is empty. ### Equals(ConditionalFormat) ```csharp public bool Equals(ConditionalFormat? other) ``` Whether the other rule has the same settings and style; texts compared ordinally. ### Errors(ConditionalStyle) ```csharp public static ConditionalFormat Errors(ConditionalStyle style) ``` Cells holding an error. ### Expression(string, ConditionalStyle) ```csharp public static ConditionalFormat Expression(string formula, ConditionalStyle style) ``` Cells for which a formula is true, such as `=$C2>$B2`, written for the range's first cell. **Parameters** - `formula`: The formula, starting with `=`. - `style`: How matching cells look. **Exceptions** - `ArgumentException`: The formula is empty. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [ConditionalFormat.Equals](SharpCell.ConditionalFormat.md#equals-conditionalformat). ### NoBlanks(ConditionalStyle) ```csharp public static ConditionalFormat NoBlanks(ConditionalStyle style) ``` Cells that are not blank. ### NoErrors(ConditionalStyle) ```csharp public static ConditionalFormat NoErrors(ConditionalStyle style) ``` Cells not holding an error. ### NotContainsText(string, ConditionalStyle) ```csharp public static ConditionalFormat NotContainsText(string text, ConditionalStyle style) ``` Cells whose text does not contain `text`. **Exceptions** - `ArgumentException`: The text is empty. ### ToString() ```csharp public override string ToString() ``` The type, and the formula or text when the rule has one. ### Top(int, ConditionalStyle, bool) ```csharp public static ConditionalFormat Top(int rank, ConditionalStyle style, bool percent = false) ``` The `rank` highest numbers of the ranges, or the highest `rank` percent of them. **Exceptions** - `ArgumentOutOfRangeException`: The rank is outside 1 to 1000, or a percentage outside 1 to 100. ### UniqueValues(ConditionalStyle) ```csharp public static ConditionalFormat UniqueValues(ConditionalStyle style) ``` Values that appear once in the ranges. --- # ConditionalFormatType ```csharp public enum ConditionalFormatType ``` Namespace `SharpCell` · package `SharpCell` What a conditional format looks at, as Excel's New Formatting Rule dialog offers it. ## Values | Name | Value | Description | |---|---|---| | `CellValue` | 0 | The cell's value compared with formulas: Highlight Cells Rules > Greater Than and the others. | | `Expression` | 1 | Cells for which a formula is true: Use a formula to determine which cells to format. | | `Top` | 2 | The highest values of the ranges: Top/Bottom Rules > Top 10 Items or Top 10%. | | `Bottom` | 3 | The lowest values of the ranges. | | `AboveAverage` | 4 | Values above the average of the ranges. | | `BelowAverage` | 5 | Values below the average of the ranges. | | `DuplicateValues` | 6 | Values that appear more than once in the ranges. | | `UniqueValues` | 7 | Values that appear once in the ranges. | | `ContainsText` | 8 | Text containing [ConditionalFormat.Text](SharpCell.ConditionalFormat.md#text), ignoring case. | | `NotContainsText` | 9 | Text not containing [ConditionalFormat.Text](SharpCell.ConditionalFormat.md#text). | | `BeginsWith` | 10 | Text beginning with [ConditionalFormat.Text](SharpCell.ConditionalFormat.md#text). | | `EndsWith` | 11 | Text ending with [ConditionalFormat.Text](SharpCell.ConditionalFormat.md#text). | | `Blanks` | 12 | Empty cells, or cells holding only spaces. | | `NoBlanks` | 13 | Cells that are not blank. | | `Errors` | 14 | Cells holding an error. | | `NoErrors` | 15 | Cells not holding an error. | | `TimePeriod` | 16 | Dates in a period around today: A Date Occurring. | | `ColorScale` | 17 | A colour scale, read from a file; every number gets a colour of its own, which SharpCell does not work out. | | `DataBar` | 18 | Data bars, read from a file. | | `IconSet` | 19 | An icon set, read from a file. | --- # ConditionalOperator ```csharp public enum ConditionalOperator ``` Namespace `SharpCell` · package `SharpCell` How a [ConditionalFormatType.CellValue](SharpCell.ConditionalFormatType.md#cellvalue) rule compares the cell's value with its formulas. ## Values | Name | Value | Description | |---|---|---| | `Between` | 0 | From the first formula to the second, both included. | | `NotBetween` | 1 | Less than the first formula or greater than the second. | | `Equal` | 2 | Equal to the first formula; text ignoring case. | | `NotEqual` | 3 | Not equal to the first formula. | | `GreaterThan` | 4 | Greater than the first formula. | | `LessThan` | 5 | Less than the first formula. | | `GreaterThanOrEqual` | 6 | Greater than or equal to the first formula. | | `LessThanOrEqual` | 7 | Less than or equal to the first formula. | --- # ConditionalStyle ```csharp public sealed class ConditionalStyle ``` Namespace `SharpCell` · package `SharpCell` What a conditional format changes about a cell's look, as Excel's Format Cells for a rule sets it: the font's style and colour, the fill, the borders and the number format. A property left null keeps what the cell has. A record: `with` makes a changed copy. ## Constructors ### ConditionalStyle() ```csharp public ConditionalStyle() ``` A look that changes nothing; set properties in an initializer. ## Properties ### Bold ```csharp public bool? Bold { get; init; } ``` Bold, or not bold; null keeps the cell's. ### BottomBorder ```csharp public CellBorder? BottomBorder { get; init; } ``` The bottom border; null keeps the cell's. ### Fill ```csharp public CellColor? Fill { get; init; } ``` The fill colour; null keeps the cell's. ### FontColor ```csharp public CellColor? FontColor { get; init; } ``` The text colour; null keeps the cell's. ### GreenFill ```csharp public static ConditionalStyle GreenFill { get; } ``` A green fill with dark green text. ### Italic ```csharp public bool? Italic { get; init; } ``` Italic, or not italic; null keeps the cell's. ### LeftBorder ```csharp public CellBorder? LeftBorder { get; init; } ``` The left border; null keeps the cell's. ### LightRedFill ```csharp public static ConditionalStyle LightRedFill { get; } ``` A light red fill with dark red text, Excel's first preset. ### NumberFormat ```csharp public string? NumberFormat { get; init; } ``` The number format code, such as `0.00%`; null keeps the cell's. ### RightBorder ```csharp public CellBorder? RightBorder { get; init; } ``` The right border; null keeps the cell's. ### Strikethrough ```csharp public bool? Strikethrough { get; init; } ``` Struck through, or not; null keeps the cell's. ### TopBorder ```csharp public CellBorder? TopBorder { get; init; } ``` The top border; null keeps the cell's. ### Underline ```csharp public CellUnderline? Underline { get; init; } ``` The underline; null keeps the cell's. ### YellowFill ```csharp public static ConditionalStyle YellowFill { get; } ``` A yellow fill with dark yellow text. ## Methods ### ApplyTo(CellStyle) ```csharp public CellStyle ApplyTo(CellStyle style) ``` The style with what this one sets put over it. **Parameters** - `style`: The cell's own style. --- # DataValidation ```csharp public sealed class DataValidation ``` Namespace `SharpCell` · package `SharpCell` A data validation rule: what the cells of a range accept and the messages Excel shows for them. Set properties in an initializer, or start from [DataValidation.List](SharpCell.DataValidation.md#list-string), [DataValidation.ListFrom](SharpCell.DataValidation.md#listfrom-string) or [DataValidation.Custom](SharpCell.DataValidation.md#custom-string). SharpCell keeps the rule with the cells and saves it; it does not check values. ## Constructors ### DataValidation() ```csharp public DataValidation() ``` A rule allowing any value; set properties in an initializer. ### DataValidation(DataValidation) ```csharp public DataValidation(DataValidation source) ``` A copy of another rule, so a factory's rule can be changed in an initializer: `new DataValidation(DataValidation.List("Yes", "No")) { ErrorMessage = "Choose Yes or No." }`. **Parameters** - `source`: The rule to copy. **Exceptions** - `ArgumentNullException`: The source is null. ## Properties ### AllowBlank ```csharp public bool AllowBlank { get; init; } ``` Whether an empty cell is accepted; true unless set. ### ErrorMessage ```csharp public string? ErrorMessage { get; init; } ``` The error message, at most 255 characters, or null for Excel's own. **Exceptions** - `ArgumentException`: The message is longer than 255 characters. ### ErrorStyle ```csharp public ValidationErrorStyle ErrorStyle { get; init; } ``` What Excel does with a value that breaks the rule; [ValidationErrorStyle.Stop](SharpCell.ValidationErrorStyle.md#stop) unless set. ### ErrorTitle ```csharp public string? ErrorTitle { get; init; } ``` The title of the error message, at most 32 characters, or null for Excel's own. **Exceptions** - `ArgumentException`: The title is longer than 32 characters. ### Formula1 ```csharp public string? Formula1 { get; init; } ``` The first formula, with a leading `=` (added if missing) as [Cell.Formula](SharpCell.Cell.md#formula) takes it, or null: the low end or the value compared with, the list (`="Yes,No"` or `=$H$1:$H$5`) or the custom condition (`=A1>0`). Relative references are relative to the first cell of the rule's first range. It is parsed when the rule is added to a sheet. ### Formula2 ```csharp public string? Formula2 { get; init; } ``` The second formula, the high end for [ValidationOperator.Between](SharpCell.ValidationOperator.md#between) and [ValidationOperator.NotBetween](SharpCell.ValidationOperator.md#notbetween), written like [DataValidation.Formula1](SharpCell.DataValidation.md#formula1); null for none. ### InputMessage ```csharp public string? InputMessage { get; init; } ``` The input message, at most 255 characters, or null for none. **Exceptions** - `ArgumentException`: The message is longer than 255 characters. ### InputTitle ```csharp public string? InputTitle { get; init; } ``` The title of the input message, at most 32 characters, or null for none. **Exceptions** - `ArgumentException`: The title is longer than 32 characters. ### Operator ```csharp public ValidationOperator Operator { get; init; } ``` How a value is compared with [DataValidation.Formula1](SharpCell.DataValidation.md#formula1) and [DataValidation.Formula2](SharpCell.DataValidation.md#formula2); [ValidationOperator.Between](SharpCell.ValidationOperator.md#between) unless set. Lists and custom rules ignore it. ### ShowDropDown ```csharp public bool ShowDropDown { get; init; } ``` Whether Excel shows the drop-down of a list rule; true unless set. ### ShowErrorMessage ```csharp public bool ShowErrorMessage { get; init; } ``` Whether Excel shows the error message when a value breaks the rule; true unless set. ### ShowInputMessage ```csharp public bool ShowInputMessage { get; init; } ``` Whether Excel shows the input message when a cell of the rule is selected; true unless set. ### Type ```csharp public ValidationType Type { get; init; } ``` What the cells may hold; [ValidationType.Any](SharpCell.ValidationType.md#any) unless set. ## Methods ### Custom(string) ```csharp public static DataValidation Custom(string formula) ``` A rule accepting a value when the formula is true, such as `Custom("=A1>0")`. **Parameters** - `formula`: The condition, relative to the first cell of the rule's first range. **Exceptions** - `ArgumentException`: The formula is null or empty. ### Equals(DataValidation) ```csharp public bool Equals(DataValidation? other) ``` Whether the other rule has the same settings; texts are compared ordinally. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is a rule with the same settings. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [DataValidation.Equals](SharpCell.DataValidation.md#equals-datavalidation). ### List(string[]) ```csharp public static DataValidation List(params string[] items) ``` A drop-down list of the given items, kept in the rule as one quoted literal: `List("Yes", "No")` has the formula `="Yes,No"`. **Parameters** - `items`: The items, in order. An item cannot hold a comma, which separates them. **Exceptions** - `ArgumentNullException`: The items or one of them is null. - `ArgumentException`: There are no items, an item holds a comma, or the items with their commas are longer than 255 characters, Excel's limit. ### ListFrom(string) ```csharp public static DataValidation ListFrom(string range) ``` A drop-down list of the values of a range, such as `ListFrom("$H$1:$H$5")` or a defined name. **Parameters** - `range`: The range or name, with or without a leading `=`. **Exceptions** - `ArgumentException`: The range is null or empty. ### ToString() ```csharp public override string ToString() ``` The type, the operator of a comparing type, and the formulas, such as `WholeNumber Between =1 =10`. --- # DateSystem ```csharp public enum DateSystem ``` Namespace `SharpCell` · package `SharpCell` The workbook's date system: which day serial number 0 or 1 stands for. ## Values | Name | Value | Description | |---|---|---| | `Date1900` | 0 | Serial 1 is 1900-01-01 and the non-existent 1900-02-29 is serial 60, as in Excel for Windows. | | `Date1904` | 1 | Serial 0 is 1904-01-01, as in old Excel for Mac. | --- # DefinedName ```csharp public sealed class DefinedName ``` Namespace `SharpCell` · package `SharpCell` A defined name as [Workbook.DefinedNames](SharpCell.Workbook.md#definednames) lists it. ## Properties ### Formula ```csharp public string Formula { get; } ``` The formula the name stands for, starting with `=`, such as `=Sheet1!$A$1:$A$10`. ### Name ```csharp public string Name { get; } ``` The name as it was defined; formulas use it ignoring case. ### Scope ```csharp public Worksheet? Scope { get; } ``` The sheet the name is visible on, or null for a name of the whole workbook. ## Methods ### ToString() ```csharp public override string ToString() ``` The name. --- # DeleteShift ```csharp public enum DeleteShift ``` Namespace `SharpCell` · package `SharpCell` Which cells take the place of deleted cells, as in Excel's Delete dialog. ## Values | Name | Value | Description | |---|---|---| | `Up` | 0 | The cells below move up: "Shift cells up". | | `Left` | 1 | The cells to the right move left: "Shift cells left". | --- # DiagnosticKind ```csharp public enum DiagnosticKind ``` Namespace `SharpCell` · package `SharpCell` What a [CalculationDiagnostic](SharpCell.CalculationDiagnostic.md) reports. ## Values | Name | Value | Description | |---|---|---| | `CircularReference` | 0 | Cells reference each other in a loop; every cell in the loop got 0. | | `FunctionFailure` | 1 | A function failed unexpectedly; the cell got `#VALUE!`. | | `UnsupportedFormula` | 2 | A formula uses something SharpCell cannot evaluate, such as a function it does not know or (in a file) a link to another workbook; the cell got `#NAME?`. | | `LimitExceeded` | 3 | A result was larger than a limit allows, such as the cells all spills of a workbook may cover together; the cell got an error. | | `CustomFunctionFailure` | 4 | A function added through [Workbook.Functions](SharpCell.Workbook.md#functions) threw an exception; the cell got `#VALUE!`. The message names the function and the exception. | | `IterationLimitReached` | 5 | With [Workbook.Iteration](SharpCell.Workbook.md#iteration) enabled, a circular reference did not settle within [IterationSettings.MaxIterations](SharpCell.IterationSettings.md#maxiterations) passes; its cells keep their last values. | --- # ErrorKind ```csharp public enum ErrorKind ``` Namespace `SharpCell` · package `SharpCell` Excel error values. Numeric values match the codes returned by `ERROR.TYPE`. ## Values | Name | Value | Description | |---|---|---| | `Null` | 1 | `#NULL!`: the intersection of two ranges is empty. | | `Div0` | 2 | `#DIV/0!`: division by zero. | | `Value` | 3 | `#VALUE!`: an argument has the wrong type. | | `Ref` | 4 | `#REF!`: a reference is not valid. | | `Name` | 5 | `#NAME?`: an unknown function or name. | | `Num` | 6 | `#NUM!`: a number is out of range or a calculation does not converge. | | `NA` | 7 | `#N/A`: a value is not available, such as a lookup that found nothing. | | `Spill` | 9 | `#SPILL!`: a dynamic array result cannot spill because cells are in the way. | | `Calc` | 14 | `#CALC!`: the calculation cannot produce a result, such as an empty array. | --- # ErrorKinds ```csharp public static class ErrorKinds ``` Namespace `SharpCell` · package `SharpCell` Conversion between [ErrorKind](SharpCell.ErrorKind.md) and its literal text (`#DIV/0!`). ## Methods ### ToText(ErrorKind) ```csharp public static string ToText(this ErrorKind kind) ``` The literal text of the error, such as `#DIV/0!`. ### TryParse(ReadOnlySpan, ErrorKind) ```csharp public static bool TryParse(ReadOnlySpan text, out ErrorKind kind) ``` Parses an error literal, ignoring case. The whole span must be the literal. --- # FormulaParseException ```csharp public sealed class FormulaParseException : Exception ``` Namespace `SharpCell` · package `SharpCell` Thrown when formula text cannot be parsed. This is the only exception the parser throws. ## Constructors ### FormulaParseException(string, int) ```csharp public FormulaParseException(string message, int position) ``` Creates the exception for a formula that cannot be parsed. **Parameters** - `message`: What is wrong. - `position`: The zero-based character position where parsing failed. ## Properties ### Position ```csharp public int Position { get; } ``` Zero-based index in the formula text where the problem was found. --- # FunctionArguments ```csharp public sealed class FunctionArguments ``` Namespace `SharpCell` · package `SharpCell` The arguments of one call of a function added through [Workbook.Functions](SharpCell.Workbook.md#functions). Valid only while the function runs. ## Properties ### Caller ```csharp public Cell? Caller { get; } ``` The cell whose formula makes the call; null when the formula was given to [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string). ### CancellationToken ```csharp public CancellationToken CancellationToken { get; } ``` The token passed to [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) or [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string-cancellationtoken), for a slow function to stop early: an `OperationCanceledException` it throws once the token is cancelled cancels the calculation. ### Count ```csharp public int Count { get; } ``` How many arguments the call has, omitted ones included. ### Culture ```csharp public CultureInfo Culture { get; } ``` The workbook's culture, as used for converting between text and numbers. ### DateSystem ```csharp public DateSystem DateSystem { get; } ``` The workbook's date system, for reading date arguments with [CellValue.AsDateTime](SharpCell.CellValue.md#asdatetime-datesystem). ### this[int] ```csharp public CellValue this[int index] { get; } ``` The argument's value: a single cell's value, a range as an [CellValueKind.Array](SharpCell.CellValueKind.md#array), an error as an error value, and [CellValue.Missing](SharpCell.CellValue.md#missing) for an argument left out, as the middle one of `F(1,,2)`. An array is a copy the function may change. **Exceptions** - `ArgumentOutOfRangeException`: There is no such argument. ## Methods ### Boolean(int) ```csharp public CellValue Boolean(int index) ``` The argument converted to a logical value as Excel converts it: TRUE or FALSE, or the error to return. ### IsMissing(int) ```csharp public bool IsMissing(int index) ``` Whether the argument was left out, as the middle one of `F(1,,2)`. ### Number(int) ```csharp public CellValue Number(int index) ``` The argument converted to a number as Excel converts it (text such as `"2"` included; an omitted argument is 0): a number, or the error to return, such as `#VALUE!` for `"abc"`. ### Text(int) ```csharp public CellValue Text(int index) ``` The argument converted to text as Excel converts it: text, or the error to return. --- # FunctionCollection ```csharp public sealed class FunctionCollection ``` Namespace `SharpCell` · package `SharpCell` The functions a workbook's formulas can call besides Excel's: add one with [FunctionCollection.Add](SharpCell.FunctionCollection.md#add-string-func-functionarguments-cellvalue-functionoptions), and formulas calling it by name (ignoring case) use it from the next calculation on. ## Properties ### Names ```csharp public IReadOnlyCollection Names { get; } ``` The names of the added functions, as they were added. ## Methods ### Add(string, Func, FunctionOptions) ```csharp public void Add(string name, Func body, FunctionOptions? options = null) ``` Adds a function. Formulas that call the name, and were `#NAME?` so far, are calculated again by the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). Calls written `_xll.NAME(...)` or `_xludf.NAME(...)`, as Excel stores calls of add-in functions in files, find it too. **Parameters** - `name`: Letters, digits, `.` and `_`, starting with a letter or `_`; not the name of a function SharpCell implements. A name Excel knows but SharpCell does not implement, such as `WEBSERVICE`, is allowed. - `body`: Calculates the result from the arguments. It must not change the workbook, and it should read cells only through its arguments: cells it reads otherwise are not dependencies, so it is not calculated again when they change. An exception it throws makes the cell `#VALUE!` with a [DiagnosticKind.CustomFunctionFailure](SharpCell.DiagnosticKind.md#customfunctionfailure) diagnostic. - `options`: Argument counts, which parameters are scalar, and whether the function is volatile; null for any number of arguments passed as they are. **Exceptions** - `ArgumentException`: The name is not a function name, is built in, or was added already; or the options are not consistent. ### Contains(string) ```csharp public bool Contains(string name) ``` Whether a function of that name was added, ignoring case. ### Remove(string) ```csharp public bool Remove(string name) ``` Removes a function. Formulas calling it become `#NAME?` at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Returns:** Whether a function of that name was there. --- # FunctionOptions ```csharp public sealed class FunctionOptions ``` Namespace `SharpCell` · package `SharpCell` How a function added through [Workbook.Functions](SharpCell.Workbook.md#functions) takes its arguments. ## Constructors ### FunctionOptions() ```csharp public FunctionOptions() ``` Options for any number of arguments, all passed as they are, and a function that is not volatile. ## Properties ### IsVolatile ```csharp public bool IsVolatile { get; init; } ``` Whether the function is calculated on every [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken), like `NOW`, instead of only when its arguments change. ### MaxArguments ```csharp public int MaxArguments { get; init; } ``` The most arguments a call may have, up to 255 as in Excel; more make the call `#VALUE!`. Default 255. ### MinArguments ```csharp public int MinArguments { get; init; } ``` The fewest arguments a call may have; fewer make the call `#VALUE!`. Default 0. ### ScalarParameters ```csharp public IReadOnlyList ScalarParameters { get; init; } ``` The 0-based positions of scalar parameters. A scalar parameter receives one value: given a range or an array, the function is called once per element and the results spill, as with Excel's own functions such as `LEN(A1:A10)`. Other parameters receive the argument as it is: a range as an array of its values. --- # Hyperlink ```csharp public sealed class Hyperlink ``` Namespace `SharpCell` · package `SharpCell` A hyperlink on a cell: a web or e-mail address, a file, or a place in the workbook. The cell's value is what Excel shows; the link neither changes it nor applies the Hyperlink cell style. ## Constructors ### Hyperlink(string, string) ```csharp public Hyperlink(string target, string? tooltip = null) ``` A link to `target`, with an optional tooltip. **Parameters** - `target`: A web or e-mail address or a file (`https://example.com`, `mailto:a@example.com`, `report.pdf`), or a place in the workbook written as Excel's HYPERLINK function takes it: `#Sheet2!A1`, `#'Q1 Sales'!A1` or `#MyName`. - `tooltip`: Excel's ScreenTip, shown when the pointer rests on the cell; at most 255 characters, or null for none. **Exceptions** - `ArgumentNullException`: The target is null. - `ArgumentException`: The target is empty or only `#`, or the tooltip is longer than 255 characters. ## Properties ### IsInWorkbook ```csharp public bool IsInWorkbook { get; } ``` Whether the link goes to a place in this workbook, that is, the target starts with `#`. ### Target ```csharp public string Target { get; } ``` Where the link goes, as given to the constructor. An empty fragment is dropped when the link is saved: `https://example.com/#` reads back as `https://example.com/`. ### Tooltip ```csharp public string? Tooltip { get; } ``` The ScreenTip, or null for none. ## Methods ### Equals(Hyperlink) ```csharp public bool Equals(Hyperlink? other) ``` Whether the other link has the same target and tooltip, compared ordinally. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is the same link. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [Hyperlink.Equals](SharpCell.Hyperlink.md#equals-hyperlink). ### ToString() ```csharp public override string ToString() ``` The target. --- # InsertShift ```csharp public enum InsertShift ``` Namespace `SharpCell` · package `SharpCell` Where the cells beside inserted cells go, as in Excel's Insert dialog. ## Values | Name | Value | Description | |---|---|---| | `Down` | 0 | The cells below move down: "Shift cells down". | | `Right` | 1 | The cells to the right move right: "Shift cells right". | --- # IterationSettings ```csharp public sealed class IterationSettings ``` Namespace `SharpCell` · package `SharpCell` Iterative calculation, Excel's "Enable iterative calculation": circular references are calculated over and over until they settle, instead of giving 0. ## Constructors ### IterationSettings(bool, int, double) ```csharp public IterationSettings(bool Enabled = false, int MaxIterations = 100, double MaxChange = 0.001) ``` Settings for iterative calculation; the defaults are Excel's, with iteration off. **Parameters** - `Enabled`: Whether circular references are calculated iteratively. - `MaxIterations`: The most passes over a circular reference, 1 to 32,767. - `MaxChange`: A circular reference has settled when no value of it changed by more than this in a pass; 0 or more. **Exceptions** - `ArgumentOutOfRangeException`: A limit is outside what Excel allows. ## Properties ### Enabled ```csharp public bool Enabled { get; } ``` Whether circular references are calculated iteratively. ### MaxChange ```csharp public double MaxChange { get; } ``` A circular reference has settled when no value changed by more than this in a pass; 0.001 by default. ### MaxIterations ```csharp public int MaxIterations { get; } ``` The most passes over a circular reference; 100 by default. ## Methods ### Equals(IterationSettings) ```csharp public bool Equals(IterationSettings? other) ``` Whether the other settings are the same. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is the same settings. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [IterationSettings.Equals](SharpCell.IterationSettings.md#equals-iterationsettings). ### ToString() ```csharp public override string ToString() ``` The settings as text, such as `Enabled, 100 iterations, change 0.001`. --- # LambdaValue ```csharp public abstract class LambdaValue ``` Namespace `SharpCell` · package `SharpCell` A function value produced by `LAMBDA`. Instances are created only by the engine. --- # Note ```csharp public sealed class Note ``` Namespace `SharpCell` · package `SharpCell` A note on a cell: Excel's Insert Note (called a comment before Excel 365), shown in a box beside the cell, by default only while the pointer rests on it. Excel's threaded comments, with replies, are not notes; a file's own are kept when it is saved. ## Constructors ### Note(string, string) ```csharp public Note(string text, string? author = null) ``` A note with `text`, written by `author`. **Parameters** - `text`: The note's text. Excel puts the author's name and a colon on the first line of a note it makes; SharpCell writes only the text it is given. - `author`: Who wrote the note, or null for nobody. Excel shows it in the status bar while the pointer rests on the cell. **Exceptions** - `ArgumentNullException`: The text is null. - `ArgumentException`: The text is longer than 32,767 characters, as Excel allows. ## Properties ### Author ```csharp public string? Author { get; } ``` Who wrote the note, or null for nobody. ### IsVisible ```csharp public bool IsVisible { get; init; } ``` Whether the note is always shown, as Excel's Show/Hide Note makes it; otherwise it is shown only while the pointer rests on its cell. ### Text ```csharp public string Text { get; } ``` The text, with `\n` between lines. ## Methods ### Equals(Note) ```csharp public bool Equals(Note? other) ``` Whether the other note has the same text, author and visibility, compared ordinally. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is the same note. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [Note.Equals](SharpCell.Note.md#equals-note). ### ToString() ```csharp public override string ToString() ``` The text. --- # PageMargins ```csharp public sealed class PageMargins ``` Namespace `SharpCell` · package `SharpCell` The margins of a printed sheet in inches, as in Excel's Page Setup > Margins: the space around the cells, and how far the header and footer are from the top and bottom edges. ## Constructors ### PageMargins(double, double, double, double, double, double) ```csharp public PageMargins(double left, double right, double top, double bottom, double header, double footer) ``` Margins of the given sizes in inches. **Exceptions** - `ArgumentOutOfRangeException`: A margin is negative, not a number, or 49 inches or more. ## Properties ### Bottom ```csharp public double Bottom { get; init; } ``` The bottom margin in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Footer ```csharp public double Footer { get; init; } ``` How far the footer is from the bottom edge, in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Header ```csharp public double Header { get; init; } ``` How far the header is from the top edge, in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Left ```csharp public double Left { get; init; } ``` The left margin in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Narrow ```csharp public static PageMargins Narrow { get; } ``` Excel's Narrow margins: 0.25 inches left and right, 0.75 top and bottom, 0.3 for the header and footer. ### Normal ```csharp public static PageMargins Normal { get; } ``` Excel's Normal margins, which a sheet has unless set otherwise: 0.7 inches left and right, 0.75 top and bottom, 0.3 for the header and footer. ### Right ```csharp public double Right { get; init; } ``` The right margin in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Top ```csharp public double Top { get; init; } ``` The top margin in inches. **Exceptions** - `ArgumentOutOfRangeException`: The value is negative, not a number, or 49 inches or more. ### Wide ```csharp public static PageMargins Wide { get; } ``` Excel's Wide margins: an inch on every side, 0.5 inches for the header and footer. --- # PageOrientation ```csharp public enum PageOrientation ``` Namespace `SharpCell` · package `SharpCell` How a sheet is turned on the printed page, as in Excel's Page Layout > Orientation. ## Values | Name | Value | Description | |---|---|---| | `Portrait` | 0 | Taller than wide; Excel's default. | | `Landscape` | 1 | Wider than tall. | --- # RecalculationResult ```csharp public sealed class RecalculationResult ``` Namespace `SharpCell` · package `SharpCell` What one [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) did. ## Properties ### ChangedCells ```csharp public IReadOnlyList ChangedCells { get; } ``` The cells whose values calculation changed since the previous [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) (or since the workbook was loaded or cloned): formula cells, and cells a spill filled, changed or left (those are now empty). Cells you changed yourself are not included, nor cells that changed and changed back. In the order of their sheets in the workbook, then by row and column. --- # SheetProtection ```csharp public sealed class SheetProtection ``` Namespace `SharpCell` · package `SharpCell` What users may still do on a protected sheet, as Excel's Protect Sheet dialog lists it: by default they may select cells and nothing else. Set properties in an initializer and pass the object to [Worksheet.Protect](SharpCell.Worksheet.md#protect-string-sheetprotection). Excel enforces the protection; SharpCell only keeps and saves it. ## Constructors ### SheetProtection() ```csharp public SheetProtection() ``` A protection allowing users to select cells and nothing else; set properties in an initializer. ### SheetProtection(SheetProtection) ```csharp public SheetProtection(SheetProtection source) ``` A copy of another protection, so it can be changed in an initializer: `new SheetProtection(sheet.Protection) { Sort = true }`. **Parameters** - `source`: The protection to copy. **Exceptions** - `ArgumentNullException`: The source is null. ## Properties ### AutoFilter ```csharp public bool AutoFilter { get; init; } ``` Whether users may use AutoFilter; false unless set. ### DeleteColumns ```csharp public bool DeleteColumns { get; init; } ``` Whether users may delete columns; false unless set. ### DeleteRows ```csharp public bool DeleteRows { get; init; } ``` Whether users may delete rows; false unless set. ### EditObjects ```csharp public bool EditObjects { get; init; } ``` Whether users may edit objects such as shapes and charts; false unless set. ### EditScenarios ```csharp public bool EditScenarios { get; init; } ``` Whether users may edit scenarios; false unless set. ### FormatCells ```csharp public bool FormatCells { get; init; } ``` Whether users may format cells; false unless set. ### FormatColumns ```csharp public bool FormatColumns { get; init; } ``` Whether users may format columns, their widths and hiding included; false unless set. ### FormatRows ```csharp public bool FormatRows { get; init; } ``` Whether users may format rows, their heights and hiding included; false unless set. ### InsertColumns ```csharp public bool InsertColumns { get; init; } ``` Whether users may insert columns; false unless set. ### InsertHyperlinks ```csharp public bool InsertHyperlinks { get; init; } ``` Whether users may insert hyperlinks; false unless set. ### InsertRows ```csharp public bool InsertRows { get; init; } ``` Whether users may insert rows; false unless set. ### PivotTables ```csharp public bool PivotTables { get; init; } ``` Whether users may use PivotTables and PivotCharts; false unless set. ### SelectLockedCells ```csharp public bool SelectLockedCells { get; init; } ``` Whether users may select locked cells; true unless set. ### SelectUnlockedCells ```csharp public bool SelectUnlockedCells { get; init; } ``` Whether users may select unlocked cells; true unless set. ### Sort ```csharp public bool Sort { get; init; } ``` Whether users may sort; false unless set. ## Methods ### Equals(SheetProtection) ```csharp public bool Equals(SheetProtection? other) ``` Whether the other protection allows the same actions. ### Equals(object) ```csharp public override bool Equals(object? obj) ``` Whether `obj` is a protection allowing the same actions. ### GetHashCode() ```csharp public override int GetHashCode() ``` A hash code consistent with [SheetProtection.Equals](SharpCell.SheetProtection.md#equals-sheetprotection). ### ToString() ```csharp public override string ToString() ``` The names of the allowed actions, such as `SelectLockedCells, SelectUnlockedCells`, or `None`. --- # Table ```csharp public sealed class Table ``` Namespace `SharpCell` · package `SharpCell` An Excel table: a named range with column names, an optional header row and an optional totals row. Formulas refer to it by name, as in `Sales[Units]`. A table does not change after it is created. ## Properties ### Columns ```csharp public IReadOnlyList Columns { get; } ``` The column names from left to right. ### HasHeaderRow ```csharp public bool HasHeaderRow { get; } ``` Whether the first row holds the column names. ### HasTotalsRow ```csharp public bool HasTotalsRow { get; } ``` Whether the last row holds totals. ### Name ```csharp public string Name { get; } ``` The name formulas use, such as `Sales` in `Sales[Units]`. ### Range ```csharp public string Range { get; } ``` The whole table as an A1 range, header and totals rows included, such as `A1:D10`. ### Worksheet ```csharp public Worksheet Worksheet { get; } ``` The sheet the table is on. ## Methods ### ToString() ```csharp public override string ToString() ``` The table name. --- # TimePeriod ```csharp public enum TimePeriod ``` Namespace `SharpCell` · package `SharpCell` The periods of Excel's A Date Occurring rule, counted from today. ## Values | Name | Value | Description | |---|---|---| | `Yesterday` | 0 | Yesterday. | | `Today` | 1 | Today. | | `Tomorrow` | 2 | Tomorrow. | | `Last7Days` | 3 | Today and the six days before. | | `LastWeek` | 4 | The week before this one, Sunday to Saturday. | | `ThisWeek` | 5 | This week, Sunday to Saturday. | | `NextWeek` | 6 | The week after this one. | | `LastMonth` | 7 | The month before this one. | | `ThisMonth` | 8 | This month. | | `NextMonth` | 9 | The month after this one. | --- # ValidationErrorStyle ```csharp public enum ValidationErrorStyle ``` Namespace `SharpCell` · package `SharpCell` What Excel does when a value breaks a rule. ## Values | Name | Value | Description | |---|---|---| | `Stop` | 0 | Refuses the value. | | `Warning` | 1 | Asks whether to keep the value. | | `Information` | 2 | Tells the user and keeps the value. | --- # ValidationOperator ```csharp public enum ValidationOperator ``` Namespace `SharpCell` · package `SharpCell` How a value is compared with a rule's formulas; lists and custom rules do not compare. ## Values | Name | Value | Description | |---|---|---| | `Between` | 0 | From the first formula to the second, both included. | | `NotBetween` | 1 | Less than the first formula or greater than the second. | | `Equal` | 2 | Equal to the first formula. | | `NotEqual` | 3 | Not equal to the first formula. | | `GreaterThan` | 4 | Greater than the first formula. | | `LessThan` | 5 | Less than the first formula. | | `GreaterThanOrEqual` | 6 | Greater than or equal to the first formula. | | `LessThanOrEqual` | 7 | Less than or equal to the first formula. | --- # ValidationType ```csharp public enum ValidationType ``` Namespace `SharpCell` · package `SharpCell` What a data validation rule lets a cell hold, as Excel's Data Validation dialog names it under Allow. ## Values | Name | Value | Description | |---|---|---| | `Any` | 0 | Any value: the rule only shows its input message. | | `WholeNumber` | 1 | A whole number compared with the formulas. | | `Decimal` | 2 | A number compared with the formulas. | | `List` | 3 | One of the items of a list, given as a quoted literal or a range; Excel shows them in a drop-down. | | `Date` | 4 | A date compared with the formulas. | | `Time` | 5 | A time compared with the formulas. | | `TextLength` | 6 | Text whose length is compared with the formulas. | | `Custom` | 7 | Any value for which the formula is true. | --- # Workbook ```csharp public sealed class Workbook ``` Namespace `SharpCell` · package `SharpCell` A workbook: sheets, defined names and calculation settings. Not thread-safe; separate workbooks can be used from different threads. ## Constructors ### Workbook() ```csharp public Workbook() ``` An empty workbook with no sheets, the invariant culture and the 1900 date system. ## Properties ### Culture ```csharp public CultureInfo Culture { get; set; } ``` Culture for text-to-number conversion, number-to-text conversion and text ordering. The process culture is never used. Defaults to the invariant culture. ### DateSystem ```csharp public DateSystem DateSystem { get; set; } ``` Whether serial dates count from 1900 or from 1904, as set in the file. ### DefaultStyle ```csharp public CellStyle DefaultStyle { get; set; } ``` The style of every cell without one of its own: the Normal style of a file read with SharpCell.Xlsx, [CellStyle.Default](SharpCell.CellStyle.md#default) for a new workbook. **Exceptions** - `ArgumentNullException`: The style is null. ### DefinedNames ```csharp public IReadOnlyList DefinedNames { get; } ``` The defined names, workbook-wide and sheet-scoped, in the order they were first defined: a snapshot that later definitions do not change. ### Diagnostics ```csharp public IReadOnlyList Diagnostics { get; } ``` Current calculation problems: circular references and failing functions. An entry of a cell disappears when the cell is edited or calculated without the problem; entries from [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string) last until the next call of it. ### Functions ```csharp public FunctionCollection Functions { get; } ``` Functions of your own that formulas of this workbook can call, besides Excel's. ### IsStructureProtected ```csharp public bool IsStructureProtected { get; } ``` Whether the workbook's structure is locked: Excel then refuses adding, deleting, renaming, moving and hiding sheets. ### IsWindowsProtected ```csharp public bool IsWindowsProtected { get; } ``` Whether the workbook's windows are locked: Excel then refuses moving and resizing them. ### Iteration ```csharp public IterationSettings Iteration { get; set; } ``` Iterative calculation of circular references, off by default as in Excel; the .xlsx reader sets it from the file. Changing it calculates every formula again at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). ### Sheets ```csharp public IReadOnlyList Sheets { get; } ``` The sheets in order. ### Tables ```csharp public IReadOnlyList Tables { get; } ``` The tables of all sheets, in the order they were added. ### this[string] ```csharp public Worksheet this[string name] { get; } ``` Gets a sheet by name, ignoring case. ## Methods ### AddSheet(string) ```csharp public Worksheet AddSheet(string name) ``` Adds a sheet at the end. **Exceptions** - `ArgumentException`: The name is not a valid sheet name, or a sheet with that name exists. ### Clone() ```csharp public Workbook Clone() ``` An independent copy: sheets, cells and their calculated values, formulas still out of date, spills, diagnostics, defined names, tables, hidden rows, settings and custom functions (the same delegates), and the file it was read from, so `XlsxWriter` can save the copy too. Nothing is calculated again. Parsed formulas and the file's bytes are shared, not copied. Cloning only reads this workbook, so several threads may clone it at the same time as long as nothing changes or calculates it meanwhile: a service can load a template once and clone it for each request. Like any workbook, each copy is for one thread at a time. **Returns:** The copy; [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) on it reports changes from this point on only. ### DefineName(string, string, Worksheet) ```csharp public void DefineName(string name, string formula, Worksheet? scope = null) ``` Defines or replaces a name. A name with a `scope` is visible only on that sheet. ### Evaluate(string) ```csharp public CellValue Evaluate(string formula) ``` Evaluates a formula that belongs to no cell, as if it were in cell A1 of the first sheet. Out-of-date cells it reads are calculated first. References without a sheet are `#REF!` when the workbook has no sheets. The formula is in no table and has no row of its own: a table reference without a table name is `#REF!` and `[#This Row]` is `#VALUE!`. ### Evaluate(string, CancellationToken) ```csharp public CellValue Evaluate(string formula, CancellationToken cancellationToken) ``` Evaluates a formula that belongs to no cell, as if it were in cell A1 of the first sheet. Out-of-date cells it reads are calculated first. References without a sheet are `#REF!` when the workbook has no sheets. The formula is in no table and has no row of its own: a table reference without a table name is `#REF!` and `[#This Row]` is `#VALUE!`. **Exceptions** - `OperationCanceledException`: The token was cancelled. ### Protect(string, bool, bool) ```csharp public void Protect(string? password = null, bool structure = true, bool windows = false) ``` Protects the workbook as Excel's Protect Workbook does. SharpCell itself does not enforce protection: code may still add, remove and rename sheets, as Excel's object model may with the password. Protecting a protected workbook replaces its locks and password. **Parameters** - `password`: The password Excel asks for to unprotect the workbook, or null (or empty) for none. Only its hash is kept, made as Excel makes it (SHA-512, a random salt, 100,000 spins); it cannot be read back. - `structure`: Whether to lock the structure: adding, deleting, renaming, moving and hiding sheets. - `windows`: Whether to lock the windows' position and size. **Exceptions** - `ArgumentException`: Both `structure` and `windows` are false. ### Recalculate(CancellationToken) ```csharp public RecalculationResult Recalculate(CancellationToken cancellationToken = default) ``` Calculates every formula that is out of date: those whose inputs changed since the last calculation and those using volatile functions (NOW, RAND). **Returns:** The cells whose values calculation changed since the previous call, including what [Workbook.Evaluate](SharpCell.Workbook.md#evaluate-string) calculated in between. **Exceptions** - `OperationCanceledException`: The token was cancelled; finished cells keep their new values, and their changes are reported by the next call. ### TryGetSheet(string, Worksheet) ```csharp public bool TryGetSheet(string name, out Worksheet? sheet) ``` Finds a sheet by name, ignoring case. **Returns:** Whether the sheet exists. ### TryGetTable(string, Table) ```csharp public bool TryGetTable(string name, out Table? table) ``` Finds a table by name, ignoring case. **Returns:** Whether the table exists. ### Unprotect() ```csharp public void Unprotect() ``` Removes the workbook's protection and its password, as Excel's Unprotect Workbook does once the password is given. --- # Worksheet ```csharp public sealed class Worksheet ``` Namespace `SharpCell` · package `SharpCell` A sheet of a [Workbook](SharpCell.Workbook.md). Reading a cell that was never set costs nothing: a [Cell](SharpCell.Cell.md) is a handle, and storage is allocated only when a value or formula is set. ## Properties ### Cells ```csharp public IEnumerable Cells { get; } ``` The cells of the sheet that hold something (a constant, a formula or a value spilled into them), row by row and left to right. Values and formulas may be changed while enumerating; adding or removing a cell makes the next step throw `InvalidOperationException`. ### ColumnPageBreaks ```csharp public IReadOnlyList ColumnPageBreaks { get; } ``` The columns that start a new printed page, in order. ### ConditionalFormats ```csharp public IReadOnlyList<(IReadOnlyList, ConditionalFormat)> ConditionalFormats { get; } ``` The conditional formats in priority order, highest first, each with its ranges. The formulas of a rule are relative to the first cell of its first range. ### DefaultColumnWidth ```csharp public double? DefaultColumnWidth { get; set; } ``` The width of columns without one of their own, as .xlsx files store it: in characters of the widest digit of the workbook's default font, padding included. Excel's standard 8.43 characters of Calibri 11 are 9.140625 here. Null when not given; Excel then uses 8 characters. **Exceptions** - `ArgumentOutOfRangeException`: The width is outside 0 to 255. ### DefaultRowHeight ```csharp public double? DefaultRowHeight { get; set; } ``` The height of rows without one of their own, in points. Null when not given; Excel then fits it to the default font, 15 points for Calibri 11. **Exceptions** - `ArgumentOutOfRangeException`: The height is outside 0 to 409. ### FilterMode ```csharp public bool FilterMode { get; set; } ``` Whether the sheet has a filter with criteria, like `Worksheet.FilterMode` in Excel. Excel then treats every hidden row of the sheet as filtered out, so SUBTOTAL with codes 1–11 skips them as well; without a filter it counts them. AGGREGATE does not look at it. ### FrozenColumns ```csharp public int FrozenColumns { get; set; } ``` How many columns on the left stay in place while the rest scrolls; 0 for none. **Exceptions** - `ArgumentOutOfRangeException`: The count is negative or leaves no column to scroll. ### FrozenRows ```csharp public int FrozenRows { get; set; } ``` How many rows at the top stay in place while the rest scrolls, as Excel's Freeze Panes keeps them; 0 for none. **Exceptions** - `ArgumentOutOfRangeException`: The count is negative or leaves no row to scroll. ### MergedAreas ```csharp public IReadOnlyList MergedAreas { get; } ``` The merged areas, in the order they were merged. ### Name ```csharp public string Name { get; } ``` The sheet name. ### Notes ```csharp public IReadOnlyList<(Cell, Note)> Notes { get; } ``` The cells with a note, row by row and left to right, with their notes. ### PageMargins ```csharp public PageMargins PageMargins { get; set; } ``` The margins of the printed pages; [PageMargins.Normal](SharpCell.PageMargins.md#normal) unless set otherwise. **Exceptions** - `ArgumentNullException`: The value is null. ### PageOrientation ```csharp public PageOrientation PageOrientation { get; set; } ``` How the sheet is turned on the printed page; [PageOrientation.Portrait](SharpCell.PageOrientation.md#portrait) unless set otherwise. **Exceptions** - `ArgumentOutOfRangeException`: The value is not a defined orientation. ### PrintArea ```csharp public string? PrintArea { get; set; } ``` The cells that print, as Excel's Print Area sets them: one range such as `A1:F40`, or several separated by commas, each printed on pages of its own. Null prints every cell that holds something, which is also what setting null gives. Read without `$` signs; written with or without them. The ranges move with their cells when rows, columns or cells are inserted or deleted, as the defined name Excel keeps them in does. **Exceptions** - `ArgumentException`: The text is not A1 ranges of this sheet separated by commas. ### PrintTitleColumns ```csharp public string? PrintTitleColumns { get; set; } ``` The columns printed at the left of every page: `A:A` or `A:B`. Null for none. They move with their columns when columns are inserted or deleted. **Exceptions** - `ArgumentException`: The text is not a span of whole columns such as `A:B`. ### PrintTitleRows ```csharp public string? PrintTitleRows { get; set; } ``` The rows printed at the top of every page, as Excel's Print Titles sets them: `1:1` or `1:3`. Null for none. They move with their rows when rows are inserted or deleted. **Exceptions** - `ArgumentException`: The text is not a span of whole rows such as `1:2`. ### Protection ```csharp public SheetProtection? Protection { get; } ``` What users may still do on the sheet when it is protected; null when it is not protected. ### RowPageBreaks ```csharp public IReadOnlyList RowPageBreaks { get; } ``` The rows that start a new printed page, as Excel's Insert Page Break puts them, in order. ### ShowGridlines ```csharp public bool ShowGridlines { get; set; } ``` Whether Excel draws gridlines between the sheet's cells; true unless turned off. ### UsedRange ```csharp public CellRange? UsedRange { get; } ``` The smallest range holding every non-empty cell of the sheet; null when the sheet is empty. ### Validations ```csharp public IReadOnlyList<(CellRange, DataValidation)> Validations { get; } ``` The data validation rules, one entry per range: a rule over several ranges has an entry for each, in order. The formulas of a rule are relative to the first cell of its first range. ### Workbook ```csharp public Workbook Workbook { get; } ``` The workbook the sheet belongs to. ### this[int, int] ```csharp public Cell this[int row, int column] { get; } ``` A cell by 1-based row and column. ### this[string] ```csharp public Cell this[string address] { get; } ``` A cell by A1 address such as `B3` (no `$`, no range). ## Methods ### AddColumnPageBreak(int) ```csharp public void AddColumnPageBreak(int column) ``` Starts a new printed page at a column: a page break left of it. **Parameters** - `column`: The first column of the new page, from 2 (B). **Exceptions** - `ArgumentOutOfRangeException`: The column is not on the sheet or is column A. ### AddConditionalFormat(string, ConditionalFormat) ```csharp public void AddConditionalFormat(string range, ConditionalFormat format) ``` Adds a conditional format to a range, below the rules the sheet has: where several apply to a cell, those added earlier win. Formulas are relative to the range's top-left cell; for several ranges, to the first one's. **Parameters** - `range`: The cells, such as `B2:B10`, or several ranges separated by spaces, such as `B2:B10 D2:D10`. - `format`: The rule. **Exceptions** - `ArgumentNullException`: The format is null. - `ArgumentException`: The range is not an A1 range, or the format is one only read from files (a colour scale, data bars or an icon set). - `FormulaParseException`: A formula cannot be parsed; nothing is added. ### AddRowPageBreak(int) ```csharp public void AddRowPageBreak(int row) ``` Starts a new printed page at a row: a page break above it. Breaks move with their rows. **Parameters** - `row`: The first row of the new page, from 2. **Exceptions** - `ArgumentOutOfRangeException`: The row is not on the sheet or is row 1. ### AddTable(string, string, bool, bool) ```csharp public Table AddTable(string name, string range, bool hasHeaderRow = true, bool hasTotalsRow = false) ``` Makes a range a table that formulas can refer to by name, as in `Sales[Units]`. Column names come from the header row as it is now: a cell's value as text (a formula's too), `ColumnN` for an empty cell or an error (N counts from the table's first column), and a number added to a repeated name (`Units2`). Without a header row the columns are `Column1`, `Column2` and so on. Changing a header cell later does not rename its column. **Parameters** - `name`: The table name; tables and defined names share one set of names, ignoring case. - `range`: An A1 range on this sheet, such as `A1:D10`, including the header and totals rows; a single cell such as `A1` is a one-cell table without a header row. - `hasHeaderRow`: Whether the first row holds the column names. - `hasTotalsRow`: Whether the last row holds totals. **Exceptions** - `ArgumentException`: The name is not valid or is taken, the range is not an area, overlaps another table or has no row for data. ### AddValidation(string, DataValidation) ```csharp public void AddValidation(string range, DataValidation validation) ``` Adds a data validation rule to a range. Cells of the range leave the rules they had, as in Excel, where a cell has one rule. The formulas are relative to the range's top-left cell; for several ranges, to the first one's. **Parameters** - `range`: The cells, such as `B2:B10`, or several ranges separated by spaces as Excel writes them, such as `B2:B10 D2:D10`. - `validation`: The rule. **Exceptions** - `ArgumentNullException`: The validation is null. - `ArgumentException`: The range is not an A1 range; or a rule other than [ValidationType.Any](SharpCell.ValidationType.md#any) has no [DataValidation.Formula1](SharpCell.DataValidation.md#formula1); or a comparing rule (whole number, decimal, date, time, text length) with [ValidationOperator.Between](SharpCell.ValidationOperator.md#between) or [ValidationOperator.NotBetween](SharpCell.ValidationOperator.md#notbetween) has no [DataValidation.Formula2](SharpCell.DataValidation.md#formula2). - `FormulaParseException`: A formula cannot be parsed; nothing is added. - `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell. ### ClearConditionalFormats() ```csharp public void ClearConditionalFormats() ``` Removes every conditional format of the sheet, as Excel's Clear Rules from Entire Sheet does. ### ClearConditionalFormats(string) ```csharp public void ClearConditionalFormats(string range) ``` Takes the cells of a range out of every conditional format, as Excel's Clear Rules from Selected Cells does; a rule left without cells is removed. **Parameters** - `range`: The cells, such as `A1:Z100`, or several ranges separated by spaces. **Exceptions** - `ArgumentException`: The range is not an A1 range. - `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell. ### ClearValidation(string) ```csharp public void ClearValidation(string range) ``` Takes the cells of a range out of every data validation rule; a rule left without cells is removed. A rule that loses its first cell keeps its formulas relative to its new first cell, so every remaining cell is checked as before. **Parameters** - `range`: The cells, such as `A1:Z100`, or several ranges separated by spaces. **Exceptions** - `ArgumentException`: The range is not an A1 range. - `NotSupportedException`: The range takes the first cell of a rule read from a file whose formula SharpCell cannot read, so the formula could not be moved to the rule's new first cell. ### ColumnWidth(int) ```csharp public double? ColumnWidth(int column) ``` A column's own width, in the units of [Worksheet.DefaultColumnWidth](SharpCell.Worksheet.md#defaultcolumnwidth); null when it has none. **Exceptions** - `ArgumentOutOfRangeException`: The column is outside the sheet. ### DeleteColumns(int, int) ```csharp public void DeleteColumns(int column, int count = 1) ``` Deletes columns; see [Worksheet.DeleteRows](SharpCell.Worksheet.md#deleterows-int-int). **Parameters** - `column`: The first column to delete, from 1 (A is 1). - `count`: How many columns to delete. **Exceptions** - `ArgumentOutOfRangeException`: The columns are not all on the sheet. - `InvalidOperationException`: The columns hold part of an array formula. - `NotSupportedException`: The columns are some but not all of a table's columns, or a formula on this sheet, or one naming it, cannot be read by SharpCell. ### DeleteRows(int, int) ```csharp public void DeleteRows(int row, int count = 1) ``` Deletes rows, as Excel's Delete Sheet Rows does: cells below move up. A reference to a deleted cell becomes `#REF!`, a range that loses some of its rows shrinks, and a table whose rows are all deleted is removed. Every formula is calculated again at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Parameters** - `row`: The first row to delete, from 1. - `count`: How many rows to delete. **Exceptions** - `ArgumentOutOfRangeException`: The rows are not all on the sheet. - `InvalidOperationException`: The rows hold part of an array formula, or a table's header or totals row or all its data rows without the whole table. - `NotSupportedException`: A formula on this sheet, or one naming it, cannot be read by SharpCell. ### InsertColumns(int, int) ```csharp public void InsertColumns(int column, int count = 1) ``` Inserts empty columns before `column`; see [Worksheet.InsertRows](SharpCell.Worksheet.md#insertrows-int-int). **Parameters** - `column`: The column the new columns take the place of, from 1 (A is 1). - `count`: How many columns to insert. **Exceptions** - `ArgumentOutOfRangeException`: The columns would not fit on the sheet. - `InvalidOperationException`: Content would be pushed past the last column, or the columns would split an array formula. - `NotSupportedException`: The columns would be inserted inside a table, or a formula on this sheet, or one naming it, cannot be read by SharpCell. ### InsertRows(int, int) ```csharp public void InsertRows(int row, int count = 1) ``` Inserts empty rows before `row`, as Excel's Insert Sheet Rows does. Cells, styles, row heights, hidden rows, merged cells, tables and data validation below move down, and every reference to them follows: in formulas of every sheet, in defined names and in data validation. A range that spans the new rows grows; a table, merged area or validated range they are inserted into grows. Every formula is calculated again at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Parameters** - `row`: The row the new rows take the place of, from 1. - `count`: How many rows to insert. **Exceptions** - `ArgumentOutOfRangeException`: The rows would not fit on the sheet. - `InvalidOperationException`: Content would be pushed past the last row, or the rows would split an array formula (Ctrl+Shift+Enter). - `NotSupportedException`: A formula on this sheet, or one naming it, cannot be read by SharpCell (such as a link to another workbook), so its references could not follow. ### IsColumnHidden(int) ```csharp public bool IsColumnHidden(int column) ``` Whether a column is hidden. **Exceptions** - `ArgumentOutOfRangeException`: The column is outside the sheet. ### IsRowHidden(int) ```csharp public bool IsRowHidden(int row) ``` Whether a row is hidden. **Exceptions** - `ArgumentOutOfRangeException`: The row is outside the sheet. ### LoadDataTable(string, DataTable, bool) ```csharp public CellRange LoadDataTable(string topLeft, DataTable table, bool headers = true) ``` Writes the rows of a `DataTable` starting at a cell, optionally under a header row of its column names, and returns the range written. Nothing is written unless every value could be converted. `DBNull` is an empty cell; other values are converted as [`Worksheet.LoadRows`](SharpCell.Worksheet.md#loadrows-string-ienumerable-t-string-func-t-object) does. **Parameters** - `topLeft`: The cell of the first header (or of the first value without headers), such as `B2`. - `table`: The table to write; it needs at least one column. - `headers`: `true` to write the column names as a header row first. **Returns:** The range written, header row included. **Exceptions** - `ArgumentNullException`: An argument is null. - `ArgumentException`: The table has no columns, the address is not a cell, or a value cannot be stored (NaN, infinity). - `ArgumentOutOfRangeException`: The data runs past the last row or column, or a date is before the date system starts. ### LoadRows(string, IEnumerable, (string, Func)[]) ```csharp public CellRange LoadRows(string topLeft, IEnumerable rows, params (string Header, Func Value)[] columns) ``` Writes a header row and then one row per item of a collection, starting at a cell, and returns the range written (the header row included). The collection is enumerated once and nothing is written unless every value could be converted. Columns are lambdas, so no reflection is needed. Values are converted like this: - `null` and `DBNull` are empty and clear the cell; a [CellValue](SharpCell.CellValue.md) is stored as it is. - A `String` or `Char` is text, never a formula: `"=1+1"` stays text. - A `Boolean` is a logical value. - Every integer type, `Single`, `Double` and `Decimal` is a number; NaN and infinities are rejected. - A `DateTime` is a date serial in the workbook's date system, shown in the culture's short date and, with a time of day, hours and minutes. `DateOnly` is a date at midnight; `DateTimeOffset` is the clock time it states. - A `TimeOnly` is a fraction of a day with a time format; a `TimeSpan` is a number of days shown as `[h]:mm:ss`. - An enum is its name; anything else is its `IFormattable` text in the workbook's culture, or its `ToString()`. Number cells keep the format they had; date and time cells take a format. **Parameters** - `topLeft`: The cell of the first header, such as `B2`. - `rows`: The items; one row each. - `columns`: The header of each column and how to get its value from an item. **Returns:** The range written, header row included. **Exceptions** - `ArgumentNullException`: An argument or a header is null. - `ArgumentException`: There are no columns, the address is not a cell, or a value cannot be stored (NaN, infinity). - `ArgumentOutOfRangeException`: The data runs past the last row or column, or a date is before the date system starts. ### Merge(string) ```csharp public void Merge(string address) ``` Merges a range of at least two cells into one, as Excel shows it: the top-left cell covers the range. The values of the other cells are kept; Excel's Merge command would clear them. **Exceptions** - `ArgumentException`: The address is not a range of two cells or more, or it overlaps a merged area. ### Protect(string, SheetProtection) ```csharp public void Protect(string? password = null, SheetProtection? allow = null) ``` Protects the sheet as Excel's Protect Sheet does: Excel then refuses changes to locked cells and allows only the actions `allow` names. SharpCell itself does not enforce protection: code may still change the sheet, as Excel's object model may with the password. Protecting a protected sheet replaces its protection and password. **Parameters** - `password`: The password Excel asks for to unprotect the sheet, or null (or empty) for none. Only its hash is kept, made as Excel makes it (SHA-512, a random salt, 100,000 spins); it cannot be read back. - `allow`: What users may still do; null for Excel's defaults, selecting cells only. ### Range(string) ```csharp public CellRange Range(string address) ``` A range of this sheet by A1 address: `A1:C10`, `B2`, whole columns `A:B` or whole rows `2:3`. **Exceptions** - `ArgumentException`: The text is not such an address; a sheet name is not allowed. ### RemoveColumnPageBreak(int) ```csharp public void RemoveColumnPageBreak(int column) ``` Removes the page break left of a column, if there is one. **Parameters** - `column`: The column. ### RemoveRowPageBreak(int) ```csharp public void RemoveRowPageBreak(int row) ``` Removes the page break above a row, if there is one. **Parameters** - `row`: The row. ### RowHeight(int) ```csharp public double? RowHeight(int row) ``` A row's own height in points; null when it has none. **Exceptions** - `ArgumentOutOfRangeException`: The row is outside the sheet. ### SetColumnHidden(int, bool) ```csharp public void SetColumnHidden(int column, bool hidden) ``` Hides or shows a column. Unlike hidden rows, hidden columns change no result, as in Excel. **Exceptions** - `ArgumentOutOfRangeException`: The column is outside the sheet. ### SetColumnWidth(int, double?) ```csharp public void SetColumnWidth(int column, double? width) ``` Gives a column a width of its own, in the units of [Worksheet.DefaultColumnWidth](SharpCell.Worksheet.md#defaultcolumnwidth); null takes it away. **Exceptions** - `ArgumentOutOfRangeException`: The column is outside the sheet, or the width outside 0 to 255. ### SetRowHeight(int, double?) ```csharp public void SetRowHeight(int row, double? height) ``` Gives a row a height of its own in points; null takes it away. **Exceptions** - `ArgumentOutOfRangeException`: The row is outside the sheet, or the height outside 0 to 409. ### SetRowHidden(int, bool) ```csharp public void SetRowHidden(int row, bool hidden) ``` Hides or shows a row. SUBTOTAL with codes 101–111 and AGGREGATE with options 1, 3, 5 and 7 skip hidden rows; see [Worksheet.FilterMode](SharpCell.Worksheet.md#filtermode) for SUBTOTAL 1–11. Like any change, it shows at the next [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken). **Exceptions** - `ArgumentOutOfRangeException`: The row is outside the sheet. ### ToString() ```csharp public override string ToString() ``` The sheet name. ### Unmerge(string) ```csharp public void Unmerge(string address) ``` Undoes a merge; the address must be exactly a merged area. **Exceptions** - `ArgumentException`: The address is not a merged area. ### Unprotect() ```csharp public void Unprotect() ``` Removes the sheet's protection and its password, as Excel's Unprotect Sheet does once the password is given. --- # XlsxReader ```csharp public static class XlsxReader ``` Namespace `SharpCell.Xlsx` · package `SharpCell.Xlsx` Reads .xlsx workbooks: sheets, values, formulas with the results Excel cached, defined names, tables, hidden rows, whether a sheet is filtered, the date system, number formats, cell styles, column widths, row heights, merged cells, frozen panes and gridlines, from .xlsx and macro-enabled .xlsm files. Charts, pivot tables and macros are not read (a save keeps them; macros never run). Every formula is out of date after loading: [Cell.Value](SharpCell.Cell.md#value) shows the value cached in the file until [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) calculates it. A formula SharpCell cannot parse (for example a link to another workbook) does not fail the load; once calculated it is `#NAME?` and listed in [Workbook.Diagnostics](SharpCell.Workbook.md#diagnostics). The workbook keeps the file's bytes in memory, so [XlsxWriter](SharpCell.Xlsx.XlsxWriter.md) can write changes into the same file. ## Methods ### Load(Stream) ```csharp public static Workbook Load(Stream stream) ``` Reads a workbook from a stream, which is left open. **Parameters** - `stream`: The .xlsx content; it need not be seekable. **Returns:** The workbook, with the results Excel saved until it is recalculated. **Exceptions** - `InvalidDataException`: The file is not a well-formed .xlsx workbook. - `NotSupportedException`: The file is .xls, .xlsb or encrypted. ### Load(string) ```csharp public static Workbook Load(string path) ``` Reads a workbook from a file. **Parameters** - `path`: Path of the .xlsx file. **Returns:** The workbook, with the results Excel saved until it is recalculated. **Exceptions** - `InvalidDataException`: The file is not a well-formed .xlsx workbook. - `NotSupportedException`: The file is .xls, .xlsb or encrypted. --- # XlsxWriteException ```csharp public sealed class XlsxWriteException : InvalidOperationException ``` Namespace `SharpCell.Xlsx` · package `SharpCell.Xlsx` A workbook was not saved because SharpCell could not calculate some of its formulas; see [XlsxWriteOptions.KeepUncalculated](SharpCell.Xlsx.XlsxWriteOptions.md#keepuncalculated). ## Properties ### Problems ```csharp public IReadOnlyList Problems { get; } ``` The formula cells that could not be calculated, with the reason for each. --- # XlsxWriteOptions ```csharp public sealed class XlsxWriteOptions ``` Namespace `SharpCell.Xlsx` · package `SharpCell.Xlsx` Options for [XlsxWriter](SharpCell.Xlsx.XlsxWriter.md). ## Constructors ### XlsxWriteOptions() ```csharp public XlsxWriteOptions() ``` The defaults: a save is refused when some formulas cannot be calculated. ## Properties ### KeepUncalculated ```csharp public bool KeepUncalculated { get; init; } ``` What to do with formulas SharpCell cannot calculate, such as calls of functions it does not know or links to other workbooks. False (the default) refuses to save with an [XlsxWriteException](SharpCell.Xlsx.XlsxWriteException.md). True keeps the result Excel saved for them and marks the file so that Excel recalculates every formula when it opens it; until then, cells that read such a formula show what SharpCell calculated for them. --- # XlsxWriter ```csharp public static class XlsxWriter ``` Namespace `SharpCell.Xlsx` · package `SharpCell.Xlsx` Saves a workbook as an .xlsx file: values, formulas, styles, number formats, sizes, merged cells, panes, sheets, defined names and tables. A workbook read by [XlsxReader](SharpCell.Xlsx.XlsxReader.md) is saved into the file it was read from: everything SharpCell does not model (charts, pivot tables, comments, conditional formats, macros) is copied unchanged, and so is every cell that did not change. A workbook built in code becomes a new file. Call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) before saving. ## Methods ### Save(Workbook, Stream) ```csharp public static void Save(Workbook workbook, Stream stream) ``` Saves the workbook to a stream, which is left open. **Parameters** - `workbook`: A recalculated workbook, read by [XlsxReader](SharpCell.Xlsx.XlsxReader.md) or built in code. - `stream`: Where the .xlsx content goes; it need not be seekable. Nothing is written when the save is refused. **Exceptions** - `NotSupportedException`: A cell of an Excel data table changed, or a cell holds `#SPILL!` or `#CALC!` as a value. - `InvalidOperationException`: Some formulas are not calculated: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first; or the workbook has no sheets. - `XlsxWriteException`: SharpCell could not calculate some formulas; see [XlsxWriteOptions.KeepUncalculated](SharpCell.Xlsx.XlsxWriteOptions.md#keepuncalculated). ### Save(Workbook, Stream, XlsxWriteOptions) ```csharp public static void Save(Workbook workbook, Stream stream, XlsxWriteOptions options) ``` Saves the workbook to a stream, which is left open. **Parameters** - `workbook`: A recalculated workbook, read by [XlsxReader](SharpCell.Xlsx.XlsxReader.md) or built in code. - `stream`: Where the .xlsx content goes; it need not be seekable. Nothing is written when the save is refused. - `options`: What to do with formulas SharpCell cannot calculate. **Exceptions** - `NotSupportedException`: A cell of an Excel data table changed, or a cell holds `#SPILL!` or `#CALC!` as a value. - `InvalidOperationException`: Some formulas are not calculated: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first; or the workbook has no sheets. - `XlsxWriteException`: SharpCell could not calculate some formulas and `options` does not keep them. ### Save(Workbook, string) ```csharp public static void Save(Workbook workbook, string path) ``` Saves the workbook to a file, replacing it if it exists (the file it was read from too). **Parameters** - `workbook`: A recalculated workbook, read by [XlsxReader](SharpCell.Xlsx.XlsxReader.md) or built in code. - `path`: Path of the .xlsx file to write (.xlsm for a macro-enabled workbook read from one). The file is replaced only once the new one is complete. **Exceptions** - `NotSupportedException`: A cell of an Excel data table changed, or a cell holds `#SPILL!` or `#CALC!` as a value. - `InvalidOperationException`: Some formulas are not calculated: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first; or the workbook has no sheets. - `XlsxWriteException`: SharpCell could not calculate some formulas; see [XlsxWriteOptions.KeepUncalculated](SharpCell.Xlsx.XlsxWriteOptions.md#keepuncalculated). ### Save(Workbook, string, XlsxWriteOptions) ```csharp public static void Save(Workbook workbook, string path, XlsxWriteOptions options) ``` Saves the workbook to a file, replacing it if it exists (the file it was read from too). **Parameters** - `workbook`: A recalculated workbook, read by [XlsxReader](SharpCell.Xlsx.XlsxReader.md) or built in code. - `path`: Path of the .xlsx file to write (.xlsm for a macro-enabled workbook read from one). The file is replaced only once the new one is complete. - `options`: What to do with formulas SharpCell cannot calculate. **Exceptions** - `NotSupportedException`: A cell of an Excel data table changed, or a cell holds `#SPILL!` or `#CALC!` as a value. - `InvalidOperationException`: Some formulas are not calculated: call [Workbook.Recalculate](SharpCell.Workbook.md#recalculate-cancellationtoken) first; or the workbook has no sheets. - `XlsxWriteException`: SharpCell could not calculate some formulas and `options` does not keep them.