Automatic layout and renaming with excel file

[vijay.shankar3BXDM]

Hello Everyone,

I would like forums help. I want to do a task in AutoCAD to create multiple layouts with data in excel file. Please provide any lisp that do the task.

[hosneyalaa]

hi

try

(defun c:QQQQQTEXTFINDly (/ CELL EXCDATA FILENAME I ICOL IROW IROWS LW-LST MAKE_LW NUMBER NUMBERSTR NUMBERSTR1 OBJ RNG SSVP1 VP1 XLAPP XLBOOK XLCELL XLFRANGE XLRANGE XLRANGEC XLSHEET)
  (vl-load-com)
  
  
  (if (setq fileName (getfiled "Select Excel file to find cell address :" (getvar "dwgprefix") "xlsx;xls" 16))
    (progn
      
      
      
      (setq xlApp (vlax-create-object "Excel.Application"))
      (vlax-put-property xlApp "Visible" :vlax-true)
      (setq xlBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) "Open" fileName ) )
      (vlax-invoke-method xlBook "Activate")
      (setq xlSheet (vlax-get-property (vlax-get-property xlBook "WorkSheets") "Item" 1) )
      
      (setq xlRangeC (vlax-invoke-method xlSheet "Activate") )
      (setq xlRange (vlax-get-property xlSheet "Range" "c1:c10"))
      (vlax-invoke-method xlRange "Select")
      (setq iCol (vlax-get-property xlRange "Column"))
      (setq iRows (vlax-get-property(vlax-get-property xlRange "Rows") "Count" )iRow  (vlax-get-property xlRange "Row") )
      (setq rng (vlax-get-property xlApp 'Cells))
      
      (setq
	ExcData	 (vlax-safearray->list
		   (vlax-variant-value
		     (vlax-get-property xlRange 'Value)
		     )
		   )
	)
      
      (setq
	ExcData (mapcar
		  (function (lambda (x)  (mapcar 'vlax-variant-value x) ))
		  ExcData
		  )
	)
      
      
      ;(setq lw-lst nil)
      (foreach pt ExcData
	(setq lw-lst (append (list(car pt)) lw-lst))
	
	)
      
      (setq make_lw (vl-remove nil (reverse lw-lst)))
      
      
      
      ;;Group-1.1
      (setq i 1)


      
      (repeat (- (length make_lw) 1)


	
	(setq numberstr (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".1" ))
	(vl-cmdf "._layout" "c" "00" numberstr)   
	(vl-cmdf "._layout" "s" numberstr "._pspace")
	
	

	(setq ssvp1 (ssget "x" (list (cons 0 "insert") (cons 410 numberstr) )))
	(setq vp1 (ssname ssvp1 0))
	(setq obj (vlax-ename->vla-object (ssname ssvp1 0)))

	(setq xlCell (vlax-invoke-method
		       xlRange
		       "Find"
		       (vlax-make-variant number)
		       xlFRange
		       -4163
		       1
		       1
		       1
		       nil
		       nil
		       )
	      )
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (vlax-get-property xlCell "Column") )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "GROUPNAME") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 1 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "ADDRESS") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 2 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "ROOM") (vla-put-textstring x (vlax-variant-value  (vlax-get-property cell 'value2))))) (vlax-invoke obj 'getattributes))
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 3 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "LEVELS") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 4 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "TYPE") (vla-put-textstring x (vlax-variant-value  (vlax-get-property cell 'value2))))) (vlax-invoke obj 'getattributes))
	
	
	(setq numberstr1 (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".2" ))
	(vl-cmdf "._layout" "c" numberstr numberstr1)
	
	
	
	
	
	
	
	
	
	(setq i (1+ i))




	
	)
      
      )
    )
  (princ)
  )

[Sea-Haven]

This may be useful you do say an alert "select range in excel"

then select the layout range in excel,

you now press OK for the Alert,

the range is returned so the c1:c10 is not needed and you can have any range you like. 

I use alpha to number by Gile to work out the "C1" etc 

; get active range selcted
(defun getrangexl ( / lst UR CR RADD )

(setq lst '())
(setq UR (vlax-get-property  (vlax-get-property xlapp "ActiveSheet") "UsedRange"))
(setq CR (vlax-get-property UR "CurrentRegion"))
(setq RADD (vlax-get-property CR "Address"))
(setq cnt (vlax-get-property CR  "Count"))

(setq lst (csv->lst radd))

(setq st (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 0 lst) )))
(setq end (vl-string-subst "" "$" (vl-string-subst "" "$" (nth 1 lst) )))

(setq st  (columnrow st)))
(setq end  (columnrow end))

(princ st)
(princ "\n")
(princ end)

)

