Discover MakerZone

MATLAB and Simulink resources for Arduino, LEGO, and Raspberry Pi

Learn more

Discover what MATLAB® can do for your career.

Opportunities for recent engineering grads.

Apply Today

Thread Subject:
Read excel cell fill color from matlab?

Subject: Read excel cell fill color from matlab?

From: Loban

Date: 7 Feb, 2008 21:54:48

Message: 1 of 5

I need to somehow read in the cell fill color of the cells in an excel
file from matlab. I know it's easy to import the data in the cells,
but I need the actual background fill color. I have a rather large
spreadsheet with a lot of configuration options for a system indicated
by cell color and I need to read that color into matlab. Is this
possible using ActiveX controls? Has anyone done this before? Do you
have a snippet of code you could show me?

Subject: Read excel cell fill color from matlab?

From: Ben Taylor

Date: 17 Oct, 2009 21:33:03

Message: 2 of 5

Have you had any luck setting this up? Being able to read Excel cell background colors into Matlab? I have a large Excel file that I am stuck with and need to import and based on the background color in the cells I have to do different actions? Thanks.

Subject: Read excel cell fill color from matlab?

From: Robert Aungst

Date: 20 Oct, 2009 21:15:21

Message: 3 of 5

Here's the basic code that would be required.

h = actxserver('Excel.Application')
h.Visible = 1
b = h.Workbooks.Add
w = b.Worksheets.Add;
get(w.Range('A1').Interior);

This example is somewhat boring because you're creating the worksheet from scratch, so you know that the color is already, but what you're looking for is the Interior property.

It'll look something like this when you get there:

Interface.Microsoft_Excel_11.0_Object_Library._Workbook

        Application: [1x1 Interface.Microsoft_Excel_11.0_Object_Library._Application]
        Creator: 'xlCreatorCode'
        Parent: [1x1 Interface.Microsoft_Excel_11.0_Object_Library.Range]
        Color: 16777215
        ColorIndex: -4142
        InvertIfNegative: [1x241 char]
        Pattern: -4142
        PatternColor: 0
        PatternColorIndex: -4142

You'll have to look up what that Color field actually means. You can loop over what range you're interested and get the color of each cell.

Cheers,
Robert

"Ben Taylor" <bentaylorche@gmail.com> wrote in message <hbdd6f$snb$1@fred.mathworks.com>...
> Have you had any luck setting this up? Being able to read Excel cell background colors into Matlab? I have a large Excel file that I am stuck with and need to import and based on the background color in the cells I have to do different actions? Thanks.

Subject: Read excel cell fill color from matlab?

From: Yair Altman

Date: 21 Oct, 2009 00:33:04

Message: 4 of 5

"Robert Aungst" <robert.aungst@gmail.com> wrote in message <hbl999$51k$1@fred.mathworks.com>...
> Here's the basic code that would be required.
>
> h = actxserver('Excel.Application')
> h.Visible = 1
> b = h.Workbooks.Add
> w = b.Worksheets.Add;
> get(w.Range('A1').Interior);
>
> This example is somewhat boring because you're creating the worksheet from scratch, so you know that the color is already, but what you're looking for is the Interior property.
>
> It'll look something like this when you get there:
>
> Interface.Microsoft_Excel_11.0_Object_Library._Workbook
>
> Application: [1x1 Interface.Microsoft_Excel_11.0_Object_Library._Application]
> Creator: 'xlCreatorCode'
> Parent: [1x1 Interface.Microsoft_Excel_11.0_Object_Library.Range]
> Color: 16777215
> ColorIndex: -4142
> InvertIfNegative: [1x241 char]
> Pattern: -4142
> PatternColor: 0
> PatternColorIndex: -4142
>
> You'll have to look up what that Color field actually means. You can loop over what range you're interested and get the color of each cell.
>
> Cheers,
> Robert
>
> "Ben Taylor" <bentaylorche@gmail.com> wrote in message <hbdd6f$snb$1@fred.mathworks.com>...
> > Have you had any luck setting this up? Being able to read Excel cell background colors into Matlab? I have a large Excel file that I am stuck with and need to import and based on the background color in the cells I have to do different actions? Thanks.


VBA colors are in Microsoft decimal RGB format: 0xRRGGBB. For example, in this specific case, 16777215 = 0xFFFFFF => Matlab color [1.0,1.0,1.0]. Reversing this conversion, a Matlab color of [.2,.4,.8] => 0x3366CC = 3368652

Here's a handy function I have for the Matlab=>VBA color conversion, which also handles string colors. Doing a reverse function is left as an exercise to the reader...

%% Convert Matlab color to a VBA color (Microsoft decimal RGB format: 0xRRGGBB)
function color = m2vbColor(color)
    try
        % Convert color names to RBG triple (0-1) if not already in that format
        if ischar(color)
            switch lower(color)
                case {'y','yellow'}, color = [1,1,0];
                case {'m','magenta'}, color = [1,0,1];
                case {'c','cyan'}, color = [0,1,1];
                case {'r','red'}, color = [1,0,0];
                case {'g','green'}, color = [0,1,0];
                case {'b','blue'}, color = [0,0,1];
                case {'w','white',''}, color = [1,1,1]; % empty '' also sets white color
                case {'k','black'}, color = [0,0,0];
                otherwise, error(['Invalid color specified: ' color]);
            end
        elseif ~isnumeric(color) | length(color)~=3 %#ok ML6
            error(['Invalid color specified: ' color]);
        end

        % Convert to Microsoft decimal RGB format
        color = sum(floor(color*255) .* (256.^[0,1,2]));
    catch
        error(['Invalid color specified: ' lasterr]);
    end


Yair Altman
http://UndocumentedMatlab.com
 

Subject: Read excel cell fill color from matlab?

From: Sander Aerts

Date: 5 Nov, 2009 20:20:06

Message: 5 of 5

Yair,

i have a few things to add to you post concerning the VBA representation of colors in the hexadecimal format.

> VBA colors are in Microsoft decimal RGB format: 0xRRGGBB. For example, in this specific case, 16777215 = 0xFFFFFF => Matlab color [1.0,1.0,1.0]. Reversing this conversion, a Matlab color of [.2,.4,.8] => 0x3366CC = 3368652

while the 0xRRGGBB format is true for html rgb format, it is slightly different for VBA code which actually (don't ask me why) uses 0xBBGGRR format. Using the html code would result in a wrong color.

The function you suggested does indeed look very handy but needs altering. The piece where you convert the rgb color to the hex format should now be (at least i think, i'm at home now so i couldn't check):

> % Convert to Microsoft decimal RGB format
> color = sum(floor(color*255) .* (256.^[2,1,0]));


Greets
Sander

Tags for this Thread

What are tags?

A tag is like a keyword or category label associated with each thread. Tags make it easier for you to find threads of interest.

Anyone can tag a thread. Tags are public and visible to everyone.

Contact us