Trying to pull dimensions from excel to drive block parameter dimensions.

[jboyceQPURR]

So our foreman order panels using an excel file, they put the height, width, and label of the panel in excel. There could be hundreds of them.

Then we manually take those dimensions and input them into the template dynamic block, then cut the panel using that layout.

I'd like to automate the process, and use the excel file to simply import it somehow, and have it create the correct blocks to use for cutting. I have a block template, with height/width parameters. I've attached my block file and a sample excel file. 

I did find a program addin called XLSparam, but it doesn't include the labels and I really need those included.

[Anonymous]

Nothing tested and this is not foolproof. But for a start look at this.

(defun c:Foo ()
  (setq red nil)
  (if (and (setq file (getfiled "Select *.CSV"  (getvar 'DWGPREFIX)  "csv" 16)) (setq filed (open file "r")))
    (progn 
      (while (setq line (read-line filed))        
        (setq lst (LM:str->lst line ";"))
        (setq red (append red (list lst)))
      );;leia o .csv e cria uma lista
      (setq pnt '(0.0 0.0 0.0));;ponto base
      (foreach x (cdr red)
        (command "_.Insert"  "EVO_RECT" pnt  "" "" "" (car x));;defina um ponto base!!!
        (setq ent (entlast)) ;isso tende a da erro!!!
        (if (= (getpropertyvalue ent "IsDynamicBlock") 1)
          (progn
            (vl-catch-all-apply 'setpropertyvalue (list ent "AcDbDynBlockPropertyHEIGHT" (atof (cadr x))))
            (vl-catch-all-apply 'setpropertyvalue (list ent "AcDbDynBlockPropertyWIDTH" (atof (caddr x))))
          )            
        )        
      )
    )
  )
)
;; String to List  -  Lee Mac
;; Separates a string using a given delimiter
;; str - [str] String to process
;; del - [str] Delimiter by which to separate the string
;; Returns: [lst] List of strings
 
(defun LM:str->lst ( str del / pos )
    (if (setq pos (vl-string-search del str))
        (cons (substr str 1 pos) (LM:str->lst (substr str (+ pos 1 (strlen del))) del))
        (list str)
    )
)

[JBerns]

@jboyceQPURR,

Also see this example to read an Excel file, developed by @llorden4.

Reading native Excel files via AutoLISP 

No conversion to CSV required.

Regards,

Jerry

[jboyceQPURR]

Do i need to adjust anything in the LISP? It doesn't seem to be working as is, i'm not very familiar with LISP programming. 

[jboyceQPURR]

this is just for opening the excel file, unfortuantely i don't know enough after that point to get it work. I need to learn LISP programming.

[jboyceQPURR]

looks like this is getting an error when trying to set the height and width, it prompts me to enter the label and then that's it.

[Anonymous]

I'm answering by cell phone. here in Brazil it is already night, tomorrow I finish and explain how to use it.

[jboyceQPURR]

You are amazing, thank you!

[Sea-Haven]

A couple of suggestion Lee-mac has a nice set dynamic block lisp routines. Not sure about the use of setproperty as above.

If dynamic block has attributes may cause problems so 

(setq oldattrec (getvar 'attreq))
(setvar 'attreq 0)

(LM:setdynpropvalue obj  "Height"  ht)
(LM:setdynpropvalue obj  "Width"  wid)

[jboyceQPURR]

Following up on this if anyone is able to help. Is this something I just need to learn LISP for?

[Sea-Haven]

Try this, note have localised variables, change the name of some variables like "LINE" as that is a reserved name ?

Removed the isdynamic as its implied block is dynamic.

(defun c:Foo ( / pnt file lst blk)
  (setq pnt '(0.0 0.0 0.0));;ponto base
  (if (setq fname (getfiled "Select *.CSV"  (getvar 'DWGPREFIX)  "csv" 16))
    (progn
	(setq filed (open fname "r"))
	(princ (read-line filed))
      (while (setq newline (read-line filed))
      (setq lst (LM:str->lst newline ",")) ; depends on world version of excel if ; or ,
;		(setq lst (LM:str->lst newline ";"))
        (command "_.Insert"  "EVO_RECT" pnt  "" "" "");;defina um ponto base!!!
		(setq blk (vlax-ename->vla-object ent ))
	    (LM:setdynpropvalue  blk "Height" (atof (nth 1 lst)))
	    (LM:setdynpropvalue  blk "Width" (atof (nth 2 lst)))
	    (setq pnt (mapcar '+ pnt (list (+ (atof (nth 2 lst)) 100) 0.0 0.0)))
       )
     )
  )
	(close filed)
	  (princ)
)


;; String to List  -  Lee Mac
;; Separates a string using a given delimiter
;; str - [str] String to process
;; del - [str] Delimiter by which to separate the string
;; Returns: [lst] List of strings
 
(defun LM:str->lst ( str del / pos )
    (if (setq pos (vl-string-search del str))
        (cons (substr str 1 pos) (LM:str->lst (substr str (+ pos 1 (strlen del))) del))
        (list str)
    )
)

;; Set Dynamic Block Property Value  -  Lee Mac
;; Modifies the value of a Dynamic Block property (if present)
;; blk - [vla] VLA Dynamic Block Reference object
;; prp - [str] Dynamic Block property name (case-insensitive)
;; val - [any] New value for property
;; Returns: [any] New value if successful, else nil

(defun LM:setdynpropvalue ( blk prp val )
    (setq prp (strcase prp))
    (vl-some
       '(lambda ( x )
            (if (= prp (strcase (vla-get-propertyname x)))
                (progn
                    (vla-put-value x (vlax-make-variant val (vlax-variant-type (vla-get-value x))))
                    (cond (val) (t))
                )
            )
        )
        (vlax-invoke blk 'getdynamicblockproperties)
    )
)
(c:foo)

[jboyceQPURR]

hmm, same issue, I load the LISP, it asks me to open the excel file. Then it asks for a label. Then nothing happens.

Was anyone able to get it to work? Am I not setting up my excel file correctly?

Thanks!