Hello Shawn
Am 04.08.2008 um 18:23 schrieb Shawn Hoover:
> This is cool. One thing I don't see from your description is how you
> interfaced to Excel from Clojure. Are you using COM via Java? It would
> be cool if you could put a snippet in the wiki showing how to get a
> handle to an Excel workbook in Clojure.
Yes. I'm using COM. That limits the whole solution to Windows, but pivot
tables are far more important to me than portability. (Not in general,
but because of my situation. Programming is not my full-time job after
all.)
To access the COM interface I use Jacob[1]. Then the basic access code
look like this:
---8<---
(import '(com.jacob.com Dispatch ComThread))
(def xl (new Dispatch "Excel.Application"))
(def wbl (.. Dispatch (get xl "Workbooks") (toDispatch)))
(def wb (.. Dispatch (call wbl "Add") (toDispatch)))
(def wsl (.. Dispatch (get wb "Worksheets") (toDispatch)))
(def ws (.. Dispatch (call wsl "Item" 1) (toDispatch)))
(def c (.. Dispatch (call ws "Cells" 1 1) (toDispatch)))
(. Dispatch put c "Value" "Hello, World!")
(. Dispatch call wb "SaveAs" "Hello.xls")
(. Dispatch call wb "Close" false)
(. Dispatch call xl "Quit")
(. ComThread Release)
---8<---
This is *sooooo* ugly. After shutting down everything you also
have to run some cleanup code per thread, were you used these things.
So the first I did, was to lock away any COM stuff as good as possible.
The ugly (toDispatch) calls in the code above are needed, because you
always get a Variant. So wrapping all the calls into a variant->clj
function was necessary. It just looks inside and translates the
variant's contents into something native for Clojure.
The next step was to get rid of the Dispatch in front. This is also ugly
like hell, but necessary due to the way Jacob and COM work. So showtime
for another macro, which takes care of this.
---8<---
(def-ole-object-class
application
[Visible Workbooks]
[Quit])
(def-ole-object-class
workbook
[Worksheets]
[Close SaveAs])
---8<---
The macro itself is too long, to remember it right now. It simply
creates wrappers with the right names to call the right things. Together
with some other small wrappers, we can then re-write the above code to
the following.
---8<---
(def xl (application-new))
(def wb (workbook-new xl "Hello.xls"))
(def ws (worksheet-new wb "Sheet1"))
(def c (worksheet-cells ws 1 1))
(range-value c "Hello, World!")
(workbook-close wb true)
(application-quit xl)
(. ComThread Release)
---8<---
This is more concise and concentrated on the task, then on getting
Dispatch right, but it still suffers from exceptions and cleaning up
stuff. The with-excel macro to the rescue!
---8<---
(defn with-excel*
[thunk]
(let [worker (fn
[]
(let [xl (new Dispatch "Excel.Application")]
(try
(thunk xl)
(finally
(application-quit xl)
(. ComThread Release))))) ; Clean from Jacob
t (new Thread worker)]
(. t start)
(. t join)))
(defmacro with-excel
[xl-var & body]
`(with-excel* (fn [~xl-var] ~@body)))
---8<---
This takes care of starting up an Excel, do what it is told to and
finally
cleaning up the mess afterwards. In this case it is synchronous to the
calling thread, but you may also fire off an asynchronous request and
come back to it later on. Also we don't get anything out, yet. But since
I just want to put stuff in, I don't care right now.
Now we can finally come up with a half-way usable version of the first
example.
---8<---
(with-excel xl
(let [wb (workbook-new xl "Hello.xls")
ws (worksheet-new wb "Sheet1")
c (worksheet-cells ws 1 1)]
(range-value c "Hello, World!")
; Check that it worked.
(println "A1 =" (range-value c))
(workbook-close wb true)))
---8<---
So, long answer to your question: it is fairly easy to access Excel via
COM, but is low-level and tedious. One could also have a look at
jacobgen (also from the Jacob project). But the above solution fits my
needs and I have Clojure functions instead of Java . calls and full
control over how things are named.
I will ask my boss, in how far I can publish things. The above is fairly
trivial, but to disclose more internals or even release the whole thing
I have to check the legal consequences first.
What I can point you to, however, is the Excel VBA help. It's named
something like vbaxl10.chm. One can more or less translate the VBA calls
directly to Jacob.
Hope this helps.
Sincerely
Meikel
[1]: http://sf.net/projects/jacob-project
