User API Reference¶

The user API (UserModel) is the high level API used by the IronCalc web application. Every action evaluates the workbook, keeps undo/redo history and produces diffs for collaboration.

Conventions:

sheet indexes are 0-based; row and column indexes are 1-based (column “A” is 1).
Range arguments are always (sheet, start_row, start_column, end_row, end_column), all bounds inclusive.
Complex values (styles, rules, themes) are plain dictionaries; see Objects.
All methods raise WorkbookError on invalid input.

Constructors¶

class UserModel(name, locale='en', tz='UTC', language_id='en')¶: Creates an empty workbook.

static UserModel.from_xlsx(file_path, locale='en', tz='UTC', language_id='en') → UserModel¶

static UserModel.from_icalc(file_name, language_id='en') → UserModel¶

static UserModel.from_bytes(bytes, language_id='en') → UserModel¶

Persistence¶

UserModel.save_to_xlsx(file: str)¶: Saves the workbook to an xlsx file.

UserModel.save_to_icalc(file: str)¶: Saves the workbook to a file in the internal binary ic format.

UserModel.to_bytes() → bytes¶: Returns the workbook as bytes in the internal binary ic format.

UserModel.to_xlsx() → bytes¶: Returns the workbook as bytes in xlsx format.

Collaboration¶

UserModel.flush_send_queue() → bytes¶: Returns (and clears) the queue of binary diffs produced by local edits.

UserModel.apply_external_diffs(external_diffs: bytes)¶: Applies a list of diffs produced by another model’s flush_send_queue.

Undo, redo and evaluation¶

UserModel.undo()¶

UserModel.redo()¶

UserModel.can_undo() → bool¶

UserModel.can_redo() → bool¶

UserModel.pause_evaluation()¶: Pauses the automatic evaluation performed after each change; useful when entering large amounts of data.

UserModel.resume_evaluation()¶

UserModel.evaluate()¶: Forces an evaluation (only needed while the evaluation is paused).

Cell values¶

UserModel.set_user_input(sheet: int, row: int, column: int, value: str)¶: Sets the input of a cell as a user would type it: "3.5" is a number, "Hello" a string, "=A1*2" a formula.

UserModel.set_user_array_formula(sheet: int, row: int, column: int, width: int, height: int, formula: str)¶: Sets an array (spill) formula covering width x height cells.

UserModel.get_cell_content(sheet: int, row: int, column: int) → str¶: Returns the content of a cell as seen in the editor: the formula if there is one, the raw value otherwise.

UserModel.get_formatted_cell_value(sheet: int, row: int, column: int) → str¶: Returns the value formatted with the cell’s number format (i.e. "$5.75").

UserModel.get_cell_value(sheet: int, row: int, column: int) → None | str | float | bool¶: Returns the evaluated value of a cell as a native Python type.

UserModel.get_cell_value_by_ref(cell_ref: str) → None | str | float | bool¶: Returns the evaluated value of a cell referenced by a string such as "Sheet1!C4" as a native Python value.

UserModel.get_cell_formula(sheet: int, row: int, column: int) → str | None¶: Returns the cell formula, including the leading =, or None if the cell does not contain a formula.

UserModel.get_cell_type(sheet: int, row: int, column: int) → CellType¶

UserModel.get_cell_array_structure(sheet: int, row: int, column: int)¶: Returns information about the array (spill) structure of a cell.

UserModel.get_sheet_dimensions(sheet: int)¶: Returns (min_row, max_row, min_column, max_column) of the non-empty cells; (1, 1, 1, 1) for an empty sheet.

UserModel.cycle_reference(value: str, start: int, end: int)¶: Cycles the references touched by the cursor through the four absolute/relative states, Excel F4 style: A1 -> $A$1 -> A$1 -> $A1 -> A1. start and end are character offsets of the cursor (or selection) in value. Returns the new text with the new cursor start and end: cycle_reference("=A1", 3, 3) returns ("=$A$1", 5, 5).

Ranges¶

UserModel.range_clear_all(sheet, start_row, start_column, end_row, end_column)¶: Clears contents and formatting.

UserModel.range_clear_contents(sheet, start_row, start_column, end_row, end_column)¶: Clears contents, keeps formatting.

UserModel.range_clear_formatting(sheet, start_row, start_column, end_row, end_column)¶: Clears formatting, keeps contents.

UserModel.auto_fill_rows(sheet, start_row, start_column, end_row, end_column, to_row: int)¶: Extends the content of the source area down (or up) until to_row, like dragging the fill handle.

