topic Re: Export polyline data (area, layer and inner text) to excel in Visual LISP, AutoLISP and General Customization Forum

Export polyline data (area, layer and inner text) to excel

Anonymous — Sat, 08 Sep 2018 17:01:41 GMT

Hello, everyone!

I am searching for a lisp that can find all closed polylines in a drawing and export to excel their following information:

-Area of each closed polyline

-Layer in which the polyline resides

-Text inside each polyline

I have a building plan with polylines enclosing each room and the corresponding room text number also inside the polyline. I am looking for a lisp that can export to excel a table like this:

Text inside polyline	Polyline Area (m2)	Layer
"Room_1.1"	10 m2	Room_Layer
"Room_1.2"	5 m2	Room_Layer
"Room_1.3"	5 m2	Room_Layer
-	20 m2	Floor_Layer

The polylines and room text numbers are located in different layers.

One of the polylines (last row in the table) encloses the whole building which means that there is more that one text inside it. For this single polyline, I only need its area and layer (text not needed).

I am fairly new to lisp creation so any help you can provide me would be highly appreciated!

Thanks in advance!

Re: Export polyline data (area, layer and inner text) to excel

devitg — Sat, 08 Sep 2018 17:40:38 GMT

Please upload your sample.dwg

Re: Export polyline data (area, layer and inner text) to excel

devitg — Sat, 08 Sep 2018 17:46:35 GMT

Please upload your sample.dwg

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Sat, 08 Sep 2018 18:25:30 GMT

Hello, devitg!

Thanks for your quick response. I send you the sample.dwg in attachment.

For this sample drawing, I would like the lisp to create a table in excel similar to the following:

Polyline nº	Text inside polyline	Polyline Area (m2)	Polyline Layer
1	1.1	51.34 m2	Room Layer
2	1.2	28.75 m2	Room Layer
3	1.3	14.41 m2	Room Layer
4	-	100.75 m2	Floor Layer

Thanks in advance!

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Sun, 09 Sep 2018 18:58:33 GMT

@Anonymous wrote:

Polyline nº Text inside polyline Polyline Area (m2) Polyline Layer

1 1.1 51.34 m2 Room Layer

2 1.2 28.75 m2 Room Layer

3 1.3 14.41 m2 Room Layer

4 - 100.75 m2 Floor Layer

Try this [results in CSV format]

