+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14
  1. #1
    Senior Member
    Using
    AutoCAD 2010
    Join Date
    Jun 2012
    Posts
    398

    Default Convert a list to excel table

    Registered forum members do not see this ad.

    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;
    Code:
    (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) 
    )
    .
    Attached Files

  2. #2
    Quantum Mechanic
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    10,175

    Default

    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 www.lee-mac.com
    A man who never made mistakes never made anything

  3. #3
    Senior Member
    Using
    AutoCAD 2010
    Join Date
    Jun 2012
    Posts
    398

    Default

    Hi Bigal,
    Thanks for your suggestions. I was however able to get my program working by adding two lines as follows:
    Code:
    (vla-put-RegenerateTableSuppressed myTable :vlax-true)
    before defining the table title and
    Code:
    (vla-put-RegenerateTableSuppressed myTable :vlax-false)
    at the end.
    Regards,
    Aloy

  4. #4
    Senior Member satishrajdev's Avatar
    Computer Details
    satishrajdev's Computer Details
    Operating System:
    Windows 7 - 64bit
    Computer:
    DELL
    RAM:
    8 GB
    Primary Storage:
    500 GB
    Monitor:
    DELL
    Discipline
    Surveying
    satishrajdev's Discipline Details
    Occupation
    CAD Engineer
    Discipline
    Surveying
    Details
    Design Survey Utilities (i.e. Bathymetry & Geophysical Survey) Design of Survey Planning. Preparation of Alignment Sheets. Programming in AutoCAD.
    Using
    AutoCAD 2014
    Join Date
    Apr 2012
    Location
    Aamchi Mumbai, India
    Posts
    442

    Default

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

    Code:
    (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))
    )
    Last edited by satishrajdev; 5th Feb 2018 at 11:40 am.
    Best Regards,
    Satish Rajdev

    If you ride like there is no tomorrow... Don't worry, there won't be !!!!
    www.facebook.com/satish.rajdev

  5. #5
    Quantum Mechanic
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    10,175

    Default

    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.

    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
    Code:
    (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.
    Last edited by BIGAL; 5th Feb 2018 at 11:59 am.
    A man who never made mistakes never made anything

  6. #6
    Senior Member
    Using
    AutoCAD 2010
    Join Date
    Jun 2012
    Posts
    398

    Default

    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.

  7. #7
    Senior Member
    Using
    AutoCAD 2010
    Join Date
    Jun 2012
    Posts
    398

    Default

    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

  8. #8
    Quantum Mechanic
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    10,175

    Default

    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.

    Code:
    ; 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.
    Attached Files
    A man who never made mistakes never made anything

  9. #9
    Quantum Mechanic
    Using
    Civil 3D 2016
    Join Date
    Dec 2005
    Location
    GEELONG AUSTRALIA
    Posts
    10,175

    Default

    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.
    A man who never made mistakes never made anything

  10. #10
    Senior Member
    Using
    AutoCAD 2010
    Join Date
    Jun 2012
    Posts
    398

    Default

    Registered forum members do not see this ad.

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

Similar Threads

  1. Convert a simple table (exploded) to a real table.
    By CafeJr in forum AutoLISP, Visual LISP & DCL
    Replies: 2
    Last Post: 30th Nov 2015, 03:23 pm
  2. AutoCAD - Update Sheet List Table and Edit Sheet List Table Settings
    By Between the Lines in forum AutoCAD RSS Feeds
    Replies: 1
    Last Post: 29th Oct 2013, 07:37 pm
  3. Replies: 2
    Last Post: 24th Oct 2013, 07:42 am
  4. List of the file list (only names) copy to excel
    By wimal in forum AutoCAD General
    Replies: 4
    Last Post: 3rd Jul 2013, 11:53 am
  5. Replies: 3
    Last Post: 13th Feb 2012, 06:09 pm

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts