add proper conda env support for RunPython

[timeregained]

OS (e.g. Windows 10 or macOS Sierra)

Windows 10 64bit 1709

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

0.12.1(xlwings-0.12.1-py37_0), Office 365, Python 3.7 distributed by anaconda(python-3.7.0-hea74fb7_0)

Describe your issue (incl. Traceback!)

When I click Run button of simulation.xlsm in this repo's eamples, an import error occurs which having flowing traceback:

Traceback (most recent call last):
File "", line 1, in 
File "C:\Users\someone\AppData\Local\Continuum\miniconda3\lib\site-packages\numpy_init_.py", line 142, in 
from . import add_newdocs
File "C:\Users\someone\AppData\Local\Continuum\miniconda3\lib\site-packages\numpy\add_newdocs.py", line 13, in 
from numpy.lib import add_newdoc
File "C:\Users\someone\AppData\Local\Continuum\miniconda3\lib\site-packages\numpy\lib_init_.py", line 8, in 
from .type_check import *
File "C:\Users\someone\AppData\Local\Continuum\miniconda3\lib\site-packages\numpy\lib\type_check.py", line 11, in 
import numpy.core.numeric as nx
File "C:\Users\someone\AppData\Local\Continuum\miniconda3\lib\site-packages\numpy\core_init.py", line 26, in 
raise ImportError(msg)
ImportError:
Importing the multiarray numpy extension module failed. Most
likely you are trying to import a failed build of numpy.
If you're working with a numpy git repo, try git clean -xdf (removes all
files not under version control). Otherwise reinstall numpy.

This issue only happened when I have upgraded python from 3.6 to 3.7 within anaconda. So I have created an issue #10205 in anaconda-issues which has been soon closed by anaconda engineer. As his description:

I removed a hack deep in the guts of our python recently whereby it modified the PATH environment variable if it detected activation may not have happened correctly. This breaks countless things in 3rd party software.

I have no choice but wish your team would follow the change to make xlwings working again if scripts using some numpy function.

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

Just the demo simulation could repeat this issue.

It seems only related to scripts which has involve numpy now, but it might bring same issues to some other 3rd packages.

[fzumstein]

this might be a bit of an effort to fix, but from what I understand, as a current workaround you can use the numpy version installed with pip.

[fzumstein]

I could not replicate this with a conda env with the following versions:

conda: 4.5.11
python: 3.7.1
numpy: 1.15.4
xlwings: 0.12.1 and latest

is this still happening on your side?

[timeregained]

@fzumstein

The issue still exits on my current conda env:
conda: 4.15.2
python: 3.7.1
numpy: 1.15.4
xlwings: 0.15.1

[fzumstein]

ok thanks seems I wasnt on the right conda version - will try again!

[fzumstein]

Ok I was able to replicate. Another work around seems to be when you add the paths of your conda base installation to your PATH env variable (either by selecting the according option at the end of the installer or manually):

C:\...\Anaconda3
C:\...\Anaconda3\Scripts
C:\...\Anaconda3\Library\bin
C:\...\Anaconda3\usr\bin
C:\...\Anaconda3\mingw-w64\bin

[timeregained]

@fzumstein , Yes , adding these path to environment variable works, however, the anaconda team does not recommend to do adding to PATH in their installer. Instead, they recommend to activate conda envs by manually calling activate script(e.g. the anaconda command prompt). So is there any way to add some procedure to activate conda envs in your addin ?

[fzumstein]

sure, as I said, it's a workaround (although I admittedly don't really understand the big issue about adding to PATH). Making it work properly is certainly possible but as it's possibly a bit of work and there's a reasonable workaround it's not at the very top of my list.

[idahopotato1]

Hello
I have the same issue when I tried to run any of the sample examples downloaded from https://www.xlwings.org/examples, if it involves numpy.
Do you think there will be a fix any time soon? Thank you for the excellent product.

Thank you
Eddie

[idahopotato1]

