xlOil documentation

xlOil is a framework for linking programming languages with Excel. It provides a way to write functions in a language of your choice and have them appear in Excel as worksheet functions and macros, as well as manipulating the application and GUI similar to what is possible with VBA.

Contents:

• xlOil
  • xlOil features
  • Why xlOil was created
  • Comparison between xlOil and other Excel Addin solutions
• xlOil Getting Started
  • Installation
  • Example sheets
  • Editing the settings file
    • Setting enviroment variables in settings files
  • Troubleshooting
    • The log file
    • Manual installation
    • Manual removal
• xlOil Concepts
  • Excel Functions (UDFs)
  • Cached Excel Objects
  • Rtd / Async
  • Custom UI: The Fluent Ribbon
  • Intellisense / Function Context Help
• xlOil Excel Events
  • Introduction
  • AfterCalculate
  • WorkbookOpen
  • NewWorkbook
  • SheetSelectionChange
  • SheetBeforeDoubleClick
  • SheetBeforeRightClick
  • SheetActivate
  • SheetDeactivate
  • SheetCalculate
  • SheetChange
  • WorkbookActivate
  • WorkbookDeactivate
  • WorkbookBeforeClose
  • WorkbookBeforeSave
  • WorkbookBeforePrint
  • WorkbookAfterClose
  • WorkbookNewSheet
  • WorkbookAddinInstall
  • WorkbookAddinUninstall
  • CalcCancelled
  • XllAdd
  • XllRemove
• xlOil Core Functions
  • xloRef: adds a value or array to the cache
    • xloRef()
  • xloVal: Retrives a value from the Excel data cache
    • xloVal()
  • xloHelp: returns help on an xlOil registered function
    • xloHelp()
  • xloLog: flushes the log file and returns its location
    • xloLog()
  • xloVersion: returns information on the xlOil version
    • xloVersion()
• xlOil C++
  • xlOil C++ Getting Started
    • Creating an xlOil Plugin
    • Creating an XLL which statically links xlOil
    • Debugging your addin
  • XLL Special Arguments
    • RangeArg
    • AsyncHandle
    • ExcelObj pointer
    • FPArray
    • Inplace returns
  • xlOil C++ Events
    • WorkbookAfterClose
    • CalcCancelled
    • XllAdd
    • XllRemove
    • Examples
  • xlOil C++ Object Handles
  • xlOil C++ GUI Customisation
    • Creating a ribbon in a static XLL
    • Creating a ribbon in an xlOil plugin
    • Creating a Custom Task Pane
  • xlOil C++ RTD functions
    • Example: Subscription
    • Example: Background task
  • xlOil C++ Dynamic Registration
  • xlOil C++ Static XLLs
  • Distributing xlOil Addins
    • Example
  • xlOil C++ Using COM
  • Quick Tour of the Key Classes
    • ExcelObj - An XLOPER wrapper
    • ExcelArray / ArrayBuilder
    • PString
    • Range / RangeArg / ExcelRef
    • callExcel - calling XLL API functions
    • runExcelThread - calling blocked API functions
• xlOil Python
  • xlOil Python Introduction
    • Introduction
    • Getting Started
      • My first xlOil module
      • My first workbook module
    • Using the Ribbon
    • Troubleshooting
    • Intellisense / Function Context Help
  • xlOil Python Concepts
    • Workbook Modules
    • Array Functions
    • Events
      • Examples
    • Looking for xlOil functions in imported modules
      • xloPyLoad: import and scan a python module (worksheet function)
    • Multiple addins and event loops
  • xlOil Python Registering Functions
    • Local Functions
    • Async and RTD Functions
    • Commands, Macros & Subroutines
    • Multi-threaded functions
  • xlOil Python Type Conversion
    • Argument Types
    • Return Types
    • Cached Objects
    • Dates
    • Dicts
    • Variable and Keyword Arguments
    • Range Arguments
    • Custom Type Conversion
    • Custom Return Conversion
    • Returning Images and Plots
  • xlOil Python: The Excel.Application object
    • Introduction
    • Calling Worksheet Functions and Application.Run
    • xlOil’s Excel Object Model
    • Excel Automation
      • Creating an Application
    • Accessing Sheets and Ranges
      • Reading from a Range
      • Writing to a range
      • Using Worksheets and Workbooks
    • Troubleshooting
  • xlOil Python GUI Customisation
    • Status Bar
    • Ribbon
      • Setting button images
    • Custom Task Panes
      • Thread-safety
      • Qt Custom Task Panes
      • Tkinter Custom Task Panes
      • wxPython Custom Task Panes
      • Task Pane Events
      • Task Pane registry
    • Async GUI Calls
  • xlOil Python Package Support
    • Pandas
    • Matplotlib
    • PIL / pillow
  • xlOil Jupyter Interaction
    • Introduction
    • Registering an Excel function from Jupyter
    • Watching a variable in a Jupyter notebook
    • Running code in the kernel
    • Examples
    • Using COM Automation
    • Limitations
  • xlOil Python Async/Rtd
    • Introduction
    • Improving RTD performance (specifying topics)
    • xlOil’s RTD Interface
  • xlOil Python Debugging
    • Visual Studio
    • VS Code
    • Pdb
    • Selecting the debugger programmatically
  • xlOil Python Distributing Addins
    • Pointing the addin to a python distribution
    • Installing the Addin
  • xlOil Python Examples
  • xlOil Python GUI Examples
  • xlOil Python Questions
    • ImportError: Typelib different than module
    • Intellisense / Function Context Help
    • Auto-reload doesn’t reload a module
    • Dynamically Resized Arrays
    • This application failed to start because it could not find or load the Qt platform plugin “windows”
  • xlOil Python Module Reference
    • Declaring Worksheet Functions
      • Arg
      • Array
      • Cache
      • CannotConvert
      • CellError
      • ExcelArray
      • FastArray
      • SingleValue
      • converter()
      • deregister_functions()
      • func()
      • register_functions()
      • returner()
      • AllowRange
    • Excel Object Model
      • workbooks
      • app()
      • active_worksheet()
      • active_workbook()
      • Application
      • Caller
      • Range
      • Workbook
      • Worksheet
      • ExcelWindow
      • ExcelWindows
      • Workbooks
      • Worksheets
    • RTD Functions
      • RtdPublisher
      • RtdServer
      • RtdSimplePublisher
      • subscribe()
    • GUI Interaction
      • StatusBar
      • ExcelGUI
      • TaskPaneFrame
      • RibbonControl
      • CustomTaskPane
      • find_task_pane()
      • QtExecutor
      • QtThreadTaskPane
      • Qt_thread()
      • TkExecutor
      • TkThreadTaskPane
      • Tk_thread()
    • Events
      • Event Class
      • Events
      • Notes
      • Examples
      • Event
      • pause()
      • allow()
      • AfterCalculate
      • WorkbookOpen
      • NewWorkbook
      • SheetSelectionChange
      • SheetBeforeDoubleClick
      • SheetBeforeRightClick
      • SheetActivate
      • SheetDeactivate
      • SheetCalculate
      • SheetChange
      • WorkbookAfterClose
      • WorkbookRename
      • WorkbookActivate
      • WorkbookDeactivate
      • WorkbookBeforeClose
      • WorkbookBeforeSave
      • WorkbookAfterSave
      • WorkbookBeforePrint
      • WorkbookNewSheet
      • WorkbookAddinInstall
      • WorkbookAddinUninstall
      • XllAdd
      • XllRemove
      • ComAddinsUpdate
      • PyBye
      • UserException
    • Everything else
      • call()
      • call_async()
      • core_addin()
      • excel_callback()
      • excel_state()
      • from_excel_date()
      • get_async_loop()
      • get_event_loop()
      • in_wizard()
      • linked_workbook()
      • run()
      • run_async()
      • source_addin()
      • source_addin()
      • ExcelState
      • Addin
      • _AddinsDict
      • _DateFormatList
      • _LogWriter
      • log
      • log_except()
      • use_debugger()
    • External libraries
      • insert_cell_image()
      • PDFrame
      • ReturnImage
      • ReturnFigure
• xlOil SQL
  • SQL Getting Started
  • xloSql
    • xloSql()
  • Stateful Database Functions
    • xloSqlDB
      • xloSqlDB()
    • xloSqlTable
      • xloSqlTable()
    • xloSqlQuery
      • xloSqlQuery()
    • xloSqlTables
      • xloSqlTables()
• xlOil Utils
  • xloBlock: creates an array from blocks
    • xloBlock()
  • xloFill: gives an array filled with a single value
    • xloFill()
  • xloFillNA: replaces Empy or #N/A with a specified value
    • xloFillNA()
  • xloSort: sorts an array by multiple columns
    • xloSort()
  • xloPad: pads an array to have at least two rols and columns
    • xloPad()
  • xloConcat: concatenates strings
    • xloConcat()
  • xloSplit: splits strings at a separator
    • xloSplit()
  • xloIndex: gets values or sub-arrays for arrays and ranges
    • xloIndex()
• xlOil Development Guide
  • Getting started as developer
  • Release Instructions

Indices and tables

• Index

• Search Page