UserModel.auto_fill_columns(sheet, start_row, start_column, end_row, end_column, to_column: int)¶

Styles¶

UserModel.update_range_style(sheet, start_row, start_column, end_row, end_column, style_path: str, value: str)¶: Updates one style property in every cell of the range. style_path examples: "font.b", "font.color", "fill.color", "alignment.horizontal", "num_fmt". The value is always a string: "true", "#FF5566", "center", "#,##0.00".

UserModel.get_cell_style(sheet: int, row: int, column: int) → dict¶: Returns the style of the cell as a dictionary.

UserModel.get_extended_cell_style(sheet: int, row: int, column: int) → dict¶: Returns the style with any conditional formatting overlay applied, plus icon-set / data-bar decorations.

UserModel.set_area_with_border(sheet, start_row, start_column, end_row, end_column, border_area: dict)¶: Applies a border to an area. border_area is {"item": {"style": "thin", "color": "#000000"}, "type": "All"} where type is one of All, Inner, Outer, Top, Right, Bottom, Left, CenterH, CenterV, None.

UserModel.on_paste_styles(styles: list[list[dict]])¶: Pastes a matrix of styles starting at the selected cell.

Named styles¶

UserModel.get_named_style_list() → list[str]¶

UserModel.get_named_style(name: str) → dict¶

UserModel.get_named_style_includes(name: str) → dict¶

UserModel.create_named_style(name: str, style: dict, includes: dict | None = None)¶: Creates a named style. includes selects which formatting categories the style carries (Excel’s “Style Includes” checkboxes); None means all.

UserModel.update_named_style(name: str, new_name: str, style: dict, includes: dict | None = None)¶

UserModel.delete_named_style(name: str)¶

UserModel.get_builtin_named_styles() → list[dict]¶: Returns all Excel built-in named styles as {"name", "style"} entries.

UserModel.on_apply_named_style(name: str)¶: Applies a named style to the current selection, adding it from the built-ins if needed.

Conditional formatting¶

Rules are dictionaries; see Objects for the shapes.

UserModel.get_conditional_formatting_list(sheet: int) → list[dict]¶: Returns entries with range, cf_rule, priority and index, sorted by priority descending.

UserModel.add_conditional_formatting(sheet: int, range: str, rule: dict)¶: Adds a rule to a range given as a string like "A1:B10".

UserModel.update_conditional_formatting(sheet: int, index: int, new_range: str, new_rule: dict)¶

UserModel.delete_conditional_formatting(sheet: int, index: int)¶

UserModel.get_dxf_for_conditional_formatting(sheet: int, index: int) → dict | None¶: Returns the differential style the rule applies, if it has one.

UserModel.raise_conditional_formatting_priority(sheet: int, index: int)¶

UserModel.lower_conditional_formatting_priority(sheet: int, index: int)¶

Sheets¶

UserModel.new_sheet()¶: Adds a sheet with an automatically generated name.

UserModel.delete_sheet(sheet: int)¶

UserModel.duplicate_sheet(sheet: int)¶

UserModel.rename_sheet(sheet: int, name: str)¶

UserModel.move_sheet(sheet: int, new_index: int)¶

UserModel.hide_sheet(sheet: int)¶

UserModel.unhide_sheet(sheet: int)¶

UserModel.set_sheet_color(sheet: int, color)¶: color is None, "#RRGGBB" or [theme_index, tint].

UserModel.get_worksheets_properties() → list[dict]¶: One entry per sheet with name, state, sheet_id and color.

UserModel.set_show_grid_lines(sheet: int, show_grid_lines: bool)¶

UserModel.get_show_grid_lines(sheet: int) → bool¶

Merged cells¶

UserModel.merge_cells(sheet, start_row, start_column, end_row, end_column)¶: Merges the range into a single cell anchored at its top-left corner. At most one cell of the range may have content: it moves to the anchor. Fails if the range is a single cell, intersects an existing merged cell or an array formula, or has more than one cell with content.

UserModel.merge_cells_center(sheet, start_row, start_column, end_row, end_column)¶: Same as merge_cells but also centers the content horizontally (“merge & center”).

UserModel.merge_cells_across(sheet, start_row, start_column, end_row, end_column)¶: Merges each row of the range separately (“merge across”). All-or-nothing.

UserModel.merge_cells_down(sheet, start_row, start_column, end_row, end_column)¶: Merges each column of the range separately (“merge down”). All-or-nothing.

