Python API

Top-level functions

xlwings.view(obj, sheet=None)

Opens a new workbook and displays an object on its first sheet by default. If you provide a sheet object, it will clear the sheet before displaying the object on the existing sheet.

參數:
  • obj (any type with built-in converter) – the object to display, e.g. numbers, strings, lists, numpy arrays, pandas dataframes
  • sheet (Sheet, default None) – Sheet object. If none provided, the first sheet of a new workbook is used.

Examples

>>> import xlwings as xw
>>> import pandas as pd
>>> import numpy as np
>>> df = pd.DataFrame(np.random.rand(10, 4), columns=['a', 'b', 'c', 'd'])
>>> xw.view(df)

0.7.1 版新加入.

Object model

Apps

class xlwings.main.Apps(impl)

A collection of all app objects:

>>> import xlwings as xw
>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
active

Returns the active app.

0.9.0 版新加入.

add()

Creates a new App. The new App becomes the active one. Returns an App object.

count

Returns the number of apps.

0.9.0 版新加入.

keys()

Provides the PIDs of the Excel instances that act as keys in the Apps collection.

0.13.0 版新加入.

App

class xlwings.App(visible=None, spec=None, add_book=True, impl=None)

An app corresponds to an Excel instance. New Excel instances can be fired up like so:

>>> import xlwings as xw
>>> app1 = xw.App()
>>> app2 = xw.App()

An app object is a member of the apps collection:

>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
>>> xw.apps[1668]  # get the available PIDs via xw.apps.keys()
<Excel App 1668>
>>> xw.apps.active
<Excel App 1668>
參數:
  • visible (bool, default None) – Returns or sets a boolean value that determines whether the app is visible. The default leaves the state unchanged or sets visible=True if the object doesn’t exist yet.
  • spec (str, default None) –

    Mac-only, use the full path to the Excel application, e.g. /Applications/Microsoft Office 2011/Microsoft Excel or /Applications/Microsoft Excel

    On Windows, if you want to change the version of Excel that xlwings talks to, go to Control Panel > Programs and Features and Repair the Office version that you want as default.

備註

On Mac, while xlwings allows you to run multiple instances of Excel, it’s a feature that is not officially supported by Excel for Mac: Unlike on Windows, Excel will not ask you to open a read-only version of a file if it is already open in another instance. This means that you need to watch out yourself so that the same file is not being overwritten from different instances.

activate(steal_focus=False)

Activates the Excel app.

參數:steal_focus (bool, default False) – If True, make frontmost application and hand over focus from Python to Excel.

0.9.0 版新加入.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

books

A collection of all Book objects that are currently open.

0.9.0 版新加入.

calculate()

Calculates all open books.

0.3.6 版新加入.

calculation

Returns or sets a calculation value that represents the calculation mode. Modes: 'manual', 'automatic', 'semiautomatic'

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.app.calculation = 'manual'

0.9.0 版更變.

display_alerts

The default value is True. Set this property to False to suppress prompts and alert messages while code is running; when a message requires a response, Excel chooses the default response.

0.9.0 版新加入.

hwnd

Returns the Window handle (Windows-only).

0.9.0 版新加入.

kill()

Forces the Excel app to quit by killing its process.

0.9.0 版新加入.

macro(name)

Runs a Sub or Function in Excel VBA that are not part of a specific workbook but e.g. are part of an add-in.

參數:name (Name of Sub or Function with or without module name, e.g. 'Module1.MyMacro' or 'MyMacro') –

Examples

This VBA function:

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

can be accessed like this:

>>> import xlwings as xw
>>> app = xw.App()
>>> my_sum = app.macro('MySum')
>>> my_sum(1, 2)
3

See also: Book.macro()

0.9.0 版新加入.

pid

Returns the PID of the app.

0.9.0 版新加入.

quit()

Quits the application without saving any workbooks.

0.3.3 版新加入.

range(cell1, cell2=None)

Range object from the active sheet of the active book, see Range().

0.9.0 版新加入.

screen_updating

