Model Grouping & Data Extraction - Zone Name & Areas

[Jake_PCH]

Dear Autodesk Community,

Being new to LISP creation, I am at this moment in time limited to trialing the various LISPs posted within related forum threads. As I am yet to find the relevant LISP or 'simplified' guidance, I am hoping to find some guidance here.

I am looking for an efficient way of (1) associating the hatches and text, and (2) extracting meta data in table form - ie. Area, quantity & Zone Name, from the attached 'School Zones' DWG.

The most relevant thread I could find is the following, of which I have attached the most relevant LISP for;

Solved: A lisp file that can read the total area of each hatch and .... - Autodesk Community - AutoC...

Any help much appreciated!

All the best,

Jake

[JBerns]

@Jake_PCH,

Thank you for supplying a sample drawing.

I think the tool or features you need may be found in AutoCAD Architecture.

It offers space and zone planning tools.

Please see this video.

https://www.youtube.com/watch?v=LiqIRHXvReI 

If you are an educator or have one of the Autodesk Collection subscriptions, I think you have access to the Autodesk Toolset, which includes AutoCAD Architecture.

Let us know if you want to pursue the Architecture solution or continue on your goal to achieve the info through an AutoLISP utility.

I hope this has been helpful. We look forward to your response.

Regards,

Jerry

[CADaSchtroumpf]

Hi,

With your drawing it seems to work, if you are interested...

(vl-load-com)
(defun WriteExcel (data / xlApp wBook cells i j)
  (setq
    xlApp (vlax-create-object "Excel.Application")
    wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Add)
    cells (vlax-get-property xlApp 'Cells)
    i 0
  )
  (foreach row data
    (setq i (1+ i) j 0)
    (foreach val row
      (setq
        j (1+ j)
        cell (vlax-variant-value (vlax-get-property cells 'Item i j))
      )
      (vlax-put-property cell 'Value2 val)
    )
  )
  (vlax-invoke-method
    (vlax-get-property
      (vlax-get-property xlApp 'ActiveSheet)
      'Columns
    )
    'AutoFit
  )
  (vlax-put-Property xlApp 'Visible :vlax-true)
)
(defun c:test ( / ss_h n ent dxf_ent loops nb pt_list ss_t txt area data)
(setq data nil)
  (cond
    ((setq ss_h (ssget "_X" '((0 . "HATCH") (67 . 0) (410 . "Model") (8 . "PCH- ROOM AREA COLOUR"))))
      (repeat (setq n (sslength ss_h))
        (setq
          ent (ssname ss_h (setq n (1- n)))
          dxf_ent (entget ent)
          loops (cdr (assoc 91 dxf_ent))
        )
        (repeat loops
          (setq
            dxf_ent (member (assoc 92 dxf_ent) dxf_ent)
            nb (cdr (assoc 93 dxf_ent))
            pt_list nil
          )
          (repeat nb
            (setq
              dxf_ent (member (assoc 10 (cdr dxf_ent)) dxf_ent)
              pt_list (cons (trans (cdr (assoc 10 dxf_ent)) 0 1) pt_list)
            )
          )
          (cond
            ((setq ss_t (ssget "_WP" pt_list '((0 . "MTEXT") (67 . 0) (410 . "Model") (8 . "PCH- ROOM NAMES"))))
              (setq
                txt (cdr (assoc 1 (entget (ssname ss_t 0))))
                area (vla-get-Area (vlax-ename->vla-object ent))
                data (cons (list area txt) data)
              )
            )
          )
        )
      )
      (if data
        (WriteExcel
          (cons
            (list "Area quantity" "Zone Name")
            (vl-sort data '(lambda (e1 e2) (< (cadr e1) (cadr e2))))
          )
        )
      )
    )
  )
)

[Sea-Haven]

My suggestion would be a similar answer but maybe a slight simplification, find the room text labels can use that text point to find Hatch. So no need for the hatch boundary.

(ssget pt '((0 . "HATCH"))) can add layers etc.

Did notice "BREAK OUT AREA" is twice and Hallway has no description.

[Jake_PCH]

Thank you all for your replies;

@JBerns , thank you for informing me of the Autocad Architecture functionality but I would like to explore solving this through Autocad if possible, as Autocad Architecture is not part of my current company's work flow.

@CADaSchtroumpf - Thank you for sending a the LSP command, however while I can load it, I do not appear to be able to run it. Do you know why this may be? I simply copied the code into Notepad and saved as a .lsp file with ANSI coding - see attached.

Kind regards,

Jake

[JBerns]

@Jake_PCH 

You're welcome, Jake.

How are you trying to run the code? Are you dropping the saved LSP file into the AutoCAD graphics window? Are you using APPLOAD to load the LSP file? Something else?

[Jake_PCH]

I used the APPLOAD command and I am receiving a notification to confirm that it has successfully loaded. However, when I try to type the name of the file to run the LSP, like other LSP's, it does not register.

[CADaSchtroumpf · Accepted solution]

The code I provided you basically only works with the drawing you gave.
So that it possibly works in other drawings, I added a few lines to obtain data (layers to process)
This code should be working...
NB: The solid hatch pattern is not easy to select: try on the edges.

[JBerns]

If you look at the code on line 28, the command name to type is, HatchArea2Excel.
Filename and command name do not always match.
Try that and let us know.

[Jake_PCH]

Thank you all - it is working perfectly.

To wrap this up with a bow, is it possible to apply the following excel field formatting through the LSP;

1. Apply unit conversion factor of 0.1⁻⁶ to area fields.

2. Sort by area name and calculate SUM area of areas with equal names.

Jake

[Sea-Haven]

You divide the area in the lisp code so mm2 becomes M2. (* area 0.0000001)

[Jake_PCH]

Apologies for the lack of knowledge on this, I hope to acquire it soon!

However, for not, where in the code would I apply this multiplication?

[Sea-Haven]

If you walk through the code line by line you will see the word Area at line 54

area (vla-get-Area (vlax-ename->vla-object ent))

So you put your conversion factor at that line have a go. Hint (* is 1st 

[Jake_PCH]

Hi Sea Haven,

Thank you for your guidance & encouragement to try myself, however I am not having any luck. The following combinations either cause the area field in excel to not populate or the LSP to not run.

area (vla-get-Area (vlax-ename->vla-object ent)*0.000001) - "Too many actual parameters"

area (vla-get-(Area*0.000001) (vlax-ename->vla-object ent)) - "Bad function"

area (vla-get-Area (vlax-ename->vla-object ent))(*0.000001) - "Bad function"

area (vla-get-Area (vlax-ename->vla-object ent)(*0.000001)) - "No function definition *0"

area ((*0.000001) vla-get-Area (vlax-ename->vla-object ent))  - "No function definition *0"

(*0.000001) area (vla-get-Area (vlax-ename->vla-object ent)) - "No function definition *0"

Do I need to provide a definition for the multiplication earlier in the code?

[JBerns · Accepted solution]

@Jake_PCH ,

Try this:

area (* (vla-get-Area (vlax-ename->vla-object ent)) 0.000001)

Area is getting the result of multiplying the calculated area by 0.000001.

In AutoLISP, the function is first. This includes addition, subtraction, etc.

See examples here:

Arithmetic Functions Reference (AutoLISP) 

[Jake_PCH]

Perfect - thank you for your assistance and introducing me to the power of AutoLISP.

One final question to aid me on my journey of learning AutoLISP - Is it possible to format excel cells through the LSP code? ie. Turn sort by name, add cell colour to field values. If it is, are there any recommended learning resources? I'd love to add this functionality when I get a chance!

[JBerns]

@Jake_PCH,

I think you should award @CADaSchtroumpf with the solution. I just explained how to factor the area.

Regarding the format of Excel cells, I would think that would best be done with Excel VBA code inside Excel.

Perhaps others on this thread have recommendations for formatting the cells.

Regards,

Jerry 

[Jake_PCH]

@JBerns , 

You're quite right - I've now accepted his response as the solution. Thank you @CADaSchtroumpf ! 😊

[Sea-Haven]

The isue with changing stuff in Excel via CAD is working out the property to reset or maybe add if you manage to look at a Excel cell its like a dim so many properties. 

(vlax-get-property cells "Item" 5 2)

So we could look at a cell properties using (vlax-dump-object, so for a Excel cell B5 I got 110 properties. So with a bit of googling could find set background colour. 

There was a great contributor FIXO who did a lot of Cad <-> Excel stuff but he has passed away.

;; = Set Range Background color = ;;
;;				;;
(defun xlsetrangebgcolor (xlapp xlrange bgcolor / xlinter )