Intermediate usage

Advanced movement and Stack saves

Cell DSL also comes with more unusual forms of save points and movement.

class xlsxwriter_celldsl.ops.classes.AtCellOp(NAME_STACK_DATA: List[str] = NOTHING, row: int = - 1, col: int = - 1)

A command to go to a cell r() and c().

class xlsxwriter_celldsl.ops.classes.BacktrackCellOp(NAME_STACK_DATA: List[str] = NOTHING, n: int = 0)

A command to rewind() the position back in time. 0 stays in current cell, 1 goes to previous cell…

class xlsxwriter_celldsl.ops.classes.StackSaveOp(NAME_STACK_DATA: List[str] = NOTHING)

A command to push current location into save stack.

class xlsxwriter_celldsl.ops.classes.StackLoadOp(NAME_STACK_DATA: List[str] = NOTHING)

A command to pop last location from save stack and jump to it.

AtCellOp is used in those rare cases where there is a necessity to jump to a very specific location, and it’s not the initial offset for whatever the current context is writing.

Warning

Do not use AtCellOp to init the imaginary cursor, use initial_row and initial_col arguments on cell_dsl_context instead.

BacktrackCellOp is an unusual form of movement which, instead of going a specific amount of cells away or going to some known cell, jumps back to a cell that was visited some time ago. This is mostly useful in cases where you have a structure generating function that performs some unwanted moves at the end.

For example, in case of Cell DSL own utility function row_chain, the unwanted last movement may be the jump back to starting position. Perhaps you want to continue writing to the row outside of the row_chain. To do so, you want to return back to the last position it wrote to, thus BacktrackCellOp proves useful here.

StackSave and StackLoad utilize a separate save point system which, instead of using a dictionary of string names to coordinates, uses a stack of coordinates, without any names. This is useful in generating functions in order to avoid polluting the save points of the host function. For example, aforementioned row_chain uses those operations in order to return back to starting position without ever using SaveOp.

Format imposition and override

In some cases it may be useful to apply changes to formats of writing operations after they’re submitted. An example of such a situation is when wants to highlight an arbitrary cell, which, in case of raw XlsxWriter, would require to calculating this cell in advance and then awkwardly merging the format at its write call with the format of the highlight.

Cell DSL provides a way to perform this operation.

class xlsxwriter_celldsl.ops.classes.ImposeFormatOp(NAME_STACK_DATA: List[str] = NOTHING, set_format={})

A command to append to merge current cell’s format with_format().

class xlsxwriter_celldsl.ops.classes.OverrideFormatOp(NAME_STACK_DATA: List[str] = NOTHING, set_format: Optional[xlsxwriter_celldsl.formats.FormatDict] = None)

A command to override current cell’s format with_format().

Advanced writes

Besides basic WriteOp, Cell DSL also comes with two additional ways to write to a cell.

class xlsxwriter_celldsl.ops.classes.WriteRichOp(NAME_STACK_DATA: List[str] = NOTHING, data: Any = '', set_format: Optional[xlsxwriter_celldsl.formats.FormatDict] = None, default_format=NOTHING, cell_format: Optional[xlsxwriter_celldsl.formats.FormatDict] = None, prev_fragment: Optional[WriteRichOp] = None)

A command to write a text run with_data() and with_format() to current position, then() perhaps write some more, optionally with_default_format().

Additionally, the first WriteRichOp may also set this cell with_cell_format().

class xlsxwriter_celldsl.ops.classes.MergeWriteOp(NAME_STACK_DATA: List[str] = NOTHING, size: int = 0, data: Any = '', data_type: Optional[str] = None, set_format: Optional[xlsxwriter_celldsl.formats.FormatDict] = None)

A command to merge with_size() cols starting from current col and write with_data() with data with_data_type() and with_format() into this cell.

WriteRichOp is specifically useful to write strings that have several formats into a single cell and also comes with a short form in commit method.

MergeWriteOp is a strange variation of a writing operation that has a Range trait, mirroring the behavior of merge_range() method in XlsxWriter.

Ranged commands

There are several operations in Cell DSL which target a range of cells, all of which have quirks in what they do.