;-------------------------------------------------------------------------------
; Number2Alpha - Converts Number into Alpha string
; Function By: Gilles Chanteau from Marseille, France
; Arguments: 1
;   Num# = Number to convert
; Syntax example: (Number2Alpha 731) = "ABC"
;-------------------------------------------------------------------------------
(defun Number2Alpha (Num# / Val#)
  (if (< Num# 27)
    (chr (+ 64 Num#))
    (if (= 0 (setq Val# (rem Num# 26)))
      (strcat (Number2Alpha (1- (/ Num# 26))) "Z")
      (strcat (Number2Alpha (/ Num# 26)) (chr (+ 64 Val#)))
    );if
  );if
);defun Number2Alpha

[vijay.shankar3BXDM]

Hi i have inserted the lips it says function not described.

[vijay.shankar3BXDM]

Hello thanks for the code. Could you please make the code to rearrange the layout as per excel sheet. The sheets are ordered other way.

[hosneyalaa]

I did not understand you
Where is the problem?
attach a picture of her

[vijay.shankar3BXDM]

Hello i am asking about the layout names it has been created as Group 3.1- Group3.2, Group 2.1-Group 2.2 etcc.... like this but i need Group 1.1 as first layout and followed by Group 1.2, Group 2.1, Group 2.2, Group 3.1, Group 3.2.

[vijay.shankar3BXDM]

Hello i didn't understand the code? What it will do? Please provide the full code.

[hosneyalaa · Accepted solution]

HI

TRY

(defun c:QQQQQTEXTFINDly (/ CELL EXCDATA FILENAME I ICOL IROW IROWS LW-LST MAKE_LW NUMBER NUMBERSTR NUMBERSTR1 OBJ RNG SSVP1 VP1 XLAPP XLBOOK XLCELL XLFRANGE XLRANGE XLRANGEC XLSHEET)
  (vl-load-com)
  
  
  (if (setq fileName (getfiled "Select Excel file to find cell address :" (getvar "dwgprefix") "xlsx;xls" 16))
    (progn
      
      
      
      (setq xlApp (vlax-create-object "Excel.Application"))
      (vlax-put-property xlApp "Visible" :vlax-true)
      (setq xlBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) "Open" fileName ) )
      (vlax-invoke-method xlBook "Activate")
      (setq xlSheet (vlax-get-property (vlax-get-property xlBook "WorkSheets") "Item" 1) )
      
      (setq xlRangeC (vlax-invoke-method xlSheet "Activate") )
      (setq xlRange (vlax-get-property xlSheet "Range" "c1:c10"))
      (vlax-invoke-method xlRange "Select")
      (setq iCol (vlax-get-property xlRange "Column"))
      (setq iRows (vlax-get-property(vlax-get-property xlRange "Rows") "Count" )iRow  (vlax-get-property xlRange "Row") )
      (setq rng (vlax-get-property xlApp 'Cells))
      
      (setq
	ExcData	 (vlax-safearray->list
		   (vlax-variant-value
		     (vlax-get-property xlRange 'Value)
		     )
		   )
	)
      
      (setq
	ExcData (mapcar
		  (function (lambda (x)  (mapcar 'vlax-variant-value x) ))
		  ExcData
		  )
	)
      
      
      ;(setq lw-lst nil)
      (foreach pt ExcData
	(setq lw-lst (append (list(car pt)) lw-lst))
	
	)
      
   
      (setq make_lw (vl-remove nil lw-lst))
      
      
      
      ;;Group-1.1
      (setq i 0)


      
      (repeat (- (length make_lw) 1)


	
	(setq numberstr (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".1" ))
	(vl-cmdf "._layout" "c" "00" numberstr)   
	(vl-cmdf "._layout" "s" numberstr "._pspace")
	
	

	(setq ssvp1 (ssget "x" (list (cons 0 "insert") (cons 410 numberstr) )))
	(setq vp1 (ssname ssvp1 0))
	(setq obj (vlax-ename->vla-object (ssname ssvp1 0)))

	(setq xlCell (vlax-invoke-method
		       xlRange
		       "Find"
		       (vlax-make-variant number)
		       xlFRange
		       -4163
		       1
		       1
		       1
		       nil
		       nil
		       )
	      )
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (vlax-get-property xlCell "Column") )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "GROUPNAME") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 1 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "ADDRESS") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 2 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "ROOM") (vla-put-textstring x (vlax-variant-value  (vlax-get-property cell 'value2))))) (vlax-invoke obj 'getattributes))
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 3 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "LEVELS") (vla-put-textstring x (RTOS (vlax-variant-value  (vlax-get-property cell 'value2)) 2 2 )))) (vlax-invoke obj 'getattributes))
	
	
	
	
	
	(setq cell (vlax-variant-value (vlax-get-property rng 'Item (vlax-get-property xlCell "Row") (+ 4 (vlax-get-property xlCell "Column")) )     )  )
	
	
	(vl-some '(lambda (x) (if (= (vlax-get x 'tagstring) "TYPE") (vla-put-textstring x (vlax-variant-value  (vlax-get-property cell 'value2))))) (vlax-invoke obj 'getattributes))
	
	
	(setq numberstr1 (strcat "Group-"  (RTOS (setq number (nth i make_lw)) 2 0 )".2" ))
	(vl-cmdf "._layout" "c" numberstr numberstr1)
	
	
	
	
	
	
	
	
	
	(setq i (1+ i))




	
	)
      
      )
    )
  (princ)
  )

[Sea-Haven · Accepted solution]

Sorry the code was for Hosneysalaa as in the code the range is hard coded. The code allows you to select a range in excel and return that to lisp variables of (row column)(row colum)

"c1:c10"

[vijay.shankar3BXDM]

Hello, I need one more help, i need to change the code little for the group number it should have 001, 002, 003 as prefix. When try execute older code it didn't worked. Please let me  know where to change the code?

[vijay.shankar3BXDM]

Hello @hosneyalaa @Sea-Haven Thanks for the response.

I would like to do small change in the code that the attribute placed in the autocad must be of multiple lines and the text need to be break into new line if it has a space. Please see below image. I made the attributes into multiline.

Could you please help me to get the code.