Range#
- class 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
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"]
- 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 parameters.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.
- autofill(destination, type_='fill_default')#
Autofills the destination Range. Note that the destination Range must include the origin Range.
- Parameters
destination (Range) – 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
New 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.
New 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 toNone
, see Examples.- Returns
RGB
- Return type
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
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’.
New 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 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() >>> 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>
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() >>> 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
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() >>> 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'
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() >>> sheet1 = wb.sheets[0] >>> sheet1.range('A1').value 'www.xlwings.org' >>> sheet1.range('A1').hyperlink 'http://www.xlwings.org'
New 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
shift (str) – Use
right
ordown
.copy_origin (str, default format_from_left_or_above) – Use
format_from_left_or_above
orformat_from_right_or_below
. Note that copy_origin is only supported on Windows.
- Returns
- Return type
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
- Return type
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)
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() >>> 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%'
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.err_to_str (Boolean, default False) –
If
True
, will include cell errors such as#N/A
as strings. By default, they will be converted toNone
.New in version 0.28.0.
:keyword => For converter-specific options, see Converters and Options.:
- Returns
- Return type
Range object
- 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 s 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_pdf(path=None, layout=None, show=None, quality='standard')#
Exports the range as PDF.
- Parameters
path (str 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.
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).
show (bool, default False) – Once created, open the PDF file with the default application.
quality (str, default
'standard'
) – Quality of the PDF file. Can either be'standard'
or'minimum'
.
New in version 0.26.2.
- 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.
New 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
- 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
xlwings.Range.options()
about how to set options, e.g., to transform it into a DataFrame or how to set a chunksize.- Returns
object – see
xlwings.Range.options()
- Return type
returned object depends on the converter being used,
- 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.