XlsxWriter-CellDSL

What is this?

XlsxWriter-CellDSL is a complementary Python module for the excellent XlsxWriter library that provides a DSL (domain specific language) to perform common operations in worksheets without having to specify absolute coordinates and keep track of them, instead opting to use primarily relative movement of an imaginary "cursor" within the spreadsheet, among other things.

The issue with absolute coordinates

If you've ever written code that generates structures that have a dynamic layout in Excel, you may have noticed that, in order to make sure writes happen in correct cells, you have to carry data that have been used to figure out the size of those structures and then sum up those size data with some offsets in order to get the coordinate that you then pass into write function.

This is really painful to do in more complex cases where many structures are present within a worksheet since you have to keep track of the sizes of each structure and drag this information across every module that's related to the worksheet.

If you want to then refactor the code or move structures around, you'll have to rewrite the coordinate calculations for every structure downstream, which is extremely error-prone. Moreover, if you then refactor writing some structures into functions, you will have to pass into the function some kind of initial coordinates, which is information that's unlikely to be relevant for the structure itself. To put it simply, a structure doesn't care where it is located and thus, keeping track of absolute coordinates is a very error-prone and redundant activity.

To put it another way, in many cases writing a structure into an Excel sheet is not a random access operation for which absolute coordinates would be more appropriate for. Writes do not occur to arbitrary cells, in fact, more often than not writes occur in some kind of sequential order, with known spacing and local positioning, but not necessarily known global positioning.

Not only that, but even writing and reading an Excel sheet follows this principle: at any given moment the operator cares more about nearby cells than distant ones. Mirroring this mode of operation is what relative coordinates are best suited for. This module implements a number of utilities that allow the developer to have an imaginary "cursor" placed somewhere in the worksheet and operations occur wherever this cursor is, followed by moving the cursor with arrow keys into the next position.

Features and uses

• MoveOp: Move the cursor around using relative coordinates.
• AtCellOp: Perform an absolute coordinate jump if no other movement option suffices.
• FormatDict: Construct formats by treating them as a composition of smaller formats instead of raw dictionaries with repeated key-value pairs.
• FormatHandler: Delegate keeping track of added formats to XlsxWriter-CellDSL and remove the need to distribute references to added formats between generating functions.
• SaveOp, LoadOp: Give current position a name and then jump back to it later or use it to retrieve the absolute coordinates of some point of interest after the script has finished execution.
• StackSaveOp, StackLoadOp: A structure composed of substructures would want to take advantage of SaveOp capabilities without having to generate a name for it.
• WriteOp, MergeWriteOp, WriteRichOp: Perform common writing actions to current cell, only focusing on data, and the cell format.
• ImposeFormatOp, OverrideFormatOp, DrawBoxBorderOp: Deferred execution of operations allows additional formatting to be applied to writing actions after they occur which would ordinarily require changing the arguments of the first writing function call.
• SectionBeginOp, SectionEndOp: Errors are inevitable and though deferred execution makes debugging more difficult, this needn't be the case if you annotate segments with names.
• AddChartOp: Add charts to the worksheet and utilize the flexibility of named ranges in an environment which does not allow named ranges with RefArrayOp.
• Exceptions provide a lot of useful information to track down the line that causes it.
• Several short forms of common operations improve conciseness of code.
• Deferred execution of operations allows taking advantage of constant_memory mode in XlsxWriter easily, without having to contend with write-to-stream limitations such as ensuring the writes occurs in left-to-right, top-to-bottom order only, thus providing the performance of constant_memory mode, but flexibility of regular mode.
• Deferred execution allows introspection into the action chain and modifying it out-of-order.
• Upon execution, history of operations can be saved and used in scripts further down.
• Avoid more bugs by preventing overwriting data over non-emtpy cells with overwrites_ok attribute.

Documentation

Read the full documentation here.

Usage example

from xlsxwriter import Workbook

# Various operations
import xlsxwriter_celldsl.ops as ops
# The entry point to the library
from xlsxwriter_celldsl import cell_dsl_context
# A factory of objects needed for the context manager
from xlsxwriter_celldsl.utils import WorkbookPair
# A number of basic formats
from xlsxwriter_celldsl.formats import FormatsNamespace as F
# Useful functions to assist in printing sequences
from xlsxwriter_celldsl.utils import row_chain, col_chain, segment

wb = Workbook('out.xlsx')
wb_pair = WorkbookPair.from_wb(wb)
ws_triplet = wb_pair.add_worksheet("TestSheet1")

