Topic: Lisp to read selection set and write to xls

[artisteroi]

Hi everybody!

I need a lisp (or other routine) to read a group of line work get the vertex and arc data and write the info to a spreadsheet. It needs to keep a running spreadsheet so that it can be run multiple times. This is WAAAYYYY beyond my level of programming. I might get lisp to draw some stuff, but reading data and writing it to excel, not in a million years.
Attached is set of line work made of arcs and 3d polylines. I need to write the 3d poly vertexes points in space (vertex 1 = 12,32,15 ; vertex 2 = 14,38,15) and the info for the arc (center point x,y,z = 12,32,15 ; radius = 22 ; start angle 22.6875 ; end angle = 132.4375). And it needs to append this data into an excel spreadsheet in a running table.
Sound like fun? Sounds like a headache to me, but that's me. I would get my staff to do this but they all got laid off last year. Anyone wishing to rise to the challenge will have my undying graditude.

[Lee Mac]

Getting the information:

Converting a SelectionSet to a list of VLA-Objects:

Code: [Select]

(defun ss->vla ( ss )
  (if ss
    (
      (lambda ( i / e l )
        (while (setq e (ssname ss (setq i (1+ i))))
          (setq l (cons (vlax-ename->vla-object e) l))
        )
        l
      )
      -1
    )
  )
)


Code: [Select]

(ss->vla <SelectionSet>)

==>  '(<VLA-object> <VLA-object> <VLA-object> ... <VLA-object>)

Arcs:

Code: [Select]

(vlax-get <Arc Object> 'StartAngle)
(vlax-get <Arc Object> 'EndAngle)
(vlax-get <Arc Object> 'Radius)
Use rtos / angtos to convert Arc data to strings.

Polylines:

Code: [Select]

(vlax-get <Polyline Object> 'Coordinates)

==>  '(x y z x y z x y z ... x y z)

Converting Polyline Coordinates into a Points list:

Code: [Select]

(defun GroupByNum ( l n / a b )
  (while l
    (
      (lambda ( i )
        (while (< 0 i)
          (setq a (cons (car l) a) l (cdr l) i (1- i))
        )
        (setq b (cons (reverse a) b) a nil)
      )
      n
    )
  )
  (reverse b)
)
 

Code: [Select]

(GroupByNum (vlax-get <Polyline Object> 'Coordinates) 3)

==>  '((x y z) (x y z) (x y z) ... (x y z))
Constructing Coordinate Strings from the Point data:

Code: [Select]

(defun lst->str ( lst del )
  (if (cdr lst)
    (strcat (car lst) del (lst->str (cdr lst) del))
    (car lst)
  )
)

Code: [Select]

(lst->str '(x y z) ",")

==>  "x,y,z"



Writing data to Excel.

This should get you going 

[artisteroi]

thanks Lee

I will see what I can do with this. But I am way out of my depth. Most of what you wrote, I don't even recognize. But I will take a swing at it.

[Lee Mac]

Not a problem, if you get stuck at all, just ask 

[artisteroi]

I appreciate the help you are offering but I am totally lost.
This is visual lisp code right? All the "vlax" stuff. I can't get any of that to work. I converted the first couple of lines to vanilla autolisp and manged to get the selection set but I cant get it to go any farther. I may have to use the vlax stuff but it won't run on my cad. Do I need to load some sort of enabler or something?
Here is what I got working. Thats all.

Code: [Select]

(defun c:dr (/) ;;;(defun  ss->vla ( ss )
(setq ss (ssget)) ;;;  (if ss

[Lee Mac]

You will need:

Code: [Select]

(vl-load-com)
at the top of the code to enable the Visual LISP functions.

[Mark]

Getting the information:

Converting a SelectionSet to a list of VLA-Objects:

GREAT post Lee, great post.

[Lee Mac]

Of course, this could be done in Vanilla LISP... (well, all except the Excel part)...

Arc Data:

Radius =  DXF group 40
StartAngle = DXF group 50
EndAngle = DXF group 51

Again, you would have to use rtos / angtos to convert this data to a string

Polylines:

You can get the vertices using this recursive code:

Code: [Select]

(defun foo ( ent / p )
  (if (and (setq ent (entnext ent)) (setq p (cdr (assoc 10 (entget ent)))))
    (cons p (foo ent))
  )
)

Code: [Select]

(foo <Polyline entity>)
==>  '((x y z) (x y z) (x y z) ... (x y z))

Then again, constructing Coordinate Strings from the Point data:

Code: [Select]

(defun lst->str ( lst del )
  (if (cdr lst)
    (strcat (car lst) del (lst->str (cdr lst) del))
    (car lst)
  )
)

Code: [Select]

(lst->str '(x y z) ",")

==>  "x,y,z"

[Lee Mac]

GREAT post Lee, great post.

Thanks Mark 

[artisteroi]

can't do the excel part with vanilla huh?
So I added the vla com load part and the script is running. Will get back to you soon with a progress check.

[Lee Mac]

can't do the excel part with vanilla huh?
So I added the vla com load part and the script is running. Will get back to you soon with a progress check.

Ok, I suppose you can do it with Vanilla, but you don't get much control. You can write to an Excel file using write-line, and tab delimited text strings.

[artisteroi]

This is beyond me I'm afraid. My knowledge of lisp is limited to doing animations and stuff like that. I dont think I can pull data (nothing to look at) from objects in model space.

Would you (Lee) be willing to write the code for me. I maybe able to get you re-imbursed for your time. Let me know how much you would charge and I will see what I can do.

[Lee Mac]

PM'ed 

[artisteroi]

e-mailed

[fixo]

Hi everybody!

I need a lisp (or other routine) to read a group of line work get the vertex and arc data and write the info to a spreadsheet. It needs to keep a running spreadsheet so that it can be run multiple times. This is WAAAYYYY beyond my level of programming. I might get lisp to draw some stuff, but reading data and writing it to excel, not in a million years.
Attached is set of line work made of arcs and 3d polylines. I need to write the 3d poly vertexes points in space (vertex 1 = 12,32,15 ; vertex 2 = 14,38,15) and the info for the arc (center point x,y,z = 12,32,15 ; radius = 22 ; start angle 22.6875 ; end angle = 132.4375). And it needs to append this data into an excel spreadsheet in a running table.
Sound like fun? Sounds like a headache to me, but that's me. I would get my staff to do this but they all got laid off last year. Anyone wishing to rise to the challenge will have my undying graditude.

Try this code

Code: [Select]

(vl-load-com)
;;local defuns
(defun group-by-num (lst num / ls ret)
  (if (= (rem (length lst) num) 0)
    (progn
      (setq ls nil)
      (repeat (/ (length lst) num)
 (repeat num
   (setq ls
      (cons (car lst) ls)
  lst (cdr lst)
   )
 )
 (setq ret (append ret (list (reverse ls)))
       ls  nil
 )
      )
    )
  )
  ret
)

(defun pline_cordinates (pline_obj / verts)
  (setq verts (vlax-get pline_obj 'Coordinates)
 verts
       (cond
  ((wcmatch (vlax-get pline_obj 'Objectname)
     "AcDb2dPolyline,AcDb3dPolyline"
   )
   (group-by-num verts 3)
  )
  ((eq (vlax-get pline_obj 'Objectname)
       "AcDbPolyline"
   )
   (group-by-num verts 2)
  )
  (T nil)
       )
  )
)
;;main program
(defun C:IEX (/     *error*  eang    ep      etang   fdesk   fname   obj_list
       pt      rad     sp      sset    stang   strcoords
       tmp
      )
 
(defun *error* (msg)
       
    (if (wcmatch (strcase msg) "*BREAK,*CANCEL*,*EXIT*")
        (princ (strcat "\n** Error: " msg " **")
        )
      )
    (if fdesk  (close fdesk)
      )
    (princ)
  )
 
  (if (setq sset (ssget (list (cons 0 "LINE,POLYLINE,ARC"))))
   
    (progn
      (setq obj_list
      (mapcar 'vlax-ename->vla-object
       (vl-remove-if
         'listp
         (mapcar 'cadr (ssnamex sset))
       )
      )
      )
      (setq fname (strcat (getvar "dwgprefix")
     (vl-filename-base (getvar "dwgname"))
     ".CSV"
    )
      )
      (setq fdesk (open fname "w"))
      (foreach obj obj_list
(setq tmp
 (cond ((and (equal "AcDbLine" (vla-get-objectname obj))
        (setq sp (vlax-get obj 'StartPoint)
       ep (vlax-get obj 'EndPoint)
        ))
        (strcat "LINE" (chr 9)
     (strcat (rtos (car sp) 2 3)
     ","
     (rtos (cadr sp) 2 3)
     ","
     (rtos (caddr sp) 2 3))
     (chr 9)
         (strcat
     (rtos (car ep) 2 3)
     ","
     (rtos (cadr ep) 2 3)
     ","
     (rtos (caddr ep) 2 3)
    )
        )
       )
       ((and(equal "AcDbArc" (vla-get-objectname obj))
        (setq pt    (vlax-get obj 'Center)
       rad   (vlax-get obj 'Radius)
       stang (vlax-get obj 'StartAngle)
       eang  (vlax-get obj 'EndAngle)
        )
        )
        (strcat "ARC" (chr 9)
     (rtos (car pt) 2 3)
     ","
     (rtos (cadr pt) 2 3)
     ","
     (rtos (caddr pt) 2 3)
     (chr 9)
     (rtos rad 2 3)
     (chr 9)
     (rtos stang 2 3)
      (chr 9)
     (rtos eang 2 3)
    )
        )
       
       ((equal "AcDb3dPolyline" (vla-get-objectname obj))
        (setq strcoords
          (apply
     'strcat
     (mapcar
       (function (lambda (x) (strcat x (chr 9))))
       (mapcar
         (function
           (lambda (a)
      (apply
        'strcat
        (cons
          (rtos (car a) 2 3)
          (mapcar
            (function
       (lambda (i) (strcat "," (rtos i 2 3)))
            )
            (cdr a)
          )
        )
      )
           )
         )
         (pline_cordinates obj)
       )
     )
          )
       strcoords (substr strcoords 1 (1- (strlen strcoords)))
        )
        (strcat "3DPOLY" (chr 9) strcoords)
        )
     
       
 )
)
 ;;debug only
 (princ "\n   =>>   ")
 (princ tmp)
 
 (write-line tmp fdesk)
      )
      (close fdesk)
    )
  )
  (*error* nil)
  (princ)
)
(prompt "\n   >>   Start command with IEX   <<")
(prin1)

~'J'~

[Lee Mac]

Thanks Fixo... 

[fixo]

Thanks Fixo... 

Ah, you're quite welcome

This one is from my very oldies

I just added your error routine

Cheers

~'J'~

[HasanCAD]

Thanks Fixo... 

Ah, you're quite welcome

~'J'~

Reading betwen lines
Lee dont want to give him a routine but giding him to ro create one
Learning how to Fish instead of giving a fish

[alanjt]

Thanks Fixo... 

Ah, you're quite welcome

~'J'~

Reading betwen lines
Lee dont want to give him a routine but giding him to ro create one
Learning how to Fish instead of giving a fish

Close....

Would you (Lee) be willing to write the code for me. I maybe able to get you re-imbursed for your time. Let me know how much you would charge and I will see what I can do.

[fixo]

Thanks Fixo... 

Ah, you're quite welcome

~'J'~

Reading betwen lines
Lee dont want to give him a routine but giding him to ro create one
Learning how to Fish instead of giving a fish

I feel free to send a finished code, because I think that not only threadstarter
need similar program and it is his will use the code or learn programming
from master Lee Mac

[HasanCAD]

Try this code

~'J'~

How to add point to selection set
this what I did but not working

Code: [Select]

(setq sset (ssget (list (cons 0 "LINE,POLYLINE,ARC,POINT"))))
Thank
Hasan

[Caddy]

I used this as a basic for my own wishes. 

[Caddy]

I used this as a basic for my own wishes. 

Forgat something.