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
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() 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
enter, the code and data are transferred to the cloud, executed in the cloud, and transferred back.
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
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,
Actually, we can import libs
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
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
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 have 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
.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.
- The multi-line string output in a cell will ignore
xl()function can read
\nfrom 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.