Lisp to export polyline data (area, length and inner text) to excel

[sajid727]

In the attached Lisp which i found in this site, on selecting closed polylines, the area, length and inner text is exported from autocad to excel. But the problem is that the excel file can only be updated when it is closed. If i keep the excel file open, while selecting polyline in autocad drawing, it gives error and the result area and length will not be updated in excel sheet. Is there a solution where i can keep both the excel and autocad files open while working.

And the other problem is that the generated excel file is in .csv format, but i need in .xlsx format.

I will appreciate if anybody can please help me with this issue.

Thanks.

[dbroad]

The lisp exports data to a CSV file, not to Excel.  It can only do that if it's not open, as you've discovered.

You'd have to gain access to Excel via something like:
(setq exl (vlax-get-or-create-object "Excel.Application"))

You would also need to be very familiar with the properties and methods exposed by Excel.  If you only want a one way export, stick with your simple approach.

[Sea-Haven]

The direct to excel can be done, BUT there is a few issues that you have to look at.

is Excel open if not open a new Excel

is Excel open, yes, is it correct excel file, part 2 is it correct worksheet

if open where do you start entering data A1 or add to existing.

Dbroad is correct. In my excel lisp there are 23 defuns each with a separate task like get & put cell are 2 defuns.

(or (setq myxl (vlax-get-object "Excel.Application"))
    (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)

You hint to write to an existing open excel, yes can be done experienced lisp coders could use what I have but for general users you may get lost, happy to discuss doing same as lisp but direct, so could do a pick multiple plines close dwg open again and continue but would need some custom coding. Just PM me.

[sajid727]

Thanks for the useful information.

[sajid727]

Thankyou for your reply. I am new to lisp. Can you please advice if I need to add these below lines you suggested to the lisp file-

(or (setq myxl (vlax-get-object "Excel.Application"))
    (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)

[Sea-Haven]

The "or" does just that 1st line is "Excel is open is true" now we get into the complicated part is it correct file ? Is it correct worksheet. If not correct file then open that file. If you look at Getexcel.lsp where I started my code from it does multiple if's checking Excel status. 

If correct file what is last row number as want to add more details. Again somewhere in code need a is Excel cells empty ? So that means start at cell A1.

That is why in what I use has 4 different defuns to handle certain scenarios and I know that I need to improve the testing is EXCEL there. Yes on my to do list to redo the Excel check.

A lot of the answers come from excel VBA examples rewritten into VL.

Command: (setq myxl (vlax-get-object "Excel.Application"))
nil so no Excel was open

Command: (setq myxl (vlax-get-or-create-object "excel.Application"))
#<VLA-OBJECT _Application 000001fcc20e2488>
Excel link established can run in non display mode.

I will add to my to do list but its getting longer, real work gets in the way.

[sajid727]

Thanks for your help. I really appreciate it. 

I came across one interesting lisp which unfortunately i am not able to attach here (pkarea). It is available online. Using this, both the excel and autocad files can be opened at the same time. But the drawback is that it is only for area calculation and further that the selection of polyline is not easy and it selects the area within the grid lines in the drawing and not the actual room area. 

I would like to have room description, area and length information in the excel table.

[Sea-Haven]

I would like to have room description, area and length information in the excel table.

You need to provide a sample dwg showing CLEARLY where these items are coming from length & width is ok if always the room shape is a rectang.

[sajid727]

Please find attached autocad drawing as requested. Thank you for your co-operation.

[CADaSchtroumpf]

With your DWG, you can try this:

First apply this code for making accord with your drawing:

(vl-load-com)
(defun c:ROOM_DATA ( / AcDoc Space old_lay sel n ent vla_obj pr lst_pt ss)
  (setq
    AcDoc (vla-get-ActiveDocument (vlax-get-acad-object))
    Space
    (if (= 1 (getvar "CVPORT"))
      (vla-get-PaperSpace AcDoc)
      (vla-get-ModelSpace AcDoc)
    )
  )
  (cond
    ((null (tblsearch "LAYER" "AREA_ROOM"))
      (vlax-put (vla-add (vla-get-layers AcDoc) "AREA_ROOM") 'color 3)
    )
  )
  (setq old_lay (getvar "CLAYER"))
  (setvar "CLAYER" "AREA_ROOM")
  (setq sel (ssget '((0 . "LWPOLYLINE") (8 . "TEXT") (70 . 1))))
  (cond
    (sel
      (repeat (setq n (sslength sel))
        (setq
          ent (ssname sel (setq n (1- n)))
          vla_obj (vlax-ename->vla-object ent)
          lst_pt nil
          pr -1
        )
        (repeat (fix (vlax-curve-getEndParam vla_obj))
          (setq
            pr (1+ pr)
            lst_pt (cons (trans (vlax-curve-GetPointAtParam vla_obj pr) 0 1) lst_pt)
          )
        )
        (setq lst_pt (cons (trans (vlax-curve-GetPointAtParam vla_obj (1+ pr)) 0 1) lst_pt))
        (setq ss (ssget "_WP" lst_pt '((0 . "TEXT") (8 . "-Text"))))
        (vlax-put-property vla_obj "Layer" "AREA_ROOM")
        (vlax-ldata-put vla_obj "ROOM_NAME" (if ss (cdr (assoc 1 (entget (ssname ss 0)))) ""))
        (vlax-ldata-put vla_obj "ROOM_AREA" (vlax-get-property vla_obj "Area"))
        (vlax-ldata-put vla_obj "ROOM_PERIMETER" (vlax-get-property vla_obj "Length"))
        (princ "\nPolyline have this data:")
        (print (vlax-ldata-list vla_obj))
      )
    )
  )
  (setvar "CLAYER" old_lay)
  (prin1)
)

And after, this for export to excel

(defun c:ROOM_DATA_OUT ( / sel file_output doc xls wks column count nb ename k_list count)
  (setq
    sel (ssget "_X" '((0 . "LWPOLYLINE") (8 . "AREA_ROOM") (70 . 1)))
    file_output (strcat (getvar "DWGPREFIX") (substr (getvar "DWGNAME") 1 (- (strlen (getvar "DWGNAME")) 4)) ".xlsm")
  )
  (cond
    ((and sel file_output)
      (setq doc (vla-get-activedocument (vlax-get-acad-object)))
      (vla-startundomark doc)
      (setq xls (vlax-get-or-create-object "Excel.Application"))
      (vlax-invoke (vlax-get xls 'workbooks) 'Add)
      (vlax-put xls 'Visible :vlax-true)
      (setq
        wks (vlax-get xls 'ActiveSheet)
        column 65
        count 1
      )
      (vlax-put (vlax-get-property wks 'range (strcat "A1:E" (itoa (sslength sel)))) 'Cells "")
      (foreach el '("HANDLE" "ROOM_NAME" "ROOM_PERIMETER" "ROOM_AREA")
        (vlax-put (vlax-get-property wks 'range (strcat (chr column) (itoa count))) 'value el)
        (setq column (1+ column))
      )
      (repeat (setq nb (sslength sel))
        (setq
          ename (ssname sel (setq nb (1- nb)))
          k_list (list (cons "HANDLE" (strcat "'" (cdr (assoc 5 (entget ename))))))
        )
        (foreach el '("ROOM_NAME" "ROOM_PERIMETER" "ROOM_AREA")
          (setq k_list (cons (cons el (vlax-ldata-get ename el)) k_list))
        )
        (setq
          count (1+ count)
          column 65
        )
        (foreach el (reverse (mapcar 'cdr k_list))
          (vlax-put (vlax-get-property wks 'range (strcat (chr column) (itoa count))) 'value el)
          (setq
            column (1+ column)
          )
        )
        (setq k_list nil)
      )
      (mapcar 'vlax-release-object (list wks xls))
      (gc)
      (vla-endundomark doc)
    )
  )
  (prin1)
)

[sajid727]

Thanks CADaSchtroumpf,

Its working fine, but the only problem is that, after excel sheet generation using ROOM_DATA_OUT command, whenever i want to add new polylines, everytime a new excel sheet is generated. I need all the data to be updated in one single excel sheet.

[sajid727]

Hi CADaSchtroumpf,

Thanks for your help. Hope you must have seen my previous reply. I would like to request you again, if you can please resolve the issue that after getting result in excel sheet (A) using ROOM_DATA_OUT command, when i select new polylines in the same drawing, the data is exported to a new excel sheet (B).

Waiting eagerly for your lisp which will be very useful for me.

[CADaSchtroumpf]

---

@sajid727  a écrit :

Hi CADaSchtroumpf,

Thanks for your help. Hope you must have seen my previous reply. I would like to request you again, if you can please resolve the issue that after getting result in excel sheet (A) using ROOM_DATA_OUT command, when i select new polylines in the same drawing, the data is exported to a new excel sheet (B).

Waiting eagerly for your lisp which will be very useful for me.

---

@sajid727 

Sorry but I don't know how to do it in the current state, it goes beyond my skills in using external applications and that's why you don't have an answer from me.

What blocks me is that the excel application is open, the data entered but the file is not saved.

In the lisp code (at the end) I followed a recommended procedure, namely:

(mapcar 'vlax-release-object (list wks xls))
(gc)

This is to release the excel application and clean the memory so as not to have a slowdown in Autocad.
Therefore it is impossible for me to re-access this unsaved excel file because the links are lost.
A new call to the procedure will make a new file.

For you, it's simpler for me, during a second call, to copy and paste the entire resulting sheet into a new sheet of the first file.

If anyone else can provide you with a solution, I'd love it for you. It may be possible but I don't know how, because I think the code will still be much more complex.

I still attach the original files that I used to adapt my answer that I advise you to use (but the problem will always be the same)

[Sea-Haven]

A new call to the procedure will make a new file.

If you have released the application you should be able to recognise that excel is already open.

(or (setq myxl (vlax-get-object "Excel.Application"))
    (setq myxl (vlax-get-or-create-object "excel.Application"))
)

...........

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

So if you use (setq myxl (vlax-get-object "Excel.Application") it will return nil if excel is not open

(setq myxl (vlax-get-or-create-object "excel.Application")) will get the application and open excel, you may not see anything as you need also to make excel visible. Yes can get and put an invisible excel.

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

 !myxl
nil so no excel link.

[sajid727]

I appreciate your efforts CADaSchtroumpf. Thanks a lot for your support, your skills are great. Even though we were not able to achieve 100% solution, the lisp you provided is helpful for me.

I would like to suggest if you could please have a look at lisp 'pkarea' available in CAD Forum (https://www.cadforum.cz/en/pkarea-acreage-of-closed-areas-from-dwg-to-excel-tip12350), maybe it will be useful for the solution needed.

[sajid727]

Hi, Can anyone help me with the solution to export area, length of polylines from autocad drawing to excel (.xlsx format).

[sajid727]

Hi CADaSchtroumpf,

Thanks for your help. Its working great. I need one help. Is it possible to change colour of the polyline once we select the polyline, so that i dont select the same polyline twice.

[ec-cad]

Rather than change color, just get the handle and see if it's already been done.

i.e.

You already get the 'Handle' value (dxf 5) of the ename.
As in: (setq handle (cdr (assoc 5 (entget ename))))
Add it to a handles_lst as you loop through each pline.
(setq handles_lst (cons handle handles_lst))
And next item in selection set, get the handle,
(if (not (member handle handles_lst)); continue loop
(progn
..... gather this one's data
..... output to Excel
); progn
); if

ECCAD

[m_danishJLQR8]

Amazing routine, can you pls add scale factor for export area and perimeter from mm drawing to meter value (data)

[m_danishJLQR8]

Amazing routine, can you pls add scale factor for export area and perimeter from mm drawing to meter value (data)