Azzera filtri
Azzera filtri

Color code the cells in excel, using a colorbar

5 visualizzazioni (ultimi 30 giorni)
Yekta Kamali
Yekta Kamali il 30 Giu 2021
Risposto: Vaibhav il 25 Apr 2024
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

Risposte (1)

Vaibhav
Vaibhav il 25 Apr 2024
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!

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by