CoCreate Modeling code examples: Hello, Excel!

CoCreate Modeling: Hello, Excel!

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 code examples on this site. The following example shows how CoCreate Modeling can connect to Office applications such as Excel through the magic of 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 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 S?"
Server: "Yup, that's me."
Client: "Ah, great - do you feel like talking about topic T to me?"
The server nods again, and a connection between the two applications is established.

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 Excel/System, or "connects" to a specific workbook and worksheet, as we shall see in a moment.

So why don't you just fire up 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 CoCreate Modeling's Integration Kit:

 (setf dde (oli:sd-dde-initiate "Excel" "[Book1]Sheet1"))

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:

  (setf cell (oli:sd-dde-request dde "R1C1"))

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:

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

The command expression looks a little complicated, but really isn't. Excel understands commands of the format [COMMAND("param1", "param2"...)]". In our case, the command is Formula, and the parameters are =TODAY() and R1C1. 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:

;; -*-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)))

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:

  (send-excel-command dde "[APP.ACTIVATE()")

Assembly in OSDM	Assembly table in Excel

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 Knowledge Base Article 128185. Another such package, potentially more recent than the aforementioned, is described in Knowledge Base Article 143466, but I haven't checked that yet.

Apart from that, Google is your friend. To get started:

See also the CoCreate Modeling FAQ list entry on DDE.

-- ClausBrod - 25 Jan 2006

Revision: r1.13 - 24 Jul 2009 - 19:55 - ClausBrod

CoCreateModeling > OsdmMacros > MacroHelloExcel