Export information to Excel

Export information to Excel

Anonymous
適用対象外
1,489件の閲覧回数
18件の返信
メッセージ1/19

Export information to Excel

Anonymous
適用対象外

Hello Fellows.

 

I am willing to get a lisp routine to export some information to either a notepad or a excel sheet which includes:

 

object -color - text inside

 

On sample attached, if I run the lisp routine, the outcome would look like:

 

BLOCK MAGENTA 07-A

CIRCLE MAGENTA 07-B

CIRCLE GRAY 07-C

 

If anyone has something like this will be much appreciate it.

 

thanks a lot!

0 件のいいね
1,490件の閲覧回数
18件の返信
返信 (18)
メッセージ2/19

hak_vz
Advisor
Advisor

@Anonymous

 

I've tried to write it down. This suppose to be a simple task, but your sample is so .....

Why text inside block is not simple text or an attribute inside block. So many blocks......  extracting info from blocks becomes too complicated.

 

This code doesn't  work but if someone is willing to continue...

It is supposed to collect all blocks, create selection from its bounding box, extract text info from text object (but this is also block) , extract color from block layer , output all to. csv and open in excel or some spreadsheet

 

 

(defun c:getout ()
(setq i -1)
(setq d "d:\\out.csv")
(setq file1 (open d "w") tt d)
(repeat (sslength(setq ss (ssget "x" '((0 . "INSERT")))))
(setq
    i (+ i 1)
	eo (vlax-ename->vla-object (ssname ss i))
	bb (vla-getboundingbox eo 'p1 'p2))
	p1  (vlax-safearray->list p1)
	p2  (vlax-safearray->list p2)
	to (vlax-ename->vla-object(ssname (ssget "w" p1 p2) 0))
	clr (itoa (cdr (assoc 62 (tblsearch "layer" (vla-get-layer eo)))))
	...

)
(write-line rec file1)
)
(close file1)
(startapp "explorer" tt)
(princ)
)

 

 

Miljenko Hatlak

EESignature

Did you find this post helpful? Feel free to Like this post.
Did your question get successfully answered? Then click on the ACCEPT SOLUTION button.
0 件のいいね
メッセージ3/19

dmfrazier
Advisor
Advisor

Have you looked into the DataExtraction command?

0 件のいいね
メッセージ4/19

Anonymous
適用対象外

Hello there. 

 

I tried this one, and for some reason is not working on my machine... I deleted the ( ... ) and I get a message saying "Command: GETOUT
; error: bad argument type: stringp nil"

0 件のいいね
メッセージ5/19

ronjonp
Advisor
Advisor

Try this .. you will need to figure out how to format your colors to names though.

 

(defun c:foo (/ _writefile a b c el o r s)
  ;; RJP » 2020-10-26
  (defun _writefile (fn l del / f)
    (cond ((and (eq 'str (type fn)) (setq f (open fn "w")))
	   (foreach x l
	     (write-line
	       (cond ((eq (type x) 'list)
		      (apply 'strcat (mapcar '(lambda (y) (strcat (vl-princ-to-string y) del)) x))
		     )
		     ((vl-princ-to-string x))
	       )
	       f
	     )
	   )
	   (close f)
	   fn
	  )
    )
  )
  (if (setq s (ssget "_X"
		     '((-4 . "<OR")
		       (-4 . "<AND")
		       (0 . "INSERT")
		       (2 . "~FMS4RM")
		       (-4 . "AND>")
		       (0 . "CIRCLE")
		       (-4 . "OR>")
		      )
	      )
      )
    (progn
      (foreach e (mapcar 'cadr (ssnamex s))
	(if (vl-catch-all-error-p
	      (vl-catch-all-apply
		'vla-getboundingbox
		(list (setq o (vlax-ename->vla-object e)) 'a 'b)
	      )
	    )
	  (print "NULL EXTENTS")
	  (progn (if (setq c (ssget "_W"
				    (vlax-safearray->list a)
				    (vlax-safearray->list b)
				    '((0 . "INSERT") (2 . "FMS4RM"))
			     )
		     )
		   (setq r (cons (list (cdr (assoc 0 (setq el (entget e))))
				       ;; COLOR INDEX NUMBER .. SEE IF YOU CAN FIGURE OUT HOW TO MAP A NAME TO IT ;)
				       (cond ((cdr (assoc 62 el)))
					     ((cdr (assoc 62 (tblsearch "layer" (cdr (assoc 8 el))))))
				       )
				       (getpropertyvalue (ssname c 0) "FMS:RMID")
				 )
				 r
			   )
		   )
		 )
		 (_writefile
		   (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".txt")
		   (reverse r)
		   " "
		 )
		 (mapcar 'print r)
	  )
	)
      )
    )
  )
  (princ)
)

 

 

 

 

0 件のいいね
メッセージ6/19

Anonymous
適用対象外

Thanks friend.

 

with this one, I'm getting a message saying "; error: Automation Error. Null extents"

0 件のいいね
メッセージ7/19

ronjonp
Advisor
Advisor

Try the code above again.

0 件のいいね
メッセージ8/19

Anonymous
適用対象外

I'm sorry fiend, is not working either.

 

It starts running, then reads the numbers (more than once), and then it gave me the same error

0 件のいいね
メッセージ9/19

ronjonp
Advisor
Advisor

Did you open the text file in the same directory ? The null extents is just an alert . 😉

0 件のいいね
メッセージ10/19

pbejse
Mentor
Mentor

@ronjonp wrote:

Did you open the text file in the same directory ? The null extents is just an alert . 😉


That is the first thing i would be looking for :

BTW: Small thing to watch out for.

 

(ssget "W"...

 

From a recent topic to which i participated ".... it applies only to objects appearing in front of the screen.."   

...

 

 

0 件のいいね
メッセージ11/19

devitg
Advisor
Advisor

@Anonymous 

 

so you have to add , 

 

(vla-ZoomWindow acadObj point1 point2)
    

Previous the SSGET 

0 件のいいね
メッセージ12/19

ronjonp
Advisor
Advisor

@pbejse wrote:

@ronjonp wrote:

Did you open the text file in the same directory ? The null extents is just an alert . 😉


That is the first thing i would be looking for :

BTW: Small thing to watch out for.

 

 

(ssget "W"...

 

 

From a recent topic to which i participated ".... it applies only to objects appearing in front of the screen.."   

...

 

 


That depends on what version of CAD you're running that limitation does not exist in newer versions ( not sure what version it starts though ).

0 件のいいね
メッセージ13/19

ronjonp
Advisor
Advisor

Here is a version that will zoom to each object just in case you're running an older version of AutoCAD. 🍻

(defun c:foo (/ _writefile a ao b c el o r s)
  ;; RJP » 2020-10-26
  (defun _writefile (fn l del / f)
    (cond ((and (eq 'str (type fn)) (setq f (open fn "w")))
	   (foreach x l
	     (write-line
	       (cond ((eq (type x) 'list)
		      (apply 'strcat (mapcar '(lambda (y) (strcat (vl-princ-to-string y) del)) x))
		     )
		     ((vl-princ-to-string x))
	       )
	       f
	     )
	   )
	   (close f)
	   fn
	  )
    )
  )
  (if (setq s (ssget "_X"
		     '((-4 . "<OR")
		       (-4 . "<AND")
		       (0 . "INSERT")
		       (2 . "~FMS4RM")
		       (-4 . "AND>")
		       (0 . "CIRCLE")
		       (-4 . "OR>")
		      )
	      )
      )
    (progn
      (setq ao (vlax-get-acad-object))
      (foreach e (mapcar 'cadr (ssnamex s))
	(if (vl-catch-all-error-p
	      (vl-catch-all-apply
		'vla-getboundingbox
		(list (setq o (vlax-ename->vla-object e)) 'a 'b)
	      )
	    )
	  (print "NULL EXTENTS")
	  (progn (vla-zoomwindow ao a b)
		 (if (setq c (ssget "_W"
				    (vlax-safearray->list a)
				    (vlax-safearray->list b)
				    '((0 . "INSERT") (2 . "FMS4RM"))
			     )
		     )
		   (setq r (cons (list (cdr (assoc 0 (setq el (entget e))))
				       ;; COLOR INDEX NUMBER .. SEE IF YOU CAN FIGURE OUT HOW TO MAP A NAME TO IT ;)
				       (cond ((cdr (assoc 62 el)))
					     ((cdr (assoc 62 (tblsearch "layer" (cdr (assoc 8 el))))))
				       )
				       (getpropertyvalue (ssname c 0) "FMS:RMID")
				 )
				 r
			   )
		   )
		 )
		 (_writefile
		   (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".txt")
		   (reverse r)
		   " "
		 )
		 (mapcar 'print r)
	  )
	)
      )
    )
  )
  (princ)
)
0 件のいいね
メッセージ14/19

Anonymous
適用対象外

Hello @ronjonp ,

 

This one is getting closer of what I need, thanks a lot.

 

Is there anyway to make Autocad read only these layers?:

 

- A-AREA-IDEN

- A-SOCL-DIS1-E

- A-SOCL-DIS2-E

- A-SOCL-DMTG-E

 

 

And also, when I run the script, looks like it adds the item one by one, and runs the scrip again. Let me give you a sample. For a drawing with 5 of these items to read, the programs starts reading one, then runs again and reads two, and this way till reach the total. Please se attach

 

 

0 件のいいね
メッセージ15/19

ronjonp
Advisor
Advisor

@Anonymous wrote:

Hello @ronjonp ,

 

This one is getting closer of what I need, thanks a lot.

 

Is there anyway to make Autocad read only these layers?:

 

- A-AREA-IDEN

- A-SOCL-DIS1-E

- A-SOCL-DIS2-E

- A-SOCL-DMTG-E

 

 

And also, when I run the script, looks like it adds the item one by one, and runs the scrip again. Let me give you a sample. For a drawing with 5 of these items to read, the programs starts reading one, then runs again and reads two, and this way till reach the total. Please se attach

 

 


To filter those layers try the code below. I don't understand the rest of your message sorry ;\

(defun c:foo (/ _writefile a b c el o r s)
  ;; RJP » 2020-10-26
  (defun _writefile (fn l del / f)
    (cond ((and (eq 'str (type fn)) (setq f (open fn "w")))
	   (foreach x l
	     (write-line
	       (cond ((eq (type x) 'list)
		      (apply 'strcat (mapcar '(lambda (y) (strcat (vl-princ-to-string y) del)) x))
		     )
		     ((vl-princ-to-string x))
	       )
	       f
	     )
	   )
	   (close f)
	   fn
	  )
    )
  )
  (if (setq s (ssget "_X"
		     '((-4 . "<OR")
		       (-4 . "<AND")
		       (0 . "INSERT")
		       (2 . "~FMS4RM")
		       (8 . "- A-AREA-IDEN,- A-SOCL-DIS1-E,- A-SOCL-DIS2-E,- A-SOCL-DMTG-E")
		       (-4 . "AND>")
		       (-4 . "<AND")
		       (0 . "CIRCLE")
		       (8 . "- A-AREA-IDEN,- A-SOCL-DIS1-E,- A-SOCL-DIS2-E,- A-SOCL-DMTG-E")
		       (-4 . "AND>")
		       (-4 . "OR>")
		      )
	      )
      )
    (progn
      (foreach e (mapcar 'cadr (ssnamex s))
	(if (vl-catch-all-error-p
	      (vl-catch-all-apply
		'vla-getboundingbox
		(list (setq o (vlax-ename->vla-object e)) 'a 'b)
	      )
	    )
	  (print "NULL EXTENTS")
	  (progn (if (setq c (ssget "_W"
				    (vlax-safearray->list a)
				    (vlax-safearray->list b)
				    '((0 . "INSERT") (2 . "FMS4RM"))
			     )
		     )
		   (setq r (cons (list (cdr (assoc 0 (setq el (entget e))))
				       ;; COLOR INDEX NUMBER .. SEE IF YOU CAN FIGURE OUT HOW TO MAP A NAME TO IT ;)
				       (cond ((cdr (assoc 62 el)))
					     ((cdr (assoc 62 (tblsearch "layer" (cdr (assoc 8 el))))))
				       )
				       (getpropertyvalue (ssname c 0) "FMS:RMID")
				 )
				 r
			   )
		   )
		 )
		 (_writefile
		   (strcat (getvar 'dwgprefix) (vl-filename-base (getvar 'dwgname)) ".txt")
		   (reverse r)
		   " "
		 )
		 ;; Is this command line print confusing you?
		 ;; (mapcar 'print r)
	  )
	)
      )
    )
  )
  (princ)
)
0 件のいいね
メッセージ16/19

pbejse
Mentor
Mentor

@ronjonp wrote:
That depends on what version of CAD you're running that limitation does not exist in newer versions ( not sure what version it starts though ).

 

Strange, it still behaves the same in 2021 and 2020 , guess i need to update and look for the latest hotfix 😀

 

0 件のいいね
メッセージ17/19

ronjonp
Advisor
Advisor

@Anonymous  Did you get what you needed?

0 件のいいね
メッセージ18/19

pbejse
Mentor
Mentor

@Anonymous wrote:

Is there anyway to make Autocad read only these layers?:

 

- A-AREA-IDEN

- A-SOCL-DIS1-E

- A-SOCL-DIS2-E

- A-SOCL-DMTG-E


 

I'm guessing the "-" prefix is not really part of the layer name? And is this limite to Blocks and Circles? and you wnt the file to open so you visually see the result on screen right after the routine has done its task?

 

Command: BAC

Results saved as CSV:

INSERT,110,07-A
CIRCLE,230,07-B
CIRCLE,251,07-C

 

HTH

0 件のいいね
メッセージ19/19

Anonymous
適用対象外

Hello friend.

 

is there any way to read the text there, whether is an attribute, or a simple text?

0 件のいいね