Convert a list to excel table

[aloy]

Hello everybody,

I have a list of about 750 points giving xyz values. I want to first convert it to an autocad table and export to excel and I am using the code given below for that. However it appears that there is a limit to number of rows. The program hangs if I remove the (princ row) line in it.

What can be the problem?. I attach my list also herewith;

(defun Maketable()
(setq n(length l))  
(setq n(- n 1)) 
(command "table" 3 1 '(0 15) 2 2)
(if (and(setq en (entlast))
(setq myTable (vlax-EName->vla-Object en))
 (= (vla-get-ObjectName myTable) "AcDbTable")
(setq rws (vla-Get-Rows myTable))
)
(vla-InsertRows myTable rws (vla-GetRowHeight myTable (1- rws)) n)
)
(vla-settext myTable 0 0 "myTable")
(vla-settext myTable 1 0 "Xvalue")
(vla-settext myTable 1 1 "Yvalue")
(vla-settext myTable 1 2 "Zvalue")
(setq row 2)
(setq cell 0)
   (while (/= l nil)
(setq lstx(car l))
        (while (<= cell 2)
   ;(vla-settextHeight myTable row cell 0.22) 
          (vla-settext myTable row cell (car lstx))
          ;(vla-setCellAlignment myTable row cell acMiddleCenter)
   (setq lstx(cdr lstx))
   (setq cell(+ 1 cell))
          (if (and (/= lstx nil) (> cell 2)) (progn (setq row(+ row 1)) (setq cell 0)))         
        )
     (princ row)
     (setq bp(getstring))

       (setq l (cdr l))        
(setq row(+ row 1))
       (setq cell 0)       
  )

(command "zoom" "E")
(princ) 
)

.

pointlist.lsp

[BIGAL]

Check your whiles and IF may be missing a bracket.

 

Also why not just read the xyz straight into excel ?

 

Also also why not just read the xyz file, why make a list of it seems to be an extra step.

 

Just google csv to table, did you check http://www.lee-mac.com

[aloy]

Hi Bigal,

Thanks for your suggestions. I was however able to get my program working by adding two lines as follows:

(vla-put-RegenerateTableSuppressed myTable :vlax-true)

before defining the table title and

(vla-put-RegenerateTableSuppressed myTable :vlax-false)

at the end.

Regards,

Aloy

[satishrajdev]

Try this, I've simplify you code for your better understanding

 

(defun maketable (/ tbl row)
 (if (setq tbl	(vla-addtable
	  (vla-get-modelspace
	    (vla-get-activedocument (vlax-get-acad-object))
	  )
	  (vlax-3d-point '(0 15 0))
	  (+ 2 (length l))	; ADD ROWS
	  3			; ADD COLUMNS
	  2			; ROW HEIGHT
	  10			; COLUMN WIDTH
	)
     )
   (progn
     (vla-unmergecells tbl 0 0 0 0)	; ADD THIS IF YOU WANT TO UNMERGE 1ST ROW
     (setq row 0)
     (foreach x '(0 1 2)
(vla-setcolumnwidth tbl x 50)
     )
     (write-row '("X Value" "Y Value" "Z Value"))
     (foreach x l
(write-row x)
     )
   )
 )
)
(defun write-row (lst / a)     ; Write row in table
 (foreach x lst
   (if	(not a)
     (setq a 0)
     (setq a (1+ a))
   )
   (vla-settext
     tbl
     row
     a
     (cond ((eq (type x) 'STR) x)
    ((eq (type x) 'REAL) (rtos x 2 3))
    ((not x) "-")
    (T (rtos x 2 2))
     )
   )
   (vla-setrowheight tbl row 2.)
   (vla-setcellalignment tbl row a acMiddleCenter)
 )
 (setq row (1+ row))
)

Edited February 5, 2018 by satishrajdev

[BIGAL]

A method "addrows" is just that rather than set how many rows you need, just keep reading a line of your xyz file and adding a row, again as suggested much easier to start with your data file and read that better than make a list. Just create your heading row etc and the 1st row fill that in and if more addrow.

 

satishrajdev has provided a good example here is a bit simpler to create the initial table I will find the addrow code.

 

; now do table 
(setq pt1 (vlax-3d-point (getpoint "\nPick point for top left hand of table:  "))) 
(setq numrows 3)))
(setq numcolumns 3)
(setq rowheight 0.2)
(setq colwidth 150)
(setq objtable (vla-addtable curspace pt1 numrows numcolumns rowheight colwidth))
(vla-settext objtable 0 0 "DRAWING REGISTER")
(vla-settext objtable 1 0 "DRAWING NUMBER") 
(vla-settext objtable 1 1 "DRAWING TITLE") 

Add a row

(setq rws (vla-Get-Rows objtable))
(vla-InsertRows objtable rws (vla-GetRowHeight objtable (1- rws)) 1)   ;; 1 number of rows to add
; now fill in x y z
(vla-settext objtable rws 0 (rtos x 2 2)) 
(vla-settext objtable rws 1 (rtos Y 2 2)) 
(vla-settext objtable rws 2 (rtos Z 2 2))

 

Please post your data file.

Edited February 5, 2018 by BIGAL

[aloy]

Satishrajdev,

Your code essentially does the same thing as mine, however the cells are too large and has the same problem of time consuming regeneration. My code as given above produce line by line as one goes on pressing any key. If I remove the (princ row) line it gets hung. But with regeneration suppressed it works fine without those two lines.

Regards,

Aloy.

[aloy]

Hi Bigal,

Thanks. If I am not mistaken it was you who gave me the solution at an earlier occasion in this forum to suppress the regeneration to speed up on a table of about 30 rows. This one has about 750 rows. However adding row by row will not have a problem but will take too long.

The data file is given in my post #1.

Regards,

Aloy

[BIGAL]

I tried the direct way and interesting is the time it takes to add the values to the table. In this example I have your points but I have put an exit at the point of making a table to see whats going on, when it asks continue "Y" press any key and table will display this is reasonably quick given 724 lines. Next run answer Y I have the repeat set to 100 lines and again a bit slow. I am running on an old I3

 

Oh yeah runs in paperspace and has a hard code path to test data simplifying testing will add the getfiled later.

 

; thanks to Lee-mac for this defun
(defun _csv->lst  (str / pos)
 (if (setq pos (vl-string-position 44 str))
   (cons (substr str 1 pos) (_csv->lst (substr str (+ pos 2))))
   (list str)))

                                       ; now do table 
(defun makexyztab
      (/ numrows numcolumns colwidth x y z doc curspace objtable k)
 (setq fo (open "C:\\BIG-AL\\lisp\\pointlist.csv" "R"))
 (setq K 1)
 (while (setq newline (read-line fo)) (setq K (+ K 1)))
 (close fo)
 (setq numrows (+ 2 K))
 (setq numcolumns 3)
 (setq rowheight 0.2)
 (setq colwidth 150)
 (setq doc (vla-get-activedocument (vlax-get-acad-object)))
 (setq curspace (vla-get-paperspace doc))
 (setq pt1 (vlax-3d-point
             (getpoint "\nPick point for top left hand of table:  ")))
 (setq objtable
        (vla-addtable curspace pt1 numrows numcolumns rowheight colwidth))
 (vla-settext objtable 0 0 "XYZ POINTS")
 (vla-settext objtable 1 0 "POINT X")
 (vla-settext objtable 1 1 "POINT Y")
 (vla-settext objtable 1 2 "POINT Z")
 (command "regen")
 (alert "table created")
 (if (= "Y" (strcase (getstring "Enter Y to continue")))
   (princ)
   (exit))
 (setq rws 2)
 (setq fo (open "C:\\BIG-AL\\lisp\\pointlist.csv" "R"))
                                       ;(while (setq newline  (read-line fo))
 (repeat 100
   (setq newline (read-line fo))
   (setq lst (_csv->lst newline))
   (setq x (nth 0 lst))
   (setq y (nth 1 lst))
   (setq z (nth 2 lst))
   (vla-settext objtable rws 0 x)
   (vla-settext objtable rws 1 y)
   (vla-settext objtable rws 2 z)
   (setq rws (+ rws 1)))
 (close fo)
 (princ))

(makexyztab)

 

Next post will be a variation on method to see if I can get speed up but need num,x,y,z to test on an existing routine.

pointlist.CSV

[BIGAL]

Did a lot more testing and found that if I used old fashioned lines and text it creates a table in seconds all 742 entries.

 

The problem seems to be in filling in the table, creating a blank table takes seconds.

 

As a comparison in CIV3D imported points 10 seconds, do a table of the points about 3 seconds. All done.

 

Really not sure where to go. Keep hanging my autocad seems to work if I limit the number of data read but takes forever to do. I will post the line/ text version later today.

[aloy]

Are you not creating a table out of the already created pointlist.csv file in your directory.

[BIGAL]

pointlist.CSValoy yes

 

It works for say 100 data lines then at some point just gives up.

 

Probably need to google to find some answers may be that a .net will create table or a entmake a table and add the data all in one go.

 

To create a blank table of 724 lines takes a few seconds as per my post above.

I added a blank line to end of data and this works takes seconds.

; SETOUTBOX.LSP draw setout details boxed co-ord list
                   ; 29/9/04 by alan houston
(setvar "menuecho" 0)
(setvar "SNAPMODE" 0)
(SETQ OLDSNAP (GETVAR "OSMODE"))
(setvar "OSMODE" 0)
(defun xyz ()
 (setq ans "")
 (setq char_found "")
 (while (/= char_found ",")
   (setq char_found (substr new_line x 1))
   (setq x (+ x 1))
   (setq ans (strcat ans char_found))
 )                    ;end while
)                    ;end defun 
(setq setsc (/ (getreal "\nEnter Dwg scale 1: ") 1000.0))
(setq stpt (getpoint "\nPick top left point for details "))
(setq xyzfiles (getfiled "\nENTER CO-ORD File name  " "" "" 4))
(setq fopen (open xyzfiles "R"))
(command "zoom" "E")
(command "zoom" "c" stpt (* setsc 100.0))
(setq pt1 stpt)
(setq txtht (* 2.5 setsc))  ; 2.5mm HIGH LETTERING
(setq num 1)
(setq llen (* 84.0 setsc))
(setq ydiff (* 4.5 setsc))  ; 4.5mm line spacing
(setq stptx (car stpt))
(setq stpty (cadr stpt))

(setq txtpt (list (+ stptx (* 42.0 setsc)) (+ stpty (* 2.0 setsc))))
(command "text" "BC" txtpt txtht "" "xxxxx")

(setq txtpt (list (+ stptx (* 42.0 setsc)) (+ stpty (* 7.0 setsc))))
(command "text" "BC" txtpt txtht "" "%%uSETOUT DETAIL")
(setq pt2 (polar stpt 0.0 llen))
(command "line" stpt pt2 "")        ;draw horizontal line
(setq pt1 (polar stpt 4.7124 (* 5.75 setsc)))
(setq txtpt (polar pt1 0.0 (* 9.0 setsc)))
(command "text" "BC" txtpt (* 3.5 setsc) "" "POINT")
(setq txtpt (polar pt1 0.0 (* 34.0 setsc)))
(command "text" "BC" txtpt (* 3.5 setsc) "" "EASTING")
(setq txtpt (polar pt1 0.0 (* 67.0 setsc)))
(command "text" "BC" txtpt (* 3.5 setsc) "" " NORTHING ")
(setq pt1 (polar stpt 4.7124 (* 8.0 setsc)))
(setq pt2 (polar pt1 0.0 llen))
(command "line" pt1 pt2 "")        ;draw horizontal line
(setq pt1 (polar pt1 4.7124 (* 1.0 setsc)))
(setq pt2 (polar pt1 0.0 llen))
(command "line" pt1 pt2 "")        ;draw horizontal line below above
(setq pt1 (polar pt1 4.7124 (* 1.0 setsc)))
(setq pt2 (polar pt1 0.0 llen))
(command "line" pt1 pt2 "")        ;draw horizontal line below above

(setq pt3 pt1)                ; sets pt to last line position
;(setq pt5 (polar pt3 1.5708 (* 1.0 setsc)))      ; 1 mm above line
(setq pt5 pt3)                ; 0.5 mm above line
(SETQ XX 1)
(while (setq new_line (read-line fopen))
(PRINC XX)
 (setq x 1)
 (setq y 5)
 (xyz)
 (setq ptno ans)            ;pull ptno out
 (xyz)
 (setq easting ans)
 (xyz)
 (setq northing ans)

 (setq pt3 (polar pt3 4.7124 ydiff))
 (setq pt4 (polar pt3 0.0 llen))
 (command "line" pt3 pt4 "")
 (setq pt5 (polar pt5 4.7124 ydiff))
 (setq pt6 (polar pt5 0.0 (* 8.0 setsc)))
 (setq pt7 (polar pt5 0.0 (* 28.0 setsc)))
 (setq pt8 (polar pt5 0.0 (* 58.0 setsc)))
 (command "text" "BL" pt6 txtht "" ptno)
 (command "text" "BL" pt7 txtht "" EASTING)
 (command "text" "BL" pt8 txtht "" NORTHING)
(SETQ XX (+ XX 1))
)                    ; end while
(command "line" stpt pt3 "")
(setq pt3 (polar pt3 0.0 (* 18.0 setsc)))
(setq stpt (polar stpt 0.0 (* 18.0 setsc)))
(command "line" stpt pt3 "")
(setq pt3 (polar pt3 0.0 (* 33.0 setsc)))
(setq stpt (polar stpt 0.0 (* 33.0 setsc)))
(command "line" stpt pt3 "")
(setq pt3 (polar pt3 0.0 (* 33.0 setsc)))
(setq stpt (polar stpt 0.0 (* 33.0 setsc)))
(command "line" stpt pt3 "")
(princ setsc )
(setq pt1  nil
     pt2  nil
     pt3  nil
     pt4  nil
     pt5  nil
     pt6  nil
     pt7  nil
     pt8  nil
     stpt nil
     ans  nil
     SETSC NIL
)
(SETVAR "OSMODE" OLDSNAP)
(princ)

Edited February 7, 2018 by BIGAL

[aloy]

Bigal,

Thanks for the try.

What I was trying to do was to prepare a csv file from my list and send to a friend who has Civil 3D 2016 and ask him to do a design of a road for a design speed of 40km/h with Horizontal and Vertical alignments, schematics and cross sections at say 20m intervals. The idea is to compare with my program implemented with AutoCAD 2014. The road is only about half a kilometer long. Unfortunately my friend is not now available and I am stuck with the list. Is there anyway you could help me to get it designed with Civ 3D?.

I attach the csv file and my design.

Thanking you in advance.

Aloy

myTable.csv

myDesign.dwg

[BIGAL]

Sorry can not help you with design.

[aloy]

It is ok Bigal.

The design given in the drawing took less than ten minutes including the preparation of TIN surface (which can be seen by turning on the 'TINaa' layer of the drawing). So I thought it would take even less time with civil 3D. Anyway I will run it on my computer with civil 3D 2010 back home (in another country) in about a weeks time and see how it comes.

Regards,

Aloy