CAD Software Forum

I want to export 2d data into excel for use with a laser cutter to calculate the time to cut out a shape.

I need the perimeter data of all the circles, squares and random shapes on a drawing exported to excel

It tried the code below, but it does not seem to be working I am using Intellicad 6.4.

When I try to save the routine in notepad it says characters in unicode, I tried saving as unicode but in Intellicad I get nil at the command line, can anyone help to convert this for Intellicad please?

The following routine, Exports perimeter Polyline, Circle, Ellipse and Region.
If random shapes is not polyline, convert it to region or polyline

;|================== XLS ========================================
* Purpose: Export of the list of data Data-list in Excell
* It is exported to a new leaf of the current book.
If the book is not present, it is created
* Arguments:
Data-list - The list of lists of data (LIST)
((Value1 Value2 ... VlalueN)(Value1 Value2 ... VlalueN)...)
Each list of a kind (Value1 Value2... VlalueN) enters the name in
a separate line in corresponding columns (Value1-A Value2-B and .т.д.)
header - The list (LIST) headings or nil a kind (" Signature A " " Signature B "...)
If header nil, is accepted ("X" "Y" "Z")
Colhide - The list of alphabetic names of columns to hide or nil - to not hide ("A" "C" "D") - to hide columns A, C, D
Name_list - The name of a new leaf of the active book or nil - is not present
* Return: nil
* Usage
(xls '((1.1 1.2 1.3 1.4)(2.1 2.2 2.3 2.4)(3.1 3.2 3.3 3.4)) '("Col1" "Col2" "Col3" "Col4") '("B") "test") |;
(vl-load-com)
(defun xls ( Data-list header Colhide Name_list / *aplexcel* *books-colection* Currsep
*excell-cells* *new-book* *sheet#1* *sheet-collection* col iz_listo row cell cols)
(defun Letter (N / Res TMP)(setq Res "")(while (> N 0)(setq TMP (rem N 26)
TMP (if (zerop TMP)(setq N (1- N) TMP 26) TMP)
Res (strcat (chr (+ 64 TMP)) Res) N (/ N 26))) Res)
(if (null Name_list)(setq Name_list ""))
(setq *AplExcel* (vlax-get-or-create-object "Excel.Application"))
(if (setq *New-Book* (vlax-get-property *AplExcel* "ActiveWorkbook"))
(setq *Books-Colection* (vlax-get-property *AplExcel* "Workbooks")
*Sheet-Collection* (vlax-get-property *New-Book* "Sheets")
*Sheet#1* (vlax-invoke-method *Sheet-Collection* "Add"))
(setq *Books-Colection* (vlax-get-property *AplExcel* "Workbooks")
*New-Book* (vlax-invoke-method *Books-Colection* "Add")
*Sheet-Collection* (vlax-get-property *New-Book* "Sheets")
*Sheet#1* (vlax-get-property *Sheet-Collection* "Item" 1)))
(setq *excell-cells* (vlax-get-property *Sheet#1* "Cells"))
(setq Name_list (if (= Name_list "")
(vl-filename-base(getvar "DWGNAME"))
(strcat (vl-filename-base(getvar "DWGNAME")) "&" Name_list))
col 0 cols nil)
(if (> (strlen Name_list) 26)
(setq Name_list (strcat (substr Name_list 1 10) "..." (substr Name_list (- (strlen Name_list) 13) 14))))
(vlax-for sh *Sheet-Collection* (setq cols (cons (strcase(vlax-get-property sh 'Name)) cols)))
(setq row Name_list)
(while (member (strcase row) cols)(setq row (strcat Name_list " (" (itoa(setq col (1+ col)))")")))
(setq Name_list row)
(vlax-put-property *Sheet#1* 'Name Name_list)
(setq Currsep (vlax-get-property *AplExcel* "UseSystemSeparators"))
(vlax-put-property *AplExcel* "UseSystemSeparators" :vlax-false) ;_не использовать системные установки
(vlax-put-property *AplExcel* "DecimalSeparator" ".") ;_разделитель дробной и целой части
(vlax-put-property *AplExcel* "ThousandsSeparator" " ") ;_разделитель тысячей
(vla-put-visible *AplExcel* :vlax-true)(setq row 1 col 1)
(if (null header)(setq header '("X" "Y" "Z")))
(repeat (length header)(vlax-put-property *excell-cells* "Item" row col
(vl-princ-to-string (nth (1- col) header)))(setq col (1+ col)))(setq row 2 col 1)
(repeat (length Data-list)(setq iz_listo (car Data-list))(repeat (length iz_listo)
(vlax-put-property *excell-cells* "Item" row col (vl-princ-to-string (car iz_listo)))
(setq iz_listo (cdr iz_listo) col (1+ col)))(setq Data-list (cdr Data-list))(setq col 1 row (1+ row)))
(setq col (1+(length header)) row (1+ row))
(setq cell (vlax-variant-value (vlax-invoke-method *Sheet#1* "Evaluate"
(strcat "A1:" (letter col)(itoa row))))) ;_ end of setq
(setq cols (vlax-get-property cell 'Columns))
(vlax-invoke-method cols 'Autofit)
(vlax-release-object cols)(vlax-release-object cell)
(foreach item ColHide (if (numberp item)(setq item (letter item)))
(setq cell (vlax-variant-value (vlax-invoke-method *Sheet#1* "Evaluate"
(strcat item "1:" item "1"))))
(setq cols (vlax-get-property cell 'Columns))
(vlax-put-property cols 'hidden 1)
(vlax-release-object cols)(vlax-release-object cell))
(vlax-put-property *AplExcel* "UseSystemSeparators" Currsep)
(mapcar 'vlax-release-object (list *excell-cells* *Sheet#1* *Sheet-Collection* *New-Book* *Books-Colection*
*AplExcel*))(setq *AplExcel* nil)(gc)(gc)(princ))
;Perimeter EXPort to eXeLl
(defun C:PEXPXL (/ selset retLst lst)
(vl-load-com)
(if (setq selset (ssget '((0 . "*POLYLINE,CIRCLE,ELLIPSE,REGION"))))
(progn
(foreach item (mapcar 'vlax-ename->vla-object (vl-remove-if 'listp (mapcar 'cadr (ssnamex selset))))
(setq lst (list
(cdr(assoc 0 (entget(vlax-vla-object->ename item))))
(rtos
(if (vlax-property-available-p item 'Perimeter)
(vla-get-Perimeter item)
(vlax-curve-getDistAtParam item (vlax-curve-getEndParam item)))
2 12)
))
(setq retLst (append retLst (list lst))));_foreach
(xls retlst '("Object" "Perimeter") nil "Perimeter")))
(princ))
(princ "\nType PEXPXL to run")

Unfortunately, Intellicad’s version of Autolisp does not support AutoCAD’s Visual lisp functions yet. Your best option would be to port this application to VBA.

Danielm103 wrote:Unfortunately, Intellicad’s version of Autolisp does not support AutoCAD’s Visual lisp functions yet. Your best option would be to port this application to VBA.

Thanks, it seems my version of Intellicad does not support VBA either

beyond,

This could be translated to lisp and the drawing entitiy data extracted and used to create an ASCII file, say a comma delimited file.

The ASCII file would have to be manually opened in excel and the cells would then be populated with the data.

Excel has the translation from an ASCII comma delimited file into seperate cells built in.

John Finlay wrote:beyond,

This could be translated to lisp and the drawing entitiy data extracted and used to create an ASCII file, say a comma delimited file.

The ASCII file would have to be manually opened in excel and the cells would then be populated with the data.

Excel has the translation from an ASCII comma delimited file into seperate cells built in.

Thanks John but how do I translate it to lisp? opening in Excel would be fine

In the meantime someone in another forum did the code below, will it work in Intellicad SE 6.4?

It will be Friday before I can try
(defun C:PEXPXL ( / ss lst fname fil sel2lst)
(defun sel2lst ( sel / l len )
(if (= 'PICKSET (type sel))
(repeat (setq len (sslength sel))
(setq len (1- len)
l (cons (ssname sel len) l)
) ;setq
) ;repeat
) ;if
)
(setvar "CMDECHO" 0)
(princ "\nSelect Circle,Polyline,Ellipse,Spline or Region")
(if
(and
(setq ss (ssget '((0 . "CIRCLE,*POLYLINE,ELLIPSE,REGION,SPLINE"))))
(setq lst (sel2lst ss))
(setq fname (getfiled "Select Excell file" "" "xls" 1))
)
(progn
(setq fil (open fname "w"))
(write-line "Object\tPerimeter" fil)
(foreach en lst
(command "_AREA" "_Object" en)
(write-line (strcat (cdr(assoc 0 (entget en)))"\t" (rtos (getvar "PERIMETER") 2 12)) fil)
)
(close fil)
(princ "\nPerimeter of ")(princ (length lst))
(princ " object export to ")(princ fname)
)
)
(princ)
)
(princ "\nType PEXPXL to run")

beyond,

I just tried the lisp code and it worked fine.

The lisp program creates an ASCII file with an extension .xls in a tab delimited format ready to be opened with Excel.

The output may need some adjustment as I placed some entities in the drawing including a line and the line was ignored (I believe you want the program to change any entities such as lines and arcs to a polyline and measure the perimeter).

The output I opened in Excel was:

Object Perimeter
ELLIPSE 61776.06116
LWPOLYLINE 37489.65517
LWPOLYLINE 96968.77373
REGION 83560.52042

You should be able to test this in SE 6.4.

John Finlay wrote:beyond,

I just tried the lisp code and it worked fine.

The lisp program creates an ASCII file with an extension .xls in a tab delimited format ready to be opened with Excel.

The output may need some adjustment as I placed some entities in the drawing including a line and the line was ignored (I believe you want the program to change any entities such as lines and arcs to a polyline and measure the perimeter).

The output I opened in Excel was:

Object Perimeter
ELLIPSE 61776.06116
LWPOLYLINE 37489.65517
LWPOLYLINE 96968.77373
REGION 83560.52042

You should be able to test this in SE 6.4.

Thanks John for your help that is great news, yes I would want lines included, when you say the output needs adjustment, how would I do that?

If you want to store your data in a database such as access, you can try using my Lisp ADO tools. Since Excel can do SQL, this might work for that as well. I couldn’t post it here so here is a link.

http://www.icadsales.com/index.pl?id=&i ... on_id=4005

Danielm103 wrote:If you want to store your data in a database such as access, you can try using my Lisp ADO tools. Since Excel can do SQL, this might work for that as well. I couldn’t post it here so here is a link.

http://www.icadsales.com/index.pl?id=&i ... on_id=4005

Thanks I do want to store it in revelation, but i am not sure how I use your tool to do that, could you elaborate a little?

I kind of think I get it, I enter the command in intellicad
(SDSADO "C:\\NewMDB.mdb" "CREATE TABLE Table1 (Name VARCHAR, Address VARCHAR, Num INTEGER)") //ok

and I get an access table NewMDB.mdb how does that work for excel?

Just having a little fun, I was unable to create a new workbook via SQL, although this could be hard coded into the dll. This does work, it will create a new sheet as defined by TableName.... it’s probably not what your after, it’s more for doing queries and such

beyond,

I am not sure what output you need and therefore; I don't know what needs modifying.

John Finlay wrote:beyond,

I am not sure what output you need and therefore; I don't know what needs modifying.

Thanks, if there are several 2D shapes on a drawing say a square a circle a rectangle, a freeform shape. I need the perimeter value of each shape output to excel, or ascii to be read into excel

I would then take the ouput and read it into a revelation database where I hold the info for the profile cutter, then from the perimeter data I would calculate the time to cut out the profile of each shape on a laser cutter

Danielm103 wrote:
[/code]

Hi Daniel,

Good news is the code I posted works, but I get a null error when I try your add on, maybe it's because I am using excel 2007?

Hard to say, It shouldn’t be because of your Excel version.
Make sure you have .NET 2.0 installed,
Make sure the .dll is loaded
make sure the file Exists ie c:\\test.xls
run this at the prompt
And you should get a (T)
Let me know, I have been using it for quite sometime now, while it’s a little finicky, it’s a great tool to have in the arsenal

[quote="Danielm103"][/quote]

Hi Daniel thanks,

Sorry to be a pain

It would be good if the routine selected 'all' and saved to a text file automatically without going through the menu

I also wanted to include a block count

sample drawing here
http://www.divshare.com/download/2222051-d92