lisp to look up in excel and insert text in cad

[shadi]

hello , hope u are doing great ...  i wanna lisp function to insert text in cad which will be read from excel sheet , let us have example ; i have written excel sheet with data of two columns , one with letters like A , b , c , etc and the other column with numbers corresponding to these letters , so i have raster background image in cad and it shown those letters which are already written in excel , now i wanna cad read the numbers in arranged sequence like A = 5 from excel and ask me to insert location for text with value 5 in cad and then ask for value of 8 for b , etc  ... hope that is clear , thanks in advance 

Edited September 4, 2020 by shadi

[BIGAL]

Did you google there is a massive amount of code out there to do what you want only thing is it will not be set up to do exactly A B C. Using something like Getexcel.lsp has all the functions in it to do what you want but again you have to write the ABC bit.

getexecel.lsp

[shadi]

thank u bigal for ur reply , i tried to use the lisp getexcel , but it doesnt give me what i want .. i need that will be in sequence , like i run the lisp and then ask me for the excel and may be range in excel it should read from "that is already in the attached lisp getexecel.lsp" and then ask me for location as i will go to A location on the raster background to put the first cell in second column even , i want label or command message line says about the  A = 5 until i place the text on cad then asks me for the next text location with message or label b=3 ... so that should simple and quick  

[shadi]

22 hours ago, BIGAL said:

Did you google there is a massive amount of code out there to do what you want only thing is it will not be set up to do exactly A B C. Using something like Getexcel.lsp has all the functions in it to do what you want but again you have to write the ABC bit.

getexecel.lsp 16.4 kB · 1 download

so i tried with that lisp and i did that attached lisp , it reading from excel from my desktop "attached too" and ask me to insert the first cell ... i am beginner in lisp so my info is less ... i want the lisp to read the cells in sequence B1, B2,B3,B4,... etc  while doing something like label or getkword about the value and another value corresponding from column A and place text in cad with the B column cell value in each time .. would u help me to do? 

lookupexcel.lsp trywithcad.xlsx

[BIGAL]

There is a get a range of cells function it may not be in getexcel will have a look for it or someone may post. so woul get A1-B3. Get and put to excel requires a bit of experience in lisp so be patient.

 

Look at this 

 

(setq addr (strcat (chr (+ 64 c1))

Look for the line you should be able to set your range in the following code as an example. need to convert to a list further down in code. Note its older code so if it has  delete it, used to show color in posts screws up code.

[rlx]

ripped the heart out of one of my routines I made a couple of weeks ago so not sure it's going to work. Did one test only , looked ok , and now its bedtime for me.

 

; rlx 6 sep 2020
(defun c:shadi ( / actApp actDoc OldErr allOpen xls-app errobj workbooks activesheet activeworkbook allwb xldata pt txt)
  (Shadi_Init)
  (if (setq xls-app (vlax-get-object "Excel.Application"))
    (progn
      (princ "\nExcel is running\n\n")
      ;;; list all open workbooks
      (setq errobj (vl-catch-all-apply (function (lambda () (setq workbooks (vlax-get-property xls-app "Workbooks"))))))
      (if (vl-catch-all-error-p errobj) (princ "\nUnable to get 'workbooks property from excel"))
      ;;; get active sheet
      (setq errobj (vl-catch-all-apply (function (lambda () (setq activesheet (vlax-get-property xls-app "activesheet"))))))
      (if (vl-catch-all-error-p errobj)(princ "\nUnable to get 'activesheet property from excel"))
      (if workbooks
        (vlax-for wb workbooks (vl-catch-all-apply (function (lambda ()(setq allwb (append allwb (list (vla-get-fullname wb)))))))))
      (princ "\nAll open workbooks :\n")
      (if (and allwb (setq wb (Shadi_cfl allwb)))
        (Shadi_OpenActiveWorkbook wb) (progn (princ "\nNo open workbooks found - switching to manual selection")(Shadi_OpenNewWorkbook)))
    );end progn (select data from active workbook)
    (progn (princ "\nExcel is not running . . . initializing\n\n") (Shadi_OpenNewWorkbook))
  )
  (if xls-app (Shadi_ReleaseExcel))
  ; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data)))))
  (if (vl-consp xldata)
    (foreach item xldata
      (setq pt (getpoint (strcat "\nSeclect point for item '" (car item) "' , value " (setq txt (itoa (fix (cadr item)))))))
      (command "text" pt 5 0 txt)
    )
    (princ "\nNothing found")
  )
  (Shadi_Exit)
  (princ)
)

