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

[Anonymous]

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!

[OM805]

dlanorh,

All results have a ' before Handle ID as shown below. Results match in CSV and Excel.

Result:

'17E84 <---- Remove '

CSV:

Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID
100A CHASE | Void,3.74,8.67,A-AREA,Yes,'17EC5
101A CHASE | Void,12.94,15.98,A-AREA,Yes,'17EC3
102A CHASE | Void,13.78,14.90,A-AREA,Yes,'17E84   <---- Add error column and flag these types of error(s) as Yes

[dlanorh]

The problem is not with AutoCAD but with excel. AutoCAD doesn't see this "error" and writes the correct handle to CSV. I can try to find an autolisp  solution to flag what may be a problem, but it is shifting sands as the same problem could occur with "17E16" or "171E18" in fact anything that just has an E in it.

I don't know enough about excel to know if there is a setting that would stop this "translation" happening.

It would be easier to set the column format in excel to text then remove the ' in excel using an excel macro. Another alternative would be to convert the hexadecimal string in AutoCAD to a decimal integer, import that to excel and convert back to a hexadecimal using the DEC2HEX formula, this would require an extra column, a bit of work in excel, and hiding the decimal column.

[dlanorh]

Had a light bulb moment and wondered if I could construct a pattern string to check the handle string against using wcmatch. I have incorporated this into the previous rectangle lisp and renamed.

This has only been briefly tested against known data strings, so I might be missing something obvious. Try this against your polylines to see if it picks up potential errors. It should only precedes the handle with an apostrophe if it thinks it has the potential to produce an error.

[OM805]

dlanorh,

At this point, it does not save the CSV file.

Regards,

[dlanorh]

The csv file has the same name as the drawing. It is saved in the same directory as the drawing. If the file already exists it appends the data to that file. If it doesn't exist it creates it.

I have amended the attached code as I missed a conversion of perimeter to ft.

[OM805]

dlanorh,

My misunderstanding, I was expecting a save prompt.  Below is the data output.  Errors are being flagged as Yes. It looks like you did it. 🙂

Space Name/Class	Area (sqft)	Perimeter (ft)	Layer	Polyline Closed?	Object Handle ID	ID Error
100A CHASE | CBP Void	3.74	8.67	A-AREA	Yes	17EC5	No
101A CHASE | CBP Void	12.94	15.98	A-AREA	Yes	17EC3	No
102A CHASE | CBP Void	13.78	14.9	A-AREA	Yes	'17E84	Yes

P.S.  You are truly a Wizard.  Thank you!

Regards,

OM

[OM805]

dlanorh,

Thanks again... Here are few minor things that I noticed.

1. New CSV file will not override existing CSV file.
2. Anyway to get the new file to automatically open?

Regards,

OM

[OM805]

dlanorh,

Would it be possible to add an additional "Polyline Around Text?" column with a return of Yes or No?

Regards,

OM

[dlanorh]

First my apologies, I missed post 107. I will sort that out. With regards to the above, i'll have a look, but is this just the boundary polyline or does the text itself have a polyline boundary?

[OM805]

dlanorh,

No worries... I'm appreciative of all the help.

Noticed something on column A, the text sometimes comes in reverse.  Instead of Space Name/Class, it comes in as Space Class/Name per below. Space tags are identical besides the text of course.

Space Name/Class	Area (sqft)	Perimeter (ft)	Layer	Polyline Closed?	Object Handle ID	Error Found?
205A CHASE | VOID	89.56	39.44	A-AREA-OTLN	Yes	94A7	No
VOID | 200B CHASE	124.22	49	A-AREA-OTLN	Yes	947C	No

[OM805]

Also of note, original code does not seem to swap text around randomly.

Original Code:

(defun c:EPD (/ all_data area csv_file d data e ent i len openfile pts s ss sstext st)
					; Export Polyline Data
  ;;            pBe Sep 2018            
  ;;	https://www.cadtutor.net/forum/topic/71019-polyline-multi-textareaperimeterclosedhandle-id/
  (if (and (setq 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)
		   ;; RJP » 2019-01-17 added length to results
		   len	(vlax-curve-getdistatparam e (vlax-curve-getendparam 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
			len
			(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
	     "Space Name/Class,Area (sqft),Perimeter (ft),Layer,Polyline Closed?,Object Handle ID"
	     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) "")
		       ","
		       (strcat (rtos (caddr itm) 2 2) "")
		       ","
		       (cadddr itm)
		       ","
		       (cadddr (cdr itm))
		       ","
		       (last itm)
	       )
	       openfile
	     )
	   )
	   (close openfile)
	   (startapp "notepad" csv_file)
    )
  )
  (princ)
)

