# Python API
## 頂層函數
`xlwings.view`(*obj*, *sheet=None*)
默認情況下，打開新工作簿并在其第一個工作表上顯示對象。 如果提供工作表對象，它將在現有工作表上顯示對象之前清除工作表。
參數：obj（具有內置轉換器的任何類型） - 要顯示的對象，例如 數字，字符串，列表，numpy數組，pandas dataframessheet（工作表，默認無） - 工作表對象。 如果未提供，則使用新工作簿的第一張表。
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中的新功能。
## 對象模型
### Apps
*class* `xlwings.main.``Apps`(*impl*)
所有`app`對象的集合：
~~~
>>> import xlwings as xw
>>> xw.apps
Apps([<Excel App 1668>, <Excel App 1644>])
~~~
`active`
返回活動的應用程序。
0.9.0版中的新功能。
`add`()
創建一個新的應用程序。 新的應用程序成為活動的應用程序。 返回一個App對象。
`count`
返回應用數量。
0.9.0版中的新功能。
`keys`()
提供用作應用程序集合中的鍵的Excel實例的PID。
0.13.0版中的新功能。
### App
*class* `xlwings.``App`(*visible=None*, *spec=None*, *add\_book=True*, *impl=None*)
應用程序對應于Excel實例。 可以像這樣啟動新的Excel實例：
~~~
>>> import xlwings as xw
>>> app1 = xw.App()
>>> app2 = xw.App()
~~~
app對象是[`apps`]()集合的成員：
~~~
>>> 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） - 返回或設置一個布爾值，用于確定應用程序是否可見。 默認情況下保持狀態不變或如果對象不存在則設置visible = True.spec（str，默認為None）-Mac-only，使用Excel應用程序的完整路徑，例如， / Applications / Microsoft Office 2011 / Microsoft Excel或/ Applications / Microsoft ExcelOn Windows，如果要更改xlwings與之對話的Excel版本，請轉到“控制面板”>“程序和功能”并修復默認情況下所需的Office版本。
>[info]注意
在Mac上，雖然xlwings允許您運行多個Excel實例，但它是Excel for Mac不正式支持的功能：與Windows不同，Excel不會要求您打開文件的只讀版本（如果已經存在） 在另一個實例中打開。 這意味著您需要注意自己，以便不會從不同的實例覆蓋相同的文件。
`activate`(*steal\_focus=False*)
激活Excel應用。
參數：steal_focus（bool，默認為False） - 如果為True，請創建最前面的應用程序并將焦點從Python移交給Excel。
0.9.0版中的新功能。
`api`
返回正在使用的引擎的本機對象（`pywin32`或`appscript` 對象）。
0.9.0版中的新功能。
`books`
當前打開的所有Book對象的集合。
0.9.0版中的新功能。
`calculate`()
計算所有打開的工作簿。
0.3.6版中的新功能。
`calculation`
返回或設置表示計算模式的計算值。 模式：`'manual'`, `'automatic'`, `'semiautomatic'`
('手動'，'自動'，'半自動')
Examples
~~~
>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.app.calculation = 'manual'
~~~
0.9.0版中的新功能。
`display_alerts`
默認值為true。將此屬性設置為false可在代碼運行時抑制提示和警報消息；當消息需要響應時，Excel將選擇默認響應。
0.9.0版中的新功能。
`hwnd`
返回Window句柄（僅限Windows）。
New in version 0.9.0.
`kill`()
強制Excel應用程序通過終止其進程退出。
New in version 0.9.0.
`macro`(*name*)
在Excel VBA中運行不屬于特定工作簿但屬于外接程序的子或函數。
參數：name（帶或不帶模塊名稱的Sub或Function的名稱，例如'Module1.MyMacro'或'MyMacro'） -
Examples
This VBA function:
~~~
Function MySum(x, y)
MySum = x + y
End Function
~~~
可以像這樣訪問：
~~~
>>> import xlwings as xw
>>> app = xw.App()
>>> my_sum = app.macro('MySum')
>>> my_sum(1, 2)
3
~~~
另請參見： [`Book.macro()`](#xlwings.Book.macro "xlwings.Book.macro")
New in version 0.9.0.
`pid`
返回應用程序的PID。
New in version 0.9.0.
`quit`()
退出應用程序而不保存任何工作簿。
New in version 0.3.3.
`range`(*cell1*, *cell2=None*)
活動工作簿的活動工作表中的范圍對象，請參見[`Range()`](#xlwings.Range "xlwings.Range").
New in version 0.9.0.
`screen_updating`
關閉屏幕更新以加快腳本速度。 您將無法看到腳本正在執行的操作，但它將運行得更快。 記住在腳本結束時將screen \ _updating屬性設置回True。
New in version 0.3.3.
`selection`
將所選單元格作為Range返回。
New in version 0.9.0.
`version`
返回Excel版本號對象。
Examples
~~~
>>> import xlwings as xw
>>> xw.App().version
VersionNumber('15.24')
>>> xw.apps[10559].version.major
15
~~~
Changed in version 0.9.0.
`visible`
獲取或設置Excel的可見性為“True”或“False”。
New in version 0.3.3.
### Books
*class* `xlwings.main.``Books`(*impl*)
所有`book`對象的集合：
~~~
>>> 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]>])
~~~
New in version 0.9.0.
`active`
Returns the active Book.
`add`()
創建一本新工作簿。 新工作簿成為活動工作簿，返回工作簿對象。
`open`(*fullname*)
如果工作簿尚未打開則打開并返回。 如果它已經打開，它不會引發異常，只是返回工作簿對象。
參數：fullname(str) - 文件名或完全限定的文件名，例如 `C:\path\to\file.xlsx`或`file.xlsm`。 如果沒有完整路徑，它將在當前工作目錄中查找該文件。返回：BookReturn類型：已打開的工作簿。
### Book
*class* `xlwings.``Book`(*fullname=None*, *impl=None*)
book對象是[`books`]()集合的成員：
~~~
>>> import xlwings as xw
>>> xw.books[0]
<Book [Book1]>
~~~
提供：它在所有應用程序實例中查找該工作簿并返回錯誤，如果同一工作簿在多個實例中打開。 要連接到活動應用程序實例中的g，請使用“xw.books”并引用特定應用程序工作簿，使用：
~~~
>>> app = xw.App() # 或類似現有應用程序的xw.apps[10559]，通過xw.apps.keys()獲取PID
>>> 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，default None） - 現有工作簿的完整路徑或名稱（包括xlsx，xlsm等）或未保存工作簿的名稱。 如果沒有完整路徑，它將在當前工作目錄中查找該文件。
`activate`(*steal\_focus=False*)
激活工作簿。
參數：獲取焦點（bool，默認為false）–如果為true，則創建最前面的窗口，并將焦點從python移交給excel。
`api`
返回正在使用的引擎的本機對象（`pywin32`或`appscript` obj）。
New in version 0.9.0.
`app`
返回表示工作簿創建者的app對象。
New in version 0.9.0.
*類方法* `caller`()
當通過`RunPython`從Excel調用Python函數時引用調用工作簿。 將它打包到從Excel調用的函數中，例如：
為了能夠從Python輕松調用此類代碼進行調試，請使用
`xw.Book.set_mock_caller()`.
New in version 0.3.0.
`close`()
關閉工作簿而不保存它。
New in version 0.1.1.
`fullname`
以字符串形式返回對象的名稱，包括其在磁盤上的路徑。 只讀字符串。
`macro`(*name*)
在Excel VBA中運行Sub或Function。
參數：name（帶或不帶模塊名稱的Sub或Function的名稱，例如'Module1.MyMacro'或'MyMacro'） -
Examples
This VBA function:
~~~
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
~~~
See also: [`App.macro()`](#xlwings.App.macro "xlwings.App.macro")
New in version 0.7.1.
`name`
以文本形式返回工作簿的名稱。
`names`
返回一個名稱集合，該集合表示指定工作簿中的所有名稱（包括所有特定于工作表的名稱）。
Changed in version 0.9.0.
*靜態* `open_template`()
使用已包含的xlwings VBA模塊創建新的Excel文件。 必須從交互式Python shell調用此方法：
~~~
>>> xw.Book.open_template()
~~~
See also: [Command Line Client](command_line.html#command-line)
New in version 0.3.3.
`save`(*path=None*)
保存工作簿。 如果提供了路徑，則其工作方式類似于Excel中的另存為。 如果未指定路徑，并且先前未保存文件，則使用當前文件名將其保存在當前工作目錄中。 在沒有提示的情況下覆蓋現有文件。
參數:path (str, default None) – 工作簿的完整路徑
Example
~~~
>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.save()
>>> wb.save(r'C:\path\to\new_file_name.xlsx')
~~~
New in version 0.3.1.
`selection`
將所選單元格作為Range返回。
New in version 0.9.0.
`set_mock_caller`()
設置Excel文件，用于在從Python調用代碼時模擬`xw.Book.caller()`，而不是通過`RunPython`從Excel調用。
Examples
~~~
# 此代碼在Excel中通過RunPython和Python直接運行
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()
~~~
New in version 0.3.1.
`sheets`
返回表示工作簿中所有工作表的工作表集合。
New in version 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>])
~~~
New in version 0.9.0.
`active`
Returns the active Sheet.
`add`(*name=None*, *before=None*, *after=None*)
創建一個新的Sheet并使其成為活動工作表。
參數：name(str,default None) - 新工作表的名稱。 如果為None，則默認為Excel的name.before (Sheet, default None) - 一個對象，指定在新工作表添加之前的added.after (Sheet, default None) - 指定工作表之后的工作表的對象 表格已添加。
### Sheet
*class* `xlwings.``Sheet`(*sheet=None*, *impl=None*)
A sheet object is a member of the [`sheets`](#xlwings.main.Sheets "xlwings.main.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>
~~~
Changed in version 0.9.0.
`activate`()
Activates the Sheet and returns it.
`api`
Returns the native object (`pywin32` or `appscript` obj) of the engine being used.
New in version 0.9.0.
`autofit`(*axis=None*)
在整個工作表上自動調整列，行或兩者的寬度。
參數:axis (string, default None) –要自動調整行, 使用以下之一: rows 或 r，要自動調整列, 使用以下之一: columns h c，要自動調整行和列, 不提供參數
Examples
~~~
>>> import xlwings as xw
>>> wb = xw.Book()
>>> wb.sheets['Sheet1'].autofit('c')
>>> wb.sheets['Sheet1'].autofit('r')
>>> wb.sheets['Sheet1'].autofit()
~~~
New in version 0.2.3.
`book`
返回指定Sheet的Book。 只讀。
`cells`
返回一個Range對象，該對象表示Sheet上的所有單元格（而不僅僅是當前正在使用的單元格）。
New in version 0.9.0.
`charts`
See [`Charts`](#xlwings.main.Charts "xlwings.main.Charts")
New in version 0.9.0.
`clear`()
清除整個工作表的內容和格式。
`clear_contents`()
清除整個工作表的內容但保留格式。
`delete`()
刪除工作表。
`index`
返回工作表的索引（以1為基準，與Excel相同）。
`name`
獲取或設置Sheet的名稱。
`names`
返回表示所有工作表特定名稱（使用“SheetName！”前綴定義的名稱）的名稱集合。
New in version 0.9.0.
`pictures`
See [`Pictures`](#xlwings.main.Pictures "xlwings.main.Pictures")
New in version 0.9.0.
`range`(*cell1*, *cell2=None*)
從活動工作簿的活動工作表中返回Range對象，請參閱[`Range()`](#xlwings.Range "xlwings.Range").
New in version 0.9.0.
`select`()
選擇工作表。 選擇僅適用于活動工作簿。
New in version 0.9.0.
`shapes`
See [`Shapes`](#xlwings.main.Shapes "xlwings.main.Shapes")
New in version 0.9.0.
`used_range`
工作表已使用的Range
返回:返回類別:xw.Range
New in version 0.13.0.
### Range
*class* `xlwings.``Range`(*cell1=None*, *cell2=None*, *\*\*可選*)
返回表示單元格或單元格范圍的Range對象。
參數:cell1 (str or tuple or Range) – A1表示法左上角或作為索引元組或名稱或xw的范圍名稱.Range對象. 它還可以使用范圍運算符（冒號）指定范圍, 例如 ‘A1:B2’，cell2 (str or tuple or Range, 默認 None) – 下方范圍的名稱
Examples
活動工作表:
~~~
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'))
~~~
特定工作表:
~~~
xw.books['MyBook.xlsx'].sheets[0].range('A1')
~~~
`add_hyperlink`(*address*, *text\_to\_display=None*, *screen\_tip=None*)
添加指定范圍的超鏈接（單個單元格）
參數:address (str) – 超鏈接的地址；text_to_display (str, default None) – 要為超鏈接顯示的文本. 默認為超鏈接地址；screen_tip (str, default None) – 鼠標指針暫停在超鏈接上時顯示的屏幕提示. Default is set to ‘<address> - Click once to follow. Click and hold to select this cell.’
New in version 0.3.0.
`address`
Returns a string value that represents the range reference. Use `get_address()` to be able to provide paramaters.
New in version 0.9.0.
`api`
Returns the native object (`pywin32` or `appscript` obj) of the engine being used.
New in version 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()`
Changed in version 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.
Returns:RGBReturn type: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
~~~
New in version 0.3.0.
`column`
Returns the number of the first column in the in the specified range. Read-only.
Returns:Return type:Integer
New in version 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.
Returns:Return type:float
New in version 0.4.0.
`columns`
Returns a [`RangeColumns`](#xlwings.RangeColumns "xlwings.RangeColumns") object that represents the columns in the specified range.
New in version 0.9.0.
`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.
Returns:Return type:Range object
`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.
Parameters: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>
~~~
New in version 0.9.0.
`expand`(*mode='table'*)
Expands the range according to the mode provided. Ignores empty top-left cells (unlike `Range.end()`).
Parameters:mode (str, default 'table') – One of 'table' (=down and right), 'down', 'right'.Returns:Return type: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
~~~
New in version 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.
New in version 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.
Parameters: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.Returns:Return type: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'
~~~
New in version 0.2.3.
`height`
Returns the height, in points, of a Range. Read-only.
Returns:Return type:float
New in version 0.4.0.
`hyperlink`
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'
~~~
New in version 0.3.0.
`last_cell`
Returns the bottom right cell of the specified range. Read-only.
Returns:Return type:Range
Example
~~~
>>> import xlwings as xw
>>> wb = xw.Book()
>>> rng = xw.Range('A1:E4')
>>> rng.last_cell.row, rng.last_cell.column
(4, 5)
~~~
New in version 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.
Returns:Return type:float
New in version 0.6.0.
`name`
Sets or gets the name of a Range.
New in version 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%'
~~~
New in version 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.
Returns:Range objectReturn type:Range
New in version 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](converters.html#converters).
Parameters:convert (object, default None) – A converter, e.g. dict, np.array, pd.DataFrame, pd.Series, defaults to default converterKeyword Arguments: ndim (int, default None) – number of dimensionsnumbers (type, default None) – type of numbers, e.g. intdates (type, default None) – e.g. datetime.date defaults to datetime.datetimeempty (object, default None) – transformation of empty cellstranspose (Boolean, default False) – transpose valuesexpand (str, default None) –One of 'table', 'down', 'right'=> For converter-specific options, see Converters and Options.Returns:Return type:Range object
New in version 0.7.0.
`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
Parameters: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).Returns:Range objectReturn type:Range
New in version 0.3.0.
`row`
Returns the number of the first row in the specified range. Read-only.
Returns:Return type:Integer
New in version 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.
Returns:Return type:float
New in version 0.4.0.
`rows`
Returns a [`RangeRows`](#xlwings.RangeRows "xlwings.RangeRows") object that represents the rows in the specified range.
New in version 0.9.0.
`select`()
Selects the range. Select only works on the active book.
New in version 0.9.0.
`shape`
Tuple of Range dimensions.
New in version 0.3.0.
`sheet`
Returns the Sheet object to which the Range belongs.
New in version 0.9.0.
`size`
Number of elements in the Range.
New in version 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.
Returns:Return type:float
New in version 0.6.0.
`value`
Gets and sets the values for the given Range.
Returns:objectReturn type:returned object depends on the converter being used, see xlwings.Range.options()
`width`
Returns the width, in points, of a Range. Read-only.
Returns:Return type:float
New in version 0.4.0.
### RangeRows
*class* `xlwings.``RangeRows`(*rng*)
Represents the rows of a range. Do not construct this class directly, use [`Range.rows`](#xlwings.Range.rows "xlwings.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.
New in version 0.9.0.
### RangeColumns
*class* `xlwings.``RangeColumns`(*rng*)
Represents the columns of a range. Do not construct this class directly, use [`Range.columns`](#xlwings.Range.columns "xlwings.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.
New in version 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>>])
~~~
New in version 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`](#xlwings.main.Shapes "xlwings.main.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>>
~~~
Changed in version 0.9.0.
`activate`()
Activates the shape.
New in version 0.5.0.
`delete`()
Deletes the shape.
New in version 0.5.0.
`height`
Returns or sets the number of points that represent the height of the shape.
New in version 0.5.0.
`left`
Returns or sets the number of points that represent the horizontal position of the shape.
New in version 0.5.0.
`name`
Returns or sets the name of the shape.
New in version 0.5.0.
`parent`
Returns the parent of the shape.
New in version 0.9.0.
`top`
Returns or sets the number of points that represent the vertical position of the shape.
New in version 0.5.0.
`type`
Returns the type of the shape.
New in version 0.9.0.
`width`
Returns or sets the number of points that represent the width of the shape.
New in version 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>>])
~~~
New in version 0.9.0.
`add`(*left=0*, *top=0*, *width=355*, *height=211*)
Creates a new chart on the specified sheet.
Parameters:left (float, default 0) – left position in pointstop (float, default 0) – top position in pointswidth (float, default 355) – width in pointsheight (float, default 211) – height in pointsReturns:Return type: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`](#xlwings.main.Charts "xlwings.main.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.
New in version 0.9.0.
`chart_type`
Returns and sets the chart type of the chart.
New in version 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.
New in version 0.9.0.
`set_source_data`(*source*)
Sets the source data range for the chart.
Parameters: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>>])
~~~
New in version 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.
Parameters:image (str 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.Returns:Return type: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>>
~~~
2. 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`](#xlwings.main.Pictures "xlwings.main.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>>
~~~
Changed in version 0.9.0.
`api`
Returns the native object (`pywin32` or `appscript` obj) of the engine being used.
New in version 0.9.0.
`delete`()
Deletes the picture.
New in version 0.5.0.
`height`
Returns or sets the number of points that represent the height of the picture.
New in version 0.5.0.
`left`
Returns or sets the number of points that represent the horizontal position of the picture.
New in version 0.5.0.
`name`
Returns or sets the name of the picture.
New in version 0.5.0.
`parent`
Returns the parent of the picture.
New in version 0.9.0.
`top`
Returns or sets the number of points that represent the vertical position of the picture.
New in version 0.5.0.
`update`(*image*)
Replaces an existing picture with a new one, taking over the attributes of the existing picture.
Parameters:image (str or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
New in version 0.5.0.
`width`
Returns or sets the number of points that represent the width of the picture.
New in version 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>]
~~~
New in version 0.9.0.
`add`(*name*, *refers\_to*)
Defines a new name for a range of cells.
Parameters: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.Returns:Return type:Name
New in version 0.9.0.
`api`
Returns the native object (`pywin32` or `appscript` obj) of the engine being used.
New in version 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`](#xlwings.main.Names "xlwings.main.Names") collection:
~~~
>>> import xlwings as xw
>>> sht = xw.books['Book1'].sheets[0]
>>> sht.names[0] # or sht.names['MyName']
<Name 'MyName': =Sheet1!$A$3>
~~~
New in version 0.9.0.
`api`
Returns the native object (`pywin32` or `appscript` obj) of the engine being used.
New in version 0.9.0.
`delete`()
Deletes the name.
New in version 0.9.0.
`name`
Returns or sets the name of the name object.
New in version 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.
New in version 0.9.0.
`refers_to_range`
Returns the Range object referred to by a Name object.
New in version 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
New in version 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.
New in version 0.10.3.
call\_in\_wizard : bool, default True
Set to False to suppress the function call in the function wizard.
New in version 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()`](#xlwings.Range.options "xlwings.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()`](#xlwings.Range.options "xlwings.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)))
~~~
2. 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))
~~~
New in version 0.10.0.
