Skip to content

Instantly share code, notes, and snippets.

@Lovesan
Created February 22, 2024 21:45
Show Gist options
  • Save Lovesan/bc5d26004b2cd3399882f907d15e3ed0 to your computer and use it in GitHub Desktop.
Save Lovesan/bc5d26004b2cd3399882f907d15e3ed0 to your computer and use it in GitHub Desktop.
Excel interop using bike library
(eval-when (:compile-toplevel :load-toplevel :execute)
(unless (find-package 'bike)
(ql:quickload 'bike)))
(named-readtables:in-readtable bike:bike-syntax)
(bike:use-namespace 'System)
;; The below is required to overcome internal bike optimizations related to property retrieval,
;; which utilize Type.GetProperty internally, which does not work for COM objects.
;; Note that you should also use bike:reflection-invoke instead of invoke, for the same reasons.
(defun comprop (obj property &rest args)
"Retrieves a value of COM object PROPERTY"
(declare (type bike:dotnet-object obj)
(type alexandria:string-designator property))
(let ((type (bike:bike-type-of obj)))
(bike:invoke type 'InvokeMember
(string property)
(bike:enum 'System.Reflection.BindingFlags 'GetProperty)
nil
obj
(bike:list-to-bike-vector args))))
(defun (setf comprop) (new-value obj property)
"Modifies a value of COM object PROPERTY"
(declare (type bike:dotnet-object obj)
(type alexandria:string-designator property))
(let ((type (bike:bike-type-of obj)))
(bike:invoke type 'InvokeMember
(string property)
(bike:enum 'System.Reflection.BindingFlags 'SetProperty)
nil
obj
(bike:list-to-bike-vector (list new-value)))))
(defvar *app* [:Activator CreateInstance [:Type GetTypeFromProgID "Excel.Application"]])
;; https://learn.microsoft.com/en-us/office/vba/api/excel.application(object)
(defvar *workbook* (bike:reflection-invoke (comprop *app* 'Workbooks) 'Add))
(defvar *sheet* (comprop *workbook* 'Worksheets 1))
(defvar *first-cell* (comprop *sheet* 'Cells 1 1))
(setf (comprop *first-cell* 'Value) "Hello, World!")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment