Hacker News Reader

Comment by tzs | Hacker News Reader

This sounds like it provides a way for Excel to hand a bunch of data to Python code for processing and get back results, and vice versa.

That's probably extremely valuable to many people, but it does not seem to be what I hope for whenever I see something about bringing Python to Excel.

What I hope for is something that is like VBA but without the VB. I.e., a Python (or JavaScript) interpreter/JIT/compiler built into Excel exposing all the internal Excel objects and methods that VBA has access to. By "Python" I just mean the language and whatever subset of the standard library makes sense in Excel.

View on HN

Install PyWin32 module and `import win32com.client` and you can access "all the internal Excel objects and methods that VBA has access to".

    C:\>python
    Python 3.10.1 (tags/v3.10.1:2cd268a, Dec  6 2021, 19:10:37) [MSC v.1929 64 bit (AMD64)] on win32
    Type "help", "copyright", "credits" or "license" for more information.

    >>> from win32com.client import Dispatch
    >>> xl = Dispatch("Excel.Application")
    >>> xl.Visible = True
    >>> wb = xl.WorkBooks.Add()
    >>> sh = wb.WorkSheets[0]
    >>> sh.Range("A1").Value = "Hello World"
    >>>

The same with xlwings (works on macOS, too):

  >>> import xlwings as xw
  >>> wb = xw.Book()
  >>> sh = wb.sheets[0]
  >>> sh["A1"].value = "Hello World"

win32com.client uses Windows' COM machinery, which supports event-driven feedback from Excel to the script. Switching to PowerShell because I'm not familiar with that in Python and from a quick Google, Python needs some more complex multithreadding handling:

    $xl = new-object -ComObject excel.application

    $xl.Visible = $true
    
    $wb = $xl.WorkBooks.Add()
    $sh = $wb.Worksheets[1]
    
    
    Register-ObjectEvent -InputObject $xl -EventName SheetChange -Action {
        Write-Host "Hi"
    }

Now when you change a cell in the spreadsheet GUI and press enter, the PowerShell "write-host" runs and prints the message. Another comment mentioned "using Excel as a GUI for Python" and the COM interfaces have the machinery to do that. It looks like xlWings has something like this, but may need an add-in installed in Excel to trigger Python code from Excel? https://docs.xlwings.org/en/latest/quickstart.html and https://docs.xlwings.org/en/latest/addin.html#xlwings-addin

That would be very cool. Python with Excel as the GUI.

Reply on news.ycombinator.com