Blog
How To Enter R1C1 Style Formulas using FormulaR1C1 Property Using xlwings?
Method
sht.Range(‘C8′).FormulaR1C1=’=SQRT(R1C1)’
Sample Code
# Use `FormulaR1C1` property for R1C1-style formulas
import xlwings as xw #Import the xlwings package
import os #Import the os package
root = os.getcwd() #Get the current path
#Create an Excel application window, visible,
#without opening a workbook
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula.xlsx',read_only=False)
sht=bk.api.Sheets(1) #Get the worksheet
#Use `FormulaR1C1` property for R1C1-style formulas
sht.Range('C8').FormulaR1C1='=SQRT(R1C1)'
bk.save('test.xlsx')
#bk.close()
#app.kill()
How To Enter A1 Style Formulas using Formula Property Using xlwings?
Method
sht.Range(‘C7′).Formula=’=$A$1+$A$5’
Sample Code
# Use `Formula` property for A1-style formulas
import xlwings as xw #Import the xlwings package
import os #Import the os package
root = os.getcwd() #Get the current path
#Create an Excel application window, visible,
#without opening a workbook
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula.xlsx',read_only=False)
sht=bk.api.Sheets(1) #Get the worksheet
#Use `Formula` property for A1-style formulas
sht.Range('C7').Formula='=$A$1+$A$5'
bk.save('test.xlsx')
#bk.close()
#app.kill()
How To Enter Regular Formulas Using xlwings?
Method
sht.Range(‘A7′).Value=’=A1*B2’
sht.Range(‘A8′).Value=’=SUM(A1:A5)’
Sample Code
#Adding Formulas - Regular Formulas
import xlwings as xw #Import the xlwings package
import os #Import the os package
root = os.getcwd() #Get the current path
#Create an Excel application window, visible,
#without opening a workbook
app=xw.App(visible=True, add_book=False)
#Open a data file, writable
bk=app.books.open(fullname=root+r'\Formula.xlsx',read_only=False)
sht=bk.api.Sheets(1) #Get the worksheet
#Enter regular formulas
sht.Range('A7').Value='=A1*B2'
sht.Range('A8').Value='=SUM(A1:A5)'
bk.save('test.xlsx')
#bk.close()
#app.kill()
How To Exchange Excel Worksheet and Python Dictionary Data Using xlwings?
Method
Reading
#xlwings
sht.range(‘A1:B2’).options(dict).value
#sht.range(‘A4:B5’).options(dict, transpose=True).value
Writing
#xlwings
#dic={ ‘a’: 1.0, ‘b’: 2.0}
#sht.range(‘A1:B2’).options(dict).value=dic
#sht.range(‘A4:B5’).options(dict, transpose=True).value=dic
Sample Code
#Data Read/Write Between Excel Sheets and Python Dictionaries
import xlwings as xw #Import the xlwings package
app=xw.App()
bk=app.books.open('data.xlsx')
sht=bk.sheets.active #Get the active worksheet
#Reading
#xlwings
#print(sht.range('A1:B2').options(dict).value)
#print(sht.range('A4:B5').options(dict, transpose=True).value)
#Writing
#xlwings
dic={ 'a': 1.0, 'b': 2.0}
sht.range('A1:B2').options(dict).value=dic
sht.range('A4:B5').options(dict, transpose=True).value=dic
#bk.close()
#app.kill()
How To Write Python List Data to Excel Worksheet Using xlwings?
Method
Write rows
#xlwings
lst=[1,2,3,4,5]
sht.range(‘A1’).value=lst #Write Python data to Excel worksheet
#Or
#lst=[1,2,3,4,5]
#sht.range(‘E1’).options(transpose=True).value=lst #Transpose
#xlwings API
#lst=[[1],[2],[3],[4],[5]]
#sht.api.Range(‘C1:C5’).Value=lst #Write directly
#Or
#lst=[1,2,3,4,5]
#sht.api.Range(‘E1:E5’).Value=app.api.WorksheetFunction.Transpose(lst) #Transpose
2D data
#xlwings
#sht.range(‘A5:B6’).value=[[1,2],[3,4]]
#sht.range(‘A1′).options(expand=’table’).value=[[1,2],[3,4]]
#xlwings API
#sht.api.Range(‘A5:B6’).Value=[[1,2],[3,4]]
Sample Code
#Write Python list data to Excel worksheet
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
#Write rows
#xlwings
#lst=[1,2,3,4,5]
#sht.range('A1').value=lst #Write Python data to Excel worksheet
#Or
#lst=[1,2,3,4,5]
#sht.range('E1').options(transpose=True).value=lst #Transpose
#xlwings API
#lst=[[1],[2],[3],[4],[5]]
#sht.api.Range('C1:C5').Value=lst #Write directly
#Or
#lst=[1,2,3,4,5]
#sht.api.Range('E1:E5').Value=app.api.WorksheetFunction.Transpose(lst) #Transpose
#2D data
#xlwings
sht.range('A5:B6').value=[[1,2],[3,4]]
sht.range('A1').options(expand='table').value=[[1,2],[3,4]]
#xlwings API
#sht.api.Range('A5:B6').Value=[[1,2],[3,4]]
#bk.close()
#app.kill()
How To Read Excel Data to Python List Using xlwings?
Method
Row data
#xlwings
bk=xw.Book()
sht=bk.sheets(1)
lst=sht.range(‘A1:E1’).value #Read data from a worksheet
print(lst)
#xlwings API
#sht=bk.api.Sheets(1)
#lst=sht.Range(‘A1:E1’).Value #Read data from a worksheet
#print(lst)
#print(list(lst[0]))
Column data
#xlwings
#lst=sht.range(‘A1:A5’).value
#print(lst)
#xlwings API
#lst=sht.Range(‘A1:E1’).Value #Read data from a worksheet
#lst2=[]
#for i in range(len(lst)):
# lst2.append(list(lst[i]))
#print(lst)
#print(lst2)
#lst3=[]
#for i in range(len(lst)):
# lst3.append(list(lst[i][0]))
#print(lst3)
Sample Code
#Reading Excel Data into Python Lists
import xlwings as xw #Import the xlwings package
#Row data
#xlwings
app=xw.App()
bk=xw.books.open('data.xlsx')
sht=bk.sheets(1)
lst=sht.range('A1:E1').value #Read data from a worksheet
print(lst)
#xlwings API
#sht=bk.api.Sheets(1)
#lst=sht.Range('A1:E1').Value #Read data from a worksheet
#print(lst)
#print(list(lst[0]))
#Column data
#xlwings
lst=sht.range('A1:A5').value
print(lst)
#xlwings API
#lst=sht.Range('A1:E1').Value #Read data from a worksheet
#lst2=[]
#for i in range(len(lst)):
# lst2.append(list(lst[i]))
#print(lst)
#print(lst2)
#lst3=[]
#for i in range(len(lst)):
# lst3.append(list(lst[i][0]))
#print(lst3)
#bk.close()
#app.kill()
How To Set Cell Borders Using xlwings?
Method
Border settings
`Index` can be one of the following `XlBordersIndex` constants: xlDiagonalDown、xlDiagonalUp、xlEdgeBottom、xlEdgeLeft、xlEdgeRight、xlEdgeTop、xlInsideHorizontal or xlInsideVertical。
#xlwings API
sht.api.Range(‘B2’).CurrentRegion.Borders.LineStyle=xw.constants.LineStyle.xlContinuous
#sht.api.Range(‘B2’).CurrentRegion.Borders.ColorIndex=3
#sht.api.Range(‘B2’).CurrentRegion.Borders.Weight=xw.constants.BorderWeight.xlThick
Sample Code
#Cell comments
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
sht.api.Range('B2').CurrentRegion.Borders.LineStyle=xw.constants.LineStyle.xlContinuous
sht.api.Range('B2').CurrentRegion.Borders.ColorIndex=3
sht.api.Range('B2').CurrentRegion.Borders.Weight=xw.constants.BorderWeight.xlThick
#bk.close()
#app.kill()
How To Set Cell Background Color Using xlwings?
Method
#xlwings
sht.range(‘A1:E1’).color=(210, 67, 9)
#sht[‘A:A, B2, C5, D7:E9’].color=(100,200,150)
#xlwings API
#sht.api.Range(‘A1:E1’).Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range(‘A1:E1’).Interior.Color=65280
#sht.api.Range(‘A1:E1’).Interior.ColorIndex=6
#sht.api.Range(‘A1:E1’).Interior.ThemeColor=5
Sample Code
#Background color of the cell
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('A1:E1').color=(210, 67, 9)
sht['A:A, B2, C5, D7:E9'].color=(100,200,150)
#xlwings API
#sht.api.Range('A1:E1').Interior.Color=xw.utils.rgb_to_int((0, 255, 0))
#sht.api.Range('A1:E1').Interior.Color=65280
#sht.api.Range('A1:E1').Interior.ColorIndex=6
#sht.api.Range('A1:E1').Interior.ThemeColor=5
#bk.close()
#app.kill()
How To Set Cell Alignment Using xlwings?
Method
#Setting the alignments of cell C3
sht.api.Range(‘C3’).HorizontalAlignment = xw.constants.Constants.xlCenter
#sht.api.Range(‘C3’).VerticalAlignment = xw.constants.Constants.xlCenter
sht.api.Range(‘C3′).Value=’Test1’
Sample Code
#Cell Alignment
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
#Setting the alignments of cell C3
sht.api.Range('C3').HorizontalAlignment = xw.constants.Constants.xlCenter
sht.api.Range('C3').VerticalAlignment = xw.constants.Constants.xlCenter
sht.api.Range('C3').Value='Test1'
#bk.close()
#app.kill()
How To Set Cell Font Using xlwings?
Method
#xlwings API
sht.api.Range(‘A1:E1’).Font.Name = ‘Arial’ #Set the font to Arial
sht.api.Range(‘A1:E1’).Font.ColorIndex = 3 #Set the font color to red
sht.api.Range(‘A1:E1’).Font.Size = 20 #Set the font size to 20
sht.api.Range(‘A1:E1’).Font.Bold = True #Set the font to bold
sht.api.Range(‘A1:E1’).Font.Italic = True #Set the font to italic
sht.api.Range(‘A1:E1’).Font.Underline=xw.constants.UnderlineStyle.xlUnderlineStyleDouble #Add a double underline to the text
sht.api.Range(‘C1′).Value=’Test123’
sht.api.Range(‘A3:E3’).Font.Color =xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range(‘A3:E3’).Font.Color =16711680 # or 0x0000FF
#sht.api.Range(‘A1:E1’).Font.ColorIndex = 3
#sht.api.Range(‘A3:E3’).Font.ThemeColor =5
sht.api.Range(‘C3′).Value=’Test456’
Sample Code
#Cell Font Styles
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
sht.api.Range('A1:E1').Font.Name = 'Arial' #Set the font to Arial
sht.api.Range('A1:E1').Font.ColorIndex = 3 #Set the font color to red
sht.api.Range('A1:E1').Font.Size = 20 #Set the font size to 20
sht.api.Range('A1:E1').Font.Bold = True #Set the font to bold
sht.api.Range('A1:E1').Font.Italic = True #Set the font to italic
sht.api.Range('A1:E1').Font.Underline=xw.constants.UnderlineStyle.xlUnderlineStyleDouble #Add a double underline to the text
sht.api.Range('C1').Value='Test123'
sht.api.Range('A3:E3').Font.Color =xw.utils.rgb_to_int((0, 0, 255))
#sht.api.Range('A3:E3').Font.Color =16711680 # 或0x0000FF
#sht.api.Range('A1:E1').Font.ColorIndex = 3
#sht.api.Range('A3:E3').Font.ThemeColor =5
sht.api.Range('C3').Value='Test456'
#bk.close()
#app.kill()