语法综述

xlwings的对象模型和VBA的非常相似。

后面所有的示例代码都依赖下面的模块导入语句:

>>> import xlwings as xw

活动对象

# Active app (i.e. Excel instance)
>>> app = xw.apps.active

# Active book
>>> wb = xw.books.active  # in active app
>>> wb = app.books.active  # in specific app

# Active sheet
>>> sheet = xw.sheets.active  # in active book
>>> sheet = wb.sheets.active  # in specific book

# Range on active sheet
>>> xw.Range('A1')  # on active sheet of active book of active app

实例化一个区域,可以使用A1引用样式、一对从1开始的下标元组、一个命名区域,也可以使用2个区域对象:

xw.Range('A1')
xw.Range('A1:C3')
xw.Range((1,1))
xw.Range((1,1), (3,3))
xw.Range('NamedRange')
xw.Range(xw.Range('A1'), xw.Range('B2'))

对象的完全限定

使用圆括号时里面用Excel惯用的的引用方式(如从1开始的下标),使用方括号时里面用Python惯用的从0开始的下标或切片。 例如,下列表达式都引用了同一个区域:

xw.apps[763].books[0].sheets[0].range('A1')
xw.apps(10559).books(1).sheets(1).range('A1')
xw.apps[763].books['Book1'].sheets['Sheet1'].range('A1')
xw.apps(10559).books('Book1').sheets('Sheet1').range('A1')

注意app的值是他们的进程ID(PID),这的确有点与众不同。可以通过 xw.apps.keys() 得到PID列表。

App context manager

If you want to open a new Excel instance via App(), you usually should use App as a context manager as this will make sure that the Excel instance is closed and cleaned up again properly:

with xw.App() as app:
    book = app.books['Book1']

区域索引/切片

区域对象支持索引和切片,下面是一些例子:

>>> rng = xw.Book().sheets[0].range('A1:D5')
>>> rng[0, 0]
 <Range [Workbook1]Sheet1!$A$1>
>>> rng[1]
 <Range [Workbook1]Sheet1!$B$1>
>>> rng[:, 3:]
<Range [Workbook1]Sheet1!$D$1:$D$5>
>>> rng[1:3, 1:3]
<Range [Workbook1]Sheet1!$B$2:$C$3>

区域快捷方式

通过在工作表对象上使用索引和切片标注,提供了引用区域对象的一种快捷方式。 根据传入的是字符串还是索引/切片,分别赋值给 sheet.rangesheet.cells 对象:

>>> sheet = xw.Book().sheets['Sheet1']
>>> sheet['A1']
<Range [Book1]Sheet1!$A$1>
>>> sheet['A1:B5']
<Range [Book1]Sheet1!$A$1:$B$5>
>>> sheet[0, 1]
<Range [Book1]Sheet1!$B$1>
>>> sheet[:10, :10]
<Range [Book1]Sheet1!$A$1:$J$10>

对象层次结构

下面用一个例子来说明xlwings中从app到range的层次顺序和反过来从range到app的层次顺序:

>>> rng = xw.apps[10559].books[0].sheets[0].range('A1')
>>> rng.sheet.book.app
<Excel App 10559>