Excel is both a blessing and a curse.

When it comes to small enough data and simple enough operations, Excel is king. However, once you find yourself struggling to get out of those areas, it can become a pain.

Of course, you can use ExcelVBA to solve these problems, but in 2020, fortunately, you won’t have to!

If there was a way to integrate Excel and Python, Excel… Will put on wings!

Now there is. A Python library called Xlwings allows users to invoke Python scripts from VBA and pass data between the two.

Why integrate Python with ExcelVBA?

In fact, users can do just about anything in VBA. So, if that’s the case, why use Python? Well, there are many reasons.

1. You can create custom functions in Excel without having to learn VBA (if you don’t already know)

2. Users are happy with Excel

3. Using Python can significantly speed up data manipulation

4. In Python, there are libraries for almost everything (machine learning, data science, etc.)

Because you can!

Ready to use XLwings

The first thing to do, as with any new library you want to use, is to install it. This is very easy to do; With these two commands, we’ll be ready in no time. So, enter the command into the terminal:

pipinstall xlwingsCopy the code

After downloading and installing the library, you need to install the Excel integration. Ensure that all Excel instances and any terminal types are closed:

xlwings addin installCopy the code

Assuming no errors are encountered, you should be able to continue. However, on Excel2016 for Win10, people often see the following errors:

Xlwings0.17.0 [Errno 2] No such file or directory:'C:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTART\\\xlwings.xlam'Copy the code

If you are lucky enough to encounter the above error, all you need to do is create the missing directory. This can be done easily by using the mkdir command. As far as I am concerned, I have done:

mkdirC:\\Users\\costa\\AppData\\Roaming\\Microsoft\\Excel\\XLSTARTCopy the code

Assuming the integration of Excel with the Python library is successfully installed, you can immediately notice the main differences in Excel:

Enable user-defined functions for Xlwings

First, you need to load the Excel add-on. Click Alt, L, H, then navigate to the directory above to load the plug-in. When you’re done, you should see the following:

Finally, you need to enable trust access to the VBA project object model. You can do this by navigating to File > Options > Trust Center > Trust Center Settings > Macro Settings.

Introduction to xlwings

There are two main ways to get from Excel to Python (Python to Excel). The first is to call Python scripts directly from VBA, and the other is through user-defined functions. Take a quick look at both.

To avoid any confusion and get it right every time, Xlwings provides the ability to create Excel spreadsheets, ready to go. Let’s use this feature.

Using terminal, navigate to your favorite directory and type:

xlwingsquickstart ProjectNameCopy the code

Let’s call this MyFirstPythonXL. The command above will create a new folder in the pre-navigated directory containing an Excel worksheet and a Python file.

When you open the.xlsm file, you immediately notice a new Excel worksheet called xlwings.conf. If you want to override the default Settings for Xlwings, simply rename this worksheet and remove the starting underscore. With that done, let’s start with xlwings.

VBA to Python

Before you start coding, make sure we’re all on the same page. To open the ExcelVBA editor, press Alt+F11. This returns the following screen:

VBA editor with Xlwings

The key thing to note here is that this code does the following:

1. Find the Python script in the same location as the spreadsheet

2. Find a Python script with the same name as the spreadsheet (but with the.py extension)

3. Call the function “main ()” from the Python script

Without further ado, let’s look at a few examples in use.

Example 1: Operate outside Excel and return output

In this example, you’ll see how to perform operations outside of Excel, but then return the results to the spreadsheet. There could be an infinite number of use cases.

Get the data from the CSV file, modify the data, and pass the output to Excel. The operation is simple:

First, the VBA code:

It remains exactly the same as the default Settings.

Then, the Python code:

importxlwings as xw
import pandas as pddef main():
    wb = xw.Book.caller()
    df =pd.read_csv(r'C:\temp\TestData.csv')
    df['total_length'] =  df['sepal_length_(cm)'] +df['petal_length_(cm)']
    wb.sheets[0].range('A1').value = dfCopy the code

The results are as follows:

Example 2: Use Excel input to drive actions

In this case, you read the input from Excel, process it in Python, and pass the results back to Excel.

More specifically, read a greeting, a name and a file location where jokes can be found. The Python script will then randomly extract a line from the file and return a joke.

First, the VBA code:

It remains exactly the same as the default Settings.

Then, the Python code:

importxlwings as xw
import randomdef random_line(afile):
    line = next(afile)
    for num, aline in enumerate(afile,2):
      if random.randrange(num): continue
      line = aline
    return line
'Function from: stackoverflowdef main():
    wb = xw.Book.caller()
    listloc =str(wb.sheets[0].range('B3').value)
    fhandle = open(listloc, encoding ='utf-8')wb.sheets[0].range('A5').value = wb.sheets[0].range('B2').value + ' ' +wb.sheets[0].range('B1').value + ' here is a joke for you'
    wb.sheets[0].range('A6').value =random_line(fhandle)Copy the code

The result is:

User-defined function with Xlwigs

Change the code in a Python file in much the same way as before. To convert something into an Excel user-defined function, we simply include “@xw.func” before the line where the function is:

Python code:

importxlwings as [email protected]
def joke(x):
    wb = xw.Book.caller()
    fhandle = open(r'C:\Temp\list.csv')
    for i, line in enumerate(fhandle):
        if i == x:
            return(line)Copy the code

The result is:

If you prefer Python to VBA but need to use spreadsheets, this tool is your best bet, and you can use it as a nice little database.

The original link: read the core technique www.toutiao.com/a6795726551… Wenyuan network, only for the use of learning, such as infringement, contact deletion.

I have compiled good technical articles and lessons learned on my public account, Python Circle.

You will definitely encounter difficulties in learning Python. Don’t panic, I have a set of learning materials, including 40+ e-books, 600+ teaching videos, covering Python basics, crawlers, frameworks, data analysis, machine learning, etc. There are learning exchange group, learning progress together ~