[转贴]：也贴一篇关于Vlisp与EXCEL的文章

hqd9639

Excel Spreadsheets and Visual LISP

Figure 1: Loading the Excel 97 Type Library  
(vl-load-com)
(defun vlxx-TypeLib-Excel8 ()
  (findfile
    (strcat
      (getenv "SYSTEMDRIVE"); Windows NT and 2000, example: "C:"
      "\\Program Files\\Microsoft Office\\Office\\Excel8.olb"
    )
  )
)


The next step is to actually load the type library and define the internal interfaces for properties, methods and constants.  These are arbitrarily named prefixes that simply allow you to single them out quickly and figure out the proper call to make in the external application.   
Throughout the rest of this document, I will put the type library prefixes in italics to help distinguish them from VLXX custom function names.

Figure 2 shows a simple function to load the type library and returns T or nil if successful:


Figure 2: Defining the Type Library Interfaces  

(defun vlxx-Load-TypeLib-Excel8 ( / tlbfile out)
  (cond
    ( (null msxl-xl24HourClock)
      (if (setq tlbfile (vlxx-TypeLib-Excel8))
        (progn
          (princ "\nInitializing Microsoft Excel 97...")
          (vlax-Import-Type-Library
            :tlb-filename      tlbfile
            :methods-prefix    "msxl-"
            :properties-prefix "msxl-"
            :constants-prefix  "msxl-"
          )
          (if msxl-xl24HourClock (setq out T))
        )
      )
    )
    ( T (setq out T) )
  )
  out
)  

Now that you've knocked at the door and Excel has answered "I'm Home! Who is it and what do you want?" you need to reply with something sexy, like "Hey!  I'm here for a new document to dump some data into!".   
Figure 3 gives a simple example function to open Excel and create a new blank workbook with the default active worksheets included (usually three).  Sheet 1 will normally be the default active worksheet.  This function returns the vla-object to the new Excel session object.


Figure 3: Opening Excel with a New Workbook  
;;; Note: <dmode> is either "SHOW" or "HIDE" depending upon whether
;;; you want the Excel session to be accessible to the user directly.
(defun vlxx-Open-Excel8-New (dmode / appsession)
  (princ "\nCreating new Excel Spreadsheet file...")
  (cond
    ( (setq appsession (vlax-Create-Object "Excel.Application.8"))
  
      (vlax-invoke-Method
        (vlax-Get-Property appsession 'WorkBooks)
        'Add
      )
      (if (= (strcase dmode) "SHOW")
        (vla-Put-Visible appsession 1)
        (vla-Put-Visible appsession 0)
      )
    )
  )
  appsession
)


Another common need is to open an existing workbook.  Figure 4 shows how to do this as a simple function call.  Note that the filename argument must be qualified prior to calling this function since it does not perform a check to see if it exists.
You may also notice that instead of using (vla-create-object), as in figure 3, this one uses (vla-get-or-create-object).  This is a very unique function that is ofen overlooked in Visual LISP examples.  It simply attempts to get an existing session object, and if that fails, it attempts to create one.  

It may be trivial, but it can save you a few lines of typing.  You may want to force your call to only create a new session, or you may want to force getting an existing one.  In that case, simply change that call to (vla-get-object) or (vla-create-object) as needed.


Figure 4: Opening Excel with an Existing Document File  
;;; Note: <xfile> is the fully-qualified filename to be opened,
;;; <dmode> is either "SHOW" or "HIDE" depending upon whether
;;; you want the Excel session to be accessible to the user directly.
(defun vlxx-Open-Excel8-Exist (xfile dmode / appsession)
  (princ "\nOpening Excel Spreadsheet file...")
  (cond
    ( (setq appsession (vlax-Get-or-Create-Object "Excel.Application.8"))
  
      (vlax-invoke-Method
        (vlax-Get-Property appsession 'WorkBooks)
        'Open xfile
      )
      (if (= (strcase dmode) "SHOW")
        (vla-Put-Visible appsession 1)
        (vla-Put-Visible appsession 0)
      )
    )
  )
  appsession
)


Now that you've got your stuff to open a workbook and get at the active worksheet, you may want to get something out of it.  

Figure 5: Getting Data from a Single Cell in the Active WorkSheet  
;;; Get cell object relative to range using <relrow> and <relcol>

(defun vlxx-Excel-Get-Cell (rng relrow relcol)
  (vlax-Variant-Value
    (msxl-Get-Item (msxl-Get-Cells rng)
      (vlax-Make-Variant relrow)
      (vlax-Make-Variant relcol)
    )
  )
)

;;; Return value of contents in cell (row, col)

(defun vlxx-Excel-Get-CellValue (row col)
  (vlax-Variant-Value
    (msxl-Get-Value
      (vlxx-Excel-Get-Cell
        (msxl-Get-ActiveSheet xlapp)
        row col
      )
    )
  )
)


While getting values from a single cell can be enough at times, you will very soon find that you need to collect a range of cells at once.  Here are some functions for grabbing data from rows, columns or arrays of cells in a single call.   
Figure 6: Getting Data from a Range in the Active WorkSheet  
;;; Return a list of cell values for a given row

(defun vlxx-Excel-Get-RowValues
  (row startcol numcells / next out)
  (setq next startcol)
  (repeat numcells
    (setq out (if out
                (append out (list (vlxx-Excel-Get-CellValue row next)))
                (list (vlxx-Excel-Get-CellValue row next))
              )
         next (1+ next)
    ); setq
  ); repeat
  out
)

;;; Return a list of cell values for a given column

(defun vlxx-Excel-Get-ColumnValues
  (col startrow numcells / next out)
  (setq next startrow)
  (repeat numcells
    (setq out
              (if out
                (append out (list (vlxx-Excel-Get-CellValue next col)))
                (list (vlxx-Excel-Get-CellValue next col))
              )
         next (1+ next)
    )
  ); repeat
  out
)
;;; Return a list of lists where each sublist is one row of information
;;; in a top-to-bottom order in the spreadsheet range
;;;
;;; (vlxx-Excel-GetRangeValues-ByRows sRow sCol nRows nCols)

(defun vlxx-Excel-GetRangeValues-ByRows
  (startrow startcol numrows numcols / nextrow rowlst outlst)
  (setq nextrow startrow)
  (repeat numrows
    (setq rowlst  (vlxx-Excel-Get-RowValues nextrow startcol numcols)
          outlst  (if outlst (append outlst (list rowlst)) (list rowlst))
          nextrow (1+ nextrow)
    )
  )
  outlst
)

(defun vlxx-Excel-GetRangeValues-ByCols (startrow startcol numrows)
  ;; I'll let you fill in the code for this one... ha ha!
  ;; Tip: Look at vlxx-Excel-GetRangeValues-ByRows for help
)



Ahhh!  Now you've snatched goodies out of Excel, but it's time Grasshopper, to start giving back.  I'll skip dumping values into a single cell for now, you can figure that out by looking at the following code examples anyway.  Figure 7 shows how to put a list of values into either a row or a column of cells.  This should make it easy for you to dump reports out of your drawings and impress your boss with you amazing talents!
   
Figure 7: Putting Data into a Spreadsheet  
;;; Write each list member to a column <startcol> starting at <startrow>
;;; (vlxx-Excel-Put-ColumnList '("1" "2" "3") 1 2)
;;; Would put '1' '2' '3' into cells (1,2) (2,2) and (3,2) respectively

(defun vlxx-Excel-Put-ColumnList (lst startrow startcol)
  (foreach itm lst
    (msxl-put-value
      (vlxx-Excel-Get-Cell range startrow startcol)
      itm
    )
    (setq startrow (1+ startrow))
  ); repeat
)

;;; Write each list member to a row <startrow> starting at <startcol>
;;; (vlxx-Excel-Put-RowList '("1" "2" "3") 2 1)
;;; Would put '1' '2' '3' into cells (1,2) (1,3) and (1,4) respectively

(defun vlxx-Excel-Put-RowList (lst startrow startcol)
  (foreach itm lst
    (msxl-put-value
      (vlxx-Excel-Get-Cell range startrow startcol)
      itm
    )
    (setq startcol (1+ startcol))
  ); foreach
)  

Data-shmata.  Data alone will not impress anyone.  Not even your type-A boss.

You need to add some color, and maybe some formatting as well.  Figure 8 gives some examples of how to apply color to a cell or a row or column of cells at once.  Keep in mind that Excel's color pallet is not the same as AutoCAD's.  You'll need to dig into the online help in Excel to find out the color index you want to use.
   
Figure 8: Changing Cell Properties in Excel (Color)  
;;; Change fill color in one cell only
;;; (vlxx-Excel-Put-CellColor 1 1 14)
;;; Apply color #14 to cell at row=1 col=1

(defun vlxx-Excel-Put-CellColor (row col intCol / rng)
  (setq rng
    (vlxx-Excel-Get-Cell
      (msxl-Get-ActiveSheet xlapp)
      row col
    )
  )
  (msxl-Put-ColorIndex (msxl-Get-Interior rng) intCol)
)

;;; Change fill color in a row of cells
;;; (vlxx-Excel-Put-RowCellsColor 1 1 5 14)
;;; Start at row=1 col=1 repeat for 5 columnes using color #14

(defun vlxx-Excel-Put-RowCellsColor
  (startrow startcol cols intcol / next)
  (setq next startcol)
  (repeat cols
    (vlxx-Excel-Put-CellColor startrow next intcol)
    (setq next (1+ next))
  )
)

;;; Change fill color in a column of cells
;;; (vlxx-Excel-Put-ColumnCellsColor 1 1 5 14)
;;; Start at row=1 col=1 repeat for 5 rows using color #14

(defun vlxx-Excel-Put-ColumnCellsColor
  (startrow startcol rows intcol / next)
  (setq next startrow)
  (repeat rows
    (vlxx-Excel-Put-CellColor next startcol intcol)
    (setq next (1+ next))
  )
)  


Figure 9: Putting it All Together  

Let's combine some of the above pieces of code to show how they might be useful to you inside of AutoCAD.  This function will prompt you to select an Excel .XLS file to open and fetch a specified range of rows and columns.  It returns the data in a list form whereby each row is a list and the master list is simply the collection of row lists within it.  To help simplify that explanation: ( (rowlist) (rowlist) (rowlist) . . .)

(defun C:GETXLREGION
  ( / xlapp xlfile ready tlbfile ash range xlist)
  (cond
    ( (vlxx-Load-TypeLib-Excel8)
      (cond
        ( (setq xlfile
            (getfiled "Excel Spreadsheet File"
              (if G$XFILE G$XFILE "") "XLS" 8
          ))
          (setq G$XFILE xlfile)
          (cond
            ( (setq xlapp (vlxx-Open-Excel8-Exist xlfile "HIDE"))
              (setq ash   (msxl-Get-ActiveSheet xlapp))
              (setq range (msxl-Get-ActiveCell  xlapp))

              ;;; fetch data starting at row=2 column=1 and get
              ;;; the next 68 rows, 6 columns each

              (setq xlist (vlxx-Excel-GetRangeValues-ByRows 2 1 68 6))

              ;;; Print out each sublist to see what you got...
              (foreach mbr xlist (princ mbr) (terpri))
              (setq xlist nil)
              (vlxx-Excel-Quit xlapp)
            )
            ( T (princ "\nFailed to start application session.") )
          )
        )
      )
    )
    ( T (alert "Failed to initialize type library for Excel 97...") )
  )
  (princ)
)
--------------------------------------------------------------------------------

;;; Quit Excel and Close

(defun vlxx-Excel-Quit (appsession)
  (vlax-Invoke-Method appsession 'QUIT)
  (if (vlax-Property-Available-p appsession 'Visible)
    (princ "\nExcel Application still active!")
    (princ "\nExcel Application has been closed.")
  )
)

;;; Force any open session of Excel to close

(defun vlxx-Excel-Kill ()
  (while (vlax-Get-Object "Excel.Application.8")
    (vlxx-Excel-Quit (vlax-Get-Object "Excel.Application.8"))
  )
)

mmmm

程序中有几个msxl-*的函数找不到定义。无法正常测试