Coloring variable cells in excel?

33 次查看（过去 30 天）
Abdelrahman Taha
Abdelrahman Taha 2020-3-6
评论： Walter Roberson 2020-3-8
Hi,
I am writing output of some caluations in excel, and I need to color certain cells in red based on some conditions. How could I do that if i don't know a priori which cells to be colored?
Thanks
  2 个评论
Walter Roberson
Walter Roberson 2020-3-6
MATLAB does not provide any function for this purpose. You will need to use ActiveX to talk to excel and command it to select the cells you want to color and apply color to the current cells.
Abdelrahman Taha
Abdelrahman Taha 2020-3-6
I know that. My quesion is about when the cells to be colored are variables, which means I can't know which cells to change their colors beforehand. It all depends on the contents of the cell. So, let's say I want to write a 5X5 matrix in excel. And I want the cells of the last column to be colred in red if their values exceed a certain value, how could I do that?. So, the problem is that I can't name the range of cells or the cell to be colored like 'D1' or 'D1:D5', I can't do that!

请先登录，再进行评论。

请先登录，再回答此问题。

回答（1 个）

Walter Roberson
Walter Roberson 2020-3-6
So, let's say I want to write a 5X5 matrix in excel. And I want the cells of the last column to be colred in red if their values exceed a certain value, how could I do that?
Read the matrix from Excel. Do whatever test is appropriate, getting back a logical matrix the same size indicating whether each individual cell is to be colored the way you are concerned about at the time.
find() on the logical matrix using the two-output form of find(), returning row and column numbers.
Now, use one of
https://www.mathworks.com/matlabcentral/fileexchange/28343-column-converter-for-excel
https://www.mathworks.com/matlabcentral/fileexchange/33905-convert-row-column-number-into-xlswrite-compatible-format
https://www.mathworks.com/matlabcentral/fileexchange/58917-getexcelrange-rowlimits-columnlimits
or similar File Exchange contributions, to convert the row and column numbers to excel cell name form.
You can now use a technique such as at https://www.mathworks.com/matlabcentral/answers/3352-how-to-set-excel-cell-color-to-red-from-matlab#answer_4983 to color the cells, one at a time. Where Jiro wrote
WB.Worksheets.Item(1).Range('A1').Interior.ColorIndex = 3;
you would pass the variable containing the Excel reference instead of hard-coding 'A1' .
For arrays that are no more than 26 columns, you can simplify the code by not using the File Exchange contributions to create the cell references:
L = 'A' : 'Z';
all_cell_references = compose('%s%d', L(column_numbers).', row_numbers(:)); %r2016b or later
for K = 1 : length(all_cell_references)
WB.Worksheets.Item(1).Range(all_cell_references{K}).Interior.ColorIndex = 3;
end
Make sure you re-check the color indices: My Answer in the referenced post gives a link to the list of colors.
  4 个评论
显示 2更早的评论
Abdelrahman Taha
Abdelrahman Taha 2020-3-8
Yes, I am working on MS Windows and I did start from (Excel = actxserver('excel.application');)
I think it's supposed to be a defined function in matlab that converts a num to an excel cell name and that there's no need to talk to excel beforehand.
Walter Roberson
Walter Roberson 2020-3-8
If you were to dig far enough into the internal code of routines such as readtable, it is plausible that you might find an internal package function that converts numbers to column names. However there is no documented MATLAB function for that purpose.
I gave links to a few File Exchange contributions that can do the translation, and I gave code for it that works for up to 26 columns.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by