.KEY File Format – Tables

How a table is stored

The table object is a TST.TableInfoArchive (type 6000), a TSD.DrawableArchive plus references to the model and the editing state. TST.WPTableInfoArchive (type 6007) wraps it with a reference to a text storage; the name suggests it is the form used inside text documents, and the source does not say so. The model, TST.TableModelArchive (6001), holds the table’s size, header and footer counts, default row height and column width, the style references, and a TST.DataStore. The data store holds the cells in tiles, the row and column headers (sizes, hiding state), and shared lists (TableDataList) of strings, styles, formulas, formats and so on. Cell values, formulas and formats use the types of the formulas page.

Unknown. Inside a tile, each row’s cells are packed in two byte buffers (cellStorageBuffer and cellOffsets) whose layout is not described by the message definitions, and none of the sources read for this site documents it. The packed numbers TST.CellID.packedData and TST.TableSize.packedData (a 32-bit value) are likewise not explained there. The storage_version fields say which layout is in use.

Enumerations

enum CellType {                       // table cell kinds (older form)
  genericCellType = 0;  spanCellType = 1;  numberCellType = 2;  textCellType = 3;
  formulaCellType = 4;  dateCellType = 5;  boolCellType = 6;  durationCellType = 7;
  formulaErrorCellType = 8;  automaticCellType = 9;
}
enum CellValueType {                  // used by Cell.valueType
  emptyCellValueType = 0;  numberCellValueType = 1;  stringCellValueType = 2;
  providedCellValueType = 3;  dateCellValueType = 4;  boolCellValueType = 5;
  durationCellValueType = 6;  errorCellValueType = 7;  richTextCellType = 8;
}
enum SelectionTypeArchive {
  cellSelectionType = 0;  rowSelectionType = 1;  columnSelectionType = 2;
  cellEditingSelectionType = 3;  tableNameSelectionType = 4;  tableSelectionType = 5;
}
enum FillDirectionArchive { kTSTFillDownArchive = 1; kTSTFillUpArchive = 2;
  kTSTFillRightArchive = 3; kTSTFillLeftArchive = 4; }
enum MergeActionArchive { mergeActionMerge = 0; mergeActionUnmerge = 1; }
enum HideShowActionArchive { hideShowActionHide = 0; hideShowActionShow = 1; }
enum TableDataList.ListType {
  STRING = 1;  FORMAT = 2;  FORMULA = 3;  STYLE = 4;  FORMULA_ERROR = 5;  CUSTOM_FORMAT = 6;
  MULTIPLE_CHOICE_LIST_FORMAT = 7;  RICH_TEXT_PAYLOAD = 8;  CONDITIONAL_STYLE = 9;
  COMMENT_STORAGE = 10;
}
enum PopUpMenuModel.CellValueType { NIL_TYPE = 1; BOOLEAN_TYPE = 2; DATE_TYPE = 3;
  NUMBER_TYPE = 4; STRING_TYPE = 5; }

The names are all the source gives. The numeric values of the cell’s explicit_format_flags and of the properties in the undo commands are not named there (unknown).

Addresses and ranges

message CellID { req fixed32 packedData = 1; }
message TableSize { req fixed32 packedData = 1; }
message CellRange { req CellID origin = 1; req TableSize size = 2; }
message CellRegion { rep CellRange cell_ranges = 1; }
message MergeRegionMapArchive {                // type 6144
  rep CellRange cell_range = 1;
}
message TableSelection { req CellRange cellRange = 1; req CellRange extendedCellRange = 2;
  req bool extendedCellRangeValid = 3; req SelectionTypeArchive selection_type = 4; }
message SelectionArchive {
  4 req SelectionTypeArchive selection_type;  5 CellID anchor_cell;  6 rep CellRange cell_ranges;
  7 rep CellRange base_ranges;  8 CellID cursor_cell;
  1 TSP.Reference tableModel [deprecated]
}

