Topic: Excel Scripting - Color Conversion

[Grrr1337]

Hey guys,
First of all - Happy new 2018!
I've decided to start-off that year with some more excel scripting practice (more specifically dealing with colors).
I was very surprised when I saw that the color pallete in Excel is not the same as in AutoCAD -
Say prompt for an ACI color from AutoCAD and apply it to some cell in excel:

Code - Auto/Visual Lisp: [Select]

1. (defun C:test ( / *error* clr xlapp xlwbs xlwbk xlsht xlcells xlrng xlint msg )
2.  
3.   (defun *error* ( m )
4.     ; (and xlapp (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit)))
5.     (foreach o (reverse (list clr xlapp xlwbs xlwbk xlsht xlcells xlrng xlint msg)) (and (eq 'VLA-OBJECT (type o)) (vl-catch-all-apply 'vlax-release-object (list o))) )
6.     (gc) (and m (princ m)) (princ)
7.   ); defun *error*
8.  
9.   (cond
10.     ( (not (setq clr (acad_colordlg 1 nil))) )
11.     ( (not (setq xlapp (vlax-get-or-create-object "Excel.Application"))) (prompt "\nUnable to interfere with Excel application.") )
12.     (
13.       (vl-catch-all-error-p
14.         (setq msg
15.           (vl-catch-all-apply
16.             (function
17.               (lambda ( / )
18.                 (setq xlwbs (vlax-get-property xlapp 'Workbooks))
19.                 (setq xlwbk (vlax-invoke-method xlwbs 'Add))
20.                 (setq xlsht (vlax-get-property  xlapp 'ActiveSheet))
21.                 (setq xlcells (vlax-get-property xlsht 'Cells))
22.                 (setq xlrng (vlax-variant-value (vlax-get-property xlcells 'Item 1 1)))
23.                 (setq xlint (vlax-get-property xlrng 'Interior))
24.                 (vl-catch-all-apply 'vlax-put-property (list xlint 'ColorIndex (vlax-make-variant clr vlax-vbInteger))) ; vlax-vbLong - no difference
25.                 (vlax-put-property xlapp 'Visible :vlax-true)
26.               ); lambda
27.             ); function
28.           ); vl-catch-all-apply
29.         ); setq msg
30.       ); vl-catch-all-error-p
31.       (prompt (strcat "\nError: " (vl-catch-all-error-message msg)))
32.     )
33.   ); cond
34.   (*error* nil) (princ)
35. ); defun C:test

So does anyone know what type of color Excel uses? And how to convert from ACI/RGB to match it? 



With VBA that color manipulation task looks very easy (but I guess that it might have the same ColorIndex issue) :

Code: [Select]

Range("A1").Interior.ColorIndex = 37

Range("A1").Interior.ColorIndex = 0

MsgBox Selection.Interior.ColorIndex

Range("A1").Interior.Color = RGB(255, 0, 0)

BTW the Interior object supports Color property aswell - not sure which one would be the right to use, just leaving this for reference:

Code: [Select]

; Interior: nil
; Property values:
;   Application (RO) = #<VLA-OBJECT _Application 0000007c535314b8>
;   Color = 1.67772e+07
;   ColorIndex = -4142
;   Creator (RO) = 1480803660
;   Gradient (RO) = nil
;   InvertIfNegative = Exception occurred
;   Parent (RO) = #<VLA-OBJECT Range 0000007c4bf42688>
;   Pattern = -4142
;   PatternColor = 0
;   PatternColorIndex = -4142
;   PatternThemeColor = -4142
;   PatternTintAndShade = 0.0
;   ThemeColor = -4142
;   TintAndShade = 0.0
; No methods

[Grrr1337]

...well the true-color way kinda works, but for some values there is an expection (visual judgement):

Code - Auto/Visual Lisp: [Select]

1. ; Example for true color prompt:
2. (defun C:test ( / *error* tmp clr xlapp xlwbs xlwbk xlsht xlcells xlrng xlint msg )
3.  
4.   (defun *error* ( m )
5.     ; (and xlapp (vl-catch-all-apply 'vlax-invoke-method (list xlapp 'quit)))
6.     (foreach o (reverse (list clr xlapp xlwbs xlwbk xlsht xlcells xlrng xlint msg)) (and (eq 'VLA-OBJECT (type o)) (vl-catch-all-apply 'vlax-release-object (list o))) )
7.     (gc) (and m (princ m)) (princ)
8.   ); defun *error*
9.  
10.   (cond
11.     ( (not (setq clr (cdr (assoc 420 (acad_truecolordlg 1))))) )
12.     ; ( (not (setq clr (acad_truecolordlg 1))) )
13.     ; ( (progn (setq clr (cond ((setq tmp (cdr (assoc 420 clr))) tmp) ((LM:ACI->TRUE (cdr (assoc 62 clr)))) )) nil) )
14.     ( (not (setq xlapp (vlax-get-or-create-object "Excel.Application"))) (prompt "\nUnable to interfere with Excel application.") )
15.     (
16.       (vl-catch-all-error-p
17.         (setq msg
18.           (vl-catch-all-apply
19.             (function
20.               ; (
21.               (lambda ( / )
22.                 (setq xlwbs (vlax-get-property xlapp 'Workbooks))
23.                 (setq xlwbk (vlax-invoke-method xlwbs 'Add))
24.                 (setq xlsht (vlax-get-property  xlapp 'ActiveSheet))
25.                 (setq xlcells (vlax-get-property xlsht 'Cells))
26.                 (setq xlrng (vlax-variant-value (vlax-get-property xlcells 'Item 1 1)))
27.                 (setq xlint (vlax-get-property xlrng 'Interior))
28.                 (vl-catch-all-apply 'vlax-put-property (list xlint 'Color (vlax-make-variant clr vlax-vbLong))) ; vlax-vbLong
29.                 ; (vlax-dump-object xlrng t)
30.                 ; (vlax-dump-object xlint t)
31.                 (vlax-put-property xlapp 'Visible :vlax-true)
32.               ); lambda
33.               ; )
34.             ); function
35.           ); vl-catch-all-apply
36.         ); setq msg
37.       ); vl-catch-all-error-p
38.       (prompt (strcat "\nError: " (vl-catch-all-error-message msg)))
39.     )
40.   ); cond
41.   (*error* nil) (princ)
42. ); defun C:test

As for the indexcolor, found this color table chart from here:

[Lee Mac]

Hint: Use my LM:rgb->ole function from here.

[Grrr1337]

Hint: Use my LM:rgb->ole function from here.

So it was OLE type afterall... worked like a charm, with quick changes:

Code: [Select]

(cond
    ( (not (setq clr (acad_truecolordlg 1))) )
    ( (progn (setq clr (cond ((setq tmp (cdr (assoc 420 clr))) (LM:True->OLE tmp))((LM:ACI->OLE (cdr (assoc 62 clr)))))) nil) )
    ...
    (vl-catch-all-apply 'vlax-put-property (list xlint 'Color (vlax-make-variant clr vlax-vbLong))) ; vlax-vbLong
    ...Thank you very much, Lee!