getcells hoe de lisp op te starten

[silverster]

Hallo,

ik was op zoek naar een Lisp routine en kwam getcells tegen, het doet iets met excel en autocad, wat het precies doet weet ik niet maar mijn probleem is dat ik hem niet opgestart krijgt, ik heb hem ingeladen zoals het hoort, alleen ik weet het commando niet.

hieronder de Lisp routine:

;;;CADALYST 06/05 Tip2038:    GETCELLS.lsp      Get Excel Data    (c) Jeff Sanders


;;;--- GETCELLS.lsp  -  Program to get values of cells from EXCEL
;;;                     and return them in a list.
;;;
;;;
;;;--- Requires AutoCAD 2000+ and Excel version 8+
;;;
;;;
;;;--- Created on 4/18/05
;;;
;;;
;;;--- Version 1.0
;;;
;;;
;;;--- Copyright 2005 by JefferyPSanders.com
;;;    All rights reserved.
;;;
;;;
;;;
;;;--- NOTES:
;;;
;;;    1.  The getcells function has three parameters:  listOfCells, fileName, and sheetName
;;;   
;;;          listOfCells is a list containg cell values.  Example: ("A3" "B4" "B5" "AD14")
;;;   
;;;          fileName is the path and name of the XL file.  Example "C:\ACAD\MyXLFile.XLS"
;;;   
;;;          sheetName is the name of the XL sheet to get the data from.  Example "SHEET1"
;;;   
;;;    2.  The program checks for valid cell names.  It looks for a alpha prefix and a numeric
;;;        suffix.  It will skip any cells that do not follow these rules.
;;;   
;;;    3.  Example of programs use:
;;;   
;;;          (setq cells(list "A1" "A2" A3" "B1" "B2" B3"))
;;;          (setq xlFile "C:\acad\lsp\myfile.xls")
;;;          (setq sht "SHEET2")
;;;          (setq cellValues(getcells cells xlFile sht))
;;;   
;;;    4.  The program returns a list in this form:
;;;   
;;;         (
;;;           ("A1" "123")
;;;           ("A2" "34")
;;;           ("A3" "This is the value of cell a3")
;;;           ("B1" "90")
;;;           ("B2" "456")
;;;           ("B3" "456.78")
;;;         )
;;;
;;;         Notice all values returned are strings.  Use the distof function to convert to real numbers
;;;         or use the atoi function to convert to integers.


;;;--- Load visual lisp entensions
(vl-load-com)



;;;--- Main function