Turn screen updating off to speed up your script. You won’t be able to see what the script is doing, but it will run faster. Remember to set the screen_updating property back to True when your script ends.

0.3.3 版新加入.

selection

Returns the selected cells as Range.

0.9.0 版新加入.

version

Returns the Excel version number object.

Examples

>>> import xlwings as xw
>>> xw.App().version
VersionNumber('15.24')
>>> xw.apps[10559].version.major
15

0.9.0 版更變.

visible

Gets or sets the visibility of Excel to True or False.

0.3.3 版新加入.

Books

class xlwings.main.Books(impl)

A collection of all book objects:

>>> import xlwings as xw
>>> xw.books  # active app
Books([<Book [Book1]>, <Book [Book2]>])
>>> xw.apps[10559].books  # specific app, get the PIDs via xw.apps.keys()
Books([<Book [Book1]>, <Book [Book2]>])

0.9.0 版新加入.

active

Returns the active Book.

add()

Creates a new Book. The new Book becomes the active Book. Returns a Book object.

open(fullname, 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)

Opens a Book if it is not open yet and returns it. If it is already open, it doesn’t raise an exception but simply returns the Book object.

參數:
  • fullname (str or path-like object) – filename or fully qualified filename, e.g. r'C:\path\to\file.xlsx' or 'file.xlsm'. Without a full path, it looks for the file in the current working directory.
  • Parameters (Other) – see: xlwings.Book()
傳回:

Book

傳回型態:

Book that has been opened.

Book

class xlwings.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)

A book object is a member of the books collection:

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

The easiest way to connect to a book is offered by xw.Book: it looks for the book in all app instances and returns an error, should the same book be open in multiple instances. To connect to a book in the active app instance, use xw.books and to refer to a specific app, use:

>>> app = xw.App()  # or something like xw.apps[10559] for existing apps, get the PIDs via xw.apps.keys()
>>> app.books['Book1']
  xw.Book xw.books
New book xw.Book() xw.books.add()
Unsaved book xw.Book('Book1') xw.books['Book1']
Book by (full)name xw.Book(r'C:/path/to/file.xlsx') xw.books.open(r'C:/path/to/file.xlsx')
參數:
  • fullname (str or path-like object, default None) – Full path or name (incl. xlsx, xlsm etc.) of existing workbook or name of an unsaved workbook. Without a full path, it looks for the file in the current working directory.
  • update_links (bool, default None) – If this argument is omitted, the user is prompted to specify how links will be updated
  • read_only (bool, default False) – True to open workbook in read-only mode
  • format (str) – If opening a text file, this specifies the delimiter character
  • password (str) – Password to open a protected workbook
  • write_res_password (str) – Password to write to a write-reserved workbook
  • ignore_read_only_recommended (bool, default False) – Set to True to mute the read-only recommended message
  • origin (int) – For text files only. Specifies where it originated. Use XlPlatform constants.
  • delimiter (str) – If format argument is 6, this specifies the delimiter.
  • editable (bool, default False) – This option is only for legacy Microsoft Excel 4.0 addins.
  • notify (bool, default False) – Notify the user when a file becomes available If the file cannot be opened in read/write mode.
  • converter (int) – The index of the first file converter to try when opening the file.
  • add_to_mru (bool, default False) – Add this workbook to the list of recently added workbooks.
  • local (bool, default False) – If True, saves files against the language of Excel, otherwise against the language of VBA. Not supported on macOS.
  • corrupt_load (int, default xlNormalLoad) – Can be one of xlNormalLoad, xlRepairFile or xlExtractData. Not supported on macOS.
activate(steal_focus=False)

Activates the book.

參數:steal_focus (bool, default False) – If True, make frontmost window and hand over focus from Python to Excel.
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

app

Returns an app object that represents the creator of the book.

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.:

To be able to easily invoke such code from Python for debugging, use xw.Book.set_mock_caller().

0.3.0 版新加入.

close()

Closes the book without saving it.

0.1.1 版新加入.

fullname

