Blog
How To Reference the Last Row or Column Using xlwings?
Method
Last row
#xlwings
sht.range(‘A1’).end(‘down’).select()
sht.range(‘A1’).end(‘down’).row
#sht.cells(1,1).end(‘down’).row
#sht.range(‘A’+str(sht.api.Rows.Count)).end(‘up’).row
#sht.cells(sht.api.Rows.Count,1).end(‘up’).row
#xlwings API
#sht.api.Range(‘A1’).End(xw.constants.Direction.xlDown).Row
#sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row
#sht.api.Range(‘A’+str(sht.api.Rows.Count)).End(xw.constants.Direction.xlUp).Row
#sht.api.Cells(sht.api.Rows.Count,1).End(xw.constants.Direction.xlUp).Row
Last column
#xlwings
#sht.range(‘A1’).end(‘right’).column
#sht.cells(1,1).end(‘right’).column
#sht.cells(1,sht.api.Columns.Count).end(‘left’).column
#xlwings API
#sht.api.Range(‘A1’).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,1).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,sht.api.Columns.Count).End(xw.constants.Direction.xlToLeft).Column
Sample Code
#Referencing the Last Row or Column
import xlwings as xw #Import the xlwings package
app=xw.App()
bk=app.books.open('last.xlsx') #Get the active workbook
sht=bk.sheets.active #Get the active worksheet
#Last row
#xlwings
sht.range('A1').end('down').select()
print(sht.range('A1').end('down').row)
#sht.cells(1,1).end('down').row
#sht.range('A'+str(sht.api.Rows.Count)).end('up').row
#sht.cells(sht.api.Rows.Count,1).end('up').row
#xlwings API
#sht.api.Range('A1').End(xw.constants.Direction.xlDown).Row
#sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row
#sht.api.Range('A'+str(sht.api.Rows.Count)).End(xw.constants.Direction.xlUp).Row
#sht.api.Cells(sht.api.Rows.Count,1).End(xw.constants.Direction.xlUp).Row
#Last column
#xlwings
#sht.range('A1').end('right').column
#sht.cells(1,1).end('right').column
#sht.cells(1,sht.api.Columns.Count).end('left').column
#xlwings API
#sht.api.Range('A1').End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,1).End(xw.constants.Direction.xlToRight).Column
#sht.api.Cells(1,sht.api.Columns.Count).End(xw.constants.Direction.xlToLeft).Column
#bk.close()
#app.kill()
How To Expand the Reference to Cell Ranges in the Current Worksheet Using xlwings?
Method
#xlwings
sht.range(‘C2’).resize(3).select() #Create a cell range C2:C4
#sht.range(‘C2’).resize(1, 3).select() #Create a cell range C2:E2
#sht.range(‘C2’).resize(3, 3).select() #Create a cell range C2:E4
#expand method
#sht.range(‘C4’).expand(‘table’).select()
#sht.range(‘C4’).expand().select() #Equivalent to the method above
#sht.range(‘C4’).expand(‘down’).select()
#sht.range(‘C4’).expand(‘right’).select()
#xlwings API
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(3)).Select()
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(1, 3)).Select()
#sht.api.Range(‘C2’, sht.api.Range(‘C2’).Resize(3, 3)).Select()
Sample Code
#Expand the reference to the current worksheet's cell 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('C2').resize(3).select() #Create a cell range C2:C4
#sht.range('C2').resize(1, 3).select() #Create a cell range C2:E2
#sht.range('C2').resize(3, 3).select() #Create a cell range C2:E4
#expand method
#sht.range('C4').expand('table').select()
#sht.range('C4').expand().select() #Equivalent to the method above
#sht.range('C4').expand('down').select()
#sht.range('C4').expand('right').select()
#xlwings API
#sht.api.Range('C2', sht.api.Range('C2').Resize(3)).Select()
#sht.api.Range('C2', sht.api.Range('C2').Resize(1, 3)).Select()
#sht.api.Range('C2', sht.api.Range('C2').Resize(3, 3)).Select()
#bk.close()
#app.kill()
How To Reference a Set of Cell Ranges Using xlwings?
Method
#xlwings API
#Union of two cell ranges
app.api.Union(sht.api.Range(‘B4:D8’), sht.api.Range(‘C2:F5’)).Select()
#Intersect of two cell ranges
#app.api.Intersect(sht.api.Range(‘B4:D8’), sht.api.Range(‘C2:F5’)).Select()
Sample Code
#Set Operations of 2 Cell Ranges
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 API
#Union of two cell ranges
#app.api.Union(sht.api.Range('B4:D8'), sht.api.Range('C2:F5')).Select()
#Intersect of two cell ranges
app.api.Intersect(sht.api.Range('B4:D8'), sht.api.Range('C2:F5')).Select()
#bk.close()
#app.kill()
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()