Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • Python tutorial: Create an input box in Tkinter Python
  • What is a Primary Key and Foreign Key SQL
  • How to count the no of rows and columns in a CSV file CSV
  • Tkinter python tutorial Python
  • TypeError: cannot unpack non-iterable int object Python
  • How do I fix TypeError: unhashable type: ‘list’ Error? Articles
  • how to create an instance of a class class
  • How to Pass Python Variables to Javascript Javascript

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

Posted on February 3, 2021February 9, 2021 By admin 6 Comments on 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.

Python, Python working with excel Tags:copy and paste, openpyxl, vlookup, xlwings

Post navigation

Previous Post: how do I declare a null value in python?
Next Post: How to use wildcards in SQL

Related Posts

  • How to sort a Python Dictionary Python
  • how to add sine and cosine in python code numpy
  • How Would You Change The Name Of a Key in a Python Dictionary Python
  • How to Compare Column Headers in CSV to a List in Python CSV
  • how do I merge two dictionaries in Python? Python
  • How to check if a file is empty Python

Comments (6) on “how to copy/paste special a range of cells with xlwings”

  1. Vignaesh says:
    April 29, 2021 at 10:26 am

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

    Reply
    1. admin says:
      April 29, 2021 at 10:52 am

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

      Data Analytics Ireland

      Reply
  2. Sri says:
    February 5, 2021 at 9:04 am

    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
    1. admin says:
      February 5, 2021 at 9:27 am

      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
  3. Sri says:
    February 3, 2021 at 6:04 pm

    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
    1. admin says:
      February 3, 2021 at 10:46 pm

      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 Cancel reply

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

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • Tkinter GUI tutorial python – how to clean excel data Python
  • how to use case statements in SQL SQL
  • TypeError: Array() Argument 1 Must Be A Unicode Character, Not List array
  • hide a column from a data frame Python Dataframe
  • How To Run Python Validation From Javascript Javascript
  • ValueError: invalid literal for int() with base 10 Value Error
  • How to add a date when a record is created SQL
  • TypeError: type object is not subscriptable strings

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT