Python API¶
Top-level functions¶
- xlwings.load(index=1, header=1, chunksize=5000)¶
Loads the selected cell(s) of the active workbook into a pandas DataFrame. If you select a single cell that has adjacent cells, the range is auto-expanded (via current region) and turned into a pandas DataFrame. If you don’t have pandas installed, it returns the values as nested lists.
Note
Only use this in an interactive context like e.g. a Jupyter notebook! Don’t use this in a script as it depends on the active book.
- Parameters
index (bool or int, default 1) – Defines the number of columns on the left that will be turned into the DataFrame’s index
header (bool or int, default 1) – Defines the number of rows at the top that will be turned into the DataFrame’s columns
chunksize (int, default 5000) – Chunks the loading of big arrays.
Examples
>>> import xlwings as xw >>> xw.load()
See also:
view
Changed in version 0.23.1.
- xlwings.view(obj, sheet=None, table=True, chunksize=5000)¶
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.
Note
Only use this in an interactive context like e.g. a Jupyter notebook! Don’t use this in a script as it depends on the active book.
- Parameters
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.
table (bool, default True) – If your object is a pandas DataFrame, by default it is formatted as an Excel Table
chunksize (int, default 5000) – Chunks the loading of big arrays.
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)
See also:
load
Changed in version 0.22.0.
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>])
- property active¶
Returns the active app.
New in version 0.9.0.
- add(**kwargs)¶
Creates a new App. The new App becomes the active one. Returns an App object.
- property count¶
Returns the number of apps.
New in version 0.9.0.
- keys()¶
Provides the PIDs of the Excel instances that act as keys in the Apps collection.
New in version 0.13.0.
App¶
- class xlwings.App(visible=None, spec=None, add_book=True, impl=None)¶
An app corresponds to an Excel instance and should normally be used as context manager to make sure that everything is properly cleaned up again and to prevent zombie processes. New Excel instances can be fired up like so:
import xlwings as xw with xw.App() as app: print(app.books)
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>
- Parameters
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
andRepair
the Office version that you want as default.
Note
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.
- Parameters
steal_focus (bool, default False) – If True, make frontmost application and hand over focus from Python to Excel.
New in version 0.9.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- property books¶
A collection of all Book objects that are currently open.
New in version 0.9.0.
- calculate()¶
Calculates all open books.
New in version 0.3.6.
- property 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'
Changed in version 0.9.0.
- property cut_copy_mode¶
Gets or sets the status of the cut or copy mode. Accepts
False
for setting and returnsNone
,copy
orcut
when getting the status.New in version 0.24.0.
- property 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.
New in version 0.9.0.
- property enable_events¶
True
if events are enabled. Read/write boolean.New in version 0.24.4.
- property hwnd¶
Returns the Window handle (Windows-only).
New in version 0.9.0.
- property interactive¶
True
if Excel is in interactive mode. If you set this property toFalse
, Excel blocks all input from the keyboard and mouse (except input to dialog boxes that are displayed by your code). Read/write Boolean. Note: Not supported on macOS.New in version 0.24.4.
- kill()¶
Forces the Excel app to quit by killing its process.
New in version 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.
- Parameters
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
Types are supported too:
>>> import xlwings as xw >>> app = xw.App() >>> my_sum = app.macro('MySum') >>> my_sum(1, 2) 3
However typed arrays are not supported. So the following won’t work
Function MySum(arr() as integer) ' code here End Function
See also:
Book.macro()
New in version 0.9.0.
- property pid¶
Returns the PID of the app.
New in version 0.9.0.
- properties(**kwargs)¶
Context manager that allows you to easily change the app’s properties temporarily. Once the code leaves the with block, the properties are changed back to their previous state. Note: Must be used as context manager or else will have no effect. Also, you can only use app properties that you can both read and write.
Examples
import xlwings as xw app = App() # Sets app.display_alerts = False with app.properties(display_alerts=False): # do stuff # Sets app.calculation = 'manual' and app.enable_events = True with app.properties(calculation='manual', enable_events=True): # do stuff # Makes sure the status bar is reset even if an error happens in the with block with app.properties(status_bar='Calculating...'): # do stuff
New in version 0.24.4.
- quit()¶
Quits the application without saving any workbooks.
New in version 0.3.3.
- range(cell1, cell2=None)¶
Range object from the active sheet of the active book, see
Range()
.New in version 0.9.0.
- render_template(template=None, output=None, book_settings=None, **data)¶
This function requires xlwings PRO.
This is a convenience wrapper around
mysheet.render_template
Writes the values of all key word arguments to the
output
file according to thetemplate
and the variables contained in there (Jinja variable syntax). Following variable types are supported:strings, numbers, lists, simple dicts, NumPy arrays, Pandas DataFrames, pictures and Matplotlib/Plotly figures.
- Parameters
template (str or path-like object) – Path to your Excel template, e.g.
r'C:\Path\to\my_template.xlsx'
output (str or path-like object) – Path to your Report, e.g.
r'C:\Path\to\my_report.xlsx'
book_settings (dict, default None) – A dictionary of
xlwings.Book
parameters, for details see:xlwings.Book
. For example:book_settings={'update_links': False}
.data (kwargs) – All key/value pairs that are used in the template.
- Returns
wb (xlwings Book)
.. versionadded:: 0.24.4
- property 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.
New in version 0.3.3.
- property selection¶
Returns the selected cells as Range.
New in version 0.9.0.
- property startup_path¶
Returns the path to
XLSTART
which is where the xlwings add-in gets copied to by doingxlwings addin install
.New in version 0.19.4.
- property status_bar¶
Gets or sets the value of the status bar. Returns
False
if Excel has control of it.New in version 0.20.0.
- property version¶
Returns the Excel version number object.
Examples
>>> import xlwings as xw >>> xw.App().version VersionNumber('15.24') >>> xw.apps[10559].version.major 15
Changed in version 0.9.0.
- property visible¶
Gets or sets the visibility of Excel to
True
orFalse
.New in version 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]>])
New in version 0.9.0.
- property active¶
Returns the active Book.
- add()¶
Creates a new Book. The new Book becomes the active Book. Returns a Book object.
- open(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, json=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.
- Parameters
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()
- Returns
Book
- Return type
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, json=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, usexw.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')
- Parameters
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 messageorigin (int) – For text files only. Specifies where it originated. Use Platform 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.
json (dict) –
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.
New in version 0.26.0.
- activate(steal_focus=False)¶
Activates the book.
- Parameters
steal_focus (bool, default False) – If True, make frontmost window and hand over focus from Python to Excel.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- property app¶
Returns an app object that represents the creator of the book.
New 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
To be able to easily invoke such code from Python for debugging, use
xw.Book.set_mock_caller()
.New in version 0.3.0.
- close()¶
Closes the book without saving it.
New in version 0.1.1.
- property fullname¶
Returns the name of the object, including its path on disk, as a string. Read-only String.
- 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=...)
.New in version 0.26.0.
- macro(name)¶
Runs a Sub or Function in Excel VBA.
- Parameters
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()
New in version 0.7.1.
- property name¶
Returns the name of the book as str.
- property names¶
Returns a names collection that represents all the names in the specified book (including all sheet-specific names).
Changed in version 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 that has the same name.New in version 0.25.0.
- Parameters
data (kwargs) – All key/value pairs that are used in the template.
Examples
>>> 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 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.
- Parameters
path (str or path-like object, default None) – Full path to the workbook
password (str, default None) –
Protection password with max. 15 characters
New in version 0.25.1.
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.
- property selection¶
Returns the selected cells as Range.
New in version 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 viaRunPython
.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()
New in version 0.3.1.
- property sheets¶
Returns a sheets collection that represents all the sheets in the book.
New in version 0.9.0.
- to_pdf(path=None, include=None, exclude=None, layout=None, exclude_start_string='#', show=False)¶
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
.- Parameters
path (str 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.
include (int 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.
exclude (int 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.
layout (str 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).
New in version 0.24.3.
exclude_start_string (str, default '#') –
Sheet names that start with this character/string will not be printed.
New in version 0.24.4.
show (bool, default False) –
Once created, open the PDF file with the default application.
New in version 0.24.6.
Examples
>>> wb = xw.Book() >>> wb.sheets[0]['A1'].value = 'PDF' >>> wb.to_pdf()
See also
xlwings.Sheet.to_pdf()
New in version 0.21.1.
PageSetup¶
- class xlwings.main.PageSetup(impl)¶
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.24.2.
- property print_area¶
Gets or sets the range address that defines the print area.
Examples
>>> mysheet.page_setup.print_area = '$A$1:$B$3' >>> mysheet.page_setup.print_area '$A$1:$B$3' >>> mysheet.page_setup.print_area = None # clear the print_area
New in version 0.24.2.
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.
- property active¶
Returns the active Sheet.
- add(name=None, before=None, after=None)¶
Creates a new Sheet and makes it the active sheet.
- Parameters
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>
Changed in version 0.9.0.
- activate()¶
Activates the Sheet and returns it.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- autofit(axis=None)¶
Autofits the width of either columns, rows or both on a whole Sheet.
- Parameters
axis (string, default None) –
To autofit rows, use one of the following:
rows
orr
To autofit columns, use one of the following:
columns
orc
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()
New in version 0.2.3.
- property book¶
Returns the Book of the specified Sheet. Read-only.
- property cells¶
Returns a Range object that represents all the cells on the Sheet (not just the cells that are currently in use).
New in version 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.
- 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.
New in version 0.22.0.
- Parameters
before (sheet object, default None) – The sheet object before which you want to place the sheet
after (sheet object, default None) – The sheet object after which you want to place the sheet, by default it is placed after all existing sheets
name (str, default None) – The sheet name of the copy
- Returns
Sheet object – The copied sheet
- Return type
Examples
# 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()¶
Deletes the Sheet.
- property index¶
Returns the index of the Sheet (1-based as in Excel).
- property name¶
Gets or sets the name of the Sheet.
- property names¶
Returns a names collection that represents all the sheet-specific names (names defined with the “SheetName!” prefix).
New in version 0.9.0.
- property page_setup¶
Returns a PageSetup object.
New in version 0.24.2.
- range(cell1, cell2=None)¶
Returns a Range object from the active sheet of the active book, see
Range()
.New in version 0.9.0.
- 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.
New in version 0.22.0.
- Parameters
data (kwargs) – All key/value pairs that are used in the template.
Examples
>>> 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. Select only works on the active book.
New in version 0.9.0.
- to_pdf(path=None, layout=None, show=False)¶
Exports the sheet to a PDF file.
- Parameters
path (str or path-like object, default 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 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).
New in version 0.24.3.
show (bool, default False) –
Once created, open the PDF file with the default application.
New in version 0.24.6.
Examples
>>> wb = xw.Book() >>> sheet = wb.sheets[0] >>> sheet['A1'].value = 'PDF' >>> sheet.to_pdf()
See also
xlwings.Book.to_pdf()
New in version 0.22.3.
- property used_range¶
Used Range of Sheet.
- Returns
- Return type
xw.Range
New in version 0.13.0.
- property visible¶
Gets or sets the visibility of the Sheet (bool).
New in version 0.21.1.
Range¶
- class xlwings.Range(cell1=None, cell2=None, **options)¶
Returns a Range object that represents a cell or a range of cells.
- Parameters
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')
- add_hyperlink(address, text_to_display=None, screen_tip=None)¶
Adds a hyperlink to the specified Range (single Cell)
- Parameters
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.’
New in version 0.3.0.
- property 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.
- property api¶
Returns the native object (
pywin32
orappscript
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.
- property 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 hex string like#efefef
or an Excel color constant. To remove the background, set the color toNone
, see Examples.- Returns
RGB
- Return type
tuple
Examples
>>> import xlwings as xw >>> wb = xw.Book() >>> xw.Range('A1').color = (255, 255, 255) # or '#ffffff' >>> 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.
- property 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.
- property 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.
- property columns¶
Returns a
RangeColumns
object that represents the columns in the specified range.New in version 0.9.0.
- copy(destination=None)¶
Copy a range to a destination range or clipboard.
- Parameters
destination (xlwings.Range) – xlwings Range to which the specified range will be copied. If omitted, the range is copied to the Clipboard.
- Returns
- Return type
None
- copy_picture(appearance='screen', format='picture')¶
Copies the range to the clipboard as picture.
- Parameters
appearance (str, default 'screen') – Either ‘screen’ or ‘printer’.
format (str, default 'picture') – Either ‘picture’ or ‘bitmap’.
versionadded: (..) – 0.24.8:
- property count¶
Returns the number of cells.
- property 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 andShift-Ctrl-Space
on Mac.- Returns
- Return type
Range object
- delete(shift=None)¶
Deletes a cell or range of cells.
- Parameters
shift (str, default None) – Use
left
orup
. If omitted, Excel decides based on the shape of the range.- Returns
- Return type
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.
- 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
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.
- property formula¶
Gets or sets the formula for the given Range.
- property formula2¶
Gets or sets the formula2 for the given Range.
- property 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.
- property has_array¶
True
if the range is part of a legacy CSE Array formula andFalse
otherwise.
- property height¶
Returns the height, in points, of a Range. Read-only.
- Returns
- Return type
float
New in version 0.4.0.
- property 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.
- insert(shift=None, copy_origin='format_from_left_or_above')¶
Insert a cell or range of cells into the sheet.
- Parameters
shift (str, default None) – Use
right
ordown
. 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
orformat_from_right_or_below
. Note that this is not supported on macOS.
- Returns
- Return type
None
- property last_cell¶
Returns the bottom right cell of the specified range. Read-only.
- Returns
- Return type
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.
- property 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.
- merge(across=False)¶
Creates a merged cell from the specified Range object.
- Parameters
across (bool, default False) – True to merge cells in each row of the specified Range as separate merged cells.
- property 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.
- property merge_cells¶
Returns
True
if the Range contains merged cells, otherwiseFalse
- property name¶
Sets or gets the name of a Range.
New in version 0.4.0.
- property note¶
Returns a Note object. Before the introduction of threaded comments, a Note was called a Comment.
New in version 0.24.2.
- property 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 object
- Return type
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.
- Parameters
convert (object, default None) – A converter, e.g.
dict
,np.array
,pd.DataFrame
,pd.Series
, defaults to default converter- Keyword Arguments
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 todatetime.datetime
empty (object, default None) – transformation of empty cells
transpose (Boolean, default False) – transpose values
expand (str, default None) – One of
'table'
,'down'
,'right'
chunksize (int) –
- Use a chunksize, e.g.
10000
to prevent timeout or memory issues when reading or writing large amounts of data. Works with all formats, including DataFrames, NumPy arrays, and list of lists.
=> For converter-specific options, see Converters and Options.
- Use a chunksize, e.g.
- Returns
- Return type
Range object
New in version 0.7.0.
- paste(paste=None, operation=None, skip_blanks=False, transpose=False)¶
Pastes a range from the clipboard into the specified range.
- Parameters
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 cellstranspose (bool, default False) – Set to
True
to transpose rows and columns.
- Returns
- Return type
None
- property raw_value¶
Gets and sets the values directly as delivered from/accepted by the engine that is being used (
pywin32
orappscript
) 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 object
- Return type
New in version 0.3.0.
- property row¶
Returns the number of the first row in the specified range. Read-only.
- Returns
- Return type
Integer
New in version 0.3.5.
- property 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.
- property rows¶
Returns a
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.
- property shape¶
Tuple of Range dimensions.
New in version 0.3.0.
- property sheet¶
Returns the Sheet object to which the Range belongs.
New in version 0.9.0.
- property size¶
Number of elements in the Range.
New in version 0.3.0.
- property table¶
Returns a Table object if the range is part of one, otherwise
None
.New in version 0.21.0.
- to_png(path=None)¶
Exports the range as PNG picture.
- Parameters
path (str or path-like, default None) – Path where you want to store the picture. Defaults to the name of the range in the same directory as the Excel file if the Excel file is stored and to the current working directory otherwise.
versionadded: (..) – 0.24.8:
- property 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.
- unmerge()¶
Separates a merged area into individual cells.
- property value¶
Gets and sets the values for the given Range. See see
xlwings.Range.options()
about how to set options, e.g. to transform it into a DataFrame or how to set a chunksize.- Returns
object
- Return type
returned object depends on the converter being used, see
xlwings.Range.options()
- property width¶
Returns the width, in points, of a Range. Read-only.
- Returns
- Return type
float
New in version 0.4.0.
- property wrap_text¶
Returns
True
if the wrap_text property is enabled andFalse
if it’s disabled. If not all cells have the same value in a range, on Windows it returnsNone
and on macOSFalse
.New in version 0.23.2.
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.
- property 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
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.
- property 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.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.
- property 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>>
Changed in version 0.9.0.
- activate()¶
Activates the shape.
New in version 0.5.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.19.2.
- delete()¶
Deletes the shape.
New in version 0.5.0.
- property height¶
Returns or sets the number of points that represent the height of the shape.
New in version 0.5.0.
- property left¶
Returns or sets the number of points that represent the horizontal position of the shape.
New in version 0.5.0.
- property name¶
Returns or sets the name of the shape.
New in version 0.5.0.
- property parent¶
Returns the parent of the shape.
New in version 0.9.0.
- scale_height(factor, relative_to_original_size=False, scale='scale_from_top_left')¶
- factorfloat
For example 1.5 to scale it up to 150%
- relative_to_original_sizebool, optional
If
False
, it scales relative to current height (default). ForTrue
must be a picture or OLE object.- scalestr, optional
One of
scale_from_top_left
(default),scale_from_bottom_right
,scale_from_middle
New in version 0.19.2.
- scale_width(factor, relative_to_original_size=False, scale='scale_from_top_left')¶
- factorfloat
For example 1.5 to scale it up to 150%
- relative_to_original_sizebool, optional
If
False
, it scales relative to current width (default). ForTrue
must be a picture or OLE object.- scalestr, optional
One of
scale_from_top_left
(default),scale_from_bottom_right
,scale_from_middle
New in version 0.19.2.
- property text¶
Returns or sets the text of a shape.
New in version 0.21.4.
- property top¶
Returns or sets the number of points that represent the vertical position of the shape.
New in version 0.5.0.
- property type¶
Returns the type of the shape.
New in version 0.9.0.
- property 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 points
top (float, default 0) – top position in points
width (float, default 355) – width in points
height (float, default 211) – height in points
- Returns
- Return type
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'
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.
- property 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>>
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- property chart_type¶
Returns and sets the chart type of the chart. The following chart types are available:
3d_area
,3d_area_stacked
,3d_area_stacked_100
,3d_bar_clustered
,3d_bar_stacked
,3d_bar_stacked_100
,3d_column
,3d_column_clustered
,3d_column_stacked
,3d_column_stacked_100
,3d_line
,3d_pie
,3d_pie_exploded
,area
,area_stacked
,area_stacked_100
,bar_clustered
,bar_of_pie
,bar_stacked
,bar_stacked_100
,bubble
,bubble_3d_effect
,column_clustered
,column_stacked
,column_stacked_100
,combination
,cone_bar_clustered
,cone_bar_stacked
,cone_bar_stacked_100
,cone_col
,cone_col_clustered
,cone_col_stacked
,cone_col_stacked_100
,cylinder_bar_clustered
,cylinder_bar_stacked
,cylinder_bar_stacked_100
,cylinder_col
,cylinder_col_clustered
,cylinder_col_stacked
,cylinder_col_stacked_100
,doughnut
,doughnut_exploded
,line
,line_markers
,line_markers_stacked
,line_markers_stacked_100
,line_stacked
,line_stacked_100
,pie
,pie_exploded
,pie_of_pie
,pyramid_bar_clustered
,pyramid_bar_stacked
,pyramid_bar_stacked_100
,pyramid_col
,pyramid_col_clustered
,pyramid_col_stacked
,pyramid_col_stacked_100
,radar
,radar_filled
,radar_markers
,stock_hlc
,stock_ohlc
,stock_vhlc
,stock_vohlc
,surface
,surface_top_view
,surface_top_view_wireframe
,surface_wireframe
,xy_scatter
,xy_scatter_lines
,xy_scatter_lines_no_markers
,xy_scatter_smooth
,xy_scatter_smooth_no_markers
New in version 0.1.1.
- delete()¶
Deletes the chart.
- property height¶
Returns or sets the number of points that represent the height of the chart.
- property left¶
Returns or sets the number of points that represent the horizontal position of the chart.
- property name¶
Returns or sets the name of the chart.
- property 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')
- to_png(path=None)¶
Exports the chart as PNG picture.
- Parameters
path (str or path-like, default None) – Path where you want to store the picture. Defaults to the name of the chart in the same directory as the Excel file if the Excel file is stored and to the current working directory otherwise.
versionadded: (..) – 0.24.8:
- property top¶
Returns or sets the number of points that represent the vertical position of the chart.
- property 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=None, top=None, width=None, height=None, name=None, update=False, scale=None, format=None, anchor=None)¶
Adds a picture to the specified sheet.
- Parameters
image (str or path-like object or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
left (float, default None) – Left position in points, defaults to 0. If you use
top
/left
, you must not provide a value foranchor
.top (float, default None) – Top position in points, defaults to 0. If you use
top
/left
, you must not provide a value foranchor
.width (float, default None) – Width in points. Defaults to original width.
height (float, default None) – Height in points. Defaults to original height.
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.scale (float, default None) – Scales your picture by the provided factor.
format (str, default None) – Only used if image is a Matplotlib or Plotly plot. By default, the plot is inserted in the “png” format, but you may want to change this to a vector-based format like “svg” on Windows (may require Microsoft 365) or “eps” on macOS for better print quality. If you use
'vector'
, it will be using'svg'
on Windows and'eps'
on macOS. To find out which formats your version of Excel supports, see: https://support.microsoft.com/en-us/topic/support-for-eps-images-has-been-turned-off-in-office-a069d664-4bcf-415e-a1b5-cbb0c334a840anchor (xw.Range, default None) –
The xlwings Range object of where you want to insert the picture. If you use
anchor
, you must not provide values fortop
/left
.New in version 0.24.3.
- Returns
- Return type
Examples
Picture
>>> import xlwings as xw >>> sht = xw.Book().sheets[0] >>> sht.pictures.add(r'C:\path\to\file.png') <Picture 'Picture 1' in <Sheet [Book1]Sheet1>>
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>>
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.
- property 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>>
Changed in version 0.9.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- delete()¶
Deletes the picture.
New in version 0.5.0.
- property height¶
Returns or sets the number of points that represent the height of the picture.
New in version 0.5.0.
- property left¶
Returns or sets the number of points that represent the horizontal position of the picture.
New in version 0.5.0.
- property lock_aspect_ratio¶
True
will keep the original proportion,False
will allow you to change height and width independently of each other (read/write).New in version 0.24.0.
- property name¶
Returns or sets the name of the picture.
New in version 0.5.0.
- property parent¶
Returns the parent of the picture.
New in version 0.9.0.
- property top¶
Returns or sets the number of points that represent the vertical position of the picture.
New in version 0.5.0.
- update(image, format=None)¶
Replaces an existing picture with a new one, taking over the attributes of the existing picture.
- Parameters
image (str or path-like object or matplotlib.figure.Figure) – Either a filepath or a Matplotlib figure object.
New in version 0.5.0.
- property 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
New in version 0.9.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- property 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>
New in version 0.9.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.9.0.
- delete()¶
Deletes the name.
New in version 0.9.0.
- property name¶
Returns or sets the name of the name object.
New in version 0.9.0.
- property 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.
- property refers_to_range¶
Returns the Range object referred to by a Name object.
New in version 0.9.0.
Note¶
- class xlwings.main.Note(impl)¶
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.24.2.
- delete()¶
Delete the note.
New in version 0.24.2.
- property text¶
Gets or sets the text of a note. Keep in mind that the note must already exist!
Examples
>>> sheet = xw.Book(...).sheets[0] >>> sheet['A1'].note.text = 'mynote' >>> sheet['A1'].note.text >>> 'mynote'
New in version 0.24.2.
Tables¶
- class xlwings.main.Tables(impl)¶
A collection of all
table
objects on the specified sheet:>>> import xlwings as xw >>> xw.books['Book1'].sheets[0].tables Tables([<Table 'Table1' in <Sheet [Book11]Sheet1>>, <Table 'Table2' in <Sheet [Book11]Sheet1>>])
New in version 0.21.0.
- add(source=None, name=None, source_type=None, link_source=None, has_headers=True, destination=None, table_style_name='TableStyleMedium2')¶
Creates a Table to the specified sheet.
- Parameters
source (xlwings range, default None) – An xlwings range object, representing the data source.
name (str, default None) – The name of the Table. By default, it uses the autogenerated name that is assigned by Excel.
source_type (str, default None) – This currently defaults to
xlSrcRange
, i.e. expects an xlwings range object. No other options are allowed at the moment.link_source (bool, default None) – Currently not implemented as this is only in case
source_type
isxlSrcExternal
.has_headers (bool or str, default True) – Indicates whether the data being imported has column labels. Defaults to
True
. Possible values:True
,FAlse
,'guess'
destination (xlwings range, default None) – Currently not implemented as this is used in case
source_type
isxlSrcExternal
.table_style_name (str, default 'TableStyleMedium2') – Possible strings:
'TableStyleLightN''
(where N is 1-21),'TableStyleMediumN'
(where N is 1-28),'TableStyleDarkN'
(where N is 1-11)
- Returns
- Return type
Examples
>>> import xlwings as xw >>> sheet = xw.Book().sheets[0] >>> sheet['A1'].value = [['a', 'b'], [1, 2]] >>> table = sheet.tables.add(source=sheet['A1'].expand(), name='MyTable') >>> table <Table 'MyTable' in <Sheet [Book1]Sheet1>>
Table¶
- class xlwings.main.Table(*args, **options)¶
The table object is a member of the
tables
collection:>>> import xlwings as xw >>> sht = xw.books['Book1'].sheets[0] >>> sht.tables[0] # or sht.tables['TableName'] <Table 'Table 1' in <Sheet [Book1]Sheet1>>
New in version 0.21.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.
- property data_body_range¶
Returns an xlwings range object that represents the range of values, excluding the header row
- property display_name¶
Returns or sets the display name for the specified Table object
- property header_row_range¶
Returns an xlwings range object that represents the range of the header row
- property insert_row_range¶
Returns an xlwings range object representing the row where data is going to be inserted. This is only available for empty tables, otherwise it’ll return
None
- property name¶
Returns or sets the name of the Table.
- property parent¶
Returns the parent of the table.
- property range¶
Returns an xlwings range object of the table.
- resize(range)¶
Resize a Table by providing an xlwings range object
New in version 0.24.4.
- property show_autofilter¶
Turn the autofilter on or off by setting it to
True
orFalse
(read/write boolean)
- property show_headers¶
Show or hide the header (read/write)
- property show_table_style_column_stripes¶
Returns or sets if the Column Stripes table style is used for (read/write boolean)
- property show_table_style_first_column¶
Returns or sets if the first column is formatted (read/write boolean)
- property show_table_style_last_column¶
Returns or sets if the last column is displayed (read/write boolean)
- property show_table_style_row_stripes¶
Returns or sets if the Row Stripes table style is used (read/write boolean)
- property show_totals¶
Gets or sets a boolean to show/hide the Total row.
- property table_style¶
Gets or sets the table style. See
Tables.add
for possible values.
- property totals_row_range¶
Returns an xlwings range object representing the Total row
- update(data, index=True)¶
Updates the Excel table with the provided data. Currently restricted to DataFrames.
Changed in version 0.24.0.
- Parameters
data (pandas DataFrame) – Currently restricted to pandas DataFrames.
index (bool, default True) – Whether or not the index of a pandas DataFrame should be written to the Excel table.
- Returns
- Return type
Examples
import pandas as pd import xlwings as xw sheet = xw.Book('Book1.xlsx').sheets[0] table_name = 'mytable' # Sample DataFrame nrows, ncols = 3, 3 df = pd.DataFrame(data=nrows * [ncols * ['test']], columns=['col ' + str(i) for i in range(ncols)]) # Hide the index, then insert a new table if it doesn't exist yet, # otherwise update the existing one df = df.set_index('col 0') if table_name in [table.name for table in sheet.tables]: sheet.tables[table_name].update(df) else: mytable = sheet.tables.add(source=sheet['A1'], name=table_name).update(df)
Font¶
- class xlwings.main.Font(impl)¶
The font object can be accessed as an attribute of the range or shape object.
mysheet['A1'].font
mysheet.shapes[0].font
New in version 0.23.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.23.0.
- property bold¶
Returns or sets the bold property (boolean).
>>> sheet['A1'].font.bold = True >>> sheet['A1'].font.bold True
New in version 0.23.0.
- property color¶
Returns or sets the color property (tuple).
>>> sheet['A1'].font.color = (255, 0, 0) # or '#ff0000' >>> sheet['A1'].font.color (255, 0, 0)
New in version 0.23.0.
- property italic¶
Returns or sets the italic property (boolean).
>>> sheet['A1'].font.italic = True >>> sheet['A1'].font.italic True
New in version 0.23.0.
- property name¶
Returns or sets the name of the font (str).
>>> sheet['A1'].font.name = 'Calibri' >>> sheet['A1'].font.name Calibri
New in version 0.23.0.
- property size¶
Returns or sets the size (float).
>>> sheet['A1'].font.size = 13 >>> sheet['A1'].font.size 13
New in version 0.23.0.
Characters¶
- class xlwings.main.Characters(impl)¶
The characters object can be accessed as an attribute of the range or shape object.
mysheet['A1'].characters
mysheet.shapes[0].characters
Note
On macOS,
characters
are currently not supported due to bugs/lack of support in AppleScript.New in version 0.23.0.
- property api¶
Returns the native object (
pywin32
orappscript
obj) of the engine being used.New in version 0.23.0.
- property font¶
Returns or sets the text property of a
characters
object.>>> sheet['A1'].characters[1:3].font.bold = True >>> sheet['A1'].characters[1:3].font.bold True
New in version 0.23.0.
- property text¶
Returns or sets the text property of a
characters
object.>>> sheet['A1'].value = 'Python' >>> sheet['A1'].characters[:3].text Pyt
New in version 0.23.0.
Markdown¶
- class xlwings.pro.Markdown(text, style=<MarkdownStyle> h1.font: .bold: True paragraph.blank_lines_after: 1 unordered_list.bullet_character: • unordered_list.blank_lines_after: 1 strong.bold: True emphasis.italic: True)¶
Markdown objects can be assigned to a single cell or shape via
myrange.value
ormyshape.text
. They accept a string in Markdown format which will cause the text in the cell to be formatted accordingly. They can also be used inmysheet.render_template()
.Note
On macOS, formatting is currently not supported, but things like bullet points will still work.
- Parameters
text (str) – The text in Markdown syntax
style (MarkdownStyle object, optional) – The MarkdownStyle object defines how the text will be formatted.
Examples
>>> mysheet['A1'].value = Markdown("A text with *emphasis* and **strong** style.") >>> myshape.text = Markdown("A text with *emphasis* and **strong** style.")
New in version 0.23.0.
MarkdownStyle¶
- class xlwings.pro.MarkdownStyle¶
MarkdownStyle
defines howMarkdown
objects are being rendered in Excel cells or shapes. Start by instantiating aMarkdownStyle
object. Printing it will show you the current (default) style:>>> style = MarkdownStyle() >>> style <MarkdownStyle> h1.font: .bold: True h1.blank_lines_after: 1 paragraph.blank_lines_after: 1 unordered_list.bullet_character: • unordered_list.blank_lines_after: 1 strong.bold: True emphasis.italic: True
You can override the defaults, e.g., to make
**strong**
text red instead of bold, do this:>>> style.strong.bold = False >>> style.strong.color = (255, 0, 0) >>> style.strong strong.color: (255, 0, 0)
New in version 0.23.0.
UDF decorators¶
- xlwings.func(category='xlwings', volatile=False, call_in_wizard=True)¶
Functions decorated with
xlwings.func
will be imported asFunction
to Excel when running “Import Python UDFs”.- categoryint or str, default “xlwings”
1-14 represent built-in categories, for user-defined categories use strings
New in version 0.10.3.
- volatilebool, 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_wizardbool, 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 asSub
(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
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)))
Dynamic array:
Note
If your version of Excel supports the new native dynamic arrays, then you don’t have to do anything special, and you shouldn’t use the
expand
decorator! To check if your version of Excel supports it, see if you have the=UNIQUE()
formula available. Native dynamic arrays were introduced in Office 365 Insider Fast at the end of September 2018.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.
Reports¶
Required Notice: Copyright (C) Zoomer Analytics GmbH.
xlwings PRO is dual-licensed under one of the following licenses:
PolyForm Noncommercial License 1.0.0 (for noncommercial use): https://polyformproject.org/licenses/noncommercial/1.0.0
xlwings PRO License (for commercial use): https://github.com/xlwings/xlwings/blob/main/LICENSE_PRO.txt
Commercial licenses can be purchased at https://www.xlwings.org
- class xlwings.pro.reports.Image(filename)¶
Use this class to provide images to either
render_template()
.- Parameters
filename (str or pathlib.Path object) – The file name or path
- class xlwings.pro.reports.Markdown(text, style=<MarkdownStyle> h1.font: .bold: True paragraph.blank_lines_after: 1 unordered_list.bullet_character: • unordered_list.blank_lines_after: 1 strong.bold: True emphasis.italic: True)¶
Markdown objects can be assigned to a single cell or shape via
myrange.value
ormyshape.text
. They accept a string in Markdown format which will cause the text in the cell to be formatted accordingly. They can also be used inmysheet.render_template()
.Note
On macOS, formatting is currently not supported, but things like bullet points will still work.
- Parameters
text (str) – The text in Markdown syntax
style (MarkdownStyle object, optional) – The MarkdownStyle object defines how the text will be formatted.
Examples
>>> mysheet['A1'].value = Markdown("A text with *emphasis* and **strong** style.") >>> myshape.text = Markdown("A text with *emphasis* and **strong** style.")
New in version 0.23.0.
- class xlwings.pro.reports.MarkdownStyle¶
MarkdownStyle
defines howMarkdown
objects are being rendered in Excel cells or shapes. Start by instantiating aMarkdownStyle
object. Printing it will show you the current (default) style:>>> style = MarkdownStyle() >>> style <MarkdownStyle> h1.font: .bold: True h1.blank_lines_after: 1 paragraph.blank_lines_after: 1 unordered_list.bullet_character: • unordered_list.blank_lines_after: 1 strong.bold: True emphasis.italic: True
You can override the defaults, e.g., to make
**strong**
text red instead of bold, do this:>>> style.strong.bold = False >>> style.strong.color = (255, 0, 0) >>> style.strong strong.color: (255, 0, 0)
New in version 0.23.0.
- xlwings.pro.reports.render_template(template, output, book_settings=None, app=None, **data)¶
This function requires xlwings PRO.
This is a convenience wrapper around
mysheet.render_template
Writes the values of all key word arguments to the
output
file according to thetemplate
and the variables contained in there (Jinja variable syntax). Following variable types are supported:strings, numbers, lists, simple dicts, NumPy arrays, Pandas DataFrames, pictures and Matplotlib/Plotly figures.
- Parameters
template (str) – Path to your Excel template, e.g.
r'C:\Path\to\my_template.xlsx'
output (str) – Path to your Report, e.g.
r'C:\Path\to\my_report.xlsx'
book_settings (dict, default None) – A dictionary of
xlwings.Book
parameters, for details see:xlwings.Book
. For example:book_settings={'update_links': False}
.app (xlwings App, default None) – By passing in an xlwings App instance, you can control where your report runs and configure things like
visible=False
. For details seexlwings.App
. By default, it creates the report in the currently active instance of Excel.data (kwargs) – All key/value pairs that are used in the template.
- Returns
- Return type
xlwings Book
Examples
In
my_template.xlsx
, put the following Jinja variables in two cells:{{ title }}
and{{ df }}
>>> from xlwings.pro.reports import render_template >>> import pandas as pd >>> df = pd.DataFrame(data=[[1,2],[3,4]]) >>> mybook = render_template('my_template.xlsx', 'my_report.xlsx', title='MyTitle', df=df)
With many template variables it may be useful to collect the data first:
>>> data = dict(title='MyTitle', df=df) >>> mybook = render_template('my_template.xlsx', 'my_report.xlsx', **data)
If you need to handle external links or a password, use it like so:
>>> mybook = render_template('my_template.xlsx', 'my_report.xlsx', book_settings={'update_links': True, 'password': 'mypassword'}, **data)