Several tips for better working with Python in Excel

After enrolled for Python in Excel preview, now I can type =py( in any Excel cell to write some Python code. Python in Excel doesn’t have detailed documents, only Get Started tutorials, like link 1, link 2, or articles talking about topics like pandas, matplotlib, seaborn, etc., it may confuse you when you want to do some real work in an unfamiliar environment. The article notes several tips from my understanding of Python in Excel.

Refer to values

With Python in Excel, we may use xl() function to access any cells, columns, rows, and sheets. When getting data from another sheet, write the code like xl('SheetName!A1:B2'). If xl() refers to more than one cell, a DataFrame object will be returned.

If we have set the category of a cell, the type of xl() will be as follows:

the Python runtime

The Python runtime of Python in Excel is deployed in Microsoft Cloud, so whenever you are typing ctrl + enter, the code and data are transferred to the cloud, executed in the cloud, and transferred back.

From the Formulas -> Python -> Initiallization, we could have a look at how the runtime is initialized in the cloud, what library is loaded at start, and what the default setting is presetted.

Despite data processing libraries like numpy, pandas(and more you may import according to the official document ), the library excel is in charge of the communication and data transformation between Excel and Microsoft Cloud. For example, the xl() function for getting data from Excel sheets, you may use excel.uploadeddata.get_runtime_id() function to get a uuid for the current Python runtime. I didn’t found the document about the excel library, maybe I can dig into it in detail later.

All the sheets in an Excel document share the same runtime. This means if you declare a function, a variable, or a class in one cell, you may refer it in any sheet and any cell in the Excel file. For example,

Network Requests

Actually, we can import libs socket, http, reqeuests in Python in Excel. But we can not make any valid requests since the runtime container is firewalled probably due to security reasons. So we can not dynamically load code or data remotely.

If the future versions of Python in Excel provide private deployment, network access should not be a problem and we may have more possibilities with Excel.

Inspecting the Python objects and middle values

When the help of Python shell, I often use functions like dir(), help(), type() to inspect the usage and structure of some objects or classes. We can still do similar things without a shell from Python in Excel.

For example, we can use dir() on excel module, and we got the methods of excel module in a column:

Or we can organize several inspected values in a list, saving some remote execution time:

Despite inspecting a module, we can also use the same method to inspect the middle result values for some complex Python code. The Python in Excel could be debugged just like Jupyter.

Load libs as in an Excel sheet

As noted before, we can not dynamically load Python code remotely. If we want to load some libs, especially some reused private code, we still need a way to load it.

The solution here is to save the reused code in a sheet, and copy the sheet to another .xlsx file whenever we need that code.

Error Message Handling

Python in Excel only returns very simple error messages, i.e., the error message of an Exception object. If you want a detailed stack trace, you may wrap your code inside try/except block and return the stack as a list. For example:

import traceback

ret = ''

try:
    # your code or function
    ret = xxx()
except:
    ret = traceback.format_exc().split('\n')

ret

The .split('\n') is for splitting return stack trace message into multiple cells(each line a cell). You may want to use a fixed width font to ensure the display of stack trace info.

Other mentions

• The multi-line string output in a cell will ignore \n.
• The xl() function can read \n from a cell correctly.
• Can not read the content of an inserted object, for example, a file as an attachment.
• It seems there is no way to write to specific cells directly.