How to package xlwings codes with pyinstaller?

Question

I just want to keep excel as a UI and allow user call python instead of VBA from the spreadsheet. Also the python files need to be packaged into one .exe file.

Without any tutorials found, this is what i tried:

test.py:

from xlwings import Book
import xlwings as xw

def test():
    sh = xw.Book.caller().sheets['a']
    sh.range('A1').value = 'hello'


if __name__ == '__main__':
    test()

Then I use pyinstaller:

pyinstaller test.py -F

I copied the spreadsheet test.xlsm to the same directory as the test.exe

The vba codes from test.xlsm:

Sub callpython()
    RunFrozenPython ("test.exe")
End Sub

Eventually i got: ---------------------------

Error

'test.exe' is not recognized as an internal or external command, operable program or batch file.

What's more annoying is that if I import pandas in python, pyinstaller won't even compiler due to "maximum recursion depth exceeded".

Can anyone provide an example of how to make these two things work together? I don't even have to use xlwings or pyinstaller, as long as it can achieve making python codes into one executable file and run it from Excel.

=======================

Update:

I finally fixed them by:

1. uninstall pyinstaller and replace it with pyinstaller development version (v3.3)
2. manually modify xlwings.bas code in VBA. It seems that PYTHON_FROZEN searches executable with a hard coded path: PYTHON_FROZEN = ThisWorkbook.Path & "\build\exe.win32-2.7

Hope xlwings team can replace the logic of finding the .exe file with a more robust one.

Answer 1

With some delay... the following spec worked out for us.

The ruses are:

• Apply/extend hidden imports
• Manually extend referenced libraries
• (Un)comment section for onefile or onedir
• enable/disable console by console=True or False

Our spec "MagicHelper.spec" looks like

from PyInstaller.utils.win32.versioninfo import *
from PyInstaller.utils.hooks import collect_data_files
import platform, os, datetime

name = "MagicHelper"
host = platform.node()
user = os.getlogin()

ts = datetime.datetime.now()
ts = ts.strftime('%d-%m-%y %H:%M')

filevers = (1, 0, 0, 1)
prodvers = (1, 0, 0, 0)

vers = VSVersionInfo(
       ffi=FixedFileInfo(
       filevers=filevers,
       prodvers=prodvers,
       mask=0x3f,
       flags=0x0,
       OS=0x40004,
       fileType=0x1,
       subtype=0x0,
       date=(0, 0)),
       kids=[StringFileInfo([StringTable(
       u'040904B0',
       [StringStruct(u'FileDescription', f'{name} [built on {host} by {user}]'),
       StringStruct(u'FileVersion', '.'.join(map(str, filevers))),
       StringStruct(u'InternalName', name),
       StringStruct(u'LegalCopyright', u'Company Ltd'),
       StringStruct(u'OriginalFilename', f'{name}.py'),
       StringStruct(u'ProductName', f'{name}'),
       StringStruct(u'ProductVersion', '.'.join(map(str, prodvers))),
       StringStruct(u'Language', u'English'),
       StringStruct(u'LegalTrademarks', u'--'),
       StringStruct(u'Comments', f'Generated on {ts}')])]),
       VarFileInfo([VarStruct(u'Translation', [1033, 1200])])]
   )

block_cipher = None
hiddenimports = [name]

datas = []
#In case of 3rd party lib, which are dynamically loaded
#datas += collect_data_files('pandas', True)
#datas += collect_data_files('numpy', True)

print("Building {name}.exe")
a = Analysis([f'./{name}.py'],
             pathex=['.'],
             binaries=[],
             datas=datas,
             hiddenimports=hiddenimports,
             hookspath=[],
             runtime_hooks=[],
             excludes=[],
             win_no_prefer_redirects=False,
             win_private_assemblies=False,
             cipher=block_cipher,
             noarchive=False)
pyz = PYZ(a.pure, a.zipped_data,
             cipher=block_cipher)

#In case of sys.path.append('...')
#a.datas += Tree(r'...', prefix=r'.\magic\xyz')    
#print(a.scripts)

exe = EXE(pyz,
          a.scripts,
          a.binaries,
          a.zipfiles,
          a.datas,
          [],
          name=f'{name}.exe',
          debug=False,
          bootloader_ignore_signals=False,
          strip=False,
          upx=True,
          runtime_tmpdir=None,
          console=False , icon='./src/MagicHelper.ico', version=vers)

"""
#In case you go for --onedir instead of --onefile
exe = EXE(pyz,
          a.scripts,
          [],
          exclude_binaries=True,
          name=f'{name}.exe',
          debug=False,
          bootloader_ignore_signals=False,
          strip=False,
          upx=True,
          console=False , icon='./src/MagicHelper.ico')
coll = COLLECT(exe,
               a.binaries,
               a.zipfiles,
               a.datas,
               strip=False,
               upx=True,
               upx_exclude=[],
               name=name)
"""

Please run it with

pyinstaller MagicHelper.spec

Finally, give your end users the good Excel experience, they deserve