Actually, I uninstalled and reinstalled Anaconda and made sure to add the path at the end of the installation process. It worked. Thank you

[fzumstein]

Thanks for your feedback, @idahopotato1. It's still on the list, but I'd need to find out a few things first before we can implement a solution. For example, @timeregained, is there absolutely nothing on your PATH, not even conda?

[timeregained]

Hi, I have added the anaconda base folder and script folder to the PATH, but the issue seems still there. However, the issue fixed after I added the Library\bin to the PATH.

…

On Thu, Jan 17, 2019 at 1:38 AM Felix Zumstein ***@***.***> wrote: Thanks for your feedback, @idahopotato1 <https://github.com/idahopotato1>. It's still on the list, but I'd need to find out a few things first before we can implement a solution. For example, @timeregained <https://github.com/timeregained>, is there absolutely nothing on your PATH, not even conda? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#954 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AGaaYGM9pJ4ZZQYvpd06rGTpDy1ictFBks5vD2OEgaJpZM4XeNRG> .

[fzumstein]

ok thanks, but @timeregained on your system, do you have conda on your path or are you forced to go via the anaconda prompt?

[timeregained]

@fzumstein Actually, the conda is not in my system's PATH when the issue could be repeated.

[fzumstein]

What's needed here is probably:

• Config: path to conda base script, i.e. to be able to execute the same code as when starting an anaconda prompt
• Config: name of conda env
• Adjust the RunCommand in ExcecuteWindows, ExcecuteMac2011
• Adjust the command in xlwings.applescript

[navicenna]

This issue also exists for me. I'm running Python 3.7 distributed by Anaconda on Windows 10.

However, I'm not using a particular conda env, i.e. I'm using the base environment.

[bosbyj]

Same issue replicated.

Python 3.7.1 (default, Dec 10 2018, 22:54:23) [MSC v.1915 64 bit (AMD64)] :: Anaconda, Inc.
Base env.

I got it running by creating a conda env and pip installed xlwings + numpy.

[Brad-eki]

I too am using an Anaconda env.

Windows 10, Python 3.7, xlwings 0.15.2

PYTHONPATH, all set to my env python directories.

Interpreter: set to my env python.exe
pythonpath: not set
UDF modules: not set
debug UDFs: not checked
log file: not set
RunPython use UDF server: not checked

I have recently had good luck with the following:

xlwings quickstart myproject --standalone

I had not previously used the --standalone option. This seems to have put things into Excel which I must have previously been relying on being loaded on the fly.

For some reason I keep missing important things in the doco...

[Brad-eki]

So, I'm up against the Anaconda environment stuff again. Over the coming days I am going to attempt to understand, and hope to prototype:

• Config: path to conda base script, i.e. to be able to execute the same code as when starting an anaconda prompt
• Config: name of conda env
• Adjust the RunCommand in ExcecuteWindows, ExcecuteMac2011

Cheers

[Brad-eki]

So, I have learned this:

When using the Anaconda environment I've named py37, I don't get SSL DLL import errors when using the pyxero library.

When trying to use xlwings I get import SSL exceptions while importing the Xero library. The error is due to the environment not being set up correctly. eg; not using the anaconda environment properly. So the below Visual Basic code (with xlwings installed) gives me errors...

RunPython ("import accounting_integration; accounting_integration.get_bank_transfers()")

The below Visual Basic code re-creates the error I am getting with the RunPython command. (the -B argument is optional). It can be noted this is using the "default" Python and not my Anaconda environment py37.

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
      
wsh.Run "cmd.exe /S /C python -B -i hello.py || timeout /T 25", windowStyle, waitOnReturn

The below Visual Basic code uses a similar technique to xlwings to run python code. Essentially using a command directly rather than running it from a file. Note: the path for hello.py needs to be in PYTHONPATH for this demonstration to work.

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
      
wsh.Run "cmd.exe /S /C python -B -c ""import hello; hello.xero()"" || timeout /T 25", windowStyle, waitOnReturn

