how to copy/paste special a range of cells with xlwings

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.

6 thoughts on “how to copy/paste special a range of cells with xlwings

  • April 29, 2021 at 10:26 am
    Permalink

    Great implementation. It just saved me a lot of time. Keep up the great work.

    Reply
    • April 29, 2021 at 10:52 am
      Permalink

      That is great you got your problem solved, thanks for the nice feedback!

      Data Analytics Ireland

      Reply
  • February 5, 2021 at 9:04 am
    Permalink

    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)

    Reply
    • February 5, 2021 at 9:27 am
      Permalink

      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

      Reply
  • February 3, 2021 at 6:04 pm
    Permalink

    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

    Reply
    • February 3, 2021 at 10:46 pm
      Permalink

      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

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *