DOCSEARCHbyAlgolia
Submit Docs

xlOil Framework

repository·master·Indexed 41 days ago

https://github.com/cunnane/xloil

A multi-language framework for extending Microsoft Excel using Python, C++, or SQL. It serves as a modern alternative to VBA, enabling the creation of high-performance worksheet functions, macros, and custom UI elements like Ribbon toolbars and Custom Task Panes. Features include deep integration with numpy and pandas, support for async and RTD functions, and COM automation for full access to the Excel object model.

Tokens
37.4K
Snippets
122
Records
166
Agent score
100%

What's inside xlOil

  1. Overview of xlOil

    master

    xlOil is a framework for interacting with Microsoft Excel using different programming languages. It allows you to write functions in languages like Python, C++, or SQL and expose them to Excel as worksheet functions, macros, or GUI controllers.

    Key capabilities include:

    • Replacing VBA with more modern languages (e.g., Python).
    • Low-overhead worksheet function calls.
    • Controlling Excel GUI elements like Ribbon toolbars and Custom Task Panes.
    • Driving Excel through COM automation and hooking Excel events.
    • Using plugins to support various languages.

  2. Overview of xlOil framework

    master

    xlOil is a framework designed to link programming languages with Excel. It allows you to write functions in languages like C++, Python, or SQL and have them appear in Excel as worksheet functions and macros. It also enables manipulation of the Excel application and GUI, similar to VBA, but with much lower overhead for calling custom worksheet functions.

    Supported languages via plugins:

    • C++
    • Python
    • SQL
    • xlOil_Utils: A collection of high-performance utility functions (e.g., multi-column sorting, string splitting/joining, and array creation).

  3. What is XLCALL.CPP and when to include it

    master

    XLCALL.CPP is a source file introduced in the Excel 2007 XLL SDK that contains the definitions for two critical Excel entry points: Excel12 and Excel12v.

    If you are building an XLL that utilizes these entry points, you must ensure the code from XLCALL.CPP is included in your build process to ensure successful compilation.

    Exception: You do not need to explicitly include XLCALL.CPP if your XLL links to frmwrk32.lib, as this library already contains the necessary code.

  4. C++ features in xlOil

    master

    The C++ plugin provides high-performance bindings for Excel development.

    Core Functionality:

    • Ease of Use: Provides safe and convenient wrappers around the Excel C-API and features automatic registration for a concise function declaration syntax.
    • Data Handling: Natural C++ handling of Excel variants, Ranges, Arrays, and strings.
    • Object Management: An object cache allows you to return opaque objects to Excel and pass them back into other functions.
    • RTD Support: Simplified RTD server creation and RTD-based background calculation.
    • UI Customization: Ability to create Ribbon toolbars and Custom Task Panes.

  5. Access the Excel.Application object in xlOil

    master

    The Excel.Application object is the root of Excel's COM interface. Depending on how you are using xlOil, you can access it in different ways:

    • If embedded in Excel (as an add-in): Use xloil.app() to get a reference to the parent application.
    • If used as a standalone package: Create a new instance using xloil.Application().

    Important Threading Rule: All COM calls must be invoked on the main thread. A function runs on the main thread if it is not declared multi-threaded, or if it is called from VBA or the GUI. If you need to schedule a callback to run on the main thread (e.g., to write to a sheet during a calculation cycle), use xloil.excel_callback.

    Note that during Excel's calculation cycle, much of the Application object model is locked, and writing to sheets is blocked.

    import xloil as xlo

# If embedded
app = xlo.app()

# If standalone
app = xlo.Application()

  6. Compare RTD vs Native Async functions

    master

    When implementing real-time or asynchronous logic, choose between RTD and Native Async based on your requirements:

    RTD (Real Time Data)

    • Pros: Operates independently of the Excel calculation cycle (true background execution); provides notifications when a function call changes or is removed.
    • Cons: Higher overhead than native async; requires 'Automatic Calculation' to be enabled (otherwise, you must manually press F9).

    Native Async

    • Pros: Lower overhead; works with 'Manual Calculation' mode.
    • Cons: Tied to the calculation cycle. Any user interaction with Excel interrupts the calculation, meaning native async functions cannot be used for true background calculations as they will be cancelled during interaction.

  7. Use Workbook Modules to load Python code automatically

    master

    When an Excel workbook is opened, xlOil attempts to load a Python module named <workbook_name>.py (this behavior is configurable). Functions decorated with @xlo.func within these modules are registered as local functions by default.

    To retrieve the file path of the workbook currently associated with the module, use xloil.linked_workbook().

    # Inside <my_workbook>.py