Returns the name of the object, including its path on disk, as a string. Read-only String.

macro(name)

Runs a Sub or Function in Excel VBA.

參數:name (Name of Sub or Function with or without module name, e.g. 'Module1.MyMacro' or 'MyMacro') –

Examples

This VBA function:

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

can be accessed like this:

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

See also: App.macro()

0.7.1 版新加入.

name

Returns the name of the book as str.

names

Returns a names collection that represents all the names in the specified book (including all sheet-specific names).

0.9.0 版更變.

save(path=None)

Saves the Workbook. If a path is being provided, this works like SaveAs() in Excel. If no path is specified and if the file hasn’t been saved previously, it’s being saved in the current working directory with the current filename. Existing files are overwritten without prompting.

參數:path (str or path-like object, default None) – Full path to the workbook

Example

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

0.3.1 版新加入.

selection

Returns the selected cells as Range.

0.9.0 版新加入.

set_mock_caller()

Sets the Excel file which is used to mock xw.Book.caller() when the code is called from Python and not from Excel via RunPython.

Examples

# 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()

0.3.1 版新加入.

sheets

Returns a sheets collection that represents all the sheets in the book.

0.9.0 版新加入.

Sheets

class xlwings.main.Sheets(impl)

A collection of all sheet objects:

>>> import xlwings as xw
>>> xw.sheets  # active book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])
>>> xw.Book('Book1').sheets  # specific book
Sheets([<Sheet [Book1]Sheet1>, <Sheet [Book1]Sheet2>])

0.9.0 版新加入.

active

Returns the active Sheet.

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

Creates a new Sheet and makes it the active sheet.

參數:
  • name (str, default None) – Name of the new sheet. If None, will default to Excel’s default name.
  • before (Sheet, default None) – An object that specifies the sheet before which the new sheet is added.
  • after (Sheet, default None) – An object that specifies the sheet after which the new sheet is added.

Sheet