with cell_dsl_context(ws_triplet) as E:
  # ExecutorHelper (as E here) is a special preprocessor object that keeps track of operations
  # to be done and performs some preprocessing on them
  # See the docs for `ExecutorHelper.commit`
  E.commit([
    # xlsxwriter_celldsl.ops exports both command classes and basic instances of those classes.
    # ExecutorHelper.commit uses instances.
    # All commands are immutable objects, however, they are cached and reused
    #   so few new instances are created.
    ops.Write
      .with_data("Hello, world, at A1, using left aligned Liberation Sans 10 (default font)!")
      .with_format(F.default_font),
    ops.Move.c(3),  # Move three columns to the right
    "Wow, short form of ops.Write.with_data('this string'), at D1, three columns away from A1!",
    11,  # Short form of ops.Move, refer to ExecutorHelper.commit to see how this works
    F.default_font_bold, "Wow, I'm at B3 now, written in bold", 2,
    [
      [
        [
          "However deeply I'm nested, I will be reached anyway, at B4"
        ]
      ]
    ], 6,
    # Rich string short form, several formats within a single text cell
    F.default_font, "A single cell, but two parts, first half normal ",
    F.default_font_bold, "but second half bold! For as long as we stay at C4...",
    # Adding a format to the end will allow to set the cell format, here enabling the text wrap.
    F.wrapped, 6,
    "Oops, D4 now",
    # Saving current position as "see you later"
    ops.Save.at("see you later"),
    # Absolute coordinate jump
    ops.AtCell.r(49).c(1), "Jumping all the way to B50",
    # Jumping to some previously saved position
    ops.Load.at("see you later"), 6, "We've gone back to D4, moved right and now it's E4",
    3333,
    ops.Save.at("Bottom Right Corner"),
    # Reversing movement back in time
    ops.BacktrackCell.rewind(1),
    # Drawing a box using borders
    ops.DrawBoxBorder.bottom_right("Bottom Right Corner"), 33,
    # Two formats may be "merged" together using OR operator
    # In this case, we add "wrapped" trait to default font
    F.default_font | F.wrapped, "And now, we're inside a 5x5 box, starting at E4, but this is G6."
                                "Even though this operation precedes the next one, the next one affect this cell"
                                ", thus we are inside a smaller box that only encloses G6.",
    ops.DrawBoxBorder,
    ops.AtCell.r(10).c(0),
    # Sections allow you to document your code segments by giving them names
    #   and also assist in debugging as you will be shown the name stack
    #     up until the line that causes the exception
    ops.SectionBegin.with_name("Multiplication table"), [
      # col_chain / row_chain write data sequentially from an iterable
      # row_chain prints it in a row, but the actual position of the cursor doesn't change!            
      "A sequence from 1 to 9, horizontally", 6, row_chain([
        f"* {v}"
        for v in range(1, 10)
      ]), 1,
      # col_chain prints it in a column
      "A sequence from 1 to 9, vertically", 2, col_chain([
        f"{v} *"
        for v in range(1, 10)
      ]), 6,
      # Nothing stops you from chaining chains
      col_chain([
        row_chain([
          ops.Write.with_data(a * b)
          for b in range(1, 10)
        ])
        for a in range(1, 10)
      ]),
      # Every SectionBegin must be matched with a SectionEnd
      ops.SectionEnd,
      # ...however you can skip that by using utils.segment to implicitly add SectionBegin and SectionEnd to
      #   a piece of code
      segment("Empty segment", [])
    ]
  ])

Changelog

0.6.0

• Exceptions coming from XlsxWriter are now also captured and provide debug info.
• Deprecated top_left and right_bottom methods in Range-d commands, now they use with_top_left and with_right_bottom to be consistent with other similar methods.
• Added SetPrintArea command.
• Moved exceptions to errors submodule.
• cell_dsl_context is now a proper class CellDSLContext.
• SectionBeginOp is no longer affected by coordinates of where they occur, thus preventing unexpected behavior where an inner section started sooner than the outer section because the inner section started at some previous coordinate.
• WriteRich can now accept a cell format. The short form also supports it by placing the cell format as the last fragment in the chain. This removes the restriction of following a format with a format or string since now not doing so will merely mark the end of the short form.
• Added an alias for SetColWidth -- SetColumnWidth, to make it consistent with the other ops.
• WriteRichOp and ImposeFormatOp/OverrideFormatOp are now implemented: they affect the cell format of the op.

0.5.0

• Charts can now be combined.
• A suite of WriteOp variations for writing data with known types like WriteNumber and WriteDatetime.
• BREAKING CHANGE: Operation instances and classes have been separated: the classes are now in ops.classes module. Regular instances are still importable from ops, so unless your code creates its own instances of operations, you needn't change anything.

0.4.0

• Add AddConditionalFormatOp and AddImageOp
• Write the format section in the docs
• New default format traits in FormatsNamespace

0.3.0

• Add overwrites_ok
• Docs!
• Removed dummy_cell_dsl_context
• Complete overhaul to AddChartOp, removing the string function name interface

0.2.0

• Add SectionBeginOp and SectionEndOp
• Improvement to error reporting: now they provide some context
• Remove format data from repr of commands
• Separate CellDSLError into MovementCellDSLError and ExecutionCellDSLError
• Raise exceptions on various error that may occur from XlsxWriter side (use proper exceptions instead of return codes)