CoCreate Modeling code examples: Hello, Excel!

topic end
<!--
   * Set TOPICTITLE = <nop>CoCreate Modeling code examples: Hello, Excel!
-->

<style type="text/css">
pre { background-color:#FFEECC; }
</style>

---++ <nop>CoCreate Modeling: Hello, Excel!

<nop>CoCreate Modeling isn't overly shy and likes to mingle, having no 
inhibitions to talk to strangers,
as you may have seen already in other [[OsdmMacros][code examples]] on this site. The following
example shows how <nop>CoCreate Modeling can connect to Office applications such as Excel through the magic
of [[http://en.wikipedia.org/wiki/Dynamic_data_exchange][DDE]].

Even though DDE as a technology is quite old now, you're still using it all the time:
Whenever you double-click a file in Windows Explorer, the shell sends DDE messages
to activate applications which feel responsible for the selected file type.

Microsoft's Office applications have very robust and versatile DDE interfaces,
and <nop>CoCreate Modeling can use those interfaces to exchange data. Our
example focuses on Excel since this is arguably the Office application which is 
nearest and dearest to the hearts of mechanical engineers.

DDE is based on an exchange of Windows messages which establish _conversations_
between applications. A typical conversation starts like this:
   * Client: "Hello, everybody! Anybody here who provides service <i>S</i>?"
   * Server: "Yup, that's me."
   * Client: "Ah, great - do you feel like talking about topic <i>T</i> to me?"
   * The server nods again, and a connection between the two applications
     is established.

<nop>CoCreate Modeling provides a DDE service called =PESD=, with a single conversation topic
called =GENERAL=. A typical Excel conversation either uses the system/topic pair
<tt>Excel</tt>/<tt>System</tt>, or "connects" to a specific workbook and worksheet,
as we shall see in a moment.

So why don't you just fire up <nop>CoCreate Modeling and Excel and follow along with some
examples? Here's how you would set up a conversation with Excel using the DDE
functionality provided in <nop>CoCreate Modeling's Integration Kit:

<pre>
 (setf dde (oli:sd-dde-initiate "Excel" "[Book1]Sheet1"))
</pre>

We assume here that Excel has already created a default workbook called "Book1",
with a default worksheet which has the name "Sheet1". If in your instance
the current workbook and worksheet have different names (for example, you
might be running a localized version of Excel), you need to adjust
the =sd-dde-initiate= call accordingly.

Once we have the connection, we can start to inquire data from the Excel worksheet:

<pre>
  (setf cell (oli:sd-dde-request dde "R1C1"))
</pre>

In DDE conversations, Excel identifies cells with its internal row/column
notation instead of "A1" or "C3" which it displays in the UI.

We can also send commands to Excel. For example, to write today's date as a new
value into cell R1C1, we'd send this:

<pre>
  (oli:sd-dde-execute dde "[Formula(\"=TODAY()\", \"R1C1\")]")
</pre>

The command expression looks a little complicated, but really isn't. Excel understands
commands of the format <tt>[COMMAND("param1", "param2"...)]"</tt>. In our case, the command
is =Formula=, and the parameters are <tt>=TODAY()</tt> and <tt>R1C1</tt>. Since we're embedding 
the command and parameters in a Lisp string, we have to escape
the quotes (i.e. \" instead of "), and that is how we arrive at the expression above.

Now we should be all set to understand the following code:

<pre>
;; -*-Lisp-*-
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; Description:  Hello, Excel!
;; Author:       Claus Brod  
;; Language:     Lisp
;;
;; (C) Copyright 2006 Claus Brod, all rights reserved
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;;

(in-package :clausbrod.de)
(use-package :oli)

(defun connect-to-excel()
  (let ((dde (oli:sd-dde-initiate "Excel" "System")))
    (if (eq :error dde) nil dde)))

(defun disconnect-from-excel(dde)
  (oli:sd-dde-close dde))

(defun set-cell(dde cell formula) 
  (oli:sd-dde-execute dde (format nil "[Formula(~S, ~S)]" formula cell)))

(defun send-excel-command(dde cmd)
  (oli:sd-dde-execute dde (format nil "[~A]" cmd)))

(defun select-range(dde range)
  (oli:sd-dde-execute dde (format nil "[select(~S)]" range)))

(defun get-object-type(obj)
  (cond ((sd-inq-part-p obj)
    (cond ((sd-inq-face-part-p obj) "face part")
          ((sd-inq-wire-part-p obj) "wire part")
          ((sd-inq-empty-part-p obj) "empty part")
          (t "part")))
   ((sd-inq-assembly-p obj) "assembly")
   ((sd-inq-container-p obj) "container")
   ((sd-inq-workplane-p obj) "workplane")
   ((sd-inq-wpset-p obj) "wpset")
   (t "unknown"))
  )

(defun get-parent-name(obj)
  (if (sd-inq-parent-obj obj)
      (sd-inq-obj-pathname (sd-inq-parent-obj obj))
      "/"))

(defun send-objects-to-excel(objects filename)
  (let ((dde (connect-to-excel)) (cnt 1))
    (unless dde
      (display "Cannot connect to Excel.")
      (return-from send-objects-to-excel nil))

    ;; open new sheet
    (send-excel-command dde "NEW()")
    
    ;; write table header
    (set-cell dde "r1c1" "Name")
    (set-cell dde "r1c2" "Type")
    (set-cell dde "r1c3" "Parent")
    
    ;; write table
    (dolist (obj objects)
      (incf cnt)
      (set-cell dde (format nil "r~Ac1" cnt) (sd-inq-obj-pathname obj))
      (set-cell dde (format nil "r~Ac2" cnt) (get-object-type obj))
      (set-cell dde (format nil "r~Ac3" cnt) (get-parent-name obj)))
    
    ;; select table, write worksheet to file
    (select-range dde (format nil "r2c1:r~Ac3" cnt))
    (send-excel-command dde (format nil "SAVE.AS(~S)" filename))
    (disconnect-from-excel dde)
    ))

;; Exports selected objects into Excel table; assumes that Excel is running
(sd-defdialog 'OBJECTS_TO_EXCEL :dialog-title "Objects to Excel"
   :variables
   '(
      (objects :multiple-items t :selection (*sd-object-seltype*))
      (filename :value-type :filename 
       :direction :output :initial-value '("foo.xls" :overwrite))
    )
    :ok-action '(send-objects-to-excel objects (first filename)))

</pre>

<img src="%ATTACHURL%/objects2excel.jpg" align="right" />
This dialog collects a variable number of objects. Typically, you'd load an assembly and use
the dialog to select all objects in an assembly recursively. Then you select the name
of a file which will hold the output in Excel format when we're done.

When the user input has been picked up, the dialog calls =send-objects-to-excel= which
performs the following tasks:
   * Establish a DDE connection with Excel (by calling =connect-to-excel=)
   * Open a new sheet in Excel (through =send-excel-command=)
   * Write a table header into the sheet (=set-cell=)
   * Loop over the objects selected by the user; for each object, write
     its name, type and the name of its parent object into the Excel table
   * Ask Excel to write the new table into an Excel file
   * Disconnect from Excel

All the other functions are just small helpers; just make sure you understand the purpose
and structure of =send-objects-to-excel=, and everything else will follow.

Oh, by the way, if you want to raise Excel's application window to the top after exporting
data to it, try this:

<pre>
  (send-excel-command dde "[APP.ACTIVATE()")
</pre>

<table border="0">
<tr>
<th>Assembly in OSDM</th>
<th>Assembly table in Excel</th>
</tr>

<tr>
<td valign="top">
<img src="%ATTACHURL%/actassy_browser.jpg" />
</td>
<td valign="top">
<img src="%ATTACHURL%/exceloutput.png" />
</td>
</tr>
</table>

But how do you know which commands are accepted by Excel through DDE? Well, the applicable
commands are derived from the old macro language which dates back to Excel version 4.
Microsoft provides an installation package which contains online help on those macros
in [[http://support.microsoft.com/kb/q128185/][Knowledge Base Article 128185]].
Another such package, potentially more recent than the aforementioned, is described in
[[http://support.microsoft.com/kb/q143466/][Knowledge Base Article 143466]], but I haven't
checked that yet.

Apart from that, Google is your friend. To get started:
   * [[http://www.angelfire.com/biz/rhaminisys/ddeapps.html#DDEExcelSpec][Controlling Excel with DDE]] (DDE FAQ)
   * [[http://genstat.co.uk/doc/8doc/html/server/DDEEXPOR.htm][DDEEXPORT procedure]]
   * [[http://www.filemaker-magazin.de/fmmforum/51015.html][DDE EXECUTE Befehlssammlung]] (German)
   * [[http://wiki.tcl.tk/996][Wiki topic on DDE in TCL]]

See also the <nop>CoCreate Modeling FAQ list [[OsdmFaqCustomization#dde][entry on DDE]].

-- Main.ClausBrod - 25 Jan 2006

PS: The code above was tested using an English version of Excel. The default names of workbooks and 
worksheets differ in other languages. For example, "Book1" is "Mappe1" in the German version of
Excel (I _think_), and "Sheet1" is probably "Blatt1". It may also be required to use
[[http://msdn.microsoft.com/en-us/library/bb213527.aspx][FormulaR1C1]] rather than =Formula= for improved language independence (not tested).

---

%COMMENT{type="below" nonotify="on"}%
to top
End of topic
Skip to action links | Back to top
You are here: CoCreateModeling > MacroHelloExcel

r1.15 - 12 Feb 2010 - 16:35 - ClausBrod to top
CoCreateModeling
Welcome