(defun Shadi_Err ($s) (princ $s)(Shadi_Exit)(setq *error* OldErr)(princ))

(defun Shadi_Exit () (Shadi_ReleaseAll)(gc))

(defun Shadi_Init () (vl-load-com) (setq OldErr *error* *error* Shadi_Err)
  (setq actApp (vlax-get-acad-object) actDoc (vla-get-activedocument actApp))
    (vlax-for doc (vla-get-documents actApp)(setq allOpen (cons (cons (strcase (vla-get-fullname doc)) doc) allOpen))))

(defun Shadi_ReleaseAll ()
  (vl-catch-all-apply 'vlax-release-object (list actdoc))(vl-catch-all-apply 'vlax-release-object (list actapp)))

; choose from list
(defun Shadi_cfl ( lst / dcl-name fp dcl drv inp)
  (if (not (findfile (setq dcl-name (strcat (getvar "MYDOCUMENTSPREFIX") "\\Shadi_cfl.dcl"))))
    (progn (setq fp (open dcl-name "w")) (write-line (strcat "ask : dialog { label = \"Kies uit lijst\"; spacer_1;"
      " : list_box { key = \"lbox\"; width = 100;} spacer_1; ok_cancel;}") fp) (if fp (close fp))(gc)))
  (if (and lst (setq dcl (load_dialog dcl-name))(new_dialog "ask" dcl))
    (progn (vl-file-delete dcl-name) (start_list "lbox")(mapcar 'add_list lst)(end_list)
      (action_tile "lbox" "(setq inp (nth (atoi $value) lst))") (action_tile "ok" "(done_dialog 1)")
        (action_tile "cancel" "(done_dialog 0)") (setq drv (start_dialog))
          (if (and (= drv 1)(not inp)) (setq inp (nth 0 lst)))) (setq inp '()))
  inp
)

(defun Shadi_OpenActiveWorkbook ( fn / wb activeworkbook activesheet)
  (if (and xls-app fn (findfile fn) workbooks) (progn (setq activeworkbook (vlax-invoke-method workbooks 'open fn))
    (setq activesheet (vlax-get-property xls-app 'activesheet)) (setq xldata (Shadi_Xls2Lst activesheet)))))

(defun Shadi_OpenNewWorkbook ( / fn err path)
  (if (and Shadi-LastXlsFile (findfile Shadi-LastXlsFile))(setq path (vl-filename-directory Shadi-LastXlsFile))(setq path ""))
  (if (and (setq fn (getfiled "Select Excel File (*.xlsx is default, change type if needed)" path "xlsx;xls;xlsm" 16))
           (setq xls-app (vlax-get-or-create-object "excel.application")))
    (progn (setq Shadi-LastXlsFile fn err (vl-catch-all-apply (function (lambda ()(setq workbooks (vlax-get-property xls-app 'workbooks)
      activeworkbook (vlax-invoke-method workbooks 'open fn) activesheet (vlax-get-property xls-app 'activesheet))))))
      (if (vl-catch-all-error-p err) (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
	(progn (vlax-invoke-method activesheet "Activate")(graphscr)(setq xldata (Shadi_Xls2Lst activesheet))))))
  (vl-catch-all-apply (function (lambda ()(vlax-invoke-method activeworkbook 'close :vlax-false))))
  (vl-catch-all-apply (function (lambda ()(vlax-invoke-method xls-app 'quit))))(Shadi_CloseExcel)
)

(defun Shadi_CloseExcel () (if xls-app (progn (vl-catch-all-apply 'vlax-invoke-method (list activeworkbook "Close" :vlax-False))
  (vl-catch-all-apply 'vla-quit (list xls-app)) (mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x)))
    (list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))))

(defun Shadi_ReleaseExcel () (mapcar '(lambda(x) (if x (vl-catch-all-apply 'vlax-release-object (list x))))
  (list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))

(defun Shadi_Xls2Lst
  ( %s / excel-range excel-used-range excel-max-row excel-max-column excel-max-range excel-variant excel-value excel-to-list lst)
  (defun _cl (i / n) (if (< i 27)(chr (+ 64 i))(if (= 0 (setq n (rem i 26)))
     (strcat (_cl (1- (/ i 26))) "Z")(strcat (_cl (/ i 26))(chr (+ 64 n))))))
  (setq excel-used-range (vlax-get-property  %s 'UsedRange))
  (setq excel-max-row (vlax-get-property (vlax-get-property excel-used-range "Rows") "Count"))
  (setq excel-max-column (vlax-get-property (vlax-get-property excel-used-range "Columns") "Count"))
  (setq excel-max-range (strcat "A1:" (_cl excel-max-column) (itoa excel-max-row)))
  (setq excel-range (vlax-get-property  %s 'Range excel-max-range))
  (setq excel-variant (vlax-get-property excel-range 'Value))
  (setq excel-value (vlax-variant-value excel-variant))
  (setq excel-to-list (vlax-safearray->list excel-value))
  (setq lst (mapcar '(lambda (x) (subst "" nil (mapcar '(lambda (y) (vlax-variant-value y)) x ))) excel-to-list))
  (princ)
  lst
)

Edited September 6, 2020 by rlx
release_excel sub was missing

[shadi]

18 hours ago, rlx said:

ripped the heart out of one of my routines I made a couple of weeks ago so not sure it's going to work. Did one test only , looked ok , and now its bedtime for me.

 

; rlx 6 sep 2020
(defun c:shadi ( / actApp actDoc OldErr allOpen xls-app errobj workbooks activesheet activeworkbook allwb xldata pt txt)
  (Shadi_Init)
  (if (setq xls-app (vlax-get-object "Excel.Application"))
    (progn
      (princ "\nExcel is running\n\n")
      ;;; list all open workbooks
      (setq errobj (vl-catch-all-apply (function (lambda () (setq workbooks (vlax-get-property xls-app "Workbooks"))))))
      (if (vl-catch-all-error-p errobj) (princ "\nUnable to get 'workbooks property from excel"))
      ;;; get active sheet
      (setq errobj (vl-catch-all-apply (function (lambda () (setq activesheet (vlax-get-property xls-app "activesheet"))))))
      (if (vl-catch-all-error-p errobj)(princ "\nUnable to get 'activesheet property from excel"))
      (if workbooks
        (vlax-for wb workbooks (vl-catch-all-apply (function (lambda ()(setq allwb (append allwb (list (vla-get-fullname wb)))))))))
      (princ "\nAll open workbooks :\n")
      (if (and allwb (setq wb (Shadi_cfl allwb)))
        (Shadi_OpenActiveWorkbook wb) (progn (princ "\nNo open workbooks found - switching to manual selection")(Shadi_OpenNewWorkbook)))
    );end progn (select data from active workbook)
    (progn (princ "\nExcel is not running . . . initializing\n\n") (Shadi_OpenNewWorkbook))
  )
  (if xls-app (Shadi_ReleaseExcel))
  ; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data)))))
  (if (vl-consp xldata)
    (foreach item xldata
      (setq pt (getpoint (strcat "\nSeclect point for item '" (car item) "' , value " (setq txt (itoa (fix (cadr item)))))))
      (command "text" pt 5 0 txt)
    )
    (princ "\nNothing found")
  )
  (Shadi_Exit)
  (princ)
)

(defun Shadi_Err ($s) (princ $s)(Shadi_Exit)(setq *error* OldErr)(princ))

(defun Shadi_Exit () (Shadi_ReleaseAll)(gc))

(defun Shadi_Init () (vl-load-com) (setq OldErr *error* *error* Shadi_Err)
  (setq actApp (vlax-get-acad-object) actDoc (vla-get-activedocument actApp))
    (vlax-for doc (vla-get-documents actApp)(setq allOpen (cons (cons (strcase (vla-get-fullname doc)) doc) allOpen))))

(defun Shadi_ReleaseAll ()
  (vl-catch-all-apply 'vlax-release-object (list actdoc))(vl-catch-all-apply 'vlax-release-object (list actapp)))

; choose from list
(defun Shadi_cfl ( lst / dcl-name fp dcl drv inp)
  (if (not (findfile (setq dcl-name (strcat (getvar "MYDOCUMENTSPREFIX") "\\Shadi_cfl.dcl"))))
    (progn (setq fp (open dcl-name "w")) (write-line (strcat "ask : dialog { label = \"Kies uit lijst\"; spacer_1;"
      " : list_box { key = \"lbox\"; width = 100;} spacer_1; ok_cancel;}") fp) (if fp (close fp))(gc)))
  (if (and lst (setq dcl (load_dialog dcl-name))(new_dialog "ask" dcl))
    (progn (vl-file-delete dcl-name) (start_list "lbox")(mapcar 'add_list lst)(end_list)
      (action_tile "lbox" "(setq inp (nth (atoi $value) lst))") (action_tile "ok" "(done_dialog 1)")
        (action_tile "cancel" "(done_dialog 0)") (setq drv (start_dialog))
          (if (and (= drv 1)(not inp)) (setq inp (nth 0 lst)))) (setq inp '()))
  inp
)

(defun Shadi_OpenActiveWorkbook ( fn / wb activeworkbook activesheet)
  (if (and xls-app fn (findfile fn) workbooks) (progn (setq activeworkbook (vlax-invoke-method workbooks 'open fn))
    (setq activesheet (vlax-get-property xls-app 'activesheet)) (setq xldata (Shadi_Xls2Lst activesheet)))))

(defun Shadi_OpenNewWorkbook ( / fn err path)
  (if (and Shadi-LastXlsFile (findfile Shadi-LastXlsFile))(setq path (vl-filename-directory Shadi-LastXlsFile))(setq path ""))
  (if (and (setq fn (getfiled "Select Excel File (*.xlsx is default, change type if needed)" path "xlsx;xls;xlsm" 16))
           (setq xls-app (vlax-get-or-create-object "excel.application")))
    (progn (setq Shadi-LastXlsFile fn err (vl-catch-all-apply (function (lambda ()(setq workbooks (vlax-get-property xls-app 'workbooks)
      activeworkbook (vlax-invoke-method workbooks 'open fn) activesheet (vlax-get-property xls-app 'activesheet))))))
      (if (vl-catch-all-error-p err) (prompt (strcat "\nError: " (vl-catch-all-error-message err)))
	(progn (vlax-invoke-method activesheet "Activate")(graphscr)(setq xldata (Shadi_Xls2Lst activesheet))))))
  (vl-catch-all-apply (function (lambda ()(vlax-invoke-method activeworkbook 'close :vlax-false))))
  (vl-catch-all-apply (function (lambda ()(vlax-invoke-method xls-app 'quit))))(Shadi_CloseExcel)
)

(defun Shadi_CloseExcel () (if xls-app (progn (vl-catch-all-apply 'vlax-invoke-method (list activeworkbook "Close" :vlax-False))
  (vl-catch-all-apply 'vla-quit (list xls-app)) (mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x)))
    (list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))))

(defun Shadi_ReleaseExcel () (mapcar '(lambda(x) (if x (vl-catch-all-apply 'vlax-release-object (list x))))
  (list activesheet activeworkbook xls-app)) (setq xls-app nil activeworkbook nil activesheet nil) (gc))

(defun Shadi_Xls2Lst
  ( %s / excel-range excel-used-range excel-max-row excel-max-column excel-max-range excel-variant excel-value excel-to-list lst)
  (defun _cl (i / n) (if (< i 27)(chr (+ 64 i))(if (= 0 (setq n (rem i 26)))
     (strcat (_cl (1- (/ i 26))) "Z")(strcat (_cl (/ i 26))(chr (+ 64 n))))))
  (setq excel-used-range (vlax-get-property  %s 'UsedRange))
  (setq excel-max-row (vlax-get-property (vlax-get-property excel-used-range "Rows") "Count"))
  (setq excel-max-column (vlax-get-property (vlax-get-property excel-used-range "Columns") "Count"))
  (setq excel-max-range (strcat "A1:" (_cl excel-max-column) (itoa excel-max-row)))
  (setq excel-range (vlax-get-property  %s 'Range excel-max-range))
  (setq excel-variant (vlax-get-property excel-range 'Value))
  (setq excel-value (vlax-variant-value excel-variant))
  (setq excel-to-list (vlax-safearray->list excel-value))
  (setq lst (mapcar '(lambda (x) (subst "" nil (mapcar '(lambda (y) (vlax-variant-value y)) x ))) excel-to-list))
  (princ)
  lst
)

wooooooooow, suppper .... thank u very very much , that is exactly what i meant  

..but please , may u help me more for add an option to skip making text of any of these cells and continue to the other one if i didnt want to write its value 

[shadi]

19 hours ago, BIGAL said:

There is a get a range of cells function it may not be in getexcel will have a look for it or someone may post. so woul get A1-B3. Get and put to excel requires a bit of experience in lisp so be patient.

 

Look at this 

 

(setq addr (strcat (chr (+ 64 c1))

Look for the line you should be able to set your range in the following code as an example. need to convert to a list further down in code. Note its older code so if it has  delete it, used to show color in posts screws up code.

thank u bigal for ur help .. i would like to learn more about lisp , may be later when i will have time 

[rlx]

(if xls-app (Shadi_ReleaseExcel))
; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data)))))
(if (vl-consp xldata)
 (foreach item xldata (initget 0)(if (setq pt (getpoint (strcat "\nSelect point (or space to skip) for item '"
  (car item) "' , value " (setq txt (itoa (fix (cadr item))))))) (command "text" pt 5 0 txt))) (princ "\nNothing found"))
(Shadi_Exit)
(princ)

Edited September 6, 2020 by rlx

[shadi]

13 minutes ago, rlx said:

(if xls-app (Shadi_ReleaseExcel))
; if there is a header use : (if (and (vl-consp xldata)(vl-consp (setq xl-data (cdr (xl-data)))))
(if (vl-consp xldata)
 (foreach item xldata (initget 0)(if (setq pt (getpoint (strcat "\nSelect point (or space to skip) for item '"
  (car item) "' , value " (setq txt (itoa (fix (cadr item))))))) (command "text" pt 5 0 txt))) (princ "\nNothing found"))
(Shadi_Exit)
(princ)

sorry rlx for my less info about lisp , is that should be added in the lisp u sent me before , if  yes so where should be added in the lisp ?!

[rlx]

I attached the complete lisp. I know it has been said many times before , this site's 1st goal is for learning (no. 2 is free software haha)

 

Shadi.lsp

[shadi]

2 hours ago, rlx said:

I attached the complete lisp. I know it has been said many times before , this site's 1st goal is for learning (no. 2 is free software haha)

 

Shadi.lsp 5.95 kB · 0 downloads

firstly i would thank u so much for ur help , i appreciate that ...and if u do it that quickly so u are really expert  in lisp programming....secondly, i am agree with u about learning lisp , i really want to do and i already had try in first lisp , even that was so small  ..i am so busy these days with much work so i would try to learn more about lisp later ..btw, i like programming in general 

[rlx]

you're welcome Shadi. To be honest, I'm anything but quick. I just happen to have written something not so long ago that also was usefull for your problem but I also sometimes have writersblock and for weeks I struggle to get anything working. So I guess this was your lucky day you lucky ...

[shadi]

19 hours ago, rlx said:

you're welcome Shadi. To be honest, I'm anything but quick. I just happen to have written something not so long ago that also was usefull for your problem but I also sometimes have writersblock and for weeks I struggle to get anything working. So I guess this was your lucky day you lucky ...

yes , it was my lucky day .. thank u once again for ur time and for ur care 

[BIGAL]

Just a out there comment you could write a excel macro/VBA that controls Autocad so its all done from excel. Thats one of the nice things that Excel <-> Autocad works both ways.

 

RetVal = object.GetPoint([Point [, Prompt]])

Edited September 7, 2020 by BIGAL

[shadi]

On 9/8/2020 at 12:54 AM, BIGAL said:

Just a out there comment you could write a excel macro/VBA that controls Autocad so its all done from excel. Thats one of the nice things that Excel <-> Autocad works both ways.

 

RetVal = object.GetPoint([Point [, Prompt]])

sorry , i didnt get what u mean .. may explain more please 

[BIGAL]

Here is a excel macro example runs inside Excel will draw 3 objects in Autocad. Rename file to txt done to post, open Macro in excel New and past can run the 3 options.control Autocad from excel.lsp

 

So you can be in excel and get points from autocad.

Edited September 9, 2020 by BIGAL

[shadi]

19 hours ago, BIGAL said:

Here is a excel macro example runs inside Excel will draw 3 objects in Autocad. Rename file to txt done to post, open Macro in excel New and past can run the 3 options.control Autocad from excel.lsp

 

So you can be in excel and get points from autocad.

i tried it , it drew some lines in autocad as i run the macro a lot , but it didnt drew circles , may that is different cause u use civil and i using autocad !!

[BIGAL]

Circle should have worked will check. Its about an example of running inside excel  not something I do normally, the getpoint is if you wanted to do a add text as you need a point, the text would be a cell.

[shadi]

10 hours ago, BIGAL said:

Circle should have worked will check. Its about an example of running inside excel  not something I do normally, the getpoint is if you wanted to do a add text as you need a point, the text would be a cell.

mmmm .. u made me thinking in another lisp programming , still thinking in the idea of it