class xlwings.Sheet(sheet=None, impl=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()

Activates the Sheet and returns it.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

autofit(axis=None)

Autofits the width of either columns, rows or both on a whole Sheet.

參數:axis (string, default None) –
  • To autofit rows, use one of the following: rows or r
  • To autofit columns, use one of the following: columns or c
  • To autofit rows and columns, provide no arguments

Examples

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

0.2.3 版新加入.

book

Returns the Book of the specified Sheet. Read-only.

cells

Returns a Range object that represents all the cells on the Sheet (not just the cells that are currently in use).

0.9.0 版新加入.

charts

See Charts

0.9.0 版新加入.

clear()

Clears the content and formatting of the whole sheet.

clear_contents()

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

delete()

Deletes the Sheet.

index

Returns the index of the Sheet (1-based as in Excel).

name

Gets or sets the name of the Sheet.

names

Returns a names collection that represents all the sheet-specific names (names defined with the 「SheetName!」 prefix).

0.9.0 版新加入.

pictures

See Pictures

0.9.0 版新加入.

range(cell1, cell2=None)

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

0.9.0 版新加入.

select()

Selects the Sheet. Select only works on the active book.

0.9.0 版新加入.

shapes

See Shapes

0.9.0 版新加入.

used_range

Used Range of Sheet.

傳回:
傳回型態:xw.Range

0.13.0 版新加入.

Range

class xlwings.Range(cell1=None, cell2=None, **options)

Returns a Range object that represents a cell or a range of cells.

參數:
  • cell1 (str or tuple or Range) – Name of the range in the upper-left corner in A1 notation or as index-tuple or as name or as xw.Range object. It can also specify a range using the range operator (a colon), .e.g. 『A1:B2』
  • cell2 (str or tuple or Range, default None) – Name of the range in the lower-right corner in A1 notation or as index-tuple or as name or as xw.Range object.

Examples

Active Sheet:

import xlwings as xw
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'))

Specific Sheet:

xw.books['MyBook.xlsx'].sheets[0].range('A1')

Adds a hyperlink to the specified Range (single Cell)

參數:
  • address (str) – The address of the hyperlink.
  • text_to_display (str, default None) – The text to be displayed for the hyperlink. Defaults to the hyperlink address.
  • screen_tip (str, default None) – The screen tip to be displayed when the mouse pointer is paused over the hyperlink. Default is set to 『<address> - Click once to follow. Click and hold to select this cell.』

0.3.0 版新加入.

address

Returns a string value that represents the range reference. Use get_address() to be able to provide paramaters.

0.9.0 版新加入.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

autofit()

Autofits the width and height of all cells in the range.

  • To autofit only the width of the columns use xw.Range('A1:B2').columns.autofit()
  • To autofit only the height of the rows use xw.Range('A1:B2').rows.autofit()

0.9.0 版更變.

clear()

Clears the content and the formatting of a Range.

clear_contents()

Clears the content of a Range but leaves the formatting.

color

Gets and sets the background color of the specified Range.

To set the color, either use an RGB tuple (0, 0, 0) or a color constant. To remove the background, set the color to None, see Examples.

傳回:RGB
傳回型態:tuple

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').color = (255,255,255)
>>> xw.Range('A2').color
(255, 255, 255)
>>> xw.Range('A2').color = None
>>> xw.Range('A2').color is None
True

0.3.0 版新加入.

column

Returns the number of the first column in the in the specified range. Read-only.

傳回:
傳回型態:Integer

0.3.5 版新加入.

column_width

Gets or sets the width, in characters, of a Range. One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

If all columns in the Range have the same width, returns the width. If columns in the Range have different widths, returns None.

column_width must be in the range: 0 <= column_width <= 255

Note: If the Range is outside the used range of the Worksheet, and columns in the Range have different widths, returns the width of the first column.

傳回:
傳回型態:float

0.4.0 版新加入.

columns

Returns a RangeColumns object that represents the columns in the specified range.

0.9.0 版新加入.

copy(destination=None)

Copy a range to a destination range or clipboard.

參數:destination (xlwings.Range) – xlwings Range to which the specified range will be copied. If omitted, the range is copied to the Clipboard.
傳回:
傳回型態:None
count

Returns the number of cells.

current_region

This property returns a Range object representing a range bounded by (but not including) any combination of blank rows and blank columns or the edges of the worksheet. It corresponds to Ctrl-* on Windows and Shift-Ctrl-Space on Mac.

傳回:
傳回型態:Range object
delete(shift=None)

Deletes a cell or range of cells.

參數:shift (str, default None) – Use left or up. If omitted, Excel decides based on the shape of the range.
傳回:
傳回型態:None
end(direction)

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing Ctrl+Up, Ctrl+down, Ctrl+left, or Ctrl+right.

參數:direction (One of 'up', 'down', 'right', 'left') –

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1:B2').value = 1
>>> xw.Range('A1').end('down')
<Range [Book1]Sheet1!$A$2>
>>> xw.Range('B2').end('right')
<Range [Book1]Sheet1!$B$2>

0.9.0 版新加入.

expand(mode='table')

Expands the range according to the mode provided. Ignores empty top-left cells (unlike Range.end()).

參數:mode (str, default 'table') – One of 'table' (=down and right), 'down', 'right'.
傳回:
傳回型態:Range

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').value = [[None, 1], [2, 3]]
>>> xw.Range('A1').expand().address
$A$1:$B$2
>>> xw.Range('A1').expand('right').address
$A$1:$B$1

0.9.0 版新加入.

formula

Gets or sets the formula for the given Range.

formula_array

Gets or sets an array formula for the given Range.

0.7.1 版新加入.

get_address(row_absolute=True, column_absolute=True, include_sheetname=False, external=False)

Returns the address of the range in the specified format. address can be used instead if none of the defaults need to be changed.

參數:
  • row_absolute (bool, default True) – Set to True to return the row part of the reference as an absolute reference.
  • column_absolute (bool, default True) – Set to True to return the column part of the reference as an absolute reference.
  • include_sheetname (bool, default False) – Set to True to include the Sheet name in the address. Ignored if external=True.
  • external (bool, default False) – Set to True to return an external reference with workbook and worksheet name.
傳回:

傳回型態:

str

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range((1,1)).get_address()
'$A$1'
>>> xw.Range((1,1)).get_address(False, False)
'A1'
>>> xw.Range((1,1), (3,3)).get_address(True, False, True)
'Sheet1!A$1:C$3'
>>> xw.Range((1,1), (3,3)).get_address(True, False, external=True)
'[Book1]Sheet1!A$1:C$3'

0.2.3 版新加入.

height

Returns the height, in points, of a Range. Read-only.

傳回:
傳回型態:float

0.4.0 版新加入.

Returns the hyperlink address of the specified Range (single Cell only)

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').value
'www.xlwings.org'
>>> xw.Range('A1').hyperlink
'http://www.xlwings.org'

0.3.0 版新加入.

insert(shift=None, copy_origin='format_from_left_or_above')

Insert a cell or range of cells into the sheet.

參數:
  • shift (str, default None) – Use right or down. If omitted, Excel decides based on the shape of the range.
  • copy_origin (str, default format_from_left_or_above) – Use format_from_left_or_above or format_from_right_or_below. Note that this is not supported on macOS.
傳回:

傳回型態:

None

last_cell

Returns the bottom right cell of the specified range. Read-only.

傳回:
傳回型態:Range

Example

>>> import xlwings as xw
>>> wb = xw.Book()
>>> rng = xw.Range('A1:E4')
>>> rng.last_cell.row, rng.last_cell.column
(4, 5)

0.3.5 版新加入.

left

Returns the distance, in points, from the left edge of column A to the left edge of the range. Read-only.

傳回:
傳回型態:float

0.6.0 版新加入.

merge(across=False)

Creates a merged cell from the specified Range object.

參數:across (bool, default False) – True to merge cells in each row of the specified Range as separate merged cells.
merge_area

Returns a Range object that represents the merged Range containing the specified cell. If the specified cell isn’t in a merged range, this property returns the specified cell.

merge_cells

Returns True if the Range contains merged cells, otherwise False

name

Sets or gets the name of a Range.

0.4.0 版新加入.

number_format

Gets and sets the number_format of a Range.

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> xw.Range('A1').number_format
'General'
>>> xw.Range('A1:C3').number_format = '0.00%'
>>> xw.Range('A1:C3').number_format
'0.00%'

0.2.3 版新加入.

offset(row_offset=0, column_offset=0)

Returns a Range object that represents a Range that’s offset from the specified range.

傳回:Range object
傳回型態:Range

0.3.0 版新加入.

options(convert=None, **options)

Allows you to set a converter and their options. Converters define how Excel Ranges and their values are being converted both during reading and writing operations. If no explicit converter is specified, the base converter is being applied, see Converters and Options.

參數:

convert (object, default None) – A converter, e.g. dict, np.array, pd.DataFrame, pd.Series, defaults to default converter

關鍵字引數:
  • ndim (int, default None) – number of dimensions
  • numbers (type, default None) – type of numbers, e.g. int
  • dates (type, default None) – e.g. datetime.date defaults to datetime.datetime
  • empty (object, default None) – transformation of empty cells
  • transpose (Boolean, default False) – transpose values
  • expand (str, default None) –

    One of 'table', 'down', 'right'

    => For converter-specific options, see Converters and Options.

傳回:

傳回型態:

Range object

0.7.0 版新加入.

paste(paste=None, operation=None, skip_blanks=False, transpose=False)

Pastes a range from the clipboard into the specified range.

參數:
  • paste (str, default None) – One of all_merging_conditional_formats, all, all_except_borders, all_using_source_theme, column_widths, comments, formats, formulas, formulas_and_number_formats, validation, values, values_and_number_formats.
  • operation (str, default None) – One of 「add」, 「divide」, 「multiply」, 「subtract」.
  • skip_blanks (bool, default False) – Set to True to skip over blank cells
  • transpose (bool, default False) – Set to True to transpose rows and columns.
傳回:

傳回型態:

None

raw_value

Gets and sets the values directly as delivered from/accepted by the engine that is being used (pywin32 or appscript) without going through any of xlwings』 data cleaning/converting. This can be helpful if speed is an issue but naturally will be engine specific, i.e. might remove the cross-platform compatibility.

resize(row_size=None, column_size=None)

Resizes the specified Range

參數:
  • row_size (int > 0) – The number of rows in the new range (if None, the number of rows in the range is unchanged).
  • column_size (int > 0) – The number of columns in the new range (if None, the number of columns in the range is unchanged).
傳回:

Range object

傳回型態:

Range

0.3.0 版新加入.

row

Returns the number of the first row in the specified range. Read-only.

傳回:
傳回型態:Integer

0.3.5 版新加入.

row_height

Gets or sets the height, in points, of a Range. If all rows in the Range have the same height, returns the height. If rows in the Range have different heights, returns None.

row_height must be in the range: 0 <= row_height <= 409.5

Note: If the Range is outside the used range of the Worksheet, and rows in the Range have different heights, returns the height of the first row.

傳回:
傳回型態:float

0.4.0 版新加入.

rows

Returns a RangeRows object that represents the rows in the specified range.

0.9.0 版新加入.

select()

Selects the range. Select only works on the active book.

0.9.0 版新加入.

shape

Tuple of Range dimensions.

0.3.0 版新加入.

sheet

Returns the Sheet object to which the Range belongs.

0.9.0 版新加入.

size

Number of elements in the Range.

0.3.0 版新加入.

top

Returns the distance, in points, from the top edge of row 1 to the top edge of the range. Read-only.

傳回:
傳回型態:float

0.6.0 版新加入.

unmerge()

Separates a merged area into individual cells.

value

Gets and sets the values for the given Range.

傳回:object
傳回型態:returned object depends on the converter being used, see xlwings.Range.options()
width

Returns the width, in points, of a Range. Read-only.

傳回:
傳回型態:float

0.4.0 版新加入.

RangeRows

class xlwings.RangeRows(rng)

Represents the rows of a range. Do not construct this class directly, use Range.rows instead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.rows) == 4  # or rng.rows.count

