Range - xlwings Documentation

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Arguments ¶

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.

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)

Arguments ¶

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

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.

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 ¶

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

Added in version 0.30.1.

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.

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
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.
Parameters¶
destinationxlwings.Range
xlwings Range to which the specified range will be copied. If omitted,
the range is copied to the clipboard.
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 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
Returns¶
Range object
Parameters¶
shiftstr, default None
Use left or up. If omitted, Excel decides based on the shape of
the range.
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'.
>>> 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.
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.
>>> wb = xw.Book()
>>> sheet1 = wb.sheets[0]
>>> sheet1.range((1,1)).get_address()
'$A$1'
>>> sheet1.range((1,1)).get_address(False, False)
>>> 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.
Arguments¶
bystr, optional
“columns” or “rows”. Figured out automatically if the range is defined as
‘1:3’ or ‘A:C’, respectively.
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'
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.
>>> 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.
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 note¶
Returns a Note object.
Before the introduction of threaded comments, a Note was called a Comment.
Added in version 0.24.2.
>>> 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
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.
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.
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).
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.
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.
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.
Arguments¶
bystr, optional
“columns” or “rows”. Figured out automatically if the range is defined as
‘1:3’ or ‘A:C’, respectively.
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 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.