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!

[Sea-Haven]

@pbejse has you covered but posting a desired Excel would be better as it can have the correct header row. Or make sure its in your CSV.

Just a PS I write direct to Excel or Libre Calc no need for CSV.

[jwylie2020]

Hi @pbejse excel is excellent. Thanks for your interest.

See attached excel table with headers and example dwg file. This will be used for civil road files but I think it will be generic enough for lots of uses. Note units for the drawings are in decimal feet.

The headers are: LAYER, TOTAL AREA (SF), TOTAL AREA (SY), TOTAL LENGTH (LF), COUNT (EA)

The areas are in square feet by default. SY (sf/9) would be ideal but obviously it could be done later on in the spreadsheet.

Another thing I thought would be great it if block count was also done. Total areas and length would say "0" if possible. Another option would be to just place a donut polyline on items you want to count. This way you get the count.

In reality only 1 of the columns will be needed for each item quantified but it seems like it is simple enough to have the ability to select all these items then get all the data and choose what you need.

Thanks for the help. JL 

[Sea-Haven]

Count the blocks can be done also, as you just look at an objects name.

How would you determine which blocks to count ? Looking at dwg only layers ON ? I would add a column "Object" name or the blocks can be added below the other details with a different header row. Please confirm.

[jwylie2020]

I was thinking to count any block in the selection. The count could be in the same total count as the selected polylines or add another excel column "COUNT BLOCK (EA)". Whatever is easier. The block name could be QTY or COUNT.

Thank you

JL

[pbejse]

---

@jwylie2020 wrote:

The areas are in square feet by default. SY (sf/9) would be ideal but obviously it could be done later on in the spreadsheet.

---

Can you elaborate on this 

---

@jwylie2020 wrote:

 Another option would be to just place a donut polyline on items you want to count. This way you get the count.

In reality only 1 of the columns will be needed for each item quantified but it seems like it is simple enough to have the ability to select all these items then get all the data and choose what you need.

---

Also this part? you mean identify the blocks you wanted to be included in the count?

---

@jwylie2020 wrote:

Hi @pbejse excel is excellent. Thanks for your interest.

Excel is indeed excellent ( I see what you did there 🙂 ), I personally exporting to CSV, this way, any other users without excel application can still use the program, for now we will write the code for CSV. 

Perhaps @Sea-Haven  can amend it for Excel format.

I'll post the code in a bit

[jwylie2020]

I have been able to put this together with good results. After selection, the result is a CSV file with 5 columns. LAYER, TOTAL AREA (SF), TOTAL LENGTH (LF), PL COUNT (EA), BLOCK COUNT (EA).

This looks sufficient. The improvements I see would be to save as an xlsx file and open excel at the end.

Note: blocks count are any blocks per layer. ChatGPT asked if would like it the block name listed and counted but I do not know how that could be organized in the spreadsheet.
The way I see people using this is to get the areas and count and copy and paste the cells to another spreadsheet with workbooks that are more formatted and lookup values from this raw data.

Thanks

JL

[Sea-Haven]

Something like this is pretty straight forward

Can do a question output to, Table, csv, Excel or all. etc

 CSV or Excel is just use write-line to a file or write to a Excel cell. one location in code. Plus header.

[jwylie2020]

Nice! I can see that working well. The layer heading would be changed to layer / block.

It would be good if selecting excel would automatically open the excel with the data.

I assume your idea would just place an autocad table in cad when done. CSV could let the user pick a save location.

This all would be very useful and versatile for many uses.

Thanks

JL

[Sea-Haven]

To explain a little more you can talk both ways between Excel and a CAD program Autocad & Bricscacd probably more, you can open an existing Excel file or just start a new one, so you just fill in all the correct cells. Like wise Excel can draw objects in a CAD program.

To write a Excel XLS file as you suggested is feasible if you know how to look in an Excel file,  but it is way more complicated than putting a value into a cell. Like wise I have a send a CAD table to an excel.

You can do extra stuff like set column widths in Excel, set back ground colors, insert a block image in a cell and much more.

Just a ps you can also talk to Word, use an Access data base.

[jwylie2020]

Ok. I think ultimately users will need Excel for estimates and bid tab simpler. So a csv would work if that is easier than excel.

An autocad table would be good for sheets that require site summary areas for buildings, pavement, yard. These tables are simplier.

Thank

[Sea-Haven]

To go to Excel requires a few lines of code to open Excel, instead of writing a line of multiple values to CSV you just write to cells in the Excel its about the same amount of work re coding.

I have added to my "to do list" your Qtoexport.lsp to see if can add the Excel part.

[jwylie2020]

Thanks. I tried the other day I could only get excel to open an empty file.

[Sea-Haven]

Give this a try, dont have Excel open. 

; https://forums.autodesk.com/t5/visual-lisp-autolisp-and-general/export-polyline-data-area-layer-and-inner-text-to-excel/td-p/8254926/page/9
; Qtoexport.lsp
; Original concept by PBE
; send to Excel added by AlanH


(defun c:QTOEXPORT ( / sel i ent obj layerData layer area length blkCount outFile f blkName )

;;	Thanks to fixo			;;
;;   = Set Excel cell text =    ;;
;;				;;
(defun xlsetcelltext ( row column text)
(setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
  (vl-catch-all-apply
    'vlax-put-property
    (list cells 'Item row column
	(vlax-make-variant (vl-princ-to-string text) vlax-vbstring)))
)


(defun doexcel ( / )
; based on the functions of Getexcel.lsp by Terry Miller 
; Additional or original functions have authors name.

;;	Thanks to fixo			;;
;;   = Set Excel cell text =    ;;
;;				;;
(defun xlsetcelltext ( row column text)
  (setq cells (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Cells"))
  (vl-catch-all-apply
  'vlax-put-property
  (list cells 'Item row column
  (vlax-make-variant (vl-princ-to-string text) vlax-vbstring)))
)

(princ "\nOpening Excel ")
(setq myxl1 (vlax-get-object "Excel.Application"))
(if (= myxl1 nil)
 (progn
  (setq myxl (vlax-get-or-create-object "excel.Application"))
  (vla-put-visible myXL :vlax-true)
  (vlax-put-property myxl 'ScreenUpdating :vlax-true)
  (vlax-put-property myXL 'DisplayAlerts :vlax-true)
  (vlax-invoke-method (vlax-get-property myXL 'WorkBooks) 'Add) ; opens a new xl
 )
 (setq myxl myxl1)
)

(setq lst '("Layer" "TOTAL AREA (SF)" "TOTAL LENGTH (LF)" "PL COUNT (EA)" "BLOCK COUNT (EA)"))
(setq row 1 col 0)
(foreach cellval lst
(setq col (1+ col))
  (xlsetcelltext row col cellval)
)
(vlax-put (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "A1") 'columnwidth 30)
(vlax-put (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "B1") 'columnwidth 16)
(vlax-put (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "C1") 'columnwidth 16)
(vlax-put (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "D1") 'columnwidth 16)
(vlax-put (vlax-get-property  (vlax-get-property myxl "ActiveSheet") "Range" "E1") 'columnwidth 16)
         ;; Write each line
(setq row 2)
(foreach entry layerData
  (xlsetcelltext row 1 (nth 0 entry))
  (xlsetcelltext row 2 (rtos (nth 1 entry) 2 3))
  (xlsetcelltext row 3 (rtos (nth 2 entry) 2 3))
  (xlsetcelltext row 4 (itoa (nth 3 entry)))
  (xlsetcelltext row 5 (itoa (nth 4 entry)))
  (setq row (1+ row))
)

(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))

(princ "\nData exported to: Excel ")

(princ)
)

(defun docsv ( / f outfile)
     ;; Ask for file path
      (setq outFile (getfiled "Save CSV File As" "QTO-EXPORT.csv" "csv" 1))

      (if outFile
        (progn
          (setq f (open outFile "w"))
          (write-line "Layer,TOTAL AREA (SF),TOTAL LENGTH (LF),PL COUNT (EA),BLOCK COUNT (EA)" f)
          ;; Write each line
          (foreach entry layerData
            (write-line
              (strcat
			  (nth 0 entry) ","
              (rtos (nth 1 entry) 2 3) ","
              (rtos (nth 2 entry) 2 3) ","
              (itoa (nth 3 entry)) ","
              (itoa (nth 4 entry)))
              f)
          )
          (close f)
          (prompt (strcat "\nData exported to: " outFile))
        )
        (prompt "\nExport canceled.")
      )
(princ)
)
	  
	  
  ;; Get property from VLA object
  (defun get-prop (ent prop)
    (vlax-get ent prop)
  )

  ;; Update polyline data for the layer
  (defun update-layer-data (layer area length)
    (if (assoc layer layerData)
      (progn
        (setq layerData
          (subst
            (list layer
                  (+ (nth 1 (assoc layer layerData)) area)
                  (+ (nth 2 (assoc layer layerData)) length)
                  (nth 3 (assoc layer layerData)) ; count unchanged
                  (nth 4 (assoc layer layerData))) ; block count unchanged
            (assoc layer layerData)
            layerData))
      )
      (setq layerData (cons (list layer area length 0 0) layerData)) ; init with zero counts
    )
  )

  ;; Increment polyline count
  (defun inc-polyline-count (layer)
    (if (assoc layer layerData)
      (setq layerData
        (subst
          (list layer
                (nth 1 (assoc layer layerData))
                (nth 2 (assoc layer layerData))
                (1+ (nth 3 (assoc layer layerData)))
                (nth 4 (assoc layer layerData)))
          (assoc layer layerData)
          layerData))
    )
  )

  ;; Increment block count
  (defun inc-block-count (layer)
    (if (assoc layer layerData)
      (setq layerData
        (subst
          (list layer
                (nth 1 (assoc layer layerData))
                (nth 2 (assoc layer layerData))
                (nth 3 (assoc layer layerData))
                (1+ (nth 4 (assoc layer layerData))))
          (assoc layer layerData)
          layerData))
      ;; if layer not yet in list, add with zero area/length/poly count but 1 block count
      (setq layerData (cons (list layer 0.0 0.0 0 1) layerData))
    )
  )

  ;; Prompt for selection
  (prompt "\nSelect polylines and blocks:")
  (setq sel (ssget '((0 . "LWPOLYLINE,INSERT"))))

  (if sel
    (progn
      (setq i 0
            layerData '()
      )
      ;; Loop through selection
      (while (< i (sslength sel))
        (setq ent (ssname sel i)
              obj (vlax-ename->vla-object ent)
              type (vla-get-objectname obj)
              layer (get-prop obj 'Layer)
        )
        (cond
          ;; If it's a polyline
          ((or (eq type "AcDbPolyline") (eq type "AcDb2dPolyline"))
            (setq area (if (= (vla-get-Closed obj) :vlax-true)
                           (get-prop obj 'Area)
                           0.0)
                  length (get-prop obj 'Length)
            )
            (update-layer-data layer area length)
            (inc-polyline-count layer)
          )
          ;; If it's a block reference
          ((eq type "AcDbBlockReference")
            (inc-block-count layer)
          )
        )
        (setq i (1+ i))
      )
)
    (prompt "\nNo objects selected.")
  )

(initget 1 "Excel Csv")
(if (= (getkword "\nExcel or Csv  ? (E / C) >") "Excel")
  (doexcel)
  (docsv)
)

  (princ)
)

[jwylie2020]

@Sea-Haven Selecting C for CSV file works well. Selecting E for excel give this error. "Opening Excel ; error: bad argument type: VLA-OBJECT nil"

[Sea-Haven]

Ok I have to reset the MYXL variable if you run the code twice in a current session it will error. I will try to fix. It should reset as part of the Excel code, not sure why not working. 

[jwylie2020]

Honestly the version that just save the spreadsheet as a csv file is very sufficient. All the user has to do is open the file and copy and paste to the xl file. Thanks for the effort and all your help.

JL

[Sea-Haven]

It works every time when I use it only once. So will solve for future reference.

A comment when you get the blocks it should return the Layer, block name and count as separate items per layer. I do have a get blocks and sort &  count by block name including by Atrributes up to 5 attributes deep.

[jwylie2020]

Hi @Sea-Haven, I messed around with it more and got it to write to excel and open the file. This works well for my purposes. Thank you for the help.

Here is an image of the result.

[Sea-Haven]

Gald you got it working. 🏆

[jwylie2020]

Thanks, you definitely helped. Now I am looking for the best way to take the excel data and further filter it to apply formulas.