rng.rows[0].value = 'a'

assert rng.rows[2] == xw.Range('A3:C3')
assert rng.rows(2) == xw.Range('A2:C2')

for r in rng.rows:
    print(r.address)
autofit()

Autofits the height of the rows.

count

Returns the number of rows.

0.9.0 版新加入.

RangeColumns

class xlwings.RangeColumns(rng)

Represents the columns of a range. Do not construct this class directly, use Range.columns instead.

Example

import xlwings as xw

rng = xw.Range('A1:C4')

assert len(rng.columns) == 3  # or rng.columns.count

rng.columns[0].value = 'a'

assert rng.columns[2] == xw.Range('C1:C4')
assert rng.columns(2) == xw.Range('B1:B4')

for c in rng.columns:
    print(c.address)
autofit()

Autofits the width of the columns.

count

Returns the number of columns.

0.9.0 版新加入.

Shapes

class xlwings.main.Shapes(impl)

A collection of all shape objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].shapes
Shapes([<Shape 'Oval 1' in <Sheet [Book1]Sheet1>>, <Shape 'Rectangle 1' in <Sheet [Book1]Sheet1>>])

0.9.0 版新加入.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Shape

class xlwings.Shape(*args, **options)

The shape object is a member of the shapes collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.shapes[0]  # or sht.shapes['ShapeName']
<Shape 'Rectangle 1' in <Sheet [Book1]Sheet1>>

