Book¶

class Book(fullname=None, update_links=None, read_only=None, format=None, password=None, write_res_password=None, ignore_read_only_recommended=None, origin=None, delimiter=None, editable=None, notify=None, converter=None, add_to_mru=None, local=None, corrupt_load=None, impl=None, json=None, mode=None, engine=None)¶

一个book对象是books集合中的一个成员:

>>> import xlwings as xw
>>> xw.books[0]
<Book [Book1]>

连接工作簿的最容易方法是由 xw.Book 提供的: 它在所有的app实例中查找需要的book对象。如果碰到同样的book在多个实例中被打开就返回一个错误。连接到活动app实例中的一个工作簿对象用 xw.books 如果要特别指定具体的app, 可以用下列方法:

>>> app = xw.App()  # or xw.apps[10559] (get the PIDs via xw.apps.keys())
>>> app.books['Book1']

	xw.Book	xw.books
新建工作簿	`xw.Book()`	`xw.books.add()`
未保存的工作簿	`xw.Book('Book1')`	`xw.books['Book1']`
带有全路径名的工作簿	`xw.Book(r'C:/path/to/file.xlsx')`	`xw.books.open(r'C:/path/to/file.xlsx')`

参数¶

fullnamestr or path-like object, default None: 已经存在的工作簿的文件名或全路径名(包括后缀 xlsx , xlsm 等)或者还未保存的工作簿的名字。没有全路径时，在当前工作目录中寻找该文件。
update_linksbool, default None: 如果这个参数被省略，会提示用户确定更新链接的方式。
read_onlybool类型, 缺省值为False: 值为 True 时以只读方式打开工作簿
formatstr: 如果打开的是文本文件，本参数指明分隔符。
passwordstr: 用于打开工作簿的密码。
write_res_passwordstr: 用于向工作簿写入数据时的密码。
ignore_read_only_recommendedbool类型, 缺省值为False: 设置为 True 时会关闭推荐只读的提示
originint: For text files only. Specifies where it originated. Use Platform constants.
delimiterstr: 如果参数format的值是6，这个参数指明分隔符。
editablebool类型, 缺省值为False: 这个选择只用于老的Excel4.0的加载项。
notifybool类型, 缺省值为False: 当一个文件暂时无法在读写模式中打开，在文件可以打开的时候提示用户。
converterint: 打开文件时，首先尝试使用的文件转换器对应的索引
add_to_mrubool类型, 缺省值为False: 把这个工作簿加到最近增加的工作簿列表中。
localbool类型, 缺省值为False: If True, saves files against the language of Excel, otherwise against the language of VBA. Not supported on macOS.
corrupt_loadint, default xlNormalLoad: 可以是xlNormalLoad、xlRepairFile或xlExtractData中的一个，在macOS上不支持此参数。
jsondict: A JSON object as delivered by the MS Office Scripts or Google Apps Script xlwings module but in a deserialized form, i.e., as dictionary.

Added in version 0.26.0.
modestr, default None: Either "i" (interactive (default)) or "r" (read). In interactive mode, xlwings opens the workbook in Excel, i.e., Excel needs to be installed. In read mode, xlwings reads from the file directly, without requiring Excel to be installed. Read mode requires xlwings PRO.

Added in version 0.28.0.

activate(steal_focus=False)¶

激活工作簿

参数¶

steal_focusbool类型, 缺省值为False: 如果是True, 把窗口显示到最上层，并且把焦点从Python切换到Excel。

property api¶: 返回正在使用的引擎的原生对象( pywin32 或 appscript 对象)。

Added in version 0.9.0.

property app¶: 返回创建工作簿的app对象。

Added in version 0.9.0.

classmethod caller()¶

References the calling book when the Python function is called from Excel via RunPython. Pack it into the function being called from Excel, e.g.:

import xlwings as xw

 def my_macro():
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = 1

为了能够从Python中唤醒以便调试, 可以使用 xw.Book.set_mock_caller() 。

Added in version 0.3.0.

close()¶: 放弃保存直接关闭工作簿。

Added in version 0.1.1.

property fullname¶: 返回对象名称字符串，包括在磁盘上的路径。是只读字符串。

json()¶: Returns a JSON serializable object as expected by the MS Office Scripts or Google Apps Script xlwings module. Only available with book objects that have been instantiated via xw.Book(json=...).

Added in version 0.26.0.

macro(name)¶

在Excel VBA中运行一个过程或者函数。

Arguments¶

name : Name of Sub or Function with or without module name, e.g., 'Module1.MyMacro' or 'MyMacro'

示例¶

下面这个VBA函数:

Function MySum(x, y)
    MySum = x + y
End Function

可以这样调用:

>>> import xlwings as xw
>>> wb = xw.books.active
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3

参考： App.macro()

Added in version 0.7.1.

property name¶: 返回工作簿名称字符串。

property names¶: 返回一个指定工作簿中的定义过的所有命名区域的集合，包括那些和特定工作表有关的命名区域。

在 0.9.0 版本发生变更.

render_template(**data)¶

This method requires xlwings PRO.

Replaces all Jinja variables (e.g {{ myvar }}) in the book with the keyword argument of the same name.

Added in version 0.25.0.

参数¶

data: kwargs: All key/value pairs that are used in the template.

示例¶

>>> import xlwings as xw
>>> book = xw.Book()
>>> book.sheets[0]['A1:A2'].value = '{{ myvar }}'
>>> book.render_template(myvar='test')

save(path=None, password=None)¶

Saves the Workbook. If a path is provided, this works like SaveAs() in Excel. If no path is specified and if the file hasn’t been saved previously, it’s saved in the current working directory with the current filename. Existing files are overwritten without prompting. To change the file type, provide the appropriate extension, e.g. to save myfile.xlsx in the xlsb format, provide myfile.xlsb as path.

Arguments¶

pathstr or path-like object, default None: Path where you want to save the Book.
passwordstr, default None: Protection password with max. 15 characters

Added in version 0.25.1.

示例¶

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.save()
>>> wb.save(r'C:\path\to\new_file_name.xlsx')

Added in version 0.3.1.

property selection¶: 把选中的单元格作为区域返回。

Added in version 0.9.0.

set_mock_caller()¶

当源代码是直接从Pyhton调用而不是从Excel中通过RunPyton调用的时候，设置一个文件，模仿从这个文件中用 xw.Book.caller() 调用这些源代码。

示例¶

# This code runs unchanged from Excel via RunPython and from Python directly
import os
import xlwings as xw

def my_macro():
    sht = xw.Book.caller().sheets[0]
    sht.range('A1').value = 'Hello xlwings!'

if __name__ == '__main__':
    xw.Book('file.xlsm').set_mock_caller()
    my_macro()

Added in version 0.3.1.

property sheet_names¶

返回¶

sheet_namesList: List of sheet names in order of appearance.

Added in version 0.28.1.

property sheets¶: 返回工作簿中所有工作表的集合。

Added in version 0.9.0.

to_pdf(path=None, include=None, exclude=None, layout=None, exclude_start_string='#', show=False, quality='standard')¶

Exports the whole Excel workbook or a subset of the sheets to a PDF file. If you want to print hidden sheets, you will need to list them explicitely under include.

参数¶

pathstr or path-like object, default None

Path to the PDF file, defaults to the same name as the workbook, in the same directory. For unsaved workbooks, it defaults to the current working directory instead.

includeint or str or list, default None

Which sheets to include: provide a selection of sheets in the form of sheet indices (1-based like in Excel) or sheet names. Can be an int/str for a single sheet or a list of int/str for multiple sheets.

excludeint or str or list, default None

Which sheets to exclude: provide a selection of sheets in the form of sheet indices (1-based like in Excel) or sheet names. Can be an int/str for a single sheet or a list of int/str for multiple sheets.

layoutstr or path-like object, default None

This argument requires xlwings PRO.

Path to a PDF file on which the report will be printed. This is ideal for headers and footers as well as borderless printing of graphics/artwork. The PDF file either needs to have only 1 page (every report page uses the same layout) or otherwise needs the same amount of pages as the report (each report page is printed on the respective page in the layout PDF).

Added in version 0.24.3.

exclude_start_stringstr, default '#'

Sheet names that start with this character/string will not be printed.

Added in version 0.24.4.

showbool类型, 缺省值为False

Once created, open the PDF file with the default application.

Added in version 0.24.6.

qualitystr, default 'standard'

Quality of the PDF file. Can either be 'standard' or 'minimum'.

Added in version 0.26.2.

示例¶

>>> wb = xw.Book()
>>> wb.sheets[0]['A1'].value = 'PDF'
>>> wb.to_pdf()