How to color a cell of excel sheet from Visual LISP?

[Ahankhah]

Could anybody show a way in Visual LISP to change the format of cells (for example color of the cells) in Excel?

 

Thanks in advance

 

Mehrdad

www.irancad.com

Edited September 27, 2010 by Ahankhah

[irneb]

Basically you'd obtain a vla object to the Excel program installed on the PC using (vlax-get-or-create-object "Excel.Application"). This should give you the ActiveX Application Object for Excel. From there you can check if the XLS file is already in the Workbooks collection, or just use the workbooks collection's Open method. This should then give you a Workbook object and its Worksheets collection. From there the Worksheet object from which you can obtain a Range. From that you can set various values such as borders, font, etc.

 

Trick is to "convert" the usual VB code to lisp ... when working with ActiveX you mostly use the vlax-.... methods. E.g. to get the value of a property you use either vlax-get-property or the undocumented vlax-get. To change the value of a property use vlax-put-property / vlax-put. To run a method use vlax-invoke-method / vlax-invoke.

[fixo]

Could anybody show a way in Visual LISP to change the format of cells (for example color of the cells) in Excel?

 

Thanks in advance

 

Mehrdad

www.irancad.com

 

See code

(defun C:DEMO (/ Bord ExcelApp FilePath Fonto Rang Sel Sht ShtNum Wbk)
(vl-load-com)

(setq FilePath (getfiled "Select Excel file to read :"
(getvar "dwgprefix")
"xls"
16
)
)
(setq ShtNum (getint "\nEnter sheet number <1>: "))
(if (not ShtNum)(setq ShtNum 1))

(setq ExcelApp (vlax-get-or-create-object "Excel.Application"))
(vla-put-visible ExcelApp :vlax-true);do not set to :vlax-false for invisible mode
(setq Wbk (vl-catch-all-apply 'vla-open
(list (vlax-get-property ExcelApp "WorkBooks") FilePath)))
(vlax-invoke-method Wbk "Activate") 
(setq Sht (vl-catch-all-apply 'vlax-get-property
(list (vlax-get-property Wbk "Sheets")
"Item" ShtNum)))
(vlax-invoke-method Sht "Activate")
;;Find last cell :
(vlax-invoke-method ExcelApp "Volatile")
(setq FindRang (vlax-get-property Sht "UsedRange"))
(setq RowNum (vlax-get-property
(vlax-get-property
FindRang "Rows") "Count"))
(setq lastRow (vlax-variant-value
(vlax-get-property (vlax-get-property
FindRang "Rows") "Item" RowNum)))
(setq lastCell (vlax-get-property lastRow "End" 2))
;;working with columns :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Columns")
"Item" 1))); 1 is column number
(vlax-put-property Rang "ColumnWidth" (vlax-make-variant 25.0 3)); 25.0 is new column width, 3 is variant type 
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
;;working with rows :
;;; (setq Rang (vlax-get-property Sht "Range" "A:A"));get first row
;; the same as :
(setq Rang (vlax-variant-value
(vlax-get-property (vlax-get-property Sht "Rows")
"Item" 1))); 1 is row number
(vlax-put-property Rang "RowHeight" (vlax-make-variant 18.0 3)); 25.0 is new row height, 3 is variant type 
(vlax-put-property Rang "Horizontalalignment" (vlax-make-variant -4108 3))
(vlax-put-property (vlax-get-property Rang "Interior")
"Colorindex" (vlax-make-variant 4))
;;working with borders :
(setq Bord (vlax-get-property Rang "Borders"))
(vlax-put-property Bord "Color" (vlax-make-variant -1 3)) ; borders off
(vlax-put-property Bord "Color" (vlax-make-variant 1 3)) ;borders on
;; border lines (thin)
(vlax-put-property Bord "LineStyle" (vlax-make-variant 1 3))
;; borders color
(vlax-put-property Bord "Colorindex" (vlax-make-variant 5))
;;working with font :
(setq Fonto (vlax-get-property Rang "Font"))
(vlax-put-property Fonto "Name" (vlax-make-variant "Times New Roman" 12))
(vlax-put-property Fonto "Size" (vlax-make-variant 12 5))
(vlax-put-property Fonto "Bold" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Italic" (vlax-make-variant 1 11))
(vlax-put-property Fonto "Colorindex" (vlax-make-variant 5));ETC

; continuing the code above
;; working with separate cell :
(vlax-invoke-method Rang "Activate");optional
(setq Cels (vlax-get-property Rang "Cells"))
(setq R1 1
C1 1)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
(vlax-put-property Cel "Value2" (vlax-make-variant "First Cell in Range" 12))
;; Set interior color :
(vlax-put-property (vlax-get-property Cel "Interior")
"Colorindex" (vlax-make-variant 28))
;; select the particular cell:
(vlax-invoke-method Cel "Select")
;; get application selection: 
(setq Sel (vlax-get-property ExcelApp "Selection"))
;; get selection borders
(setq Bords (vlax-get-property Sel "Borders"))
;; iterate through all edges of selection
(setq cnt 0)
(vlax-for a Bords
(setq cnt (1+ cnt))
(vl-catch-all-apply (function (lambda()
(progn 
(if (< cnt 5)
(progn 
(vlax-put-property a "LineStyle"
(vlax-make-variant 1 3))
(vlax-put-property a "Weight"
(vlax-make-variant 4 3))
(vlax-put-property a "ColorIndex"
(vlax-make-variant 1 5)));progn
;; turn off the diagonal lines:
(vlax-put-property a "LineStyle" (vlax-make-variant -4142 3))
))))))
;; Horizontal alignment Center :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4108 3))
;; Vertical alignment Bottom :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4107 3))
;; Set number format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "0,00" )
(setq R1 1
C1 2)
(setq Cel (vlax-variant-value
(vlax-get-property Cels "Item"
;; row number :
(vlax-make-variant R1)
;; column number :
(vlax-make-variant C1))))
;; get cell value :
(setq cval (vlax-variant-value (vlax-get-property Cel "Value")))
;; Horizontal alignment Left(Indent) :
(vlax-put-property Cel "Horizontalalignment" (vlax-make-variant -4131 3))
;; Vertical alignment Center :
(vlax-put-property Cel "VerticalAlignment" (vlax-make-variant -4108 3))
;; Set text format :
(vlax-put-property Cel "NumberFormat" (vlax-make-variant "@" )
;; ETC
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close")
;;; );close file w/o saving of changes

