xlwings 1.0 roadmap

[fzumstein]

This is all super far away, work in progress, and very high level. However, I wanted to have a place where I'll put down my vision and let everybody comment and provide their inputs.

Modernize

• Add type hints
• Enums: use enums instead of simple strings. Should be mostly backward compatible.
• Smaller syntax changes to be more in line with Office Scripts/Google Apps Script rather than VBA
• Exception handling: sort of not really existing right now, ideally will raise the same exceptions on all platforms
• Speed: some things like value cleaning/expand() should be able to be implemented in a more efficient way with reasonable effort

Breaking Changes

• Replace Book.caller() with something like def func(mybook: xw.Caller):
• Align DataFrame reading with pandas, i.e., set index=False, or rather, replace it with index_col, tbd if the current default behavior for column headers should remain.
• Decide and document 0 vs 1 based indexing. Might be a breaking change. Office Scripts and Google Apps Script have taken different decisions:

  // Office Scripts
  mybook.getWorksheets()[0].getPosition() // 0
  
  // Google Apps Script
  mybook.getSheets()[0].getIndex()  // 1

  A possibility would be to follow Jinja2 with .index and index0.
• Looks like COM/AppleScript are the only interfaces that convert ints to floats, so maybe it's better to have all other stick to ints instead of converting them actively to floats.
• mysheet.index etc. return a 1-based index, would make more sense to be 0-based (?)
• Return all integers as ints instead of floats. All engines except pywin32 and appscript do this automatically

Add Features

• Add support for multi-ranges sheet.range("A1", "C1"). This is helpful for applying single-cell properties efficiently, such as applying a background color to different areas in one go.
• Add support for reading/writing files by wrapping OpenPyXL etc.
• Add support for manipulating Google Sheets via Sheets API and Excel via Graph API
• Make the remote interpreter a drop-in replacement for current approaches (autostart on localhost). This will enable:
  • UDFs on macOS and Google Sheets
  • Handling UDFs with Errors (#N/A) properly
  • No more OneDrive issues as the connection doesn't depend on the path
  • No more COM issues (zombie processes etc.)
  • No pywin32 dependency for RunPython/UDFs (pywin32 will only be used for from-Python-to-Excel automation)
  • Can be used locally or via server
  • RunPython may hit limits though with size
• Better configuration in general, including support for env vars and function args in addition to current possibilities. Use a standard config file format (maybe via Pydantic).
• Add possibility to select a script to run in the add-in, similar to how it's done in Office Scripts (Automate tab).
• Decide whether the current quickstart behavior (creates a Python file with the same name in the same directory) is the best default approach.
• Make add-in play nicer with anti-virus by offering versions that don't have the dll/shell calls but instead work via remote interpreter
• Document None vs "" for empty cells: None has been chosen so there's no additional transformation needed when turning number data into a NumPy array/DataFrame.
• Decide and document whether the current behavior of returning scalars/ 1-dim lists is the way to go or if ndim=2 should be the default for everything.
• Allow to access 'legacy' sheet types like chart sheets -- they are currently ignored in mybook.sheets (shows only worksheets)

Declutter

• Remove xw.Range in favor of myapp.range and mysheet.range (might not be possible because of typing though)
• Remove support for other office apps (Access etc.)
• Remove REST API
• Remove round parenthesis indexing/slicing, e.g., mysheet(1) in favor of mysheet[0] (There should be one-- and preferably only one --obvious way to do it)
• Remove support for legacy dynamic arrays on Windows UDFs

Refactor

• Internal use of 0 vs 1-based index is currently super messy
• Split main.py/ cli.py into modules
• Framework to provide custom engines
• Refactor unit tests (use pytest and make them fast)
• replace rst with md
• refactor setup.py to pyproject.toml
• dlls should not have to be next to the python.exe anymore