Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about how to use Python to enhance Excel to reduce the pain of dealing with complex data, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Excel is both a blessing and a curse.

When it comes to sufficiently small data and sufficiently simple operations, Excel is king. However, once you find yourself trying to get out of these areas, it becomes a pain.

Of course, you can use ExcelVBA to solve these problems, but luckily you don't have to do so in 2020!

If there is a way to integrate Excel and Python, Excel … You'll put on your wings!

There is now. A python library called xlwings allows users to call python scripts through VBA and pass data between the two.

Why integrate Python with ExcelVBA?

In fact, users can do anything in VBA. So, if so, why use Python? Well, there are many reasons.

1. You can create custom functions in Excel without having to learn VBA (if the reader doesn't already know it)

two。 Users are satisfied with Excel.

3. Using Python can significantly speed up data manipulation

4. In Python, almost everything has a library (machine learning, data science, etc.)

5. Because you can!

Prepare to use xlwings

The first thing to do, like any new library you want to use, is to install it. This is very easy to do; with these two commands, you will soon be ready. So, enter the command into the terminal:

Pipinstall xlwings

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

Xlwings addin install

Assuming no errors are encountered, you should be able to continue. However, on Win10's Excel2016, 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'

If you are lucky enough to encounter the above error, all you need to do is create the missing directory. You can easily do this by using the mkdir command. As far as the author is concerned, the author has achieved:

MkdirC:\\ Users\\ costa\\ AppData\\ Roaming\ Microsoft\\ Excel\\ XLSTART

Assuming that the integration of excel and python libraries is installed successfully, you can immediately notice the main differences in excel:

Enable user-defined functions for xlwings

First, you need to load the Excel add-in. Click Alt,L,H and navigate to the directory above to load the plug-in. When finished, you should be able to see the following:

Finally, you need to enable trusted access to the VBA engineering object model. You can do this by navigating to File > options > Trust Center > Trust Center Settings > Macro Settings:

Getting started with xlwings

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

To avoid any confusion and set it up correctly every time, xlwings provides the ability to create Excel spreadsheets and is ready. Let's use this feature. Using the terminal, navigate to your favorite directory and type:

Xlwingsquickstart ProjectName

Call this MyFirstPythonXL. The above command creates a new folder in the pre-navigated directory that contains an Excel worksheet and a python file.

When you open the .xlsm file, you will immediately notice a new Excel worksheet named xlwings.conf. If you want to override the default settings for xlwings, simply rename the worksheet and remove the starting underscore. In this way, the preparation is done, and let's start using 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 will do the following:

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

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

3. Call the function "main ()" from the Python script

Back to the point, let's take a look at a few examples of use.

Example 1: operate outside Excel and return output

In this case, you will see how to perform the operation outside of Excel, but then return the results to the spreadsheet. There can 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 (ritual C:\ temp\ TestData.csv') df ['total_length'] = df [' sepal_length_ (cm)'] + df ['petal_length_ (cm)'] wb.sheets [0] .ra nge ('A1'). Value = df

The results are as follows:

Example 2: using Excel input to drive an operation

In this case, the input is read from Excel, processed with Python, and then passed back to Excel.

More specifically, read a greeting, a name and a file location, where jokes can be found. The Python script then randomly picks a line from the file and returns 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)

The result is:

User-defined functions with xlwigs

Change the code in the python file in almost the same way as before. To convert something to an Excel user-defined function, we just need to include "@ xw.func" before the line of the function:

Python Code:

Importxlwings as xw@xw.func def joke (x): wb = xw.Book.caller () fhandle = open (ritual C:\ Temp\ list.csv') for I, line in enumerate (fhandle): if I = = x: return (line)

The result is:

If, like Xiaoxing, you prefer to use Python rather than VBA, but need to use spreadsheets, this tool is your best choice and you can think of it as a beautiful small database.

After reading the above, do you have any further understanding of how to use Python to enhance Excel to reduce the pain of dealing with complex data? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.