Jump to content

Want to draw graph as we do in graph sheet ! Help

sanrajbhar

By sanrajbhar
in AutoLISP, Visual LISP & DCL

 Share

Recommended Posts

sanrajbhar Apprentice

sanrajbhar

Posted
Posted

hello frnds !

 

Can any body has idea to draw pline along X axis and Y axis. Actually i have two colume in excel spreadsheet. one colume contains value (that i want to draw along X axis) and other clumn has value that i want to draw along Y Axis. my pupose is simple make drawing as we do in Graph paper sheet. if anybody know any lisp programe or trick. Please share !!

 

 

Kindly find attached snapshot for more !

 

thnks

 

 

 

1.jpg

SOliver Enthusiast

SOliver

Posted
Posted (edited) 

(vl-load-com)
(defun c:fx( / *error* columnIdToChar aDoc mSpace excel wBooks sSheetPath sSheets sSheet xColumnIndex yColumnIndex
                           endOfData dataSet)
   (defun *error*( msg / )
           (if(not (wcmatch (strcase msg) "*CANCEL*,*EXIT*"))
               (princ (strcat "\nFX: " msg "\n"))
           )
           (if(= 'VLA-OBJECT (type excel))(vlax-release-object excel))
           (if(= 'VLA-OBJECT (type wBooks))(vlax-release-object wBooks))
           (if(= 'VLA-OBJECT (type wBook))(vlax-release-object wBook))
           (if(= 'VLA-OBJECT (type sSheets))(vlax-release-object sSheets))
           (if(= 'VLA-OBJECT (type sSheet))(vlax-release-object sSheet))
       
   )
   (defun columnIdToChar(i / )
       (if(> i 26)
           (strcat (chr (+ 64(/ (1- i) 26))) (chr (+ 65 (rem (1- i) 26))))
           (chr (+ 64 i))                
       )
   )
   (setq aDoc (vla-get-activedocument(vlax-get-acad-object)))
   (Setq mSpace (vla-get-modelspace aDoc))
   (setq excel (vlax-create-object "Excel.Application"))
   (setq wBooks (vlax-get excel 'workbooks))
   
   ;Get and open
   (setq sSheetPath(getfiled "Select workbook" "c:\\lisp\\graph\\" "xls" 4))
   (if (= (setq wBook(vla-add wBooks sSheetPath)) nil)
       (progn
           (princ "\nUnable to open the selected workbook")
           (exit)
       )
   )
   ;Set spreadsheet
   (setq sSheets (vlax-get-property wBook 'sheets))
   (Setq i 1)
   (vlax-for c sSheets
       (princ (strcat "\n" (rtos i 2 0) " - " (vla-get-name c)))
       (Setq i (1+ i))
   )
   (while (= sSheet nil)
       (setq sSheetIndex (getint "\nEnter spreadsheet index:"))
       (if(/= sSheetIndex nil)
           (if(and (> sSheetIndex 0)(< sSheetIndex  i))
               (setq    sSheet (vlax-get-property ssheets  'Item   sSheetIndex))
               (princ "\nInvalid worksheet index")
           )
       )
   )
   ;Get columns
   (princ "\nColumn ID:    Header value:")
   (vlax-for c (vlax-get-property ssheet 'Range "A1:IV1")
       (if(/= (vlax-get c 'Text) "")
           (progn
               (setq colIndices (append colIndices (list(vlax-get c 'column))))
               (princ (strcat "\n" (rtos (vlax-get c 'Column) 2 0) ".    -        " (vlax-get c 'Text)))
           )
       )
   )
   (while (= xColumnIndex nil)
       (setq xColumnIndex (getint "\nEnter X column index:"))
       (if(vl-position xColumnIndex colIndices) 
           (setq xColumnIndex (columnIdToChar xColumnIndex))
           (progn
               (setq xColumnIndex nil)
               (princ "\nInvalid column index.")
           )    
       )
   )
   (while (= yColumnIndex nil)
       (setq yColumnIndex (getint "\nEnter Y column index:"))
       (if(vl-position yColumnIndex colIndices)
           (setq yColumnIndex (columnIdToChar yColumnIndex))
           (progn
               (setq yColumnIndex nil)
               (princ "\nInvalid column index.")
           )    
       )
   )
   
   ;Get data set
   (setq i 2)
   (while(= endOfData nil)
       (setq entry (list
                                       (vlax-get
                                           (vlax-get-property sSheet 'Range
                                               (strcat xColumnIndex (rtos i 2 0))) 'Text)
                                       (vlax-get
                                           (vlax-get-property sSheet 'Range
                                               (strcat yColumnIndex (rtos i 2 0))) 'Text)))
       (if(/= (car entry) "")
           (setq dataSet (append dataSet (list (list (atof (car entry))(atof (cadr entry))))) i (1+ i))
           (setq endOfData t)
       )
   )
   ;Plot
 (makePline mSpace (vl-sort dataSet '(lambda(x y)(< (car x) (car y)))))
   (vla-addline mSpace(vlax-3d-point (list 0.0 0.0 0.0))
        (vlax-3d-point(list
                                        (car(car (vl-sort dataSet '(lambda(x y)(> (car x) (car y))))))0.0 0.0)))
   (vla-addline mSpace(vlax-3d-point (list 0.0 0.0 0.0))
        (vlax-3d-point(list
                                       0.0 (car(car (vl-sort dataSet '(lambda(x y)(> (car x) (car y))))))0.0)))
   ;Force exit error to release activeX objects
 (exit)
)

;;  by CAB 10/05/2007
;;  Expects pts to be a list of 2D or 3D points
;;  Returns new pline object
(defun makePline (spc pts)
 ;;  flatten the point list to 2d
 (if (= (length (car pts)) 2) ; 2d point list
   (setq pts (apply 'append pts))
   (setq pts (apply 'append (mapcar '(lambda (x) (list (car x) (cadr x))) pts)))
 )
 (setq
   pts (vlax-make-variant
         (vlax-safearray-fill
           (vlax-make-safearray vlax-vbdouble (cons 0 (1- (length pts))))
           pts
         )
       )
 )
 (vla-addlightweightpolyline spc pts)
)

This will hopefully get you started.

 

Credit to CAB for the makePline method.

 

Tested with Acad 07 / Excel 03

Edited by SOliver
sanrajbhar Apprentice

sanrajbhar

Posted
  • Author
Posted

hey buddy ! Sound useful when i testing but it returns some error. kindly look into below command line. please suggest necessary remedies. i am using Au

 

Column ID: Header value:

1. - 1

2. - 0.4

3. - 32

Enter X column index:2

Enter Y column index:3

FX: bad argument type: VLA-OBJECT nil

 

 

 

 

(vl-load-com)
(defun c:fx( / *error* columnIdToChar aDoc mSpace excel wBooks sSheetPath sSheets sSheet xColumnIndex yColumnIndex
                           endOfData dataSet)
   (defun *error*( msg / )
           (if(not (wcmatch (strcase msg) "*CANCEL*,*EXIT"))
               (princ (strcat "\nFX: " msg "\n"))
           )
           (if(= 'VLA-OBJECT (type excel))(vlax-release-object excel))
           (if(= 'VLA-OBJECT (type wBooks))(vlax-release-object wBooks))
           (if(= 'VLA-OBJECT (type wBook))(vlax-release-object wBook))
           (if(= 'VLA-OBJECT (type sSheets))(vlax-release-object sSheets))
           (if(= 'VLA-OBJECT (type sSheet))(vlax-release-object sSheet))
       
   )
   (defun columnIdToChar(i / )
       (if(> i 26)
           (strcat (chr (+ 64(/ (1- i) 26))) (chr (+ 65 (rem (1- i) 26))))
           (chr (+ 64 i))                
       )
   )
   (setq aDoc (vla-get-activedocument(vlax-get-acad-object)))
   (Setq mSpace (vla-get-modelspace aDoc))
   (setq excel (vlax-create-object "Excel.Application"))
   (setq wBooks (vlax-get excel 'workbooks))
   
   ;Get and open
   (setq sSheetPath(getfiled "Select workbook" "c:\\lisp\\graph\\" "xls" 4))
   (if (= (setq wBook(vla-add wBooks sSheetPath)) nil)
       (progn
           (princ "\nUnable to open the selected workbook")
           (exit)
       )
   )
   ;Set spreadsheet
   (setq sSheets (vlax-get-property wBook 'sheets))
   (Setq i 1)
   (vlax-for c sSheets
       (princ (strcat "\n" (rtos i 2 0) " - " (vla-get-name c)))
       (Setq i (1+ i))
   )
   (while (= sSheet nil)
       (setq sSheetIndex (getint "\nEnter spreadsheet index:"))
       (if(/= sSheetIndex nil)
           (if(and (> sSheetIndex 0)(< sSheetIndex  i))
               (setq    sSheet (vlax-get-property ssheets  'Item   sSheetIndex))
               (princ "\nInvalid worksheet index")
           )
       )
   )
   ;Get columns
   (princ "\nColumn ID:    Header value:")
   (vlax-for c (vlax-get-property ssheet 'Range "A1:IV1")
       (if(/= (vlax-get c 'Text) "")
           (progn
               (setq colIndices (append colIndices (list(vlax-get c 'column))))
               (princ (strcat "\n" (rtos (vlax-get c 'Column) 2 0) ".    -        " (vlax-get c 'Text)))
           )
       )
   )
   (while (= xColumnIndex nil)
       (setq xColumnIndex (getint "\nEnter X column index:"))
       (if(vl-position xColumnIndex colIndices) 
           (setq xColumnIndex (columnIdToChar xColumnIndex))
           (progn
               (setq xColumnIndex nil)
               (princ "\nInvalid column index.")
           )    
       )
   )
   (while (= yColumnIndex nil)
       (setq yColumnIndex (getint "\nEnter Y column index:"))
       (if(vl-position yColumnIndex colIndices)
           (setq yColumnIndex (columnIdToChar yColumnIndex))
           (progn
               (setq yColumnIndex nil)
               (princ "\nInvalid column index.")
           )    
       )
   )
   
   ;Get data set
   (setq i 2)
   (while(= endOfData nil)
       (setq entry (list
                                       (vlax-get
                                           (vlax-get-property s 'Range
                                               (strcat xColumnIndex (rtos i 2 0))) 'Text)
                                       (vlax-get
                                           (vlax-get-property s 'Range
                                               (strcat yColumnIndex (rtos i 2 0))) 'Text)))
       (if(/= (car entry) "")
           (setq dataSet (append dataSet (list (list (atof (car entry))(atof (cadr entry))))) i (1+ i))
           (setq endOfData t)
       )
   )
   ;Plot
 (makePline mSpace (vl-sort dataSet '(lambda(x y)(< (car x) (car y)))))
   (vla-addline mSpace(vlax-3d-point (list 0.0 0.0 0.0))
        (vlax-3d-point(list
                                        (car(car (vl-sort dataSet '(lambda(x y)(> (car x) (car y))))))0.0 0.0)))
   (vla-addline mSpace(vlax-3d-point (list 0.0 0.0 0.0))
        (vlax-3d-point(list
                                       0.0 (car(car (vl-sort dataSet '(lambda(x y)(> (car x) (car y))))))0.0)))
)

;;  by CAB 10/05/2007
;;  Expects pts to be a list of 2D or 3D points
;;  Returns new pline object
(defun makePline (spc pts)
 ;;  flatten the point list to 2d
 (if (= (length (car pts)) 2) ; 2d point list
   (setq pts (apply 'append pts))
   (setq pts (apply 'append (mapcar '(lambda (x) (list (car x) (cadr x))) pts)))
 )
 (setq
   pts (vlax-make-variant
         (vlax-safearray-fill
           (vlax-make-safearray vlax-vbdouble (cons 0 (1- (length pts))))
           pts
         )
       )
 )
 (vla-addlightweightpolyline spc pts)
   ;Force exit error to release activeX objects
   (exit)
)

This will hopefully get you started.

 

Credit to CAB for the makePline method.

 

Tested with Acad 07 / Excel 03

SOliver Enthusiast

SOliver

Posted
Posted
hey buddy ! Sound useful when i testing but it returns some error. kindly look into below command line. please suggest necessary remedies. i am using Au

 

Column ID: Header value:

1. - 1

2. - 0.4

3. - 32

Enter X column index:2

Enter Y column index:3

FX: bad argument type: VLA-OBJECT nil

 

Sorry about that, the code was primed for debugging. I've updated the code above which should get you on your way.

sanrajbhar Apprentice

sanrajbhar

Posted
  • Author
Posted

Can i have that Code. kindly Post here. Plz!

 

Sorry about that, the code was primed for debugging. I've updated the code above which should get you on your way.
Organic Grand Master

Organic

Posted
Posted

He edited his earlier post, the code is in that.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...