Does the Interior.Color property invert colors?

Question

I wrote a code snippet that allows me to get the shading color of a specific cell inside an Excel workbook sheet. I successfully got the integer RGB value by starting the COM server using MATLAB actxserver , and then accessed the Color Property of the internal object of this particular cell object. Then I get the equivalent RGB triplet of this integer, so I can use it later for building in MATLAB.

To test the performance of my code, I developed the following test: I created an Excel workbook called colorTest.xlsx with 8 different colors:

Then I run my MATLAB code, which retrieves the color information in each cell of column B. I should get a plot with colors in the same vertical order and a table with an int value and an RGB triplet of each color.

However, something unexpected happens! Look at the results:

Please note that the integer value obtained from the color property does not always correspond to the color of the original cell, for black, white, green and magenta values ​​the integer values ​​are correct, but this is not true for all other colors, For example, you can see that for red in Excel, the output tap and RGB trigger are blue.

I have added the following table with the correct results I should get for reference:

 Color Int RGB -------- -------- ----- Black 0 0 0 0 White 16777215 1 1 1 Red 16711680 1 0 0 Green 65280 0 1 0 Blue 255 0 0 1 Cyan 65535 0 1 1 Magenta 16711935 1 0 1 Yellow 16776960 1 1 0 

I got the correct integer values ​​for each color using this RGB Int calculator. .

If we compare the two tables, we can conclude that the Red and Blue channels are inverted .

Code:

The function that I run to run the test is called getCellColor . Take a look at the code:

 function getCellColor() clear all; clc; % Excel filename = 'colorTest.xlsx'; % Start Excel as ActiveX server process on local host Excel = actxserver('Excel.Application'); % Handle requested Excel workbook filename path = validpath(filename); % Cleanup tasks upon function completion cleanUp = onCleanup(@()xlsCleanup(Excel, path)); % Open Excel workbook. readOnly = true; [~, workbookHandle] = openExcelWorkbook (Excel, path, readOnly); % Initialise worksheets object workSheets = workbookHandle.Worksheets; % Get the sheet object (sheet #1) sheet = get(workSheets,'item',1); % Print table headers fprintf('Color \t Int \t RGB\n'); fprintf('--------\t --------\t -----\n'); % Create figure figure; hold on; % Loop through every color on the Excel file for row = 1:8 % Get the cell object with name of color cell = get(sheet, 'Cells', row, 1); cName = cell.value; % Get the cell object with colored background cell = get(sheet, 'Cells', row, 2); % Get the interior object interior = cell.Interior; % Get the color integer property cInt = get(interior, 'Color'); % <-- Pay special attention here(*) % Get the RGB triplet from its integer value cRGB = int2rgb(cInt); % Plot the color patch([0 0 1 1], [8-row 9-row 9-row 8-row], cRGB); % Print row with color data fprintf('%-8s\t %8d\t %d %d %d\n', cName, cInt, cRGB); end % Turn off axes set(findobj(gcf, 'type','axes'), 'Visible','off') end 

(*) This instruction is responsible for restoring an integer color.

---

Note: The functions described below do not cause problems because they are not involved in obtaining the whole color (they are used only for secondary tasks). I have included this information for completeness only.

During this process, I use three private functions from the MATLAB iofun folder: validpath , xlsCleanup and openExcelWorkbook . I just copied them to a folder called private inside the project folder.

Finally, to get the RGB triplet from an integer number of colors, I use a function that I adapted from this other function that I found on the network.

Here is the code for my int2rgb function:

 function[RGB] = int2rgb(colorInt) % Returns RGB triplet of an RGB integer. if colorInt > 16777215 || colorInt < 0 error ('Invalid int value. Valid range: 0 <= value <= 16777215') end R = floor(colorInt / (256*256)); G = floor((colorInt - R*256*256)/256); B = colorInt - R*256*256 - G*256; RGB = [R, G, B]/255; end 

---

I am trying to figure this out, but I really don't know what is going on. I did some research without much luck, but this post and this other post caught my attention. Perhaps this has something to do with my problem.

So does the Interior.Color property really invert colors?

If so, should this be considered normal behavior or is it a mistake?

---

Download link:

I packed the entire project into a .zip file and downloaded it, so you can immediately run this test on your machine. Download the file and unzip it.

getCellColor.zip

Answer 1

From the MSDN article in the RGB color model:

The RGB color model is used to indicate colors. This model determines the intensity of red, green, and blue on a scale from 0 to 255, with 0 (zero) indicating the minimum intensity. Three color settings are converted to a single integer using this formula:

RGB value = red + (green * 256) + (blue * 256 * 256)

As suggested in the chris neilsen answer , there is no “right” or “wrong” in terms of color coding. Microsoft chose this particular way of encoding colors for reasons that only they know, and I have to stick with that.

So, the RGB values ​​that I get are absolutely correct.

In the following table, I have included the RGB values ​​presented in the MSDN article next to the ones I get in MATLAB, and they are perfect.

 Color Int RGB values from MSDN -------- -------- -------- Black 0 0 White 16777215 16777215 Red 255 255 Green 65280 65280 Blue 16711680 16711680 Cyan 16776960 16776960 Magenta 16711935 16711935 Yellow 65535 65535 

Answer 2

There is no “right” or “wrong” here, Matlab and Excel simply encode the color differently. You should consider this in your code.

The closest I can find to the official source is the MSDN article, about half down, see the blue encoding example

MSDN Article

In the following examples, the inside of the cell selection is blue. Selection.Interior.Color = 16711680
Selection.Interior.Color = & HFF0000
Selection.Interior.Color = & O77600000
Selection.Interior.Color = RGB (0, 0, 255)

Answer 3

My first thought is to check the order of the RGB and BGR channels.

You could simplify this int2rgb function by int2rgb instead. Here is an example of using the values ​​you posted:

 clrs = [0; 16777215; 16711680; 65280; 255; 65535; 16711935; 16776960] for i=1:numel(clrs) bgra = typecast(int32(clrs(i)), 'uint8') end 

Output:

 clrs = 0 16777215 16711680 65280 255 65535 16711935 16776960 bgra = 0 0 0 0 bgra = 255 255 255 0 bgra = 0 0 255 0 bgra = 0 255 0 0 bgra = 255 0 0 0 bgra = 255 255 0 0 bgra = 255 0 255 0 bgra = 0 255 255 0 

Answer 4

Your int2rgb method inverts R and B. Replace them and you get the correct conversion. The Interior.Color property uses the convention, where R is the least significant, and the used FileExchange function uses the opposite convention.

To convert from int to RGB:

 B = floor(colorInt / (256*256)); G = floor((colorInt - B*256*256)/256); R = colorInt - B*256*256 - G*256; colorRGB = [RGB]; 

To convert from RGB to int:

 colorInt = colorRGB * [1 256 256*256]';