Table info and model

message TableInfoArchive {                     // type 6000
  req TSD.DrawableArchive super = 1;  req TSP.Reference tableModel = 2;
  TSP.Reference editingState = 3;
}
message WPTableInfoArchive {                   // type 6007
  req TableInfoArchive super = 1;  TSP.Reference storage = 2;
}
message EditingStateArchive {                  // type 6219
  1 TSP.Reference editingStorage;  2 req Cell editingCell;  3 SelectionArchive selection;
  4 CellID lasteditingcellid;  5 CellID editingcellid;
  6 TSWP.UndoTransaction deprecated_setup_textUndoRedoTransaction;
  7 bool cell_was_edited_in_current_session
}
message TableModelArchive {                    // type 6001
  1 req string table_id;  43 string from_table_id;
  3 req Ref table_style;  24 req Ref body_text_style;  25 req Ref header_row_text_style;
  26 req Ref header_column_text_style;  27 req Ref footer_row_text_style;
  18 req Ref body_cell_style;  19 req Ref header_row_style;  20 req Ref header_column_style;
  21 req Ref footer_row_style;  30 Ref table_name_style;  36 Ref table_name_shape_style;
  4 req DataStore data_store;  5 Ref provider;
  6 req uint32 number_of_rows;  7 req uint32 number_of_columns;  8 req string table_name;
  22 bool table_name_enabled;  33 double table_name_height;  37 bool table_name_border_enabled;
  9 uint32 number_of_header_rows;  10 uint32 number_of_header_columns;
  11 uint32 number_of_footer_rows;  12 bool header_rows_frozen;  13 bool header_columns_frozen;
  14 uint32 number_of_hidden_rows;  15 uint32 number_of_hidden_columns;
  41 uint32 number_of_user_hidden_rows;  42 uint32 number_of_user_hidden_columns;
  40 uint32 number_of_filtered_rows;
  16 req double default_row_height;  17 req double default_column_width;
  23 CellID origin_offset [deprecated];  28 req uint32 preset_index;
  29 bool repeating_header_rows_enabled;  32 bool repeating_header_columns_enabled;
  31 bool style_apply_clears_all;
  34 Ref hidden_state_formula_owner_for_columns;  35 Ref hidden_state_formula_owner_for_rows;
  38 Ref filter_set;  39 TSCE.CFUUIDArchive conditional_style_formula_owner_id
}
                                               // (Ref = TSP.Reference)
message TableStyleNetworkArchive {             // type 6247
  9 req Ref table_style;  1 req Ref body_text_style;  2 req Ref header_row_text_style;
  3 req Ref header_column_text_style;  4 req Ref footer_row_text_style;
  5 req Ref body_cell_style;  6 req Ref header_row_style;  7 req Ref header_column_style;
  8 req Ref footer_row_style;  10 Ref table_name_style;  11 Ref table_name_shape_style;
  12 uint32 preset_index
}
message DurationWrapperArchive { }

Data store, tiles and headers