So, at least in my case, we need to prove a way through which uses an Anaconda environment.

The belowVisual Basic code uses the Anaconda script to activate a particular Anaconda environment (eg; py37). Again the -B argument is optional, and the path for hello.py needs to be in the PYTHONPATH.

Dim wsh As Object
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
      
wsh.Run "cmd.exe /S /C C:\Users\me\AppData\Local\Continuum\anaconda3\condabin\activate.bat py37 && python -B -c ""import hello; hello.xero()"" ", windowStyle, waitOnReturn

For completeness, the hello.py Python code.

import os
import time

from xero import Xero
from xero.exceptions import XeroUnauthorized
from xero.auth import PrivateCredentials

def xero():
    try:
        print('Conda ENV:', os.environ["CONDA_DEFAULT_ENV"])
    except KeyError:
        print('Not a Conda ENV')

    rsa_private_key = None
    with open('privatekey.pem', 'rb') as f:
        rsa_private_key = f.read()
        f.close()

    credentials = PrivateCredentials('MY CREDENTIALS', rsa_private_key)

    try:
        xero = Xero(credentials)
        print(xero.users.all())
    except XeroUnauthorized:
        print("Not auth'd")
        time.sleep(25)

xero()

As @fzumstein has mentioned, remaining is to:

• Config: path to conda base script, i.e. to be able to execute the same code as when starting an anaconda prompt
• Config: name of conda env
• Adjust the RunCommand in ExcecuteWindows, ExcecuteMac2011
• Adjust the command in xlwings.applescript

I don't have a development environment which will allow me to actually develop any of that code to the point where I can generate a pull request. I am happy to develop the modification to the xlwings module, ExecuteWindows subroutine. But will need to hand it to someone to actually develop into the code.

Anyone able to help me out on this?

[navicenna]

@Brad-eki , I can probably help you out, or at least try my best.

What kind of development environment is necessary for this project?

[Brad-eki]

@nbahmanyar Thanks for putting your hand up.

Sadly the only computer I currently have access to is a work machine which is locked down so I can't install things on it. I have enough to develop in Python but don't have Visual Studio or any of the Microsoft development tools (eg; .NET SDK or a C++ compiler).

I'm happy for others to correct what I've said here.

Xlwings appears to be a C++ project. Outside that I'm not entirely certain as I've not developed a Microsoft Ribbon application before. From what I can see I would think Visual Studio is the IDE to use for this project as there are .sln and .vcxproj files around the place, and I think VS might help with finding the Ribbon XML definition.

The closest I can get to "knowing anything" is from the website for creating a Ribbon application https://docs.microsoft.com/en-us/windows/desktop/windowsribbon/windowsribbon-stepbystep

The process appears to be:

• Update some XML which defines the user interface (the bit that actually appears on the Excel Ribbon). I can't find the XML in the project, but this project is really well built so it'll be somewhere nearby (maybe even in the .xlam itself?)
• Add to the existing section "Python" a boolean labelled "Use Anaconda", which could reveal the section called "Anaconda" (If this kind of thing can be done... else have an Anaconda section always visible)
• Create a fourth GUI section called "Anaconda"
• • Put in the Anaconda section some text fields for "Base script" and "Environment name".
• Extend the RibbonXlwings VisualBasic Module to support SetUseAnaconda, GetUseAnaconda, SetAnacondaBasePath, GetAnacondaBasePath, SetEnvironmentName, GetEnvironmentName
• Modify the xlwings VisualBasic Module update RunPython;
• • support the "use Anaconda" boolean from the "Python" section
• • the new fields from the user interface Anaconda section might need to be passed through this sub.
• Modify the xlwings VisualBasic Module update ExecuteMac and ExecuteWindows;
• • based on the "Use Anaconda" boolean update the RunCommand variable in ExecuteWindows to support the additional Anaconda activate command (as found in my previous post)
• • *** I dont' know how this would work in Mac so I can't help ATM on how to modify ExecuteMac ***

Without an IDE and a few other things I don't think I can help get you much closer to it.