;; *** or if you need to save changes :

;;;(vlax-invoke-method
;;;Wbk
;;;'SaveAs
;;;(vlax-get-property wbk "Name");short name
;;;-4143 ;exel file format (excel constant)
;;;nil
;;;nil
;;;:vlax-false
;;;:vlax-false
;;;1
;;;2
;;
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list Wbk "Close" )
;;; )
;; **** 
;;; (vl-catch-all-apply
;;; 'vlax-invoke-method
;;; (list ExcelApp "Quit")
;;; )
(mapcar
(function (lambda (x)
(vl-catch-all-apply (function (lambda()
(if (not (vlax-object-released-p x))

(vlax-release-object x)
)
)
))))
(list Bord Bords Cel Fonto lastCell lastRow FindRang Rang Sel Sht Wbk ExcelApp)
)
(setq Bord nil
Bords nil
Fonto nil
Cel nil
Sel nil
Rang nil
Sht nil
Wbk nil
ExcelApp nil
)
(gc)
(gc)
(princ)
)

 

~'J'~

[Ahankhah]

irneb and fixo, thank you very much for your help.

 

Mehrdad Ahankhah

http://www.irancad.com

[fixo]

irneb and fixo, thank you very much for your help.

 

Mehrdad Ahankhah

www.irancad.com

You're welcome

Cheers

 

~'J'~

[hosneyalaa]

 Hello my friend fixo

How to know the latest cell number

Only in the column a

As it is shown in the picture

 

Where it is in the language VBA EXCEL
The variable ( LD )  gives another cell number containing data in the column A

Dim LD As Long
LD = Range("A" & Rows.Count).End(xlUp).Row

 

You can help with this idea in your LISP

THANK YOU

 

 

Edited July 15, 2019 by hosneyalaa

[BIGAL]

Fixo has not contributed for a number of years now, he provided some great answers. It is unknown why he stopped.

[BIGAL]

This link talks about using excel to get the answer you want you may be able to get Autocad to run the command. and return the values. I Know you can do stuff like set cell color.

 

https://exceljet.net/formula/last-row-number-in-range

for column A

=LOOKUP(2,1/(A:A<>""),ROW(A:A))

[hosneyalaa]

BIGAL Hi

Praise be to God 

 I found the solution

 

 
(setq SelRng (vlax-get-property Sht 'Range "A1000"));CELL A1000
(vlax-invoke-method SelRng "Select")
(setq NextRng (vlax-get-property ExcelApp "Selection"));SELECT_RANGE
(setq DesRng (vl-catch-all-apply'vlax-get-property (list NextRng "End" -4162))); -4121 = xlUp ;GO TO UP CELLS
(SETQ lastCellE (vlax-invoke-method DesRng "Select")); SELECT

 

COUNT ROWS IN RANG LAST CELL TO A1
  (setq RangROW (vlax-get-property Sht "Range" DesRng (strcat "A" (itoa 1))));get RANG LAST CELL+A1
(vlax-invoke-method RangROW "Select")
(setq RowNum0 (vlax-get-property(vlax-get-property RangROW "Rows") "Count"));COUNT ROWS IN RANG LAST CELL TO A1

 

[BIGAL]

Have a look at this returns the max col row of spread sheet rather than use -4162

 

(SETQ CURREGION (VLAX-GET-PROPERTY (VLAX-GET-PROPERTY
        (VLAX-GET-PROPERTY *EXCELAPP% "ACTIVESHEET") "RANGE" "A1") "CURRENTREGION")
      );SETQ
      (SETQ MAXROW# (VLAX-GET-PROPERTY (VLAX-GET-PROPERTY CURREGION "ROWS") "COUNT"))
      (SETQ MAXCOLUMN# (VLAX-GET-PROPERTY (VLAX-GET-PROPERTY CURREGION "COLUMNS") "COUNT"))