Range

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

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

Arguments

cell1str 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’

cell2str 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

import xlwings as xw
sheet1 = xw.Book("MyBook.xlsx").sheets[0]

sheet1.range("A1")
sheet1.range("A1:C3")
sheet1.range((1,1))
sheet1.range((1,1), (3,3))
sheet1.range("NamedRange")

# Or using index/slice notation
sheet1["A1"]
sheet1["A1:C3"]
sheet1[0, 0]
sheet1[0:4, 0:4]
sheet1["NamedRange"]

Adds a hyperlink to the specified Range (single Cell)

Arguments

addressstr

The address of the hyperlink.

text_to_displaystr, 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.’

Added in version 0.3.0.

property address

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

Added in version 0.9.0.

adjust_indent(amount)

Adjusts the indentation in a Range.

Arguments

amountint

Number of spaces by which the indent is adjusted. Can be positive or negative.

property api

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

Added in version 0.9.0.

autofill(destination, type_='fill_default')

Autofills the destination Range. Note that the destination Range must include the origin Range.

Arguments

destinationRange

The origin.

type_str, default "fill_default"

One of the following strings: "fill_copy", "fill_days", "fill_default", "fill_formats", "fill_months", "fill_series", "fill_values", "fill_weekdays", "fill_years", "growth_trend", "linear_trend", "flash_fill

Added in version 0.30.1.

autofit()

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

  • To autofit only the width of the columns use myrange.columns.autofit()

  • To autofit only the height of the rows use myrange.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.

clear_formats()

Clears the format of a Range but leaves the content.

Added in version 0.26.2.

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 to None, see Examples.

Returns

RGB : tuple

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> sheet1 = xw.sheets[0]
>>> sheet1.range('A1').color = (255, 255, 255)  # or '#ffffff'
>>> sheet1.range('A2').color
(255, 255, 255)
>>> sheet1.range('A2').color = None
>>> sheet1.range('A2').color is None
True

Added in version 0.3.0.

property column

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

Returns

Integer

Added 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

float

Added in version 0.4.0.

property columns

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

Added in version 0.9.0.

copy(destination=None)

Copy a range to a destination range or clipboard.

Parameters

destinationxlwings.Range

xlwings Range to which the specified range will be copied. If omitted, the range is copied to the clipboard.

Returns

None

copy_picture(appearance='screen', format='picture')

Copies the range to the clipboard as picture.

Parameters

appearancestr, default ‘screen’

Either ‘screen’ or ‘printer’.

formatstr, default ‘picture’

Either ‘picture’ or ‘bitmap’.

Added in version 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 and Shift-Ctrl-Space on Mac.

Returns

Range object

delete(shift=None)

Deletes a cell or range of cells.

Parameters

shiftstr, default None

Use left or up. If omitted, Excel decides based on the shape of the range.

Returns

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()
>>> sheet1 = xw.sheets[0]
>>> sheet1.range('A1:B2').value = 1
>>> sheet1.range('A1').end('down')
<Range [Book1]Sheet1!$A$2>
>>> sheet1.range('B2').end('right')
<Range [Book1]Sheet1!$B$2>

Added 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

modestr, default ‘table’

One of 'table' (=down and right), 'down', 'right'.

Returns

Range

Examples

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

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

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

Arguments

row_absolutebool, default True

Set to True to return the row part of the reference as an absolute reference.

column_absolutebool, default True

Set to True to return the column part of the reference as an absolute reference.

include_sheetnamebool, default False

Set to True to include the Sheet name in the address. Ignored if external=True.

externalbool, default False

Set to True to return an external reference with workbook and worksheet name.

Returns

str

Examples

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

Added in version 0.2.3.

group(by=None)

Group rows or columns.

Arguments

bystr, optional

“columns” or “rows”. Figured out automatically if the range is defined as ‘1:3’ or ‘A:C’, respectively.

property has_array

True if the range is part of a legacy CSE Array formula and False otherwise.

property height

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

Returns

float

Added in version 0.4.0.

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

Examples

>>> import xlwings as xw
>>> wb = xw.Book()
>>> sheet1 = wb.sheets[0]
>>> sheet1.range('A1').value
'www.xlwings.org'
>>> sheet1.range('A1').hyperlink
'http://www.xlwings.org'