class xlsxwriter_celldsl.ops.traits.Range(top_left_point: Union[str, int, Tuple[int, int]] = 0, bottom_right_point: Union[str, int, Tuple[int, int]] = 0)

Commands with this trait target a range of cells bounded by a box with top_left_point and bottom_right_point.

Notes

top_left_point and bottom_right_point have different behavior depending on the value type

• If it’s a string, this will be the save point name at which the save occurs.

• If it’s an Integral

  • If positive: this will be last n-th visited cell.

  • If negative: this will be last n-th position in save stack which will be retrieved without popping it from the stack.

  • If zero, current cell will be the target.

• If it’s Coords, it will be the absolute coords of the cell.

The range trait itself has a fairly complicated set of rules regarding its value types.

class xlsxwriter_celldsl.ops.classes.DefineNamedRangeOp(NAME_STACK_DATA: List[str] = NOTHING, top_left_point: Union[str, int, Tuple[int, int]] = 0, bottom_right_point: Union[str, int, Tuple[int, int]] = 0, name: str = '__DEFAULT')

A command to make a box with_top_left() and with_bottom_right() which are respective corners of a range with_name().

Just as using named ranges is good practice in Excel, you may also leverage their power in Cell DSL in situations where you need to refer to a literal cell range by name, such as in formulas.

However, an exception to this exists where a literal cell range must be by value only: charts. Cell DSL provides a separate operation for charts and related commands specifically.

class xlsxwriter_celldsl.ops.classes.RefArrayOp(NAME_STACK_DATA: List[str] = NOTHING, top_left_point: Union[str, int, Tuple[int, int]] = 0, bottom_right_point: Union[str, int, Tuple[int, int]] = 0, point_name: str = '__DEFAULT')

A forward reference to an array of cells with_name() defined using a rectangle with top_left() and bottom_right() specified. This is only used in charts.

This is also a marker for such an array, meaning in commands that support forward references, you can use RefArray.at(‘name’) to use a reference, which will be replaced with a string like '=SheetName!$C$1:$F$9'.

Operations with a trait of ForwardRef receive a literal cell range into resolved_refs at the time of their execution. This is only relevant for custom commands that want to take advantage of this functionality.

Charts

Unlike many other operations, charts are significantly more involved to work with in both XlsxWriter and Cell DSL.

class xlsxwriter_celldsl.ops.classes.AddChartOp(NAME_STACK_DATA: List[str] = NOTHING, resolved_refs: Dict[str, str] = NOTHING, type: str = 'bar', subtype: Optional[str] = None, target: xlsxwriter_celldsl.ops.classes.T = NOTHING, action_chain: List[Tuple[str, Tuple[Any, ...], Dict[str, Any]]] = NOTHING)

A command to add a chart to this cell perhaps with_subtype() and then do() call some methods on the associated target class.

As Cell DSL doesn’t execute operations until exiting the context, chart methods may not be called during submission. In order to mitigate this, chart operations come with their own action chains, but there is no need to create a separate operation set for them as long as only methods are called.

Every instance of AddChartOp is associated with a specific type of Chart and a specific XlsxWriter class. Those classes provide various methods depending on the type of the chart.

In order to queue up methods to be called during Cell DSL execution stage, use target attribute of AddChartOp. This attribute will contains an object that mimics the associated chart class and allows you to call various chart methods as if it was a real instance of that chart class.

Here is an example of it in action:

from xlsxwriter_celldsl.ops import AddBarChart, RefArray

with cell_dsl_context(ws_triplet) as E:
    E.commit([
        RefArray.top_left((0, 0)).bottom_right((0, 3)).at('some ref array'),
        AddBarChart.do([
            AddBarChart.target.add_series({'values': RefArray.at('some ref array')}),
            AddBarChart.target.add_series({'values': '=TestSheet!$A$2:$C$7'})
        ]),
    ])

This will ensure that add_series() of XlsxWriter is called during execution. Values of type RefArrayOp will automatically be replaced with a literal cell range string, in this case this string will be '=SheetName!$A$1:$D$1', because coordinates of A1 are (0, 0) and of D1 are (0, 3).

Note

Substitution functionality is unique to charts, custom commands will have to reinvent the wheel.