0.9.0 版更變.

activate()

Activates the shape.

0.5.0 版新加入.

delete()

Deletes the shape.

0.5.0 版新加入.

height

Returns or sets the number of points that represent the height of the shape.

0.5.0 版新加入.

left

Returns or sets the number of points that represent the horizontal position of the shape.

0.5.0 版新加入.

name

Returns or sets the name of the shape.

0.5.0 版新加入.

parent

Returns the parent of the shape.

0.9.0 版新加入.

top

Returns or sets the number of points that represent the vertical position of the shape.

0.5.0 版新加入.

type

Returns the type of the shape.

0.9.0 版新加入.

width

Returns or sets the number of points that represent the width of the shape.

0.5.0 版新加入.

Charts

class xlwings.main.Charts(impl)

A collection of all chart objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].charts
Charts([<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>, <Chart 'Chart 1' in <Sheet [Book1]Sheet1>>])

0.9.0 版新加入.

add(left=0, top=0, width=355, height=211)

Creates a new chart on the specified sheet.

參數:
  • left (float, default 0) – left position in points
  • top (float, default 0) – top position in points
  • width (float, default 355) – width in points
  • height (float, default 211) – height in points
傳回:

傳回型態:

Chart

Examples

>>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.range('A1').value = [['Foo1', 'Foo2'], [1, 2]]
>>> chart = sht.charts.add()
>>> chart.set_source_data(sht.range('A1').expand())
>>> chart.chart_type = 'line'
>>> chart.name
'Chart1'
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Chart

