Conditional formatting in Excel 2010 from MATLAB

Question

Ashish 2014-10-29

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/160533-conditional-formatting-in-excel-2010-from-matlab

评论： Stephen23 2015-1-4

I write an excel sheet using active COM from MATLAB. The sample data is attached in the image.

The columns and rows can vary and are not fixed, i.e. the number of turbines (rows) and number of bins(columns) can vary. I want to condition format for each of the binMean values (B2:Q6). The condition format has to be performed separately for each column.

Following is the conditional formatting required: For each column (i_col), the value of binMean should lie between 9th percentile and 91st percentile. If not, then the color of that cell should be RED! Following is the general matlab code that I wrote to determine these outliers (9th and 91st percentile)

if (binMean(i_turbine,i_col) >= prctile(binMean(:,i_col),9)) && ...
                (binMean(i_turbine,i_col) <= prctile(binMean(:,i_col),91))|| ...
                isnan(binMean(i_turbine,i_col))

I need help to write the code using active server to condition format from MATLAB.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

Answer 1

Gaurav Shukla 2014-10-29

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/160533-conditional-formatting-in-excel-2010-from-matlab#answer_156982

在 MATLAB Online 中打开

Excel = actxserver('excel.application');
WB = Excel.Workbooks.Open(File_Path,0,false);
for j=1:length(Result_Frm_Scope)
    for i = 1:length(Result_Frm_Scope)
        rng = strcat(Starting_Cell,num2str(i+1));
        if(Condition_For_Formating )
            WB.Worksheets.Item(1).Range(rng).Font.ColorIndex = 4;
%             WB.Worksheets.Item(1).Range(rng).Value = 'Pass';
        else
            WB.Worksheets.Item(1).Range(rng).Font.ColorIndex = 3;
%             WB.Worksheets.Item(1).Range(rng).Value = 'Fail';
        end
    end
end

Just Update the Condition For Formating. It works fine for me.

2 个评论
显示无隐藏无

Ashish 2014-10-29

Thanks Gaurav!

Stephen23 2015-1-4

Note that you should not use either of i or j for the loop variables, as these are both names of the inbuilt imaginary unit . This is poor coding practice in MATLAB.

请先登录，再进行评论。