Added in version 0.3.0.

insert(shift, copy_origin='format_from_left_or_above')

Insert a cell or range of cells into the sheet.

Parameters

shiftstr

Use right or down.

copy_originstr, default format_from_left_or_above

Use format_from_left_or_above or format_from_right_or_below. Note that copy_origin is only supported on Windows.

Returns

None

Changed in version 0.30.3: shift is now a required argument.

property last_cell

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

Returns

Range

Example

>>> import xlwings as xw
>>> wb = xw.Book()
>>> sheet1 = wb.sheets[0]
>>> myrange = sheet1.range('A1:E4')
>>> myrange.last_cell.row, myrange.last_cell.column
(4, 5)

Added 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

float

Added in version 0.6.0.

merge(across=False)

Creates a merged cell from the specified Range object.

Parameters

acrossbool, 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, otherwise False

property name

Sets or gets the name of a Range.

Added in version 0.4.0.

property note

Returns a Note object. Before the introduction of threaded comments, a Note was called a Comment.

Added 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()
>>> sheet1 = wb.sheets[0]
>>> sheet1.range('A1').number_format
'General'
>>> sheet1.range('A1:C3').number_format = '0.00%'
>>> sheet1.range('A1:C3').number_format
'0.00%'

Added 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 : Range

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

Arguments

convertobject, default None

A converter, e.g. dict, np.array, pd.DataFrame, pd.Series, defaults to default converter

Keyword Arguments

ndimint, default None

number of dimensions

numberstype, default None

type of numbers, e.g. int

datestype, default None

e.g. datetime.date defaults to datetime.datetime

emptyobject, default None

transformation of empty cells

transposeBoolean, default False

transpose values

expandstr, default None

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

chunksizeint

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.

err_to_strBoolean, default False

If True, will include cell errors such as #N/A as strings. By default, they will be converted to None.

Added in version 0.28.0.

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

Returns

Range object

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

Pastes a range from the clipboard into the specified range.

Parameters

pastestr, 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.

operationstr, default None

One of “add”, “divide”, “multiply”, “subtract”.

skip_blanksbool, default False

Set to True to skip over blank cells

transposebool, default False

Set to True to transpose rows and columns.

Returns

None

property raw_value

Gets and sets the values directly as delivered from/accepted by the engine that s 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

Arguments

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

Added in version 0.3.0.

property row

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

Returns

Integer

Added 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

float

Added in version 0.4.0.

property rows

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

Added in version 0.9.0.

select()

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

Added in version 0.9.0.

property shape

Tuple of Range dimensions.

Added in version 0.3.0.

property sheet

Returns the Sheet object to which the Range belongs.

Added in version 0.9.0.

property size

Number of elements in the Range.

Added in version 0.3.0.

property table

Returns a Table object if the range is part of one, otherwise None.

Added in version 0.21.0.

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

Exports the range as PDF.

Parameters

pathstr or path-like, default None

Path where you want to store the pdf. Defaults to the address of the range in the same directory as the Excel file if the Excel file is stored and to the current working directory otherwise.

layoutstr or path-like object, default None

This argument requires xlwings PRO.

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

showbool, default False

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

qualitystr, default 'standard'

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

Added in version 0.26.2.

to_png(path=None)

Exports the range as PNG picture.

Parameters

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

Added in version 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

float

Added in version 0.6.0.

ungroup(by=None)

Ungroup rows or columns

Arguments

bystr, optional

“columns” or “rows”. Figured out automatically if the range is defined as ‘1:3’ or ‘A:C’, respectively.

unmerge()

Separates a merged area into individual cells.

property value

Gets and sets the values for the given Range. See xlwings.Range.options() about how to set options, e.g., to transform it into a DataFrame or how to set a chunksize.

Returns

objectreturned object depends on the converter being used,

see xlwings.Range.options()

property width

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

Returns

float

Added in version 0.4.0.

property wrap_text

Returns True if the wrap_text property is enabled and False if it’s disabled. If not all cells have the same value in a range, on Windows it returns None and on macOS False.

Added in version 0.23.2.