message DataStore {
  1 req HeaderStorage rowHeaders;  2 req TSP.Reference columnHeaders;
  3 req TileStorage tiles;  4 req Ref stringTable;  5 req Ref styleTable;  6 req Ref formulaTable;
  12 Ref formulaErrorTable;  11 req Ref formatTable;  16 Ref multipleChoiceListFormatTable;
  13 Ref merge_region_map;  15 Ref customFormatTable;
  7 req uint32 nextRowStripID;  8 req uint32 nextColumnStripID;
  9 req TableRBTree rowTileTree;  10 req TableRBTree columnTileTree;
  14 uint32 storage_version;  17 Ref richTextPayloadTable;  18 Ref conditionalstyletable;
  19 Ref commentStorageTable
}
message TileStorage { rep Tile tiles = 1;  message Tile { 1 req uint32 tileid; 2 req TSP.Reference tile } }
message Tile {                                 // type 6002
  1 req uint32 maxColumn;  2 req uint32 maxRow;  3 req uint32 numCells;  4 req uint32 numrows;
  6 uint32 storage_version;  5 rep TileRowInfo rowInfos
}
message TileRowInfo {
  5 uint32 storage_version;  1 req uint32 tileRowIndex;  2 req uint32 cellCount;
  3 req bytes cellStorageBuffer;  4 req bytes cellOffsets
}
message TableRBTree { rep Node nodes = 1;  message Node { 1 req uint32 key; 2 req uint32 value } }
message HeaderStorage { 1 req uint32 bucketHashFunction;  2 rep TSP.Reference buckets }
message HeaderStorageBucket {                  // type 6006
  1 req uint32 bucketHashFunction;  2 rep Header headers;
  message Header { 1 req uint32 index; 2 req float size; 3 req uint32 hidingState;
    4 req uint32 numberOfCells; 5 Ref cell_style; 6 Ref text_style }
}
message TableDataList {                        // types 6005 and 6201
  1 req ListType listType;  2 req uint32 nextListID;  3 rep ListEntry entries;
  message ListEntry { 1 req uint32 key; 2 req uint32 refcount; 3 string string;
    4 TSP.Reference reference; 5 TSCE.FormulaArchive formula; 6 TSK.FormatStructArchive format;
    7 CellRange region; 8 TSK.CustomFormatArchive custom_format;
    9 TSP.Reference richTextPayload; 10 TSP.Reference comment_storage }
}
message PopUpMenuModel {                       // type 6206
  2 rep TSCE.CellValueArchive tsce_item;  1 rep CellValue item [deprecated];
  message CellValue { 1 req CellValueType cell_value_type; 2 TSCE.BooleanCellValueArchive boolean_value;
    3 TSCE.DateCellValueArchive date_value; 4 TSCE.NumberCellValueArchive number_value;
    5 TSCE.StringCellValueArchive string_value }
}
message RichTextPayloadArchive {               // type 6218
  1 req TSP.Reference storage;  2 TSP.Range range;  3 req CellID cellid
}

The message names suggest that rowTileTree and columnTileTree are red-black trees (TableRBTree) of key and value numbers that locate tiles; the source does not say what the numbers mean (unknown). A table data list entry has a key that cells use to refer to it and a refcount; the field that carries the value (a string, a reference, a formula, a format, a region) corresponds to the list’s listType, though the source does not spell out the pairing.

Cells

message Cell {
  2 req CellValueType valueType;  5 double numberValue;  6 string stringValue;  7 bool boolValue;
  3 TSP.Reference cell_style;  4 TSP.Reference text_style;  8 TSCE.FormulaArchive formula;
  10 TSP.Reference formulaError;  9 TSK.FormatStructArchive current_format;
  11 number_format;  12 currency_format;  13 duration_format;  14 date_format;  15 control_format;
  16 custom_format;  17 base_format;  18 multiple_choice_list_format     // all TSK.FormatStructArchive
  19 uint32 explicit_format_flags;  20 TSP.Reference richTextPayload;
  21 TSP.Reference conditional_style;  22 TSP.Reference multiple_choice_list;
  23 TSP.Reference comment_storage;  24 uint32 conditional_style_applied_rule
}
message CellMapArchive {
  1 req uint32 capacity;  2 req uint32 count;  3 rep CellMapEntry cellMapEntry;
  4 bool may_modify_formulas_in_cells [default true];
  5 bool may_modify_values_referenced_by_formulas [default true];  6 bool applies_to_hidden [default false]
  message CellMapEntry { 1 req CellID cellID; 2 Cell cell }
}
message DoubleStyleMapArchive { 1 req uint32 capacity; 2 req uint32 count; 3 rep DoubleStyleMapEntryArchive cell_map_entry;
  message DoubleStyleMapEntryArchive { 1 req CellID cell_id; 2 req Ref old_style; 3 req Ref new_style } }
