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.