[dlanorh]

I am now totally confused as to exactly what you want?

What new columns do you want and what does the new column relate to; as "Polyline Around Text" column with a return of Yes or No? doesn't mean anything as I have no reference?

With reference to the "Space Name / Class" column, are these text items on different layers?

The swapping text randomly here isn't random, it is the order in which the text is found. Again I have no reference as to whether this text is on different layers. I could sort the text, but don't know the rules for sorting.

Do you want a new file each time or an option for a new file?

I am totally in the dark as I have no example drawing of what you are working with, perhaps supplying something saved as AutoCAD 2012 format or earlier could clear up some of this. 😁

[OM805]

dlanorh,

First off, apologies for the confusion.  Attached is example DWG and the XLSX output.  Included, in the XLSX is also an eventually tab showing what I would like to eventually see in the report.  Please, let me know if you have any further questions or concerns.

Regards,

OM

[dlanorh]

OK. That makes almost everything clear. The one problem is that the current lisp uses the polyline to find the text, so if there is no polyline it won't find "text without a polyline". Do you want to find text without a polyline?

A few other questions that may help

1. Are the text layers always the same?

2. Same question as 1 but for the polylines

3. Is the point entity always there?

4. If the answer to question 1 is "No", is the "Space" always above the "Class" ?

It's late here but I have a relatively clear day tomorrow, so can cobble something together then.

[OM805]

dlanorh,

Answers are below. Thanks in advance.

1. Do you want to find text without a polyline? Yes...
2. Are the text layers always the same? Yes...
3. Same question as 1 but for the polylines? Yes...
4. Is the point entity always there? No... Is there a benefit, if it was?
5. If the answer to question 1 is "No", is the "Space" always above the "Class" ? N/A

Regards,

OM

[dlanorh]

OK. try the attached, I think it covers everything and briefly tested against your example drawing. A few changes.

It creates a csv file with the same name and in the same directory as the drawing. If the file doesn't exist it will go straight to the selection.

If the file already exists you will be prompted that the file exists and asked if you want to append to this file, save as a New file or Overwtite the eisting file. Options are Append, New and Overwrite. Default is Append. If you select new a file dialog will pop up asking for the new file name and allowing you to change directories if you require.

Next comes the selection stage. This has been altered to select LWPolyline and Text, but only on the required layers. This was needed to enable Text without polylines to be found.

On completion the csv file is opened in Notepad.

[OM805]

dlanorh,

Took it through it's paces and...  It runs like a champ on my end; hitting all the marks. Excellent!  I could not even ask for more. You've out down yourself. Thank you so much!

Regards,

OM

[dlanorh]

That's great. 😁

[OM805]

dlanorh,

Ran a few more test... Runs great but, it does not seem to be flagging the "corrupt" Handle IDs and marking them as yes. I've attached an example DWG file containing the various test parameters; including a "corrupt" polyline. Report is also attached.

dlanorh wrote:

"Had a light bulb moment and wondered if I could construct a pattern string to check the handle string against using wcmatch. I have incorporated this into the previous rectangle lisp and renamed."

Apply Older Fix Example:

'17E84

Yes

Current Data:

Space Name/Class	Area (sqft)	Perimeter (ft)	Layer	Polyline Closed?	Object Handle ID	ID Error?	Polyline Around Text?
-	124.22	49	A-AREA-OTLN	Yes	BDDD	No	Yes	Good Row
Void | Polyline Not Closed	124.22	48.39	A-AREA-OTLN	No	BDDC	No	Yes	Good Row
Void | Normal	124.22	49	A-AREA-OTLN	Yes	BDD8	No	Yes	Good Row
Void | Polyline has ID error	256	69.33	A-AREA-OTLN	Yes	6.50E+10	No	Yes	Bad Row
Admin Circulation | No Polyline Around Text							No	Good Row
					Note: Bad ID	Note: Should be Yes

[OM805]

Figured it out...  Edit is below.

(setq hpat "##E#" hstr (strcase hstr)) ;;"#E##"

Thank you!

OM