Is it possible to populate an existing XLS sheet

[smaher12]

I already have a lisp that collects X,Y,Z points and creates a .csv file. My problem is I have to cut and paste the data points and put them in this pretty .xls file which contains formulas and conditional formating.

So my question is, can I somehow bybass the cut and paste process. Can I append to the existing .xls file? How can I get it to place the data in the correct row/column? Any ideas?

[hmsilva]

---

@smaher12 wrote:

..

So my question is, can I somehow bybass the cut and paste process. Can I append to the existing .xls file? How can I get it to place the data in the correct row/column? Any ideas?

---

Hi smaher12

This post may be useful.

HTH

Henrique

[Lee_Mac]

Some more Excel functions:

GetExcel - Terry Miller

.NET Excel Read & Write Functions - gile (requires Swamp membership)

Excel Read & Write Functions - gile

Excel API - Patrick_35

[smaher12]

Well I understand this much thus far. But what if I need to browse to the directory and select different spreadsheets?

(vl-load-com)
(defun c:22 ()
  (setq xlapp (vlax-create-object "Excel.Application")
	wbooks (vlax-get-property excel 'Workbooks)
	sheet (vlax-invoke-method wbooks 'Open "C:\\tmp\\test.xls")
      )
)

[dgorsman]

Instead of providing the fixed string, you would provide a variable.  The variable, in turn, gets set by either extra code (e.g. calling (getfiled ...)) or calling another function which encapsulates similar code.

[smaher12]

Okay lost again. How do I get the X points to start in I12, Y points J12, and Z points in B12? And how do you save the excel file?

(vl-load-com)
(defun c:22 ()

  (setq FilePath (getfiled "Select file :" (getvar "dwgprefix") "xls;xlsx" 10))
  (setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
  (setq Workbook (vlax-get-property ExcelApp 'ActiveWorkBook))
  (setq Activesheet (vlax-get-property ExcelApp 'ActiveSheet))
  (setq Cells (vlax-get-property ActiveSheet 'Cells))

  (setq row 1)
  (setq column 1)

     (while
       (setq PT (getpoint "\nSpecify point: "))
       (setq X (rtos (car PT) 2 1))
       (setq Y (rtos (cadr PT) 2 1))
       (setq Z (rtos (last PT) 2 1))
     )

 (princ)
)

[hmsilva · Accepted solution]

As a "demo"...

(defun c:test (/	 ACTIVESHEET	     CELLS     EXCELAPP	 FILEPATH
	       PT	 WORKBOOK  WORKBOOKS X	       XCOLUMN	 XROW
	       Y	 YCOLUMN   YROW	     Z	       ZCOLUMN	 ZROW
	      )
  (vl-load-com)
  (if (setq
	FilePath (getfiled "Select file :" (getvar "dwgprefix") "xls;xlsx" 0)
      )
    (progn
      (setq ExcelApp	(vlax-get-or-create-object "Excel.Application")
	    Workbooks	(vlax-get-property ExcelApp 'Workbooks)
	    Workbook	(vlax-invoke-method Workbooks 'Open FilePath)
	    Activesheet	(vlax-get-property ExcelApp 'ActiveSheet)
	    Cells	(vlax-get-property ActiveSheet 'Cells)
	    xrow	12
	    xcolumn	9
	    yrow	12
	    ycolumn	10
	    zrow	12
	    zcolumn	2
      );; setq
      (while
	(setq PT (getpoint "\nSpecify point: "))
	 (setq X (rtos (car PT) 2 1))
	 (setq Y (rtos (cadr PT) 2 1))
	 (setq Z (rtos (last PT) 2 1))
	 (vlax-put-property cells 'Item xrow xcolumn X)
	 (setq xrow (1+ xrow))
	 (vlax-put-property cells 'Item yrow ycolumn Y)
	 (setq yrow (1+ yrow))
	 (vlax-put-property cells 'Item zrow zcolumn Z)
	 (setq zrow (1+ zrow))
      )
      ;; while
      (vlax-invoke-method Workbook 'Save)
      (vlax-invoke-method Workbooks 'Close)
      (vlax-invoke-method ExcelApp 'Quit)
      (vlax-release-object cells)
      (vlax-release-object activesheet)
      (vlax-release-object workbook)
      (vlax-release-object workbooks)
      (vlax-release-object ExcelApp)
    );; progn
  );; if
  (princ)
)

HTH

Henrique

[smaher12]

Thank you very much Henrique! You made it look so easy. I was using a example I found from CADTutor and comment blocking sections, testing little by little. I would have been at it for the next 20+ days I just did not know what I was doing. Thanks again!

[hmsilva]

You're welcome, smaher12
Glad I could help

Henrique

[hmsilva]

smaher12 wrote:
"But what if I need to browse to the directory and select different spreadsheets?"

instead of hardcoded rows and columns, you can access to cell data with some "user interference", again only as a "demo", and without major tests...

(defun c:test (/	 ACTIVESHEET	     CELLS     EXCELAPP	 FILEPATH
	       PT	 WORKBOOK  WORKBOOKS X	       XACTCELL	 XCOLUMN
	       XROW	 Y	   YACTCELL  YCOLUMN   YROW	 Z
	       ZACTCELL	 ZCOLUMN   ZROW
	      )
  (vl-load-com)
  (if (setq FilePath (getfiled "Select file :" (getvar "dwgprefix") "xls;xlsx" 0))
    (progn
      (setq ExcelApp	(vlax-get-or-create-object "Excel.Application")
	    Workbooks	(vlax-get-property ExcelApp 'Workbooks)
	    Workbook	(vlax-invoke-method Workbooks 'Open FilePath)
	    Activesheet	(vlax-get-property ExcelApp 'ActiveSheet)
	    Cells	(vlax-get-property ActiveSheet 'Cells)
      );; setq
      (vla-put-visible ExcelApp :vlax-true)
      (alert "\nIn Excel Activate the first Cell to put the X Value\n
      then return to AutoCAD and Press OK")
      (if (setq Xactcell (vlax-get-property ExcelApp 'ActiveCell))
	(setq xrow    (vlax-get-property Xactcell 'Row)
	      xcolumn (vlax-get-property Xactcell 'Column))
      );; if
      (alert "\nIn Excel Activate the first Cell to put the Y Value\n
      then return to AutoCAD and Press OK")
      (if (setq Yactcell (vlax-get-property ExcelApp 'ActiveCell))
	(setq yrow    (vlax-get-property Yactcell 'Row)
	      ycolumn (vlax-get-property Yactcell 'Column))
      );; if
      (alert "\nIn Excel Activate the first Cell to put the Z Value\n
      then return to AutoCAD and Press OK")
      (if (setq Zactcell (vlax-get-property ExcelApp 'ActiveCell))
	(setq zrow    (vlax-get-property Zactcell 'Row)
	      zcolumn (vlax-get-property Zactcell 'Column))
      );; if
      (vla-put-visible ExcelApp :vlax-false)
      (if (and Xactcell Yactcell Zactcell)
	(while
	  (setq PT (getpoint "\nSpecify point: "))
	   (setq X (rtos (car PT) 2 1))
	   (setq Y (rtos (cadr PT) 2 1))
	   (setq Z (rtos (last PT) 2 1))
	   (vlax-put-property cells 'Item xrow xcolumn X)
	   (setq xrow (1+ xrow))
	   (vlax-put-property cells 'Item yrow ycolumn Y)
	   (setq yrow (1+ yrow))
	   (vlax-put-property cells 'Item zrow zcolumn Z)
	   (setq zrow (1+ zrow))
	);; while
      );; if
      (vlax-invoke-method Workbook 'Save)
      (vlax-invoke-method Workbooks 'Close)
      (vlax-invoke-method ExcelApp 'Quit)
      (mapcar 'vlax-release-object
	      (list cells activesheet workbook workbooks ExcelApp))
    );; progn
  );; if
  (princ)
);; test

Hope that helps
Henrique

[dgorsman]

Just to give you a kick in the developers pants, so to speak: note what was done with the call to (getfiled ...).  The (if ...) conditional tests to see if something was returned; have a look in the help for (getfiled ...) and what it would return if the user x'd out or canceled the dialog.  Its a good practice, when a function can return different values, to test what is returned to ensure its valid for use later on.

[smaher12]

Very nice Henrique. Now you have me thinking about this a little bit. With your first solution, I was thinking of doing some sort of append.

In the program it will ask the user what point number to start with. If the user enters 1 then the data will start in row 12. If the user enters 10 then the data will start in row 21, overwriting data in that row if any. So I'm thinking put the row(s) back to 1 and add 11 to whatever the user enters for a starting point number. Correct??  

[hmsilva]

---

@smaher12 wrote:

...

In the program it will ask the user what point number to start with. If the user enters 1 then the data will start in row 12. If the user enters 10 then the data will start in row 21, overwriting data in that row if any. So I'm thinking put the row(s) back to 1 and add 11 to whatever the user enters for a starting point number. Correct??  

---

Now I'm lost!

This reasoning is relative to the first code I posted?

If so, and if the column location is always the same, and seting x, y and zrow to zero, perhaps something like this

(setq xrow 0 yrow 0 zrow 0)
(initget 6);; 2 = not allowed entering 0 + 4 = not allowed entering a negative value
(if (setq row (getint "\nEnter the Excel Start Row number: <exit>"))
  (setq xrow (+ xrow row)
	yrow (+ yrow row)
	zrow (+ zrow row)
	);; setq
  );; if

 the use only have to enter the real starting row number...

HTH

Henrique

[smaher12]

My apologies for any confusion, but you pretty much got what I wanted to do. In my spreadsheets, point data always starts in row 12 (Point 1). Therefore if I ever need to append/overwrite point data - well, you get the picture. 

I would have never thought of the initget 6. Thanks again for everything.

(setq xrow 11 yrow 11 zrow 11)

(initget 6);; 2 = not allowed entering 0 + 4 = not allowed entering a negative value

(if (setq pt# (getint "\nSpecify point number: <exit> "))

  (setq xrow (+ xrow pt#)

        yrow (+ yrow pt#)

        zrow (+ zrow pt#)

        );; setq

  );; if

[hmsilva]

You're welcome, smaher12

I'm glad you got a solution.

Cheers

Henrique

[smaher12]

Well, I have been at this on/off for two weeks now. I'm trying to combine Henrique code with PBE code and haven't had any luck.

(defun c:xyz (/ dirname op mlist file entc)
(vl-load-com)
;;;	pBe Nov2011	;;;
 
 (setvar "cmdecho" 0)
 (setq OS (getvar "OSMODE"))

  (defun _def (val fn msg)
    (setq val
     (cond
       (((eval fn) (strcat msg "<" (if (eq (type val) 'INT)(itoa val)
                                     (rtos val 2 1)) ">: ")))
       (val)
       )
    )
  )
  (defun prnt2cad (pt_ zv pt#_ sd_ / ts tpt)
  (setq TS (getvar "textsize"))
  (if pt_
    (progn
      (setq TPT (list (car PT_) (+ (cadr PT_) (* TS 5.25)) (last PT_)))
      (setq TAG (strcat "PT" (rtos pt#_ 2 0)" Z=" (rtos zv 2 1)" A=" (rtos (+ zv sd_) 2 1)))
      (setvar "OSMODE" 0)

  (entmake 
    (list 
      (cons 0 "MTEXT")         
      (cons 100 "AcDbEntity")
      (cons 100 "AcDbMText")
      (cons 8 (getvar "CLayer"))        ;layer
      (cons 7 (getvar "TextStyle"))     ;current textstyle
      (cons 71 2)                       ;justify
      (cons 72 5)
      (cons 73 1)
      (cons 10 TPT)                     ;insert point
      (cons 50 0)                       ;angle
      (cons 40 (getvar "TextSize"))     ;current size
      (cons 41 18)                      ;textbox width
      (cons 1 TAG) 
    )
  )
      (ssadd (entlast) entc)
      (princ (strcat "\nCurrent Value: "
		     "\nPoint Number: "
		     (itoa pt#_)
		     "\tSurface Difference: "
		     (rtos sd_ 2 1)
		     "\tZ value: "
		     (rtos zv 2 1)
	     )
      )(princ)
    ) nil
    )
     )
  (foreach nm '(pt# z sd) (if (not (eval nm)) (set nm 1) ))
   (setq dirname (getfiled "Select a location to save output..." "Level.csv" "csv" 1) entc (ssadd))
   (if (and
  	(setq PT# (1- (_def PT# 'Getint "\nSpecify point number ")))
        (setq sd  (_def  (* sd 0.0) 'getreal "\nSpecify surface difference ")) ;;;;
	)
	(while
          (progn
            	(if (not (eq op "U"))(setq z  (_def  (* z 1.0) 'getreal "\nSpecify the Z elevation ")))
                (setvar "OSMODE" 4)
                 (initget "U S")
                   (setq  op
                      (getpoint "\nPick Point [Surface/Undo]:<Exit> " 
                        )
                     )
          (cond ((eq op "U")
                 (if (zerop (setq i (sslength entc)))
                   (princ "\nNothing to Undo")
                   (progn
                 	(repeat 1   ;;;(repeat 3 
                   	(entdel (setq d (ssname entc (setq i (1- i)) )))
                        (ssdel   d entc)
                          )
                 (setq mlist (vl-remove (car mlist) mlist) pt# (1- pt#)))
                   
		 )
                 (princ)
		)
            	((eq op "S")
                 (setq sd  (_def sd 'getreal "\nSpecify surface difference "))
                 )
		((null op) (setvar "OSMODE" OS)
		 (setq pt nil)
		)
		((listp op)
                 (setq  mlist (cons (list
		       (strcat
		         (itoa (setq pt# (1+ PT#)))
		         ","
		         (rtos (/ (car op) 12) 2 1)
		         ","
		         (rtos (/ (cadr op) 12) 2 1)
		         ","
		         (rtos z 2 1)
		         ","
		         (rtos sd 2 1)
		         ","
		         (rtos (+ z sd) 2 1)
		         )
		       )
		     mlist
		     )
		  )
                 (prnt2cad op z PT# sd))
		)
		  )
          )
     )
     (if mlist (progn
       	(setq file (open dirname "w"))
        (write-line "" file)
        (write-line (strcat "No." "," "X" "," "Y" "," "Z" "," "Diff." "," "Adj.") file)
        (foreach txt (reverse mlist)
          	(write-line (car txt) file)
          	)
       (close file)
       )
     )
  (princ)
  )

[hmsilva]

Hi smaher12,

i'm having some health problems, so, I can't give you much help now.

But, looking at your code saw some things that you should write differently, such as:

- when need for user intervention (getfiled, getreal, getint...), you should test if a valid argument is returned, if not, the code will give error.

- the while function, will evaluate a test expression, and if you use a progn function after the while, you are transforming all within the progn in test function.

HTH

Henrique

[bhull1985]

Take a look at this thread to see how I accomplished some of the same tasks.

All the code is visible, my issue in the thread was DCL tile alignment but it still shows some fairly easy-to-follow methods to do what you're attempting to do.

http://forums.autodesk.com/t5/Visual-LISP-AutoLISP-and-General/How-to-fix-DCL-alignment/m-p/4885258/...

[smaher12]

Still no luck with getting the following to write to an existing xls file with everything starting in row 11 and the X data in column 9, Y data in column10, Z data in column 2 and the  SD data in column 7.

(defun c:xyz (/ dirname op mlist file entc)
(vl-load-com)
;;;	pBe Nov2011	;;;
 
 (setvar "cmdecho" 0)
 (setq OS (getvar "OSMODE"))

  (defun _def (val fn msg)
    (setq val
     (cond
       (((eval fn) (strcat msg "<" (if (eq (type val) 'INT)(itoa val)
                                     (rtos val 2 1)) ">: ")))
       (val)
       )
    )
  )
  (defun prnt2cad (pt_ zv pt#_ sd_ / ts tpt)
  (setq TS (getvar "textsize"))
  (if pt_
    (progn
      (setq TPT (list (car PT_) (+ (cadr PT_) (* TS 5.0)) (last PT_)))
      (setq TAG (strcat "PT" (rtos pt#_ 2 0)" Z=" (rtos zv 2 1)" A=" (rtos (+ zv sd_) 2 1)))
      (setvar "OSMODE" 0)

      (command "TEXT" "J" "M" TPT "" "" (strcat "PT" (rtos pt#_ 2 0)))
      (ssadd (entlast) entc)
      (command "TEXT" "" (strcat "Z=" (rtos zv 2 1)))
      (ssadd (entlast) entc)
      (command "TEXT" "" (strcat "A=" (rtos (+ zv sd_) 2 1)))
      (ssadd (entlast) entc)
      (princ (strcat "\nCurrent Value: "
		     "\nPoint Number: "
		     (itoa pt#_)
		     "\tSurface Difference: "
		     (rtos sd_ 2 1)
		     "\tZ value: "
		     (rtos zv 2 1)
	     )
      )(princ)
    ) nil
    )
     )
  (foreach nm '(pt# z sd) (if (not (eval nm)) (set nm 1) ))
   (setq dirname (getfiled "Select a location to save output..." "Level.csv" "csv" 1) entc (ssadd))
   (if (and
  	(setq PT# (1- (_def PT# 'Getint "\nSpecify point number ")))
        (setq sd  (_def  (* sd 0.0) 'getreal "\nSpecify surface difference ")) ;;;;
	)
	(while
          (progn
            	(if (not (eq op "U"))(setq z  (_def  (* z 1.0) 'getreal "\nSpecify the Z elevation: ")))
                (setvar "OSMODE" 4)
                 (initget "U S")
                   (setq  op
                      (getpoint "\nPick Point [Surface/Undo]:<Exit> " 
                        )
                     )
          (cond ((eq op "U")
                 (if (zerop (setq i (sslength entc)))
                   (princ "\nNothing to Undo")
                   (progn
                 	(repeat 1
                   	(entdel (setq d (ssname entc (setq i (1- i)) )))
                        (ssdel   d entc)
                          )
                 (setq mlist (vl-remove (car mlist) mlist) pt# (1- pt#)))
                   
		 )
                 (princ)
		)
            	((eq op "S")
                 (setq sd  (_def sd 'getreal "\nSpecify surface difference "))
                 )
		((null op) (setvar "OSMODE" OS)
		 (setq pt nil)
		)
		((listp op)
                 (setq  mlist (cons (list
		       (strcat
		         (rtos (/ (car op) 12) 2 1)
		         ","
		         (rtos (/ (cadr op) 12) 2 1)
		         ","
		         (rtos z 2 1)
		         ","
		         (rtos sd 2 1)
		         )
		       )
		     mlist
		     )
		  )
                 (setq pt# (1+ PT#))
                 (prnt2cad op z PT# sd))
		)
		  )
          )
     )
     (if mlist (progn
       	(setq file (open dirname "w"))
        (foreach txt (reverse mlist)
          	(write-line (car txt) file)
          	)
       (close file)
       )
     )
  (princ)
  )

[hmsilva]

---

@smaher12 wrote:

Still no luck with getting the following to write to an existing xls file with everything starting in row 11 and the X data in column 9, Y data in column10, Z data in column 2 and the  SD data in column 7.

... 

---

k

Hi smaher12,

try the attached code, minimally tested...

HTH

Henrique