1

I used pandas to read a excel A and write formula on K column, then saved to a excel B.

for row_num in range(1, 18):
    worksheet.write_formula(row_num ,10, '=MID($J%d,LEN(LEFT($J%d,SEARCH(" ",$J%d)+1)),3)' % (row_num+1, row_num+1, row_num+1))

I could see the formula that I inserted from excel B and its correct value.

enter image description here

But when I used pandas to read excel B and print K column, its value became "0", how to solve it? I want to see the calculated value, not formula, not 0.

I give a example:

#this is about writing formula to excel file
import pandas as pd
cname = []
for i in range(1,5):
    cname.append('=MID("ABCDE",LEN(LEFT("ABCDE",SEARCH("","ABCDE")+1)),3)')
s = pd.DataFrame({'col':cname})
writer = pd.ExcelWriter('output.xlsx')
writer.save()

result: enter image description here

Another program to read above excel file: result: it should be 'BCD', not 0

enter image description here

Sora Liu
  • 117
  • 3
  • 11
  • 2
    Possible duplicate of [How can I see the formulas of an excel spreadsheet in pandas / python?](https://stackoverflow.com/questions/42102674/how-can-i-see-the-formulas-of-an-excel-spreadsheet-in-pandas-python) – gehbiszumeis Oct 02 '18 at 07:15
  • Thank you, but it can't work. What I mean was I used pandas to write formula to cells and read, I can't see the value, only "0". – Sora Liu Oct 02 '18 at 09:44
  • How did you use pandas to read excel? It should return calculated value just fine. – zipa Oct 02 '18 at 09:56
  • @zipa, please check my example, thank you. – Sora Liu Oct 03 '18 at 01:48
  • 1
    But I find a related post, maybe it is a issue: [link](https://xlsxwriter.readthedocs.io/working_with_formulas.html#formula-result) – Sora Liu Oct 03 '18 at 03:41
  • @SoraLiu - That link from the XlsxWriter documentation is your whole issue. That spells it out nicely. You could also check out [my answer](https://stackoverflow.com/a/24126789/95852) to a similar question. – John Y Oct 03 '18 at 21:16
  • You almost definitely need to use Excel itself to calculate those formulas. The usual way to do that with Python these days is [xlwings](https://www.xlwings.org/). – John Y Oct 03 '18 at 21:22
  • @JohnY, thank you for your reply! xlwings seems cool, I will try it later. – Sora Liu Oct 04 '18 at 01:34
  • Finally, I decided to transform dataframe to string to fetch substrings. It can replace formula question. – Sora Liu Oct 04 '18 at 01:43

0 Answers0