Workbook

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()

public Workbook()

An empty workbook with no sheets, the invariant culture and the 1900 date system.

Properties

Culture

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

public DateSystem DateSystem { get; set; }

Whether serial dates count from 1900 or from 1904, as set in the file.

DefaultStyle

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 for a new workbook.

Exceptions

ArgumentNullException: The style is null.

DefinedNames

public IReadOnlyList<DefinedName> 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

public IReadOnlyList<CalculationDiagnostic> 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 last until the next call of it.

Functions

public FunctionCollection Functions { get; }

Functions of your own that formulas of this workbook can call, besides Excel's.

IsStructureProtected

public bool IsStructureProtected { get; }

Whether the workbook's structure is locked: Excel then refuses adding, deleting, renaming, moving and hiding sheets.

IsWindowsProtected

public bool IsWindowsProtected { get; }

Whether the workbook's windows are locked: Excel then refuses moving and resizing them.

Iteration

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.

Sheets

public IReadOnlyList<Worksheet> Sheets { get; }

The sheets in order.

Tables

public IReadOnlyList<Table> Tables { get; }

The tables of all sheets, in the order they were added.

this[string]

public Worksheet this[string name] { get; }

Gets a sheet by name, ignoring case.

Methods

AddSheet(string)

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()

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 on it reports changes from this point on only.

DefineName(string, string, Worksheet)

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)

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)

public CellValue Evaluate(string formula, CancellationToken cancellationToken)

Exceptions

OperationCanceledException: The token was cancelled.

Protect(string, bool, bool)

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)

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

public bool TryGetSheet(string name, out Worksheet? sheet)

Finds a sheet by name, ignoring case.

Returns: Whether the sheet exists.

TryGetTable(string, Table)

public bool TryGetTable(string name, out Table? table)

Finds a table by name, ignoring case.

Returns: Whether the table exists.

Unprotect()

public void Unprotect()

Removes the workbook's protection and its password, as Excel's Unprotect Workbook does once the password is given.