CODEWITHSUNDEEP
pythonexcelopenpyxl
Bogdan Alexandru
Bogdan Alexandru

Reputation: 33

Use a value from a cell which is written with formulas

I have two columns in Excel. The first(column C) has cells with values, the second one(column B), I had used a script to extract some values from the first one with Excel formulas.

Now I want to use the values from the second column in another column and the script doesn't have any errors but gives me empty cells because the second column contains formulas.

Is it possible to paste values or to extract only the values from the second column?

Here is my code: 

for i in range(0,len(listaunica)):
ws4.cell(row=i+1,column=3).value=listaunica[i]


for i in range(0,len(listaunica)):
    ws4.cell(row=i+1,column=2).value='=iferror(find(".",C{0}),C{0})'.format(i+1)

Can someone help me with this?

Upvotes: 2

Views: 355

Answers (2)

John Y
John Y

Reputation: 14519

I do not fully understand your situation, so I will explain some possibilities:

(1) You have an Excel workbook that was saved using Excel itself. In this case, column B should have both formulas and the results of those formulas, because Excel would have calculated them.

(2) You have an Excel workbook that was saved using some other method, such as being written by OpenPyXL, and has not (yet) been opened and saved by Excel. In this case, you most likely have either formulas or results stored in column B.

When you are reading using OpenPyXL, you have to choose whether you want formulas or results. This is controlled by the data_only parameter. Set this to True if you want just the results. If your workbook was saved in Excel, and thus has both formulas and results, then the way to read them both in OpenPyXL is to open the workbook twice, once with data_only=False and once with data_only=True. Cumbersome, but that is how OpenPyXL is designed.

If you have a workbook from scenario (2), and column B still looks like it has formulas, then most likely trying to open the workbook using data_only=True will just return zeros for column B. You won't be able to get the results from this workbook until you open it in Excel and then save it.

Upvotes: 1

Maddy
Maddy

Reputation: 781

Try this

for i in range(0,len(listaunica)):
ws4.cell(row=i+1,column=3).value=listaunica[i]
for i in range(0,len(listaunica)):
ws4.cell(row=i+1,column=2).value='=iferror(find(".",C{0}),C{0})'.format(i+1)
ws4.cell(row=i+1,column=2).value = ws4.cell(row=i+1,column=2).value

For reference Does .Value = .Value act similar to Evaluate() function in VBA??

Upvotes: 0

Related Questions