UserModel.unmerge_cells(sheet, start_row, start_column, end_row, end_column)¶: Removes every merged cell that intersects the range, keeping the content of the anchors. Unmerging nothing is not an error.

UserModel.get_merged_cells(sheet: int) → list[dict]¶: One entry per merged cell: {"row", "column", "width", "height"} where (row, column) is the anchor cell.

Rows and columns¶

UserModel.insert_rows(sheet: int, row: int, row_count: int)¶

UserModel.insert_columns(sheet: int, column: int, column_count: int)¶

UserModel.delete_rows(sheet: int, row: int, row_count: int)¶

UserModel.delete_columns(sheet: int, column: int, column_count: int)¶

UserModel.move_rows(sheet: int, row: int, row_count: int, delta: int)¶

UserModel.move_columns(sheet: int, column: int, column_count: int, delta: int)¶

UserModel.get_row_height(sheet: int, row: int) → float¶

UserModel.get_column_width(sheet: int, column: int) → float¶

UserModel.set_rows_height(sheet: int, row_start: int, row_end: int, height: float)¶

UserModel.set_columns_width(sheet: int, column_start: int, column_end: int, width: float)¶

UserModel.set_rows_hidden(sheet: int, row_start: int, row_end: int, hidden: bool)¶

UserModel.set_columns_hidden(sheet: int, column_start: int, column_end: int, hidden: bool)¶

UserModel.get_frozen_rows_count(sheet: int) → int¶

UserModel.get_frozen_columns_count(sheet: int) → int¶

UserModel.set_frozen_rows_count(sheet: int, count: int)¶

UserModel.set_frozen_columns_count(sheet: int, count: int)¶

UserModel.get_last_non_empty_in_row_before_column(sheet, row, column) → int | None¶

UserModel.get_first_non_empty_in_row_after_column(sheet, row, column) → int | None¶

UserModel.get_rows_with_data(sheet: int, column: int) → list[int]¶: Rows that have a cell (content or style) in column; useful to compute the automatic width of a column.

UserModel.get_columns_with_data(sheet: int, row: int) → list[int]¶: Columns that have a cell (content or style) in row; useful to compute the automatic height of a row.

Defined names¶

UserModel.get_defined_name_list() → list[dict]¶: Entries have name, scope (sheet index or None for global) and formula.

UserModel.new_defined_name(name: str, scope: int | None, formula: str)¶

UserModel.update_defined_name(name, scope, new_name, new_scope, new_formula)¶

UserModel.delete_defined_name(name: str, scope: int | None)¶

UserModel.is_valid_defined_name(name: str, scope: int | None, formula: str)¶: Raises WorkbookError if the name or formula is not valid.

Selection¶

Some operations (applying named styles, pasting styles, copying to the clipboard) act on the current selection.

UserModel.get_selected_sheet() → int¶

UserModel.get_selected_cell()¶

UserModel.get_selected_view() → dict¶

UserModel.set_selected_sheet(sheet: int)¶

UserModel.set_selected_cell(row: int, column: int)¶

UserModel.set_selected_range(start_row, start_column, end_row, end_column)¶: The selected cell must be one of the corners of the range; call UserModel.set_selected_cell() first.

Clipboard¶

UserModel.copy_to_clipboard() → dict¶: Copies the selected area. The result has csv (tab separated text), data (internal representation), sheet and range.

UserModel.paste_from_clipboard(source_sheet: int, source_range: tuple, clipboard: dict, is_cut: bool)¶: Pastes data copied with copy_to_clipboard into the selected area, adjusting relative references. Pass clipboard["data"] as the clipboard argument.

UserModel.paste_csv_string(sheet, start_row, start_column, end_row, end_column, csv: str)¶: Pastes tab separated text starting at the top-left corner of the area.

Workbook properties¶

UserModel.get_name() → str¶

UserModel.set_name(name: str)¶

UserModel.get_timezone() → str¶

UserModel.set_timezone(timezone: str)¶

UserModel.get_locale() → str¶

UserModel.set_locale(locale: str)¶

UserModel.get_language() → str¶

UserModel.set_language(language: str)¶

UserModel.get_fmt_settings() → dict¶: Returns locale dependent formatting settings (currency, date formats, …).

UserModel.get_theme() → dict¶

UserModel.set_theme(theme: dict)¶

UserModel.resolve_color(color) → str¶: Resolves a color to a CSS hex string using the current workbook theme; returns "" for no color.