Archive

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()