class xlwings.Chart(name_or_index=None, impl=None)

The chart object is a member of the charts collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.charts[0]  # or sht.charts['ChartName']
<Chart 'Chart 1' in <Sheet [Book1]Sheet1>>
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

chart_type

Returns and sets the chart type of the chart.

0.1.1 版新加入.

delete()

Deletes the chart.

height

Returns or sets the number of points that represent the height of the chart.

left

Returns or sets the number of points that represent the horizontal position of the chart.

name

Returns or sets the name of the chart.

parent

Returns the parent of the chart.

0.9.0 版新加入.

set_source_data(source)

Sets the source data range for the chart.

參數:source (Range) – Range object, e.g. xw.books['Book1'].sheets[0].range('A1')
top

Returns or sets the number of points that represent the vertical position of the chart.

width

Returns or sets the number of points that represent the width of the chart.

Pictures

class xlwings.main.Pictures(impl)

A collection of all picture objects on the specified sheet:

>>> import xlwings as xw
>>> xw.books['Book1'].sheets[0].pictures
Pictures([<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>, <Picture 'Picture 2' in <Sheet [Book1]Sheet1>>])

0.9.0 版新加入.

add(image, link_to_file=False, save_with_document=True, left=0, top=0, width=None, height=None, name=None, update=False)

Adds a picture to the specified sheet.