message StyleTableMapArchive { 1 req uint32 capacity; 2 req uint32 count; 3 rep StyleTableMapEntryArchive map_entry;
  message StyleTableMapEntryArchive { 1 req uint32 key; 2 req Ref old_style; 3 req Ref new_style } }
message LayoutHintArchive {                    // type 6198
  1 req bool isValid;  2 req CellRange cellRange;  3 req CellID hintID;  4 req TSP.Size maximumSize;
  5 req uint32 partitionPosition;  6 req bool horizontal;  7 TSP.Size effectiveSize;
  8 uint32 partitioningPass
}

A cell has three value fields, numberValue, stringValue and boolValue, and a valueType. How dates and durations are held is not stated in the message definitions (unknown); formats are TSK.FormatStructArchive messages, described on document messages.

Table and cell styles, presets

message TableStyleArchive {                    // type 6003
  1 req TSS.StyleArchive super;  10 uint32 override_count [default 0];
  11 TableStylePropertiesArchive table_properties
}
message CellStyleArchive {                     // type 6004
  1 req TSS.StyleArchive super;  10 uint32 override_count [default 0];
  11 CellStylePropertiesArchive cell_properties
}
message CellStylePropertiesArchive {
  1 TSD.FillArchive cell_fill;  3 bool text_wrap;  8 int32 vertical_alignment;
  9 TSWP.PaddingArchive padding;
  10 TSD.StrokeArchive top_stroke;  11 right_stroke;  12 bottom_stroke;  13 left_stroke;
  4..7 Deprecated_TableStrokeArchive deprecated_top/right/bottom/left_stroke
}
message Deprecated_TableStrokeArchive { 1 TSD.StrokeArchive stroke; 2 bool background [deprecated];
  3 float opacity [deprecated]; 4 bool empty [deprecated] }
message TableStylePropertiesArchive {
  1 bool banded_rows;  2 TSD.FillArchive banded_fill;  21 bool behaves_like_spreadsheet;
  22 bool auto_resize;  32 StrokePresetListArchive stroke_preset_list;
  33 bool v_strokes_visible;  34 bool h_strokes_visible;  35 bool hr_separator_visible;
  36 bool hc_separator_visible;  37 bool footer_separator_visible;  38 bool table_border_visible;
  39 bool table_header_border_visible;  42 bool table_hc_divider_visible;
  43 bool table_hr_divider_visible;  44 bool table_footer_divider_visible;
  40 int32 OBSOLETE_master_font_size;  41 string master_font_family;
  45 TSWP.WritingDirectionType writing_direction;
  all TSD.StrokeArchive:  46 header_row_separator_stroke  47 header_row_border_stroke
    48 header_row_horizontal_stroke  49 header_row_vertical_stroke  50 header_column_border_stroke
    51 header_column_separator_stroke  52 header_column_horizontal_stroke
    53 header_column_vertical_stroke  54 footer_row_separator_stroke  55 footer_row_border_stroke
    56 footer_row_horizontal_stroke  57 footer_row_vertical_stroke
    58 table_body_horizontal_border_stroke  59 table_body_vertical_border_stroke
    60 table_body_horizontal_stroke  61 table_body_vertical_stroke
  deprecated (Deprecated_TableStrokeArchive): 4 header_row_separator  5 header_row_border
    23 header_row_horizontal  24 header_row_vertical  7 header_column_border
    8 header_column_separator  25 header_column_horizontal  26 header_column_vertical
    10 footer_row_separator  11 footer_row_border  27 footer_row_horizontal
    28 footer_row_vertical  12 table_body_horizontal_border  29 table_body_vertical_border
    30 table_body_horizontal  31 table_body_vertical      (each name has the prefix deprecated_ and the suffix _stroke)
}
message StrokePresetDataArchive { 1 TSD.StrokeArchive horizontal_stroke; 2 vertical_stroke;
  3 exterior_stroke; 4 int32 visible_mask }
