Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
647 views
in Technique[技术] by (71.8m points)

python 2.7 - how to get formula result in excel using xlwings

What i want to do is 1)get a folmula result in excel and 2)update the values to the existing excel file. [output_result I created and wrote the folmula using "xlsxwriter". But when I tried openpyxl (or pandas) to retrieve the folmula result, it returns 0. I want to use "xlwings" to solve this problem, but no idea how to do it. can anyone help?

#openpyx
wb = openpyxl.load_workbook(filename=xlsx_name,data_only=True)
ws = wb.get_sheet_by_name("sheet1")
print "venn_value",(ws.cell('X2').value)
#pandas 
fold_merge_data=pd.read_excel(xlsx_name,sheetname=1)
print fold_merge_data['Venn diagram'][:10]
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Yes, xlwings can solve this problem for you because it uses pywin32 objects to interact with Excel, rather than just reading/writing xlsx or csv documents like openpyxl and pandas. This way, Excel actually executes the formula, and xlwings grabs the result.

In order to get the value you can do:

import xlwings as xw
sheet = xw.sheets.active # if the document is open
#otherwise use sheet = xw.Book(r'C:/path/to/file.xlsx').sheets['sheetname']
result = sheet['X2'].value

Also, note that you can set the formula using, for example

sheet['A1'].value = '=1+1' # or ='B1*2' if you want to reference other cells

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...