參數:
  • image (str or path-like object or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
  • left (float, default 0) – Left position in points.
  • top (float, default 0) – Top position in points.
  • width (float, default None) – Width in points. If PIL/Pillow is installed, it defaults to the width of the picture. Otherwise it defaults to 100 points.
  • height (float, default None) – Height in points. If PIL/Pillow is installed, it defaults to the height of the picture. Otherwise it defaults to 100 points.
  • name (str, default None) – Excel picture name. Defaults to Excel standard name if not provided, e.g. 『Picture 1』.
  • update (bool, default False) – Replace an existing picture with the same name. Requires name to be set.
傳回:

傳回型態:

Picture

Examples

  1. Picture
>>> import xlwings as xw
>>> sht = xw.Book().sheets[0]
>>> sht.pictures.add(r'C:\path\to\file.jpg')
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>
  1. Matplotlib
>>> import matplotlib.pyplot as plt
>>> fig = plt.figure()
>>> plt.plot([1, 2, 3, 4, 5])
>>> sht.pictures.add(fig, name='MyPlot', update=True)
<Picture 'MyPlot' in <Sheet [Book1]Sheet1>>
api

Returns the native object (pywin32 or appscript obj) of the engine being used.

count

Returns the number of objects in the collection.

Picture

class xlwings.Picture(impl=None)

The picture object is a member of the pictures collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.pictures[0]  # or sht.charts['PictureName']
<Picture 'Picture 1' in <Sheet [Book1]Sheet1>>

0.9.0 版更變.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

delete()

Deletes the picture.

0.5.0 版新加入.

height

Returns or sets the number of points that represent the height of the picture.

0.5.0 版新加入.

left

Returns or sets the number of points that represent the horizontal position of the picture.

0.5.0 版新加入.

name

Returns or sets the name of the picture.

0.5.0 版新加入.

parent

Returns the parent of the picture.

0.9.0 版新加入.

top

Returns or sets the number of points that represent the vertical position of the picture.

0.5.0 版新加入.

update(image)

Replaces an existing picture with a new one, taking over the attributes of the existing picture.

參數:image (str or path-like object or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.

0.5.0 版新加入.

width

Returns or sets the number of points that represent the width of the picture.

0.5.0 版新加入.

Names

class xlwings.main.Names(impl)

A collection of all name objects in the workbook:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.names
[<Name 'MyName': =Sheet1!$A$3>]

0.9.0 版新加入.

add(name, refers_to)

Defines a new name for a range of cells.

參數:
  • name (str) – Specifies the text to use as the name. Names cannot include spaces and cannot be formatted as cell references.
  • refers_to (str) – Describes what the name refers to, in English, using A1-style notation.
傳回:

傳回型態:

Name

0.9.0 版新加入.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

count

Returns the number of objects in the collection.

Name

class xlwings.Name(impl)

The name object is a member of the names collection:

>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.names[0]  # or sht.names['MyName']
<Name 'MyName': =Sheet1!$A$3>

0.9.0 版新加入.

api

Returns the native object (pywin32 or appscript obj) of the engine being used.

0.9.0 版新加入.

delete()

Deletes the name.

0.9.0 版新加入.

name

Returns or sets the name of the name object.

0.9.0 版新加入.

refers_to

Returns or sets the formula that the name is defined to refer to, in A1-style notation, beginning with an equal sign.

0.9.0 版新加入.

refers_to_range

Returns the Range object referred to by a Name object.

0.9.0 版新加入.

UDF decorators

xlwings.func(category="xlwings", volatile=False, call_in_wizard=True)

Functions decorated with xlwings.func will be imported as Function to Excel when running 「Import Python UDFs」.

category : int or str, default 「xlwings」

1-14 represent built-in categories, for user-defined categories use strings

0.10.3 版新加入.

volatile : bool, default False

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it’s not inside a user-defined function used to calculate a worksheet cell.

0.10.3 版新加入.

call_in_wizard : bool, default True

Set to False to suppress the function call in the function wizard.

0.10.3 版新加入.

xlwings.sub()

Functions decorated with xlwings.sub will be imported as Sub (i.e. macro) to Excel when running 「Import Python UDFs」.

xlwings.arg(arg, convert=None, **options)

Apply converters and options to arguments, see also Range.options().

Examples:

Convert x into a 2-dimensional numpy array:

import xlwings as xw
import numpy as np

@xw.func
@xw.arg('x', np.array, ndim=2)
def add_one(x):
    return x + 1
xlwings.ret(convert=None, **options)

Apply converters and options to return values, see also Range.options().

Examples

  1. Suppress the index and header of a returned DataFrame:
import pandas as pd

@xw.func
@xw.ret(index=False, header=False)
def get_dataframe(n, m):
    return pd.DataFrame(np.arange(n * m).reshape((n, m)))
  1. Dynamic array:

expand='table' turns the UDF into a dynamic array. Currently you must not use volatile functions as arguments of a dynamic array, e.g. you cannot use =TODAY() as part of a dynamic array. Also note that a dynamic array needs an empty row and column at the bottom and to the right and will overwrite existing data without warning.

Unlike standard Excel arrays, dynamic arrays are being used from a single cell like a standard function and auto-expand depending on the dimensions of the returned array:

import xlwings as xw
import numpy as np

@xw.func
@xw.ret(expand='table')
def dynamic_array(n, m):
    return np.arange(n * m).reshape((n, m))

0.10.0 版新加入.