2

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]
talktalk
  • 123
  • 2
  • 2
  • 6
  • The reason that the XlsxWriter formula is 0 is explained in the docs: [XlsxWriter Formula Results](https://xlsxwriter.readthedocs.io/working_with_formulas.html#formula-results). – jmcnamara Dec 02 '16 at 08:03
  • Actually I read the docs before, and I also read in another thread xlwings might help to solve this problem; [link1](http://stackoverflow.com/questions/38915228/fails-to-read-the-value-of-formular-after-using-xlsxwriter-to-close-and-then-usi/38922683#38922683) and [link2](http://stackoverflow.com/questions/28517508/read-excel-cell-value-and-not-the-formula-computing-it-openpyxl). I just wonder what piece of code using xlwings (or anything else) can fix this. – talktalk Dec 05 '16 at 01:49
  • Why do you want to bother with xlwings if you are already using XlsxWriter? Those docs also show you how to write the value into the cell (along with the formula) so that it's not zero. – John Y Oct 09 '17 at 21:25

2 Answers2

3

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
sheridp
  • 1,386
  • 1
  • 11
  • 24
  • One important thing to note is that this doing this will open Excel, so scripts based on xlwings won't work on Linux systems (maybe through Wine, I haven't tried). Also, this method is significantly slower than through e.g. pandas, and so if you're trying to process lots of documents, it's not very scalable. – sheridp Jun 27 '17 at 21:21
  • A shame, I am needing something like this in Linux. –  Nov 04 '17 at 05:18
1
import xlwings as xw
sheet = xw['Sheet1']
a2_formula = sheet.range('A2').formula
sheet.range('A2:A300').formula = a2_formula #it copys relative

You can use this method for copy formula or value

Rufai DEMIR
  • 97
  • 7