import xloil as xlo

def get_path():
    return xlo.linked_workbook()

  8. Understand the xlOil multi-addin architecture

    master

    xlOil supports running multiple independent add-ins (multiple XLL loaders with different settings/codebases) within a single Excel session.

    Concurrency and Threading Model:

    • Add-in Isolation: Each add-in runs in a background thread with its own asyncio event loop. Access this loop via xloil.get_event_loop().
    • Shared Resources: All add-ins share the same Python interpreter and the same Python object cache.
    • Execution Contexts:
      • Worksheet Functions: Executed in Excel's main thread or a worker thread.
      • Async / RTD Functions: Executed in a dedicated xlOil Core event loop, accessible via xloil.get_async_loop().
    • Identification: Use xloil.source_addin() to identify which add-in is currently executing the code.

    Note: Due to limitations in CPython and extensions like NumPy, subinterpreter support is not currently provided.

  9. SQL and Utils features in xlOil

    master

    xlOil includes specialized support for SQL queries and a utility library.

    SQL Plugin:

    • Allows you to create tables from Excel ranges and arrays.
    • Enables querying and joining these tables using full sqlite3 SQL syntax.

    xlOil_Utils: Provides high-performance utility functions for common tasks:

    • Sorting on multiple columns.
    • Splitting and joining strings.
    • Creating arrays from blocks.

  10. Choose a C++ integration method for xlOil

    master

    There are three primary ways to build a C++ project with xlOil, depending on how you want to interact with the Excel environment and the xlOil object cache:

    1. xlOil plugin: You write a DLL that is loaded by the main xlOil loader XLL. This is the most integrated method, allowing full cooperation with the object cache and settings file. You can use cache references created by xloRef within your add-in.
    2. XLL with dynamic linking: Your XLL links against xlOil.lib. This allows cooperation with the Excel object cache, but your XLL must be able to locate xlOil.dll at load time (e.g., via delay loading, system PATH, or placing it in the same directory).
    3. XLL with static linking: You link against a static version of xlOil to create a standalone XLL. This method does not include the xlOil cache functions xloRef and xloVal, though you can implement a private object cache with custom function names.

  11. Use cancellable Excel events

    master

    Several xlOil events allow you to intercept and cancel default Excel actions by setting a Cancel parameter to True.

    Cancellable Events:

    • SheetBeforeDoubleClick: Prevents the default double-click action.
      • Parameters: Worksheet name, Target (Range), Cancel (bool).
    • SheetBeforeRightClick: Prevents the default right-click action.
      • Parameters: Worksheet name, Target (Range), Cancel (bool).
    • WorkbookBeforeClose: Prevents the workbook from closing.
      • Parameters: Workbook name, Cancel (bool).
    • WorkbookBeforeSave: Prevents the workbook from saving.
      • Parameters: Workbook name, SaveAsUI (bool), Cancel (bool).
    • WorkbookBeforePrint: Prevents the workbook from printing.
      • Parameters: Workbook name, Cancel (bool).

  12. Python integration features in xlOil

    master

    xlOil provides deep integration for Python developers, allowing for high-performance Excel automation and function creation:

    • Function Declaration: Concise syntax to declare Excel functions with optional type checking and support for keyword arguments.
    • Scope Control: Choose between globally declared functions or code modules limited to a single workbook (similar to VBA workbook-level functions).
    • Data Handling: Tight integration with numpy for low-overhead array functions. It natively understands Python tuples, lists, dictionaries, and pandas dataframes.
    • Advanced Excel Features: Supports async functions, RTD (Real-Time Data) functions with on-the-fly server creation, and Macro-type functions that write to sheet ranges.
    • Excel Interaction: Access to the Excel Application object, ability to hook Excel events, and on-the-fly creation/destruction of COM add-in objects and Ribbon UI.
    • Two-way Connectivity: Pass any Python object to Excel and back into Python functions. Includes a two-way connection to Jupyter notebooks, allowing you to run worksheet functions in Jupyter and query variables in the Jupyter kernel.