(defun c:demo (/ ss i area layer all_data pts csv_file openfile)
;;			pBe Sep 2018			;;
  (if (and
        (setq all_data nil
              ss       (ssget '((0 . "LWPOLYLINE")))
        )
        (repeat (setq i (sslength ss))
          (setq e     (ssname ss (setq i (1- i)))
                area  (vlax-curve-getarea e)
                layer (cdr (assoc 8 (setq ent (entget e))))
                pts   (mapcar 'cdr
                              (vl-remove-if-not
                                '(lambda (d)
                                   (= 10 (car d))
                                 )
                                ent
                              )
                      )
          )
          (if (setq ssText (ssget "_CP" pts '((0 . "TEXT"))))
            (setq all_data
                   (cons
                     (list (if (= (sslength ssText) 1)
                             (cdr (assoc 1 (entget (ssname ssText 0))))
                             "-"
                           )
                           area
                           layer
                     )
                     all_data
                   )
            )
          )
        )
        (setq csv_file (getfiled "Save CSV File"
                                 (strcat
                                   (getvar 'dwgprefix)
                                   (vl-filename-base (getvar 'dwgname))
                                   ".csv"
                                 )
                                 "csv"
                                 45
                       )
        	)
        )
        

        (progn
          (setq openfile (open csv_file "w"))
          (write-line
            "Text inside polyline,Polyline Area (m2),Layer"
            openfile
          )
          (foreach itm (vl-sort all_data
                                '(lambda (a b) (< (Cadr a) (cadr b)))
                       )
            (write-line
              (Strcat (Car itm)
                      ","
                      (strcat (rtos (Cadr itm) 2 2) " m2")
                      ","
                      (caddr itm)
              )
              openfile
            )
          )
          (close openfile)
          (startapp "notepad" csv_file)
        )
      )
  (princ)

)

HTH

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Mon, 10 Sep 2018 23:28:18 GMT

Thank you very much, pbejse!! You're a lifesaver, that's exactly what I needed.

Just one more detail, would it be possible to add a 4th column to the table with the information on whether the polylines are closed or not (field "Closed" in "Misc" properties, attached image).

Text inside polyline	Polyline Area (m2)	Polyline Layer	Closed
1.1	51.34 m2	Room Layer	Yes
1.2	28.75 m2	Room Layer	Yes
1.3	14.41 m2	Room Layer	Yes
-	100.75 m2	Floor Layer	Yes

Once again, that you very much for any help you can provide

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Tue, 11 Sep 2018 16:59:30 GMT

@Anonymous wrote:

Just one more detail, would it be possible to add a 4th column to the table with the information on whether the polylines are closed or not (field "Closed" in "Misc" properties, attached image).

Here you go.

(defun c:EPD (/ ss i area layer all_data pts csv_file openfile) ; Export Polyline Data
;;			pBe Sep 2018			;;
  (if (and
        (setq all_data nil
              ss       (ssget '((0 . "LWPOLYLINE")))
        )
	        (repeat (setq i (sslength ss))
	          (setq e     (ssname ss (setq i (1- i)))
	                area  (vlax-curve-getarea e)
	                layer (cdr (assoc 8 (setq ent (entget e))))
	                cl_op (cdr (assoc 70 ent))
	                pts   (mapcar 'cdr
	                              (vl-remove-if-not
	                                '(lambda (d)
	                                   (= 10 (car d))
	                                 )
	                                ent
	                              )
	                      )
	          )
	          (if (setq ssText (ssget "_CP" pts '((0 . "TEXT"))))
	            (setq all_data
	                   (cons
	                     (list (if (= (sslength ssText) 1)
	                             (cdr (assoc 1 (entget (ssname ssText 0))))
	                             "-"
	                           )
	                           area
	                           layer
	                           (if (zerop ( logand 1 cl_op)) "No" "Yes")
	                     )
	                     all_data
	                   )
	            	)
                 )
               all_data
               )
	        (setq csv_file (getfiled "Save CSV File"
	                                 (strcat
	                                   (getvar 'dwgprefix)
	                                   (vl-filename-base (getvar 'dwgname))
	                                   ".csv"
	                                 )
	                                 "csv"
	                                 45
	                       )
	        	)
	        )

        (progn
          (setq openfile (open csv_file "w"))
          (write-line
            "Text inside polyline,Polyline Area (m2),Layer,Closed"
            openfile
          )
          (foreach itm (vl-sort all_data
                                '(lambda (a b) (< (Cadr a) (cadr b)))
                       )
            (write-line
              (Strcat (Car itm)
                      ","
                      (strcat (rtos (Cadr itm) 2 2) " m2")
                      ","
                      (caddr itm)
                      ","
                      (last itm)
              )
              openfile
            )
          )
          (close openfile)
          (startapp "notepad" csv_file)
        )
      )
  (princ)

)

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Wed, 12 Sep 2018 07:08:54 GMT

Hello, pbejse

It works perfectly! You never fail to deliver.

Thank you very much for your valuable help!!

Re: Export polyline data (area, layer and inner text) to excel

emsquare.arch — Wed, 12 Sep 2018 09:55:02 GMT

Hi
If you add some more lines as per my requirement. Procedure will be same but with slight change. I need unit area , Duct area ( any polyline inside the unit will be duct) and Balconies area ( any polyline outside the unit will be balcony but outside polyline must snap to unit). Please refer the attached

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Sun, 16 Sep 2018 23:44:07 GMT

Hello pbejse,

I am so so sorry for bothering you again with this topic but I promise you this is the last change I need for the code to be (even more) perfect! 🙂

Would it be possible for the table to:

-include polylines without texts

-include one last column with the polyline's "Handle ID"

-include all the texts inside the polyline in the first column

Text inside polyline	Polyline Area (m2)	Polyline Layer	Closed	Handle ID
1.1	51.34 m2	Room Layer	Yes
1.2	28.75 m2	Room Layer	Yes
1.3	14.41 m2	Room Layer	Yes
1.1, 1.2, 1.3	100.75 m2	Floor Layer	Yes

Once again, thank you so so much for you help and patience and forgive me for bothering with this request!!

Best Regards,

Miguel Souza

Re: Export polyline data (area, layer and inner text) to excel

devitg — Mon, 17 Sep 2018 15:53:59 GMT

No comments .

It´s easy to ask by step, when it would be better to ask the whole stair.

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Mon, 17 Sep 2018 16:55:37 GMT

@Anonymous wrote:

Would it be possible for the table to:

-include polylines without texts

How would you want to label those polylines on the table? as blank or a dash perhaps?

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Mon, 17 Sep 2018 17:42:17 GMT

Hello pbejse,

The polylines without texts can be labelled with a dash.

Thank you very much for asking!

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Tue, 18 Sep 2018 12:08:04 GMT

@devitg wrote:

It´s easy to ask by step, when it would be better to ask the whole stair.

True.

@Anonymous wrote:

-include polylines without texts

-include one last column with the polyline's "Handle ID"

-include all the texts inside the polyline in the first column

(defun c:EPD (/ ss i area layer all_data pts csv_file openfile) ; Export Polyline Data
;;			pBe Sep 2018			;;
  (if (and
        (setq all_data nil
              ss       (ssget '((0 . "LWPOLYLINE")))
        )
	        (repeat (setq i (sslength ss))
	          (setq e     (ssname ss (setq i (1- i)))
                     ent   (entget e)
	                area  (vlax-curve-getarea e)
                     data  (mapcar '(lambda (d)(cdr (assoc d ent))) '( 8 70 5))
	                pts   (mapcar 'cdr
	                              (vl-remove-if-not
	                                '(lambda (d)
	                                   (= 10 (car d))
	                                 )
	                                ent
	                              )
	                      )
	          )
		(setq all_data
	                   (cons
	                     (list 
				(cond
                              		((null (setq ssText (ssget "_CP" pts '((0 . "TEXT")))))	"-"
                                    							)
                                   	((= (sslength ssText) 1)
                                    		(cdr (assoc 1 (entget (ssname ssText 0))))
                                                  				)
                                   	((substr 
					   (apply 'strcat
					      (mapcar '(lambda (st)
					                 (strcat " | " st))
							(vl-sort
							  (mapcar '(lambda (s)
							             (cdr (assoc 1 (Entget s)))
							           )
							          (vl-remove-if 'listp (mapcar 'cadr (ssnamex ssText)))
							  )
							  (function (lambda (a b)
							              (< a b)
							            )
								  )
								)
						              )
							)
						   4
						)
					)
                                    )                                
	                           area
	                           (car data)
	                           (if (zerop ( logand 1 (cadr data))) "No" "Yes")
                            	  (caddr data)
	                     )
	                     all_data
	                   )
	            	)
  
               all_data
               )
	        (setq csv_file (getfiled "Save CSV File"
	                                 (strcat
	                                   (getvar 'dwgprefix)
	                                   (vl-filename-base (getvar 'dwgname))
	                                   ".csv"
	                                 )
	                                 "csv"
	                                 45
	                       )
	        	)
	        )

        (progn
          (setq openfile (open csv_file "w"))
          (write-line
            "Text inside polyline,Polyline Area (m2),Layer,Closed,Handle"
            openfile
          )
          (foreach itm (vl-sort all_data
                                '(lambda (a b) (< (Cadr a) (cadr b)))
                       )
            (write-line
              (Strcat (Car itm)
                      ","
                      (strcat (rtos (Cadr itm) 2 2) " m2")
                      ","
                      (caddr itm)
                      ","
                      (cadddr itm)
		      ","
                      (last itm)		      
              )
              openfile
            )
          )
          (close openfile)
          (startapp "notepad" csv_file)
        )
      )
  (princ)

)

I used the pipe " | " as the separator for collected names, the comma symbol is used for delimiter for the csv file.

1.1 | 1.2 | 1.3

187.25 m2

Floor Layer

Yes

8BBD

HTH

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Tue, 18 Sep 2018 13:05:08 GMT

Hello, pbejse

The code is more than perfect!! Exactly what I needed.

I cannot thank you enough for helping me out with this and excuse me if I caused you any trouble!

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Wed, 19 Sep 2018 13:57:41 GMT

This code is what I've been looking for a while.

I'm new to lisp coding. What line(s) do I have to change to get it to list the areas in square feet?

Thanks in advance and thanks for sharing your knowledge!

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Wed, 19 Sep 2018 16:21:08 GMT

@Anonymous wrote:

. What line(s) do I have to change to get it to list the areas in square feet?

Are you referring to the unit string?

This

(strcat (rtos (Cadr itm) 2 2) " m2")

(strcat (rtos (Cadr itm) 2 2) " sq. ft")

or you need conversion?

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Wed, 19 Sep 2018 16:24:21 GMT

I need the conversion to square feet.

I tried to come up with it myself but I could not get the math right.

Thanks!

Re: Export polyline data (area, layer and inner text) to excel

pbejse — Wed, 19 Sep 2018 16:45:08 GMT

@Anonymous wrote:

I need the conversion to square feet.

I tried to come up with it myself but I could not get the math right.

Thanks!

Any of this two will work

(strcat (rtos
          (* (Cadr itm) 10.7639104)  2 2) " sq. ft")

(strcat (rtos
          (cvunit (Cadr itm) "sq meter" "sq feet")
              2 2) " sq. ft")

HTH

Re: Export polyline data (area, layer and inner text) to excel

Anonymous — Wed, 19 Sep 2018 17:33:27 GMT

Thank you - but I didn't explain my situation completely.

The AutoCAD drawings I work with are drawn in architectural units.

So when I switched out your conversion code I did not get the right results.

For a 10'-0" x 20'-0" room I get 310000.62 sq. ft.

None of my drawings are in metric, so I guess I can not use your code.

Thank you for your input in trying to solve my problem.