Overview of the Excel Object Model

More About the Excel Object Model

Previous chapters introduced many examples when explaining Python syntax. These examples required reading data from Excel worksheets or writing processed results back to Excel worksheets, thus inevitably involving operations related to the Excel object model.

This chapter systematically introduces the four major objects in the Excel object model, including the use of common attributes and methods of objects (such as background and font settings for cell ranges), common operations (such as selecting, copying, and pasting cell ranges), and data processing methods.

Two Programming Approaches of Python xlwings

This book introduces the Excel object model using Python's xlwings package. The xlwings package provides two programming approaches:

Python xlwings API approach: This is essentially a VBA-like programming style, with syntax nearly identical to VBA. Readers familiar with VBA syntax can easily master it.

Python xlwings approach: A new syntax obtained by secondary encapsulation of the win32com package. It encapsulates commonly used functions, and missing features can be supplemented using API functions.

For example, to select cell A1 in a worksheet, the following two approaches can be used:

Python xlwings

>>> sht = bk.sheets(1)
>>> sht.range('A1').select()

Python xlwings API

>>> sht = bk.sheets(1)
>>> sht.api.Range('A1').Select()

Note: In Python, variable, attribute, and method names are case-sensitive. For the Python xlwings approach, range and select are lowercase (re-encapsulated). For the Python xlwings API approach, Range and Select (referencing VBA syntax) start with uppercase letters after sht.api. Thus, the Python xlwings API approach can reuse most VBA code, allowing VBA-proficient readers to adapt quickly. However, the Python xlwings approach offers benefits in coding efficiency and extended functionality.

In subsequent sections, for each topic, we will illustrate Excel VBA, Python xlwings, and Python xlwings API approaches to facilitate comparative learning. If the Python xlwings approach is not provided, the corresponding feature may not be encapsulated in the new syntax.