Jump to content
myoujou cain

lisp does not add "," to list until it is reloaded

Recommended Posts

myoujou cain

I have a lisp that reads a bunch of cells from excel, adds a "," to the end of each item, then places the item in a list. when I first start autocad it creats the list but does not add the ",". I reload the lisp, then it adds the commas. and I cant figure out why. the lisp code is below:

 

;;                     GET CELLS FROM EXCEL            ;;
  (defun getCells(fileName sheetName cellName / myXL myBook mySheet myRange cellValue)
    (setq myXL(vlax-get-or-create-object "Excel.Application"))
;;;    (vla-put-visible myXL :vlax-false)
    (vlax-put-property myXL 'DisplayAlerts :vlax-false)
    (setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") fileName)))
    (setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" sheetName)))
    (vlax-invoke-method mySheet "Activate")
    (setq myRange (vlax-get-property (vlax-get-property mySheet 'Cells) "Range" cellName))
    (setq cellValue(vlax-variant-value (vlax-get-property myRange 'Value2)))
;;;    (vl-catch-all-apply 'vlax-invoke-method (list myBook "Close"))
;;;    (vl-catch-all-apply 'vlax-invoke-method (list myXL "Quit"))
    (if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
    (if (not (vlax-object-released-p mySheet))(progn(vlax-release-object mySheet)(setq mySheet nil)))
    (if (not (vlax-object-released-p myBook))(progn(vlax-release-object myBook)(setq myBook nil)))
    (if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))   
    (if(= 'safearray (type cellValue))
      (progn
        (setq tempCellValue(vlax-safearray->list cellValue))
        (setq cellValue(list))
        (if(= (length tempCellValue) 1)
          (progn
            (foreach a tempCellValue
              (if(= (type a) 'LIST)
                (progn
                  (foreach b a
                    (if(= (type b) 'LIST)
                      (setq cellValue(append cellValue (list (vlax-variant-value (car b)))))
                      (setq cellValue(append cellValue (list (vlax-variant-value b))))
                    )
                  )
                )
                (setq cellValue(append cellValue (list (vlax-variant-value a))))
              )
            )
          )
          (progn
            (foreach a tempCellValue
              (setq tmpList(list))
              (foreach b a
  (SETQ VALUE (TYPE(VLAX-VARIANT-VALUE B)))
                (setq tmp (STRCAT (IF(= (TYPE (vlax-variant-value b)) 'STR ) (vlax-variant-value b) (VL-STRING-RIGHT-TRIM "." (VL-STRING-RIGHT-TRIM "0" (RTOS (vlax-variant-value b)))))","))
                (setq tmpList(append tmpList (LIST tmp)))
              )
              (setq cellValue(cons tmpList cellValue))
            )
          )
        )
      )
     )
    cellValue
  )
 

Share this post


Link to post
Share on other sites
rlx

maybe a little rusty (too much work... :sweat:  )

 


; $fn = filename (string) [*.xls/xlsx] , $sht = name of sheet (string) , $rng = range (string) [i.e. "A1" or "A1:A9"]
(defun getCells ($fn $sht $rng / myXL myBook mySheet myRange cellValues get_cell_values Cell->Str lst->csv release_me)
  (vl-load-com)
  ; not really necessary to put it in separate defun just old (bad?) habbit
  (defun get_cell_values ( %range %XlsApp / ExcelRange range ExcelVariant ExcelValue etype)
    (if %XlsApp
      (progn
    ; change single cell range to extended range : "A1" -> "A1:A1"
    (if (not (wcmatch %range "*:*")) (setq range (strcat %range ":" %range))(setq range %range))
    (setq ExcelRange (vlax-get-property %XlsApp "Range" range)
          ExcelVariant (vl-catch-all-apply (function (lambda ()(vlax-get-property ExcelRange 'Value))))
          ExcelValue (vl-catch-all-apply (function (lambda ()(vlax-variant-value ExcelVariant)))))
    (if ExcelValue
      (setq etype (type ExcelValue)
        ExcelValue
         (cond
           ; format a cell depending on value type (else just use vl-princ-to-string)
           ; simple integer to string
           ((= etype 'INT) (itoa ExcelValue))
           ; real number to string , decimal / precission
           ((= etype 'REAL) (rtos ExcelValue 2 0))
           ; strip spaces
           ((= etype 'STR) (vl-string-trim " " ExcelValue))
           ; make string for every item in array
           ((= etype 'safearray)
            (mapcar
              '(lambda (s / v) (if (eq (setq v (vl-princ-to-string (vlax-variant-value s))) "nil") "Empty" v))
               (car (vlax-safearray->list excelvalue))))
           ; thou shall not pass (without a value)
           (t "Empty")
         )
      )
      ; no value -> return empty string
      (setq ExcelValue "I dunno")
    )
      )
    )
    ;'black hole' (nil) detector ... just making sure I always get a value , even if it's "Empty"
    (if (listp ExcelValue) (setq ExcelValue (subst "Empty" nil ExcelValue)))
    ExcelValue
  )

  ; %l = list of strings , (lst->csv '("a" "b" "c")) -> "a,b,c"
  (defun lst->csv (%l)(apply 'strcat (cdr (apply 'append (mapcar (function (lambda (x) (list "," x))) %l)))))

  ; clean desk policy
  (defun release_me (lst)
    (mapcar '(lambda(x)(if (and (= 'vla-object (type x))(not (vlax-object-released-p x)))(vlax-release-object x))(set (quote x) nil)) lst))

  (cond
    ((or (null $fn) (not (eq (type $fn) 'STR))) (alert "Invalid filename for excel file"))
    ((not (findfile $fn)) (alert (strcat "Unable to find :\n" $fn)))
    ((not (setq myXL (vlax-get-or-create-object "Excel.Application")))
     (alert "Unable to initialize Excel"))
    ((vl-catch-all-error-p (setq myBook (vl-catch-all-apply 'vla-open (list (vlax-get-property myXL "WorkBooks") $fn))))
     (alert "Unable to retrieve workbooks collection"))
    ((vl-catch-all-error-p (setq mySheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property myBook "Sheets") "Item" $sht))))
     (if (= (type $sht) 'STR) (alert (strcat "Unable to find sheet : " $sht)) (alert "Sheet not found")))
    (t
     (vlax-invoke-method mySheet "Activate")
     ; (setq myRange (vlax-get-property (vlax-get-property mySheet 'Cells) "Range" $rng))
     (setq cellValues (get_cell_values $rng myXL))
     (release_me (list myRange mySheet myBook myXL))
     (if (vl-consp cellValues) (lst->csv cellValues) (princ "\nNo data"))
    )
  )
)

(defun c:t1 ( / ) (setq result (getcells "d:\\Temp\\Lisp\\getcells-test.xlsx" "DrukMetingen" "A1:Q1")))
(defun c:t2 ( / ) (setq result (getcells "d:\\Temp\\Lisp\\getcells-test.xlsx" "DrukMetingen" "A2:Q2")))

; (c:t1)
; (c:t2)

 

Share this post


Link to post
Share on other sites
myoujou cain

So is this some custom code that you made to do the same thing?

Share this post


Link to post
Share on other sites
rlx
4 minutes ago, myoujou cain said:

So is this some custom code that you made to do the same thing?

 

I have a few routines that do something with excel , not too many, and I always try to make parts generic so I can recycle them more easily. So sometimes a code can be made shorter or simpler but I have not always the time to write a new code. In this case I also did some cut copy & paste and tried to leave parts of your code intact. I also tried to built in as much error checking as I could so you can get a relevant error message when debugging. But there are ton's of lisp routines out there that can work with excel. Many if not most far more better than mine.

Share this post


Link to post
Share on other sites
myoujou cain

I know, what I have is kind of a Frankenstein I made out of a few examples. I will give your code a try. what bugs me the most is I don't understand why or how the code can do one thing when auto loaded and something different when manually loaded. do you have any insight on what could cause that?

 

thanks,

Share this post


Link to post
Share on other sites
rlx

I'm not sure but believe it error'd on  (vlax-put-property myXL 'DisplayAlerts :vlax-false).

With VL-commands sometimes the order of your commands must be done in a specific order and also , not in your case , sometimes it's better to break up a command sequence and first save the result in a variable before proceeding. Also useful during debugging.

 

You can for example get the active document in one command call like

 (setq actDoc (vla-get-activedocument (vlax-get-acad-object))) 

 

usually this is no problem but some commands can give problems when you nest them too much.

When writing your app , use lots of steps and when it works you can try to shorten your code.

Share this post


Link to post
Share on other sites
myoujou cain

I'm not real good with vla commands yet, is there a way to improve this line (break it up or change its order?):

 

(setq tmp (STRCAT (IF(= (TYPE (vlax-variant-value b)) 'STR ) (vlax-variant-value b) (VL-STRING-RIGHT-TRIM "." (VL-STRING-RIGHT-TRIM "0" (RTOS (vlax-variant-value b)))))","))

Share this post


Link to post
Share on other sites
myoujou cain

your code is interesting because it seems to not actualy open the excel document. but at the very end I get a "bad argument type: consp" error. and it only returns the first row in a weird format princ returns: a,b,c,d"a,b,c,d"

 

it should be:

a,b,c,d

f,g,h,i

 

etc.

Share this post


Link to post
Share on other sites
rlx

Have posted while back this routine , RLXls.lsp

Its been a while back but if I remember correctly this one can select a range and paste it as a table. But of course you can bypass the table making part and use the list in another way.

 

when you have this error you talked about , is excel already running at that time or maybe you have another excel file loaded at the time?

 

when you retrieve the value maybe first put it in a variable so you can set a breakpoint there and inspect what is happening (setq myVal ( vlax-variant-value b)) and then check its type and do your stuff with it.

 

Well, workday is done for me , time to go home. Catch you later aligator

 

Share this post


Link to post
Share on other sites
myoujou cain

thank you for all your help, I really appreciate it :)

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...