message Deprecated_StrokePresetDataArchive { 2 req Deprecated_TableStrokeArchive deprecated_horizontal_stroke;
  1 req ... deprecated_vertical_stroke; 3 req ... deprecated_exterior_stroke; 5 req int32 deprecated_visible_mask }
message StrokePresetListArchive { 1 req int32 count; 2 rep Deprecated_StrokePresetDataArchive deprecated_preset;
  3 rep StrokePresetDataArchive preset }
message TableStylePresetArchive {              // type 6008
  1 req int32 index;  2 TSP.Reference image;  3 TSP.Reference style_network }
message TableStrokePresetArchive { 1 req int32 index }   // type 6009
message ThemePresetsArchive {
  1 rep TSP.Reference table_style_presets;  2 rep TSP.Reference table_cell_stroke_presets
  extends TSS.ThemeArchive: 200 req ThemePresetsArchive extension
}

The values of vertical_alignment, visible_mask and the like are plain integers; the source names none of them (unknown).

Filters, conditional styles

message FilterSetArchive {                     // type 6220
  1 FilterSetType type [default FilterSetArchiveTypeAll];  2 bool is_enabled [default true];
  3 rep FilterRuleArchive filter_rules;  4 bool needs_formula_rewrite_for_import [default false];
  5 rep uint32 filter_offsets;  6 rep bool filter_enabled
  enum FilterSetType { FilterSetArchiveTypeAll = 0; FilterSetArchiveTypeAny = 1; }
}
message FilterRuleArchive { 1 req FormulaPredicateArchive predicate; 2 bool disabled }
message FormulaPredicateArchive {
  1 req TSCE.FormulaArchive formula;  2 req FormulaPredicateType predicate_type;
  3 req QualifierType qualifier1;  4 req QualifierType qualifier2;
  5 req int32 param_index1;  6 req int32 param_index2;  7 req int32 param_index0
}
message ConditionalStyleSetArchive {           // type 6010
  1 req uint32 ruleCount;  2 rep ConditionalStyleRule rules;  3 CellID cellID
  message ConditionalStyleRule { 1 req FormulaPredicateArchive predicate; 2 req Ref cell_style;
    3 req Ref text_style }
}
message HiddenStateFormulaOwnerArchive {       // type 6204
  1 TSCE.CFUUIDArchive owner_id;  2 rep TSCE.CellValueArchive threshold_value;
  3 bool needs_to_update_filter_set_for_import [default false]
}

What param_index0, param_index1 and param_index2 index is not stated in the source (unknown).

Formula tokens in the editor

message TokenAttachmentArchive {               // type 6181
  1 req TSWP.UIGraphicalAttachment super;  2 TSP.Reference expressionNode }
message CompletionTokenAttachmentArchive {     // type 6199
  1 req TokenAttachmentArchive super;  2 string completionText;  3 uint32 caretPosition;
  4 uint32 prefixStart }
message FormulaEqualsTokenAttachmentArchive {  // type 6179
  1 req TSWP.UIGraphicalAttachment super }
message FormulaArchive (TST) { 1 TSP.Reference expressionTree }

The TST.ExpressionNodeArchive messages and their subclasses (types 6182 to 6197 and 6235) form a tree of formula nodes; they are listed on the formulas page. The cell and the data lists themselves refer to a TSCE.FormulaArchive, which holds a flat list of syntax tree nodes. The source does not say where the TST expression nodes are still used (unknown).

Source

TSTArchives.proto and TSTStylePropertyArchiving.proto from Sean O’Brien’s iWorkFileFormat project (Messages/Proto), read in full for this page; type numbers from its Common.json. Not covered: the binary layout of tile rows, packed cell identifiers, the meaning of unnamed integer codes, and anything added after Keynote 6.0.