Boost your productivity with a full Clojure programming environment in Microsoft Excel
Excel REPL makes it easy to start a ClojureCLR Repl from within Excel. Simply install it as an Excel Add-In to provide a few additional Excel Functions
Installer (requires administrator permissions).
There is a Manual Installer if you lack administrator permissions on your machine.
Select output column
Type =Load(A:A) where A is the input column. Press CTRL+SHIFT+ENTER, not just enter.
Create your first bug. Excel-REPL splits the stack trace down the output column.
NB: Excel 2003 or earlier does not support entire column selection. Type =Load(A1:A200) instead.
Download Excel-REPL.xlsx for a quick demo. If this doesn't work correctly you must have an installation error. Please contact the author straight away.
(defn ^:export f [] ...)
(defn ^:export g ([] "No Args") ([x] "One Arg"))
(defn ^:export h [single-cell-argument [_ :as excel-array-argument]] ...)
(defn ^:export i [[[_] :as two-d-array] ...)
(require 'excel-repl.udf)
(excel-repl.udf/export-fns) ;exports the above functionsexcel-repl.udf/export-fns will export as Excel user defined functions all functions with ^:export metadata in the current namespace. Functions with a single arglist are simply exported as their name. Multiarity functions include the arity. In the example above f will export =F() and g will export =G0() and =G1(x).
Excel REPL assumes all arguments are passed as single cell selections (A1, B6 etc). To indicate that an argument should be an array selection declare that argument with vector destructuring. For 2D arrays use double destructuring.
excel-repl.udf/export-fns abuses Excel slightly and may fail on the first one or two invocations in a given session.
(defn ^:export ^:async f [x] ...)Asynchronous export caches on arguments to f.
You may read values directly from the workbook
(require '[excel-repl.interop :as interop])
(interop/get-values "MySheet" "A6")
(interop/get-values "AnotherSheet" "A6:B7")
Please see interop.clj for the functions to manipulate the worksheet.
If Load returns a 1 or 2 dimensional collection you may paste it into a range of Excel Cells. To do so
-
Drag from the top left hand corner the number of cells for your output
-
Click in the formula bar and enter your formula
-
Press Control + Shift + Enter instead of simply enter
WARNING: Only Load can be used in this way. If you return exported functions in this way Excel will crash.
Errors are caught and returned as text within the output cells. The stacktrace is split down the column so select multiple cells for output as mentioned above.
Excel REPL adds useful functions and macros to clojure.core that are useful when interacting with a worksheet. Please see excel-repl.clj for details.
If you wish to pull stuff off the net straight into your worksheet clr-http-lite is included
(require '[clr-http.lite.client :as client])
(client/get "http://google.com")
=> {:status 200
:headers {"date" "Sun, 01 Aug 2010 07:03:49 GMT"
"cache-control" "private, max-age=0"
"content-type" "text/html; charset=ISO-8859-1"
...}
:body "<!doctype html>..."}
Excel REPL provides convenience methods for connecting directly to a mongo database
(require '[excel-repl.coerce-db :as coerce-db])
(defonce connection (DB/Connect))
(DB/Set connection "test-values" (coerce-db/clojure->mongo {:hi "there"} ))
(-> connection (DB/Get "test-values") coerce-db/mongo->clojure str); {:hi "there"}Excel REPL uses ClojureCLR which has less support than the main JVM implementation. You may wish to connect to an external Clojure repl. Both HTTP and TCP connections are supported.
(require '[clojure.tools.nrepl :as nrepl])
(require '[clojure.data.drawbridge-client :as drawbridge-client]) ;Adds Http support to Nrepl
(def timeout 10000); 10 seconds
(def tcp-client (nrepl/client (nrepl/url-connect "nrepl://localhost:50000")) timeout)
(def http-client (nrepl/client (nrepl/url-connect "http://some.server/drawbridge-client")) timeout)
(defn remote-eval-str
"evaluates string on remote repl"
[code-str]
(-> tcp-client
(nrepl/message {:op "eval" :code code-str})
nrepl/response-values))
(defmacro remote-eval [& body]
`(first (remote-eval-str (nrepl/code ~@body))))
(remote-eval (+ 1 2)); 3For information about connecting to a Clojure repl via Http, visit Drawbridge.
You may also use Excel-REPL as an nrepl server. This makes it easy to push data over to the spreadsheet
(require '[clojure.tools.nrepl.server :as server])
(defonce server (server/start-server))The build process is a bit of a manual hack. Please contact the author if you want help with this.
Excel Repl works with Microsoft Excel 97+ (that's quite old) and Microsoft .NET 4.0 or 4.5.
Be careful when spitting. Excel sometimes runs the code several times, creating a race condition. Use the following pattern
(defonce o (Object.))
(locking o (spit my-file contents))