Can anyone please help translate this code for Intellicad?

Topics relating to AutoLisp and SDS.

Moderators: CMS Inc, avaernes

beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Can anyone please help translate this code for Intellicad?

Post by beyond » Tue Oct 02, 2007 6:39 am

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")
Danielm103
Valued Contributor
Posts: 54
Joined: Mon Aug 07, 2006 11:01 pm
Location: Albuquerque,NM,USA

Post by Danielm103 » Tue Oct 02, 2007 10:14 am

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.
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Tue Oct 02, 2007 6:22 pm

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 :oops:
John Finlay

Post by John Finlay » Tue Oct 02, 2007 7:45 pm

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.
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Tue Oct 02, 2007 11:51 pm

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")
John Finlay

Post by John Finlay » Wed Oct 03, 2007 1:32 am

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.
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Wed Oct 03, 2007 3:10 am

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?
Danielm103
Valued Contributor
Posts: 54
Joined: Mon Aug 07, 2006 11:01 pm
Location: Albuquerque,NM,USA

Post by Danielm103 » Wed Oct 03, 2007 7:45 am

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
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Wed Oct 03, 2007 7:53 am

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?
Danielm103
Valued Contributor
Posts: 54
Joined: Mon Aug 07, 2006 11:01 pm
Location: Albuquerque,NM,USA

Post by Danielm103 » Wed Oct 03, 2007 12:54 pm

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

Code: Select all

;(SDSADO "C:\\test.xls;Extended Properties=Excel 8.0;" 
;     "CREATE TABLE Table1 (Name VARCHAR, Address VARCHAR, Num DOUBLE)")
;(SDSADO "C:\\test.xls;Extended Properties=Excel 8.0;"
;     "INSERT INTO Table1 (Name, Address, Num ) VALUES ('Yo Dude', 'Somewhere in Albuquerque', 1.01)")
;(SDSADO "C:\\test.xls;Extended Properties=Excel 8.0;" 
;        "SELECT * FROM Table1")
;(SDSADO "C:\\test.xls;Extended Properties=Excel 8.0;" "SELECT Num * 12 FROM Table1 WHERE Name = 'Yo Dude'")
;
;


(DEFUN C:PEXPXL	( / connectstr en lst sel ss tablename)

  (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
  )
  (setq tableName "ThisIsIt")
  (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 CONNECTSTR (STRCAT "C:\\test.xls" ";Extended Properties=Excel 8.0;"))
      )
    (PROGN
      (SDSADO CONNECTSTR (strcat "CREATE TABLE " tableName 
        " (ObjectType VARCHAR, Perimeter DOUBLE)"))
      (FOREACH EN LST 
      (COMMAND "_AREA" "_Object" EN)
	(SDSADO
	  CONNECTSTR
	    (STRCAT "INSERT INTO "  tableName " (ObjectType, Perimeter) VALUES ('"
	      (CDR (ASSOC 0 (ENTGET EN)))"', "(RTOS(GETVAR "PERIMETER")2 12)")")
	)
      )
      (princ (SDSADO CONNECTSTR (strcat "SELECT * FROM "tableName)))
    )
  )
  (PRINC)
)
(PRINC "\nType PEXPXL to run")

John Finlay

Post by John Finlay » Thu Oct 04, 2007 1:04 am

beyond,

I am not sure what output you need and therefore; I don't know what needs modifying.
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Thu Oct 04, 2007 5:58 am

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
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Thu Oct 04, 2007 5:21 pm

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?
Danielm103
Valued Contributor
Posts: 54
Joined: Mon Aug 07, 2006 11:01 pm
Location: Albuquerque,NM,USA

Post by Danielm103 » Thu Oct 04, 2007 8:00 pm

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

Code: Select all

(sdsado "c:\\test.xls;extended properties=excel 8.0;"
          "create table table1 (name varchar, address varchar, num double)")
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
beyond
Contributor
Posts: 11
Joined: Mon Oct 01, 2007 10:53 pm

Post by beyond » Thu Oct 04, 2007 8:43 pm

[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
Last edited by beyond on Fri Oct 05, 2007 2:20 am, edited 1 time in total.
Post Reply