I am very disappointed I can't hammer this one home due to the tools I have available. I'm happy to remain involved.

Cheers

[Brad-eki]

A prototype code snippit for ExecuteWindows() that I found works for me;

    If IsFrozen = False Then
        RunCommand = "C:\Users\me\AppData\Local\Continuum\anaconda3\condabin\activate.bat py37 && " & PythonInterpreter & " -B -c ""import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'" & PYTHONPATH & "')).split(';'); " & PythonCommand & """ "
    ElseIf IsFrozen = True Then
        RunCommand = "C:\Users\me\AppData\Local\Continuum\anaconda3\condabin\activate.bat py37 && " & PythonCommand & " "
    End If

For this to work I needed to set the Interpreter value in the config to simply be "python"

[Brad-eki]

I have hacked my way through, but this is a long way from a good solution... (and still requires effort to be put in to properly support Anaconda)

*** I doubt the frozen RunCommand would work. Currently un-tested ***

I have added to the xlwings.conf tab in my Excel file two rows.

• A7: Activate command
• A8: Environment name

Where

• B7 defines the command for Anaconda's activate command
• B8 defines the name of the environment

Sub ExecuteWindows(IsFrozen As Boolean, PythonCommand As String, PYTHON_WIN As String, LOG_FILE As String, Optional PYTHONPATH As String)
    ' Call a command window and change to the directory of the Python installation or frozen executable
    ' Note: If Python is called from a different directory with the fully qualified path, pywintypesXX.dll won't be found.
    ' This seems to be a general issue with pywin32, see http://stackoverflow.com/q/7238403/918626

    Dim wsh As Object
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 0
    Set wsh = CreateObject("WScript.Shell")
    Dim DriveCommand As String, RunCommand As String, WORKBOOK_FULLNAME As String, PythonInterpreter As String, PythonDir As String
    Dim ExitCode As Integer
    Dim AnacondaActivatePath As String: AnacondaActivatePath = Range("xlwings.conf!B7")
    Dim AnacondaEnvironmentName As String: AnacondaEnvironmentName = Range("xlwings.conf!B8")
    
    If LOG_FILE = "" Then
        LOG_FILE = Environ("APPDATA") + "\xlwings.log"
    End If

    If Not IsFrozen And (PYTHON_WIN <> "python" And PYTHON_WIN <> "pythonw") Then
        If FileExists(PYTHON_WIN) Then
            PythonDir = ParentFolder(PYTHON_WIN)
        Else
            MsgBox "Could not find Interpreter!", vbCritical
            Exit Sub
        End If
    Else
        PythonDir = ""  ' TODO: hack
    End If

    If Left$(PYTHON_WIN, 2) Like "[A-Za-z]:" Then
        ' If Python is installed on a mapped or local drive, change to drive, then cd to path
        DriveCommand = Left$(PYTHON_WIN, 2) & " & cd """ & PythonDir & """ & "
    ElseIf Left$(PYTHON_WIN, 2) = "\\" Then
        ' If Python is installed on a UNC path, temporarily mount and activate a drive letter with pushd
        DriveCommand = "pushd """ & PythonDir & """ & "
    End If

    ' Run Python with the "-c" command line switch: add the path of the python file and run the
    ' Command as first argument, then provide the WORKBOOK_FULLNAME and "from_xl" as 2nd and 3rd arguments.
    ' Then redirect stderr to the LOG_FILE and wait for the call to return.
    WORKBOOK_FULLNAME = ThisWorkbook.FullName

    If PYTHON_WIN <> "python" And PYTHON_WIN <> "pythonw" Then
        PythonInterpreter = Chr(34) & PYTHON_WIN & Chr(34)
    Else
        PythonInterpreter = "python"
    End If

    If IsFrozen = False Then
        RunCommand = AnacondaActivatePath & " " & AnacondaEnvironmentName & " && " & PythonInterpreter & " -B -c ""import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'" & PYTHONPATH & "')).split(';'); " & PythonCommand & """ "
    ElseIf IsFrozen = True Then
        RunCommand = AnacondaActivatePath & " " & AnacondaEnvironmentName & " && " & PythonCommand & " "
    End If

    ExitCode = wsh.Run("cmd.exe /C " & DriveCommand & _
                   RunCommand & _
                   "--wb=" & """" & WORKBOOK_FULLNAME & """ --from_xl=1" & " --app=" & Chr(34) & _
                   Application.Path & "\" & Application.Name & Chr(34) & " --hwnd=" & Chr(34) & Application.Hwnd & Chr(34) & _
                   " 2> """ & LOG_FILE & """ ", _
                   windowStyle, waitOnReturn)

    'If ExitCode <> 0 then there's something wrong
    If ExitCode <> 0 Then
        Call ShowError(LOG_FILE)
    End If

    ' Delete file after the error message has been shown
    On Error Resume Next
        'Kill LOG_FILE
    On Error GoTo 0

    ' Clean up
    Set wsh = Nothing
End Sub

[fzumstein]

hey guys, thanks for your help so far! I'll try to add a developer guide today to demystify a few things.

[fzumstein]

See: https://github.com/ZoomerAnalytics/xlwings/blob/master/DEVELOPER_GUIDE.md

[Brad-eki]

@fzumstein, the guide is awesome. Very much appreciated, thank you.

Hopefully between the developer guide, @nbahmanyar and whatever I can do (if anything) we will get Anaconda support off the ground. :D

[Brad-eki]

Hi all,

Apologies to @nbahmanyar, I've had a go at it. I was able to borrow a computer for a while to add the fields to the Ribbon and from there it was indeed all Visual Basic as @fzumstein had mentioned.

Hopefully I have done a good enough job to only need a minor change here or there to get through...

Cheers.

[navicenna]

@Brad-eki , no worries. I'm glad you were able to figure it out. If I had approached it, it would probably have taken some time for me to familiarize myself with the layout of the code before I could implement the changes.

[Brad-eki]

@nbahmanyar this is a great problem to set the environment up and become familiar with the code.

I would be immensely appreciative if you could test it for me.

I'm also open to comments and corrections...

I'm happy to explain what I've put where and why.

[navicenna]

@Brad-eki ,

I have tested your pull request and I believe it's working. Here are the steps I took:

1. Remove the xlwings addin temporarily: xlwings addin remove from anaconda prompt
2. Create a separate conda env: conda env create -n xlwings_testing python=3.7 anaconda. Activate the environment as well.
3. Clone the xlwings repo and then fetch the pull request:
   git clone https://github.com/ZoomerAnalytics/xlwings
git fetch origin pull/1055/head
4. Install pandas and then install xlwings from the newly cloned repo directory
   conda install pandas
pip install -e \...\xlwings
5. From the xlwings testing conda env: xlwings addin install
6. Use xlwings quickstart to create a testing file.

After these steps, what I tried was a simple import of pandas, and then creating a dummy dataframe and pasting the dataframe into Excel. At first, I got the same error as before.

However, after I checked "Use Anaconda", and set Activate file to "C:\anaconda\Scripts\activate.bat", and set Environment name to "xlwings_testing", I was able to run the code without error.

Note that setting Activate file to "C:\anaconda\Scripts" did not work for me. I had to specify the full path to the file.

[Brad-eki]

Hi @nbahmanyar, sounds good to me.

Yes, I have not assumed the file name for the activate script. Whether we should or not... I'm happy for the argument to go either way. Maybe we take a the lead from the Python interpreter setting and take the file when given but assume activate.bat when not.

If you want to make that mod I'll be happy to offer assistance where I can. I reckon there might be some other clean-up if you get really engaged.

[idahopotato1]

You showed me before but how do I go back to the previous version? I am having trouble with the latest version. I would like to go back to v. 11.4

[fzumstein]

resolved for RunPython by #1089 , opened a dedicated issue for UDFs: #1090