Blog
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()
How To Reference Cells with Variables Using xlwings?
Method
#xlwings
i=3 #Create a variable `i`
sht.range(“C”+ str(i)).select() #Reference a cell using the variable `i`
#sht.cells(i,i).select()
#xlwings API
#i=3
#sht.api.Range(“C”+ str(i)).Select()
#sht.api.Cells(i,i).Select()
Sample Code
#Referencing Cells Using Variables
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
i=3 #Create a variable `i`
sht.range("C"+ str(i)).select() #Reference a cell using the variable `i`
#sht.cells(i,i).select()
#xlwings API
#i=3
#sht.api.Range("C"+ str(i)).Select()
#sht.api.Cells(i,i).Select()
#bk.close()
#app.kill()
How To Reference Cells by Name Using xlwings?
Method
#xlwings
cl=sht.cells(3,3)
cl.name=’test’ #Setting the cell name
sht.range(‘test’).select() #Referencing the cell by it’s name
#xlwings API
#cl=sht.api.Range(‘C3’)
#cl.Name=’test’
#sht.api.Range(‘test’).Select()
Sample Code
#Referencing by Cell 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.cells(3,3)
cl.name='test' #Setting the cell name
sht.range('test').select() #Referencing the cell by it's name
#xlwings API
#cl=sht.api.Range('C3')
#cl.Name='test'
#sht.api.Range('test').Select()
#bk.close()
#app.kill()
How To Reference Multiple Cells Using xlwings?
Method
#xlwings
sht.range(‘B2, C5, D7’).select()
#sht[‘B2, C5, D7’].select()
#xlwings API
#sht.api.Range(‘B2, C5, D7’).Select()
Sample Code
#Referencing Multiple 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('B2, C5, D7').select()
#sht['B2, C5, D7'].select()
#xlwings API
#sht.api.Range('B2, C5, D7').Select()
#bk.close()
#app.kill()
How To Reference a Single Cell Using xlwings?
Method
#xlwings
sht.range(‘A1’).select()
#sht.range(1,1).select()
#sht[‘A1’].select()
#sht.cells(1,1).select()
#sht.cells(1,’A’).select()
#xlwings API
#sht.api.Range(‘A1’).Select()
#sht.api.Cells(1, ‘A’).Select()
#sht.api.Cells(1,1).Select()
Sample Code
#Referencing a Single Cell
import xlwings as xw #Import the xlwings package
bk=xw.Book()
sht=bk.sheets.active #Get the active worksheet
#xlwings
sht.range('C3').select()
#sht.range(3,3).select()
#sht['C3'].select()
#sht.cells(3,3).select()
#sht.cells(3,'C').select()
#xlwings API
#sht.api.Range('C3').Select()
#sht.api.Cells(3, 'C').Select()
#sht.api.Cells(3,3).Select()
#bk.close()
#app.kill()
How To Set Row Height and Column Width Using xlwings?
Method
Row height
#xlwings API
sht.api.Rows(3).RowHeight = 30
#sht.api.Range(‘C5’).EntireRow.RowHeight = 40
#sht.api.Range(‘C5’).RowHeight = 40
#sht.api.Cells.RowHeight = 30
Column width
#sht.api.Columns(2).ColumnWidth = 20
#sht.api.Range(‘C4’).ColumnWidth = 15
#sht.api.Range(‘C4’).EntireColumn.ColumnWidth = 15
#sht.api.Cells.ColumnWidth = 10
Sample Code
#Setting Row Heights and Column Widths
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
#Row height
#xlwings API
sht.api.Rows(3).RowHeight = 30
#sht.api.Range('C5').EntireRow.RowHeight = 40
#sht.api.Range('C5').RowHeight = 40
#sht.api.Cells.RowHeight = 30
#Column width
sht.api.Columns(2).ColumnWidth = 20
#sht.api.Range('C4').ColumnWidth = 15
#sht.api.Range('C4').EntireColumn.ColumnWidth = 15
#sht.api.Cells.ColumnWidth = 10
#bk.close()
#app.kill()
How To Delete Rows and Columns Using xlwings?
Method
Delete empty rows
#xlwings API
sht.api.Columns(‘A:A’).SpecialCells(xw.constants.CellType.xlCellTypeBlanks).EntireRow.Delete()
Delete duplicate rows
#a=sht.cells(sht.api.Rows.Count, 1).end(‘up’).row
#for i in range(a,1,-1):
# if app.api.WorksheetFunction.CountIf(sht.api.Columns(1), sht.api.Cells(i,1))>1:
# sht.api.Rows(i).Delete()
Sample Code
#Delete rows
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
sht.range('a1').value='abd'
sht.range('a3').value='efg'
sht.range('a4').value='efg'
#Delete empty rows
#xlwings API
#sht.api.Columns('A:A').SpecialCells(xw.constants.CellType.xlCellTypeBlanks).EntireRow.Delete()
#Delete duplicate rows
a=sht.cells(sht.api.Rows.Count, 1).end('up').row
for i in range(a,1,-1):
if app.api.WorksheetFunction.CountIf(sht.api.Columns(1), sht.api.Cells(i,1))>1:
sht.api.Rows(i).Delete()
#bk.close()
#app.kill()How To Insert Rows and Columns Using xlwings?
Method
Row
#xlwings
sht.range(‘A2’).color=(0,255,0)
#sht.range(‘C2’).color=(0,0,255)
#sht.range(‘E2’).color=(255,0,0)
#sht[‘3:3′].insert(shift=’down’,copy_origin=’format_from_left_or_above’)
#xlwings API
#sht.api.Range(‘A2’).Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range(‘C2’).Interior.Color=xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range(‘E2’).Interior.Color=xw.utils.rgb_to_int((255, 0, 0))
#sht.api.Rows(3).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown,CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)
Column
#xlwings
#sht[‘B:B’].insert()
#xlwings API
#sht.api.Columns(2).Insert()
Sample Code
# Insert rows and columns
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
#Row
#xlwings
sht.range('A2').color=(0,255,0)
sht.range('C2').color=(0,0,255)
sht.range('E2').color=(255,0,0)
sht['3:3'].insert(shift='down',copy_origin='format_from_left_or_above')
#xlwings API
#sht.api.Range('A2').Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range('C2').Interior.Color=xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range('E2').Interior.Color=xw.utils.rgb_to_int((255, 0, 0))
#sht.api.Rows(3).Insert(Shift=xw.constants.InsertShiftDirection.xlShiftDown,CopyOrigin=xw.constants.InsertFormatOrigin.xlFormatFromLeftOrAbove)
#Column
#xlwings
#sht['B:B'].insert()
#xlwings API
#sht.api.Columns(2).Insert()
#bk.close()
#app.kill()