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).
• The date system, 1900 or 1904.
• Each cell's number format, for Cell.NumberFormat and Cell.Text (see Cells, ranges and dates).
• Each cell's style: font, solid fill, borders, alignment; theme colours as RGB (see Cells, ranges and dates).
• Column widths, row heights, hidden columns and merged cells (see Cells, ranges and dates).
• Frozen panes and whether gridlines show (see Cells, ranges and dates).
• Page orientation, margins, print areas, print titles and page breaks (see Cells, ranges and dates).
• Conditional formats with their looks (see Cells, ranges and dates).
• Notes on cells (see Cells, ranges and dates).
• The iterative calculation settings (see 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.

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

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.