(defun getCells(listOfCells fileName sheetName / newCellList cellList myApp sysDrive myWBooks myWBook sht
                                                 mySheets sheetList cnt shtName mySht mySheet myAddress myCells)

  ;;;--- Check for valid cells and split them into separate list < (col row) >

  ;;;--- Create an empty list to hold the cell data
  (setq newCellList(list))

  ;;;--- Cycle through each cell location
  (foreach a listOfCells

    ;;;--- Set up a counter
    (setq cnt 1)

    ;;;--- Get the first character of the cell location
    (setq chk(substr a cnt 1))

    ;;;--- While the character is a letter in the alphabet
    (while(and (> (ascii chk) 64)(< (ascii chk) 123))

      ;;;--- Increment the counter to get the next character
      (setq cnt(+ cnt 1))

      ;;;--- Get the next character
      (setq chk(substr a cnt 1))
    )

    ;;;--- If the cnt is greater than the length of the cell's address then
    ;;;    there must not be a number in the address.  Make sure the address
    ;;;    had a number in it...
    (if(<= cnt (strlen a))
      (progn

        ;;;--- Save the col which should be a letter
        (setq col(substr a 1 (- cnt 1)))

        ;;;--- Save the row which should be a number represented as a string
        (setq row(substr a cnt))

        ;;;--- Convert the string to a number and make sure it is valid...
        (if(> (atoi row) 0)

          ;;;--- If it is valid then add the col and row as a list to the newcell list
          (setq newCellList(append newCellList (list (list col row))))

          ;;;--- Else alert the user that the cell's address is bad
          (alert (strcat "Invalid cell number - " a " will be skipped."))
        )
      )

      ;;;--- Else there must not be a number in the address...alert the user
      (alert (strcat "Invalid cell number - " a " will be skipped."))
    )
  ) 


  ;;;--- Make sure a valid cell list was created
  (if newCellList
    (progn
                 
      ;;;--- Make sure the XL file exist
      (if(findfile fileName)
        (progn
                 
          ;;;--- Get the system drive <We will need this to locate the EXCEL OBJECT LIBRARY>
          (setq sysDrive (getenv "systemdrive"))
                 
          ;;;--- If the excel object library is not found...load it
          (if (null Library)
            (progn
                 
              ;;;--- Find out which version we should use by looking in the default install locations
              ;;;    for EXCEL's Object Library.  If it is not found you may need to add the path to
              ;;;    your location for the excel object library.
              (setq Library
                (cond
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel8.olb")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel9.olb")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel10.olb")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office\\Excel.exe")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office10\\Excel.exe")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office11\\Excel.exe")))
                  ((findfile (strcat sysDrive "\\Program Files\\Microsoft Office\\Office11\\XL5EN32.OLB")))
                )
              )
           
         
                   
              ;;;--- If the library was found...
              (if Library
                (progn

                  ;;;--- Import the library...
                  (vlax-import-type-library
                    :tlb-filename Library
                    :methods-prefix "JXCL-"
                    :properties-prefix "JXCL-"
                    :constants-prefix "JXCL-"
                  )
                )

                ;;;--- Else alert the user of failure...
                (alert "Excel Object Library was not found!")
              )
            )
          )

          ;;;--- If an excel application is not loaded, proceed...
          (if (null myApp)
            (progn
                   
              ;;;--- If Excel executes correctly...
              (if(setq myapp(vlax-get-or-create-object "Excel.Application"))
                (progn
                             
                  ;;;--- Open the workbook
                  (vlax-invoke-method (vlax-get-property myapp 'WorkBooks) 'Open fileName)
                         
                  ;;;--- Set it to invisible mode
                  (vla-put-visible myApp 0)
                         
                  ;;;--- Get the workbooks object
                  (setq myWBooks(vlax-get myApp "Workbooks"))
                         
                  ;;;--- Open the excel file
                  (setq myWBook(vla-open myWBooks fileName))
                         
                  ;;;;--- Get the sheets object
                  (setq mySheets(vlax-get myWBook "Sheets"))

                  ;;;--- Get a list of the sheet names
                  (setq shtCnt(vla-get-count mySheets))
                  (setq sheetList(list))
                  (setq cnt 1)
                  (while(<= cnt shtCnt)
                    (setq sht(JXCL-get-item mySheets cnt))
                    (setq shtName(vla-get-name sht))
                    (setq sheetList(append sheetList(list (strcase shtName))))
                    (setq cnt(+ cnt 1))
                  )
                           
                  ;;;--- Make sure the sheet name exist...
                  (if(member (strcase sheetName) sheetList)
                    (progn
                     
                      ;;;--- Get the worksheet...
                      (setq mySht(vlax-get-property mySheets 'Item sheetName))
                         
                      ;;;--- Make the selected worksheet active
                      (vlax-invoke-method mysht "Activate")
                     
                      ;;;--- Build an empty list to hold the values of the cells
                      (setq cellList(list))
                         
                      ;;;--- Cycle through the list of cells
                      (foreach a newCellList

                        ;;;--- Get the col and row
                        (setq col (car a))
                        (setq row (cadr a))
                           
                        ;;;--- Add the address and value to the cell list
                        (setq cellList
                          (append cellList
                 
                            ;;;--- Build a list containing the cell's col:row and value
                            (list
                              (list

                                ;;;--- First add the col and row as a string
                                (strcat col row)
                               
                                ;;;--- Next, Get the value of the cell
                                (vlax-variant-value
                                  (JXCL-get-value
                                    (vlax-variant-value
                                      (JXCL-get-item
                                        (JXCL-get-cells (JXCL-get-ActiveSheet myApp))
                                        (vlax-make-variant row)
                                        (vlax-make-variant col)
                                      )
                                    )   
                                  )
                                )
                              )
                            )
                          )       
                        )
                      )
                    )

                    ;;;--- Else the sheet name was not in the workbook...
                    (alert (strcat "The sheet - " sheetName " could not be found!"))
                  )
                 
                  ;;;--- Shut Excel down
                  (cond
                    (
                      (not(vlax-object-released-p myApp))
                      (vlax-invoke-method myApp 'QUIT)
                      (vlax-release-object myApp)
                    )
                  )
                )
                (alert "Could not start an EXCEL application! Aborting!")
              )                         
            )
            (alert "An Excel application must be open.  Close it and try again!")
          )
        )
        (alert "Could not find the XL file name.")
      )
    )
  )
  ;;;--- Finally, return the list containing the row,col and values of the cells
  cellList 
) 

[Huib]

(defun getCells(listOfCells fileName sheetName / newCellList cellList myApp sysDrive myWBooks myWBook sht
                                                 mySheets sheetList cnt shtName mySht mySheet myAddress myCells)

Opstarten dus met (getcells Filename Sheetname)
Geen "C:" in de definitie, dus opstarten als listroutine, incl de (       ).
Filename en Sheetname zijn kennelijk verplichte parameters.

[silverster]

okee bedankt ik zal morgen een even kijken opdat het werkt

[silverster]

het opstarten lukt nu inderdaad wel, alleen kom ik er nog steeds niet helemaal uit wat er nu de bedoeling is van de lisp iemand enig idee?

[HofCAD]

het opstarten lukt nu inderdaad wel, alleen kom ik er nog steeds niet helemaal uit wat er nu de bedoeling is van de lisp iemand enig idee?

Beste Silverster,

Stel je hebt een Excel bestand met bijvoorbeeld daarin de
geometrie waarden van profielen, dan kun je met 'GETCELL'
de geometrie van een bepaald profiel uitlezen en dat profiel
met AutoLisp laten tekenen in AutoCAD.
Je zou bijvoorbeeld ook de attribuutwaarden van een block
via 'GETCELL' kunnen veranderen.

Met vriendelijke groet,
HofCAD CSI

PS In http://www.cadsite.be/smf/index.php/topic,399.0.html
zie je het 'Geheim van de smid'.

[HofCAD]

Beste Silvester,

Op
http://www.theswamp.org/index.php?PHPSESSID=97u3ci1och9d4qbp7fvk6cmrc7&topic=18361.0
kan je het programma GetExcel.lsp van Terry Miller downloaden.
In dat programma staan een aantal hulpfuncties.
Na het laden kun je bijvoorbeeld het programma The_Put_Part en
het programma The_Get_Part  gebruiken.
Of bijv. het programma cte of cte2 gebruiken.
Het Excel bestand C:\Temp\Test with Autolisp.xls moet daarbij
al reeds bestaan.

Code Selecteer Uitklappen


(defun c:cte2 (/ excelfile$ sheetname$)
(defun putdata(letter Index# n All-Data@)
(repeat n
(setq Cell-ID$ (strcat letter (itoa Index#)))
(PutCell Cell-ID$ (nth (1- Index#) All-Data@))
(setq Index# (1+ Index#))
);repeat
)
(setq ExcelFile$ "C:\\Temp\\Test with Autolisp.xls")
(setq SheetName$ "Sheet1")
(OpenExcel ExcelFile$ SheetName$ nil)
;(OpenExcel ExcelFile$ SheetName$ T) met T zichtbaar
(putdata "A" 1 6 '("No." "1" "2" "3" "4" "5"))
(putdata "B" 1 6 '("NAAM" "Bout" "Moer" "Ring" "Spijker" "Tandwiel"))
(putdata "C" 1 6 '("GROOTTE" "M10" "M5" "M12" "5 cm" "Klein"))
;of met PutCell
;(PutCell "A1" '("No." "NAAM" "GROOTTE"))
;(PutCell "A2" '(1 "Bout" "M10"))
;(PutCell "A3" '(2 "Moer" "M5"))
;(PutCell "A4" '(3 "Ring" "M12"))
;(PutCell "A5" '(4 "Spijker" "5 cm"))
;(PutCell "A6" '(5 "Tandwiel" "Klein"))
(CloseExcel ExcelFile$)
(princ)
);defun


Met vriendelijke groet,
HofCAD CSI

PS GetExcel.lsp staat ook op http://web2.airmail.net/terrycad/

Zie ook voor hulpfunkties: http://www.cadsite.be/smf/index.php/topic,194.0.html