SharpCell

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

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

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