Blog

How To Reference the Used Range of a Worksheet Using xlwings?

Method

#xlwings

sht.used_range.select()

 

#xlwings API

#sht.api.UsedRange.Select()

Sample Code

#Reference the used range of a worksheet

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.open('current.xlsx')    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.used_range.select()

#xlwings API
#sht.api.UsedRange.Select()

#bk.close()
#app.kill()

How To Reference the Current Region of a Given Cell Using xlwings?

Method

What is the current region of a given cell? 

The shaded area in the image indicates the current region of cell `C3`. The current region of a cell refers to the rectangular area that expands from the cell in all four directions until a rectangular area surrounded by empty spaces is reached, i.e., where there are empty rows or columns in the four directions (within the region, not across the entire row/column).

 
Sample Code

#Referencing the Current Cell Range of a Specified Cell

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.open('current.xlsx')    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.range('C3').current_region.select()

#xlwings API
#sht.api.Range('C3').CurrentRegion.Select()

#bk.close()
#app.kill()

How To Reference Multiple Cell Ranges at Once Using xlwings?

Method

#xlwings

sht[‘A2, B3:C8, E2:F5’].select()    #Reference multiple cell ranges

#sht.range(‘A2, B3:C8, E2:F5’).select()

 

#xlwings API

#sht.api.Range(‘A2, B3:C8, E2:F5’).Select()

Sample Code

#Reference multiple cell ranges at once

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht['A2, B3:C8, E2:F5'].select()    #Reference multiple cell ranges
#sht.range('A2, B3:C8, E2:F5').select()

#xlwings API
#sht.api.Range('A2, B3:C8, E2:F5').Select()

#bk.close()
#app.kill()

How To Reference All Cells Using xlwings?

Method

#xlwings

sht.cells.select()    #Referencing all cells

 

#xlwings API

#sht.api.Cells.Select()

#sht.api.Range(sht.api.Cells(1,1),sht.api.Cells(sht.api.Cells.Rows.Count,sht.api.Cells. Columns.Count)).Select()

 

#Reference all rows

#sht.api.Rows.Select()

#Reference all columns

#sht.api.Columns.Select()

Sample Code

#Referencing All Cells

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.cells.select()    #Referencing all cells 

#xlwings API
#sht.api.Cells.Select()
#sht.api.Range(sht.api.Cells(1,1),sht.api.Cells(sht.api.Cells.Rows.Count,sht.api.Cells. Columns.Count)).Select()

#Reference all rows
#sht.api.Rows.Select()
#Reference all columns
#sht.api.Columns.Select()

#bk.close()
#app.kill()

How To Reference Cells In a Cell Range Using xlwings?

Method

Coordinate Index

#xlwings

rng=sht.range(‘B2:D5’)

rng[0,0].select()    #B2，Note: The index is 0-based

 

#xlwings API

#rng=sht.api.Range(‘B2:D5’)

#rng(1,1).Select()

 

Linear Index

#xlwings

#rng=sht.range(‘B2:D5’)

#rng[2].select()

 

#xlwings API

#rng=sht.api.Range(‘B2:D5’)

# rng(3).Select()

 

Slicing

#xlwings

#rng=sht.range(‘B2:D5’)

#rng[1:3,1:3].select()    #Slicing C3:D4

#rng[:,2].select()     #Slicing  D2:D5

Sample Code

#Referencing Cells Within a Range

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active      #Get the active worksheet

#Coordinate Index
#xlwings
rng=sht.range('B2:D5')
rng[0,0].select()    #B2，Note: The index is 0-based

#xlwings API
#rng=sht.api.Range('B2:D5')
#rng(1,1).Select()

#Linear Index
#xlwings
#rng=sht.range('B2:D5')
#rng[2].select()

#xlwings API
#rng=sht.api.Range('B2:D5')
# rng(3).Select()

#Slicing
#xlwings
#rng=sht.range('B2:D5')
#rng[1:3,1:3].select()    #Slicing C3:D4
#rng[:,2].select()     #Slicing  D2:D5

#bk.close()
#app.kill()

How To Reference Cells by Name Range Using xlwings?

Method

#xlwings

cl = sht.range(‘A3:C8’)

cl.name = ‘MyData’    #Create a name

sht.range(‘MyData’).select()    #Reference by Name

 

#xlwings API

#cl = sht.api.Range(‘A3:C8’)

#cl.Name = ‘MyData’

#sht.api.Range(‘MyData’).Select()

Sample Code

#Reference a cell range by its name

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
cl = sht.range('A3:C8')
cl.name = 'MyData'    #Create a name
sht.range('MyData').select()    #Reference by Name

#xlwings API
#cl = sht.api.Range('A3:C8')
#cl.Name = 'MyData'
#sht.api.Range('MyData').Select()

#bk.close()
#app.kill()

How To Reference Offsets Constructed Cell Ranges Using xlwings?

Method

#xlwings

sht.range(‘A3:C8’).offset(1).select()     #A4:C9

#sht.range(‘A3:C8’).offset(0,1).select()     #B3:D8

#sht.range(‘A3:C8’).offset(1,1).select()     #B4:D9

 

#xlwings API

#sht.api.Range(sht.api.Range(‘A3’).Offset(2),sht.api.Range(‘C8’).Offset(2)).Select()     #A4:C9

#sht.api.Range(sht.api.Range(‘A3’).Offset(1,2),sht.api.Range(‘C8’).Offset(1,2)).Select()     #B3:D8

#sht.api.Range(sht.api.Range(‘A3’).Offset(2,2),sht.api.Range(‘C8’).Offset(2,2)).Select()     #B4:D9

Sample Code

#Reference an offset-constructed range

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.range('A3:C8').offset(1).select()     #A4:C9
#sht.range('A3:C8').offset(0,1).select()     #B3:D8
#sht.range('A3:C8').offset(1,1).select()     #B4:D9

#xlwings API
#sht.api.Range(sht.api.Range('A3').Offset(2),sht.api.Range('C8').Offset(2)).Select()     #A4:C9
#sht.api.Range(sht.api.Range('A3').Offset(1,2),sht.api.Range('C8').Offset(1,2)).Select()     #B3:D8
#sht.api.Range(sht.api.Range('A3').Offset(2,2),sht.api.Range('C8').Offset(2,2)).Select()     #B4:D9

#bk.close()
#app.kill()

How To Reference General Cell Ranges Using xlwings?

Method

#xlwings

sht.range(‘A3:C8’).select()    #Select the cell range A3:C8

#sht.range(‘A3′,’C8’).select()

#sht.range(sht.range(‘A3’),sht.range(‘C8’)).select()

#sht.range(sht.cells(3,1),sht.cells(8,3)).select()

#sht.range((3,1),(8,3)).select()

 

#xlwings API

#sht.api.Range(‘A3:C8’).Select()

#sht.api.Range(‘A3′,’C8’).Select()

#sht.api.Range(sht.api.Range(‘A3’), sht.api.Range(‘C8’)).Select()

#sht.api.Range(sht.api.Cells(3,1),sht.api.Cells(8,3)).Select()

Sample Code

#Referencing a General Range of Cells

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#xlwings
sht.range('A3:C8').select()    #Select the cell range A3:C8
#sht.range('A3','C8').select()
#sht.range(sht.range('A3'),sht.range('C8')).select()
#sht.range(sht.cells(3,1),sht.cells(8,3)).select()
#sht.range((3,1),(8,3)).select()

#xlwings API
#sht.api.Range('A3:C8').Select()
#sht.api.Range('A3','C8').Select()
#sht.api.Range(sht.api.Range('A3'), sht.api.Range('C8')).Select()
#sht.api.Range(sht.api.Cells(3,1),sht.api.Cells(8,3)).Select()

#bk.close()
#app.kill()

How To Reference Entire Columns Using xlwings?

Method

Single Column

#xlwings

sht.range(‘A:A’).select()    #Column 1

 

#xlwings API

#sht.api.Columns(1).Select()

#sht.api.Columns(‘A’).Select()

#sht.api.Range(‘A:A’).Select()

#sht.api.Range(‘A1’).EntireColumn.Select()

 

Multiple Columns

#xlwings

#sht.range(‘B:C’).select()

#sht[:,1:3].select()

 

#xlwings API

#sht.api.Columns(‘B:C’).Select()

#sht.api.Range(‘B:C’).Select()

#sht.api.Range(‘B1:C2’).EntireColumn.Select()

Sample Code

#Referencing an Entire Column

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#Single Column
#xlwings
sht.range('A:A').select()    #Column 1

#xlwings API
#sht.api.Columns(1).Select()
#sht.api.Columns('A').Select()
#sht.api.Range('A:A').Select()
#sht.api.Range('A1').EntireColumn.Select()

#Multiple Columns
#xlwings
#sht.range('B:C').select()
#sht[:,1:3].select()

#xlwings API
#sht.api.Columns('B:C').Select()
#sht.api.Range('B:C').Select()
#sht.api.Range('B1:C2').EntireColumn.Select()

#bk.close()
#app.kill()

How To Reference Entire Rows Using xlwings?

Method

Single Row

#xlwings

sht.range(‘1:1’).select()     #Row 1

#sht[‘1:1’].select()

 

#xlwings API

#sht.api.Rows(1).Select()

#sht.api.Range(‘1:1’).Select()

#sht.api.Range(‘A1’).EntireRow.Select()

 

Multiple Rows

#xlwings

#sht.range(‘1:5’).select()    #Rows 1–5

#sht[‘1:5’].select()

#sht[0:5,:].select()

 

#xlwings API

#sht.api.Rows(‘1:5’).Select()

#sht.api.Range(‘1:5’).Select()

#sht.api.Range(‘A1:C5’).EntireRow.Select()

Sample Code

#Referencing an Entire Row

import xlwings as xw    #Import the xlwings package

app=xw.App()
bk=app.books.active    #Get the active workbook
sht=bk.sheets.active    #Get the active worksheet

#Single Row
#xlwings
sht.range('1:1').select()     #Row 1
#sht['1:1'].select()

#xlwings API
#sht.api.Rows(1).Select()
#sht.api.Range('1:1').Select()
#sht.api.Range('A1').EntireRow.Select()

#Multiple Rows
#xlwings
#sht.range('1:5').select()    #Rows 1–5
#sht['1:5'].select()
#sht[0:5,:].select()

#xlwings API
#sht.api.Rows('1:5').Select()
#sht.api.Range('1:5').Select()
#sht.api.Range('A1:C5').EntireRow.Select()

#bk.close()
#app.kill()