change excel spreadsheet color when exporting data

Question

Marco 2014-7-18

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/142475-change-excel-spreadsheet-color-when-exporting-data

评论： KRUNAL 2014-8-5

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

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

请先登录，再进行评论。

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

Answer 1

KRUNAL 2014-7-29

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/142475-change-excel-spreadsheet-color-when-exporting-data#answer_146773

在 MATLAB Online 中打开

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

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

请先登录，再进行评论。

Answer 2

KRUNAL 2014-8-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/142475-change-excel-spreadsheet-color-when-exporting-data#answer_147104

编辑：KRUNAL 2014-8-1

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?

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

请先登录，再进行评论。

Answer 3

Image Analyst 2014-8-1

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/142475-change-excel-spreadsheet-color-when-exporting-data#answer_147109

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.

10 个评论
显示 8更早的评论隐藏 8更早的评论

Image Analyst 2014-8-1

在 MATLAB Online 中打开

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 2014-8-1

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

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.
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 2014-8-4

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 2014-8-5

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

请先登录，再进行评论。

change excel spreadsheet color when exporting data

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

回答（3 个）

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

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

10 个评论
显示 8更早的评论隐藏 8更早的评论

另请参阅

类别

标签

Community Treasure Hunt

change excel spreadsheet color when exporting data

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

回答（3 个）

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

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

10 个评论 显示 8更早的评论隐藏 8更早的评论

另请参阅

类别

标签

Community Treasure Hunt

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

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

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

10 个评论
显示 8更早的评论隐藏 8更早的评论