Color code the cells in excel, using a colorbar

Hello,

An automated code compares the values of multiple matrixes (with values of zeros and ones) and creates a new matrix and specifies how many of the matrixes had one in each cell.

For example:

A=[ 0 0 0 0 ; 0 1 1 0 ; 0 1 1 0 ; 0 0 0 0];
B=[ 1 0 1 0 ; 0 1 0 0 ; 0 1 1 0 ; 1 1 1 1];
C=[ 0 1 1 1 ; 0 1 1 0 ; 0 0 1 0 ; 0 0 1 1];
D=[ 1 1 2 1 ; 0 3 2 0 ; 0 2 3 0 ; 1 1 2 2]; % after checking it creates D

Now, after writing this matrix in excel using

xlswrite('Binary_Analysis.xlsx',D);

I want to color code the cells in excel, using a colorbar, from red=min to green=Max number (remember the number of matrixes is a variable, in this case, A, B, and C)

Using something like

Excel = actxserver('excel.application'); % Connect to Excel
WB = Excel.Workbooks.Open(fullfile(pwd, 'Binary_Analysis.xlsx','B1:M10'),0,false); % Get Workbook object
% Set the color of cell "A1" of Sheet 1 to RED
WB.Worksheets.Item(1).Range('A1').Interior.ColorIndex = 3; 
WB.Save(); % Save Workbook
WB.Close(); % Close Workbook
Excel.Quit(); % Quit Excel

This example specifically changes the color of cell A1 but I want a loop that checks the values in each cell and then indicates a color.

Another thing, I specifically said to read the excel file B1:M10 but I do not know the size of each matrix in advance. I just wanted to exclude the axis of the matrix. Do not read the first column and last row.

This is an automated code so it checks the size of each matrix.

Thank you

Answer 1

[Vaibhav]

Hi Yekta

I understand that you would like to create a loop that checks the values in each cell and then indicates a colour. You can adjust the initial code by dynamically determining the range of cells to color code based on the size of the matrix.

Here is a code snippet for your reference:

A=[ 0 0 0 0 ; 0 1 1 0 ; 0 1 1 0 ; 0 0 0 0];
B=[ 1 0 1 0 ; 0 1 0 0 ; 0 1 1 0 ; 1 1 1 1];
C=[ 0 1 1 1 ; 0 1 1 0 ; 0 0 1 0 ; 0 0 1 1];
D=[ 1 1 2 1 ; 0 3 2 0 ; 0 2 3 0 ; 1 1 2 2]; % after checking it creates D


xlswrite('Binary_Analysis.xlsx', D);

% Connect to Excel
Excel = actxserver('excel.application');
Excel.Visible = true; % Optional: make Excel visible
WB = Excel.Workbooks.Open(fullfile(pwd, 'Binary_Analysis.xlsx'));

% Get the used range, excluding the first column and last row
sheet = WB.Worksheets.Item(1);
usedRange = sheet.UsedRange;
numRows = size(D, 1) - 1; % Assuming D is the matrix you wrote to Excel
numCols = size(D, 2) - 1;

% Loop through each cell in the specified range
for i = 2:numRows+1 % Start from 2 to exclude the first column
    for j = 1:numCols
        cellValue = sheet.Cells.Item(i, j).Value;
        % Map cellValue to a color
        % Assuming cellValue ranges from 0 to max(D(:)), you can adjust the color mapping logic as needed
        if cellValue == min(D(:))
            colorIndex = 3; % Red for min value
        elseif cellValue == max(D(:))
            colorIndex = 4; % Green for max value
        else
            % For intermediate values, you might need a more complex mapping
            % This is a simple linear interpolation between red and green
            % More sophisticated mappings may require custom RGB values
            colorIndex = round(3 + (cellValue - min(D(:))) / (max(D(:)) - min(D(:))) * (4 - 3));
        end
        sheet.Cells.Item(i, j).Interior.ColorIndex = colorIndex;
    end
end

% Save and close
WB.Save();
WB.Close();
Excel.Quit();

Hope this gets you started!