change excel spreadsheet color when exporting data

Hi

When I'm writing data out to excel, I would like to color code cells with values less than 0.0. I'm currently using xlswrite

xlswrite(xlsfile,[header; output],sheetText,'A2');

where header is the column header for the data, and output is the numeric data being exported. I'm usually working with data sized 100 rows by 15 column.

I've seen scripts like http://www.mathworks.com/matlabcentral/fileexchange/4981-xlsfont--xlsalign--xlsborder--xlswordart--xlscomment/content/xlsfont.m and http://www.mathworks.com/matlabcentral/answers/102070-how-do-i-write-data-to-an-excel-spreadsheet-with-a-custom-cell-background-color-and-custom-font-colo that color cells, but they require you know the address of the cell beforehand...

I was wondering if there was a way to look and loop inside excel, find a negative number, highlight it red and loop to the next... Thanks

Answer 1

[KRUNAL]

Hey, even I am also facing the same issue. However after observing the 1st link you have posted I think you can do something like this:

%eg check the numbers that are greater than 100 in each row/column and should print 'bigger'(text1) in cells where you get num > 100 else print smaller(text2) %%

 if num > 100
%whatever you want to output say any "text" %
xlsfont(filename,sheetname,'Find',text1, color,color index number1)
else
xlsfont(filename,sheetname,'Find',text2, color,color index number2)
end

Is this something you are trying to do? If so I would suggest you should try it,if you haven't tried it yet

Answer 2

[KRUNAL]

I tried what I had asked you to do. However for me there was unusual thing that is happening. After reading all data, it does change color of the cell but it does only for the text that it gets for the 1st time out of the 10-12 cells containing the same text. Rest all are remaining the same and this happens in all the sheets of that excel file(I have 5-6 sheets in 1 excel file).What about you?

Answer 3

[Image Analyst]

Sure, you can do it. Either find out the cell addresses before you throw the data in there, or scan it afterwards. Either way you'll have to use ActiveX. Will you be willing to use ActiveX? You can use xlsfont but you'll have to modify it so that it doesn't launch and shutdown Excel every time you call it or else it will take an eternity to modify a bunch of cells.

[KRUNAL]

When I am using xlsfont it doesn't launch and shutdown excel on its own for each of the time I am calling it.

For ActiveX you suggested one of the methods as to scan for the desired text or number after the data is thrown in various cells right? Can you suggest an example of it?

[Image Analyst]

Are you sure? Look at these lines of code:

Excel = actxserver('Excel.Application');
set(Excel,'Visible',0);
...
invoke(Workbook, 'Save');
invoke(Excel, 'Quit');
delete(Excel);

Sure looks like it to me. Maybe you're just not noticing if it never set Excel to be visible (in fact it purposely makes it hidden).

After seeing those lines of code, why are you so sure that it never launch and shuts down Excel?

[KRUNAL]

I have mentioned in my above comment that I have tried xlsfont and not actxserver

[Image Analyst]

I know that. And the lines of code I showed you are internal to xlsfont. xlsfont uses ActiveX to launch and shutdown Excel.

[Image Analyst]

KRUNAL's "Answer" moved here since it's a reply to me:

ok,but yes.It really isnt shutting down.U can see my original code where I have added the xlsfont line

clc;
clear all;
SEfile = '\\psf\Home\Downloads\06_Data\Events\STG04_SE\';
LBfile = '\\psf\Home\Downloads\06_Data\Events\STG04_LB\';
dstfile = '\\psf\Home\Downloads\06_Data\Events\STG04_F\PostRock_Varnum4-1H_LOC1_STG04.xlsx';
d1=dir(fullfile(SEfile,'*.xlsx'));
d2=dir(fullfile(LBfile,'*.xlsx'));
for i=1:length(d1)
  sheet='sheet1';
  a = {d1(i).name};
  c = {d2(i).name};
  ps = {'P_SE'};
  xlswrite(dstfile,ps,i,'A1');
  ps1 = {'S_SE'};
  xlswrite(dstfile,ps1,i,'B1');
  pl = {'P_LB'};
  xlswrite(dstfile,pl,i,'C1');
  pl1 = {'S_LB'};
  xlswrite(dstfile,pl1,i,'D1');
  pcorr = {'S_P'};
  xlswrite(dstfile,pcorr,i,'AA1');
  scorr = {'S_S'};
  xlswrite(dstfile,scorr,i,'AB1');
  b = strcat(SEfile,a);
  d = strcat(LBfile,c);
  v1=xlsread(b{1},sheet,'AA:AB') ;
  v2=xlsread(d{1},sheet,'AA:AB') ;
  xlRange = 'A2:B11';
  xlswrite(dstfile,v1,i,xlRange);
  xlRange1 = 'C2:D11';
  xlswrite(dstfile,v2,i,xlRange1);
  Xp= xlsread(dstfile,i);
  Xp(:,27) = Xp(:,3)-Xp(:,1);
  Yp = Xp(:,27);
  ind = Yp >= 350; %1's where you want to check values again.
  Yp = cell(size (Yp)); %make a cell array so you can have different length text.
  Yp(ind)= {'check values again'}; %for the indexes (marked with logical 1) put in this text.
  Yp(~ind)= {'no change'}; %for !ind (inverse) put no change.
  xlRangep = 'AA2:AA11';
  xlswrite (dstfile,Yp,i,xlRangep);
  Xp(:,28) = Xp(:,4)-Xp(:,2);
  Ys = Xp(:,28);
  ind1 = Ys >= 350; %1's where you want to check values again.
  Ys = cell(size (Ys)); %make a cell array so you can have different length text.
  Ys(ind1)= {'check values again'}; %for the indexes (marked with logical 1) put in this text.
  Ys(~ind1)= {'no change'}; %for !ind (inverse) put no change.
  xlRanges = 'AB2:AB11';
  xlswrite (dstfile,Yp,i,xlRanges);
  xlsfont(dstfile,'Sheet1','Find','no change','color',3);
end
system(dstfile)

[Image Analyst]

A lot of people think incorrectly like you. They'll do things like put xlsread() and xlswrite() in loops. Then they complain about it being really slow. Well if you're launching and shutting down Excel thousands of times in a loop, it's going to be slow.

You, and most others, don't realize that every time you call xlsread it launches Excel and shuts down Excel. And every time you call xlswrite() it launches Excel and shuts down Excel. So your script launches and shuts down Excel dozens of times or more. If you don't believe me, then just all the Mathworks and ask them.

And it's the same thing with xlsfont, as I showed you the commands in the function that do exactly that: launch and shutdown Excel.

Because it's so slow, when I want to put stuff into Excel in a loop, I either

1. launch Excel with ActiveX and in the loop write with xlswrite1(), not xlswrite. xlswrite assumes Excel is open so it doesn't launch it or shut it down, saving a LOT of time. Then shutdown using the Excel.Quit ActiveX method.
2. Or save/append all my data into array(s) (inside the loop), then after the loop, construct one big cell array (if necessary to conbine several arrays) and toss all my data at one time into Excel with xlswrite().

With either of those methods, Excel is just launched once .

[KRUNAL]

Image Analyst can you suggest me how can I achieve the same using ActiveX? I mean I want to achieve the same using ActiveX

I have never used ActiveX server and so I am doing it with the above mentioned method. can you suggest me some codes which will help understand and write the above code using ActiveX method. I tried understanding it from the help of matlab but I wasn't able to clear it that much as it was using GUI with it.If possible a format of code lines to write the above code. It will really be helpful if its possible for you to do the 2nd option

[Image Analyst]

See my Excel demo, attached.

[KRUNAL]

Thank you. i will try to understand this file and based on it will try to create my own file from it. If required,will post questions on it here.

[KRUNAL]

I tried running the 1st part.It basically creates a new excel file and then throws the error of file open and existing