Are you using Microsoft Excel in conjunction with Python for your data analytics projects, but have a need to automate certain tasks?
In this blog post we will take you through how to remove formulas in a cell , and replace them with their returned values.
This is achieved through using xlwings, a very powerful library that can be used with Python.
So what we want to do is remove the formulas in an excel sheet, normally this is achieved through “copy and paste special values” in excel.
Below is a screenshot of the before:
In order to remove the formulas we use the following code:
This code basically loads the file( input) and looks for the range F2:F5.
Then using the the xlwings functionality, it makes the old file range values equal to the new range values.
The difference is that it looks at what the vlookup returned value to the cell and not the formula.
from openpyxl import load_workbook
import xlwings as xlfile
filepath_input = r'your file path here'
filepath_output = r'your file path here'
input_workbook = load_workbook(filepath_input)
output_workbook = load_workbook(filepath_output)
ws = input_workbook['Sheet1']
### Removing formulas in the spreadsheet
oldlist = xlfile.Book(filepath_input)
newlist = xlfile.Book(filepath_output)
my_values = oldlist.sheets['Sheet1'].range('F2:F5').options(ndim=2).value
my_values1 = newlist.sheets['Sheet1'].range('F2:F5').options(ndim=2).value
newlist.sheets['Sheet1'].range('F2:F5').value = my_values1
The output is a new file , with the formulas removed!
And there you go, there are other options though.
Theoretically you don’t have to create a new sheet like I did above, that was done to show the before and after, otherwise the input file is overwritten, and if that is what you need then your problem is solved!
In rolling out this solution, there are other options out there as well, I found this the simplest to implement.
Openpyxl can be used and it was the most common suggestion , but I found its implementation not as straight forward.
Great implementation. It just saved me a lot of time. Keep up the great work.
That is great you got your problem solved, thanks for the nice feedback!
Data Analytics Ireland
Same Pycharam 3.8 Version Windows 64 bit environment. also I am unable to install pywin32 and xlwings using pip install
“Collecting pywin32>=224 (from xlwings)
Could not find a version that satisfies the requirement pywin32>=224 (from xlwings) (from versions: )
No matching distribution found for pywin32>=224 (from xlwings)
“
Ok, are you using a console or a package like Jupyter notebook ? I found in the past use jupyter notebook has seamlessly allowed me to install anything with pip.
Here is another link, based on the error message above, possibly might help? Stackoverflow solution
Was looking at this PyPi website and noticed it references Windows, are you using that operating system?
Sorry, I am not familiar with the issue, so help I can provide is limited, but will help.
Data Analytics Ireland
Collecting pywin32>=224 (from xlwings[all])
Could not find a version that satisfies the requirement pywin32>=224 (from xlwings[all]) (from versions: )
No matching distribution found for pywin32>=224 (from xlwings[all])
Getting Above error while trying to install the xlwings library
Hi,
Thanks for your message. When I coded my code, I was using Pycharm and got no errors, it did all the imports into the system for me. What program are you using?
From looking at the error it possibly relates to the version you are of Python, I used 3.8, what version are you on?
Gives us more information and will see if I can help you!
Was reading through this link, does it help? Stackoverflow Python PIP
Data Analytics Ireland