Sheet

class Sheet(sheet=None)

A sheet object is a member of the sheets collection:

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets[0]
<Sheet [Book1]Sheet1>
>>> wb.sheets['Sheet1']
<Sheet [Book1]Sheet1>
>>> wb.sheets.add()
<Sheet [Book1]Sheet2>

在 0.9.0 版本发生变更.

activate()

激活sheet并把它返回。

property api: Any: Returns the native object (pywin32 or appscript obj) of the engine being used.

在 0.9.0 版本加入.

autofit(axis=None)

在整个工作表中对行、列或者两者同时根据内容进行自适应。

参数:: axis (str | None) -- To autofit rows, use "rows" or "r". To autofit columns, use "columns" or "c". To autofit rows and columns, provide no arguments.

示例

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets['Sheet1'].autofit('c')
>>> wb.sheets['Sheet1'].autofit('r')
>>> wb.sheets['Sheet1'].autofit()

在 0.2.3 版本加入.

property book: Book: 返回指定工作表所属的工作簿。只读。

property cells: Range: 返回一个代表工作表上所有单元格的区域对象(不仅仅是那些正在使用中的单元格)。

在 0.9.0 版本加入.

property charts: Charts: See Charts

在 0.9.0 版本加入.

clear()

重新格式化工作表，清除所有内容及格式。

clear_contents()

清除工作表的所有内容但是保留原有格式。

clear_formats()

Clears the format of the whole sheet but leaves the content.

在 0.26.2 版本加入.

property comments: Comments: Threaded comments on this worksheet. In xlwings Lite, use await get_comments().

copy(before=None, after=None, name=None)

Copy a sheet to the current or a new Book. By default, it places the copied sheet after all existing sheets in the current Book. Returns the copied sheet.

在 0.22.0 版本加入.

参数:

before (Sheet | None) -- The sheet object before which you want to place the sheet
after (Sheet | None) -- The sheet object after which you want to place the sheet, by default it is placed after all existing sheets
name (str | None) -- The sheet name of the copy

返回类型:

Sheet

示例

# Create two books and add a value to the first sheet of the first book
first_book = xw.Book()
second_book = xw.Book()
first_book.sheets[0]['A1'].value = 'some value'

# Copy to same Book with the default location and name
first_book.sheets[0].copy()

# Copy to same Book with custom sheet name
first_book.sheets[0].copy(name='copied')

# Copy to second Book requires to use before or after
first_book.sheets[0].copy(after=second_book.sheets[0])

delete()

删除工作表。

在 0.6.0 版本加入.

property freeze_panes: FreezePanes

Interface to freeze/unfreeze panes.

示例

>>> mysheet.freeze_panes.freeze_at("A1")
>>> mysheet.freeze_panes.freeze_at(mysheet["A1"])
>>> mysheet.freeze_panes.freeze_at("A:A")
>>> mysheet.freeze_panes.freeze_at("1:1")
>>> mysheet.freeze_panes.unfreeze()

async get_comments()

Fetch this worksheet's threaded comments.

Requires xlwings Lite.

返回类型:: Comments

async get_used_range(values_only=False)

Returns the used range fetched from the current worksheet state, or None if the worksheet is empty according to values_only.

参数:: values_only (bool) -- If True, only cells with values count as used. If False, cells with values or formatting count as used.
返回类型:: Range | None

Unlike this method, used_range is a values-only snapshot in xlwings Lite and returns A1 for an empty worksheet for backward compatibility.

Requires xlwings Lite.

在 0.37.5 版本加入.

property index: int: 返回工作表的索引值(按照Excel的方式，从1开始的)。

async load(values=None)

(Re)loads the sheet's data from Excel on demand.

Like Book.load, this loads only metadata by default on an async book and everything (including values) on a regular book. Pass values=True to also load this sheet's cell values.

Requires xlwings Lite.

返回类型:: Sheet

move(before=None, after=None)

Move a sheet within its current Book.

Provide exactly one of before or after. Both the sheet being moved and the target sheet must belong to the same Book.

参数:

before (Sheet | None) -- The sheet before which you want to place this sheet.
after (Sheet | None) -- The sheet after which you want to place this sheet.

示例

book.sheets["Sheet3"].move(after=book.sheets["Sheet1"])

在 0.37.5 版本加入.

property name: str: 取得或者设置工作表的名字。

property names: Names: 返回所有名字与本工作表有关的命名区域的集合(名字定义中包含"SheetName!" (工作表名!)前缀)。

在 0.9.0 版本加入.

property notes: Notes: Notes on this worksheet, indexed by cell address or position.

property page_setup: PageSetup: Returns a PageSetup object.

在 0.24.2 版本加入.

property pictures: Pictures: See Pictures

在 0.9.0 版本加入.

property pivot_tables: PivotTables: See PivotTables

在 0.37.3 版本加入.

range(cell1, cell2=None)

Returns a Range object from the active sheet of the active book, see Range.

在 0.9.0 版本加入.

返回类型:: Range

render_template(**data)

This method requires xlwings PRO.

Replaces all Jinja variables (e.g {{ myvar }}) in the sheet with the keyword argument that has the same name. Following variable types are supported:

strings, numbers, lists, simple dicts, NumPy arrays, Pandas DataFrames, PIL Image objects that have a filename and Matplotlib figures.

在 0.22.0 版本加入.

参数:: data (Any) -- 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.sheets[0].render_template(myvar='test')

select()

Selects the Sheet. Activates the book if it isn't the active one.

在 0.9.0 版本加入.

property shapes: Shapes: See Shapes

在 0.9.0 版本加入.

property show_gridlines: bool

Gets or sets whether the sheet displays gridlines. This only affects what is shown on screen, not what is printed.

With classic xlwings (Python running locally), the sheet must be (temporarily) activated by xlwings, which means that you can't use this with hidden sheets. xlwings Server and xlwings Lite don't have this restriction.

示例

>>> mysheet.show_gridlines = False

在 0.37.3 版本加入.

property tables: Tables: See Tables

在 0.21.0 版本加入.

to_html(path=None)

Export a Sheet as HTML page.

参数:: path (str | PathLike[str] | None) -- Path where you want to save the HTML file. Defaults to Sheet name in the current working directory.

在 0.28.1 版本加入.

to_pdf(path=None, layout=None, show=False, quality='standard')

Exports the sheet to a PDF file.

参数:

path (str | PathLike[str] | None) -- Path to the PDF file, defaults to the name of the sheet in the same directory of the workbook. For unsaved workbooks, it defaults to the current working directory instead.
layout (str | PathLike[str] | 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). New in version 0.24.3.
show (bool) -- Once created, open the PDF file with the default application. New in version 0.24.6.
quality (str) -- Quality of the PDF file. Can either be 'standard' or 'minimum'. New in version 0.26.2.

返回类型:

str

示例

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