MATLAB Answers

1

How do I write data to an Excel Spreadsheet with a custom cell background color and custom font color via MATLAB?

I have data that I want to write to a cell in an Excel Spreadsheet, but I also want to assign a background color and font color to this cell. I would like to know the way to do this through MATLAB.

1 Answer

Answer by MathWorks Support Team on 4 Oct 2017
 Accepted Answer

This task can be accomplished using ActiveX with Excel. The following example function shows one way to do this:
 
function xlscolor(file, data, range)
%XLSCOLOR writes data like XLSWRITE
%but adds color options
% XLSCOLOR(FILE,DATA,RANGE) writes the variable in
% DATA to FILE, in the range specified by RANGE.
%Obtain the full path name of the file
file = fullfile(pwd, file);
%Open an ActiveX connection to Excel
h = actxserver('excel.application');
%Create a new work book (excel file)
wb=h.WorkBooks.Add();
%Select the appropriate range
ran = h.Activesheet.get('Range',range);
%write the data to the range
ran.value = data;
%The color is specified as an BGR triplet in hexadecimal notation
%RED = 0000FF
%BLUE= FF00FF
%GREEN=00FF00
%BLACK=000000
%WHITE=FFFFFF
ran.interior.Color=hex2dec('00FF00'); %Green
ran.font.Color=hex2dec('FF0000'); %Blue
% save the file with the given file name, close Excel
wb.SaveAs(file);
wb.Close;
h.Quit;
h.delete;
end
Example invocation:
xlscolor('Tone.xls',rand(5,5),'A1:E5')
For more information on programming with Excel (For example, the commands like 'Add'), please consult the "Microsoft Excel Visual Basic Reference" via Excel's help menu.
For further information please refer:
1. ACTXSERVER:
2. MATLAB software as an Automation client and the Microsoft® Excel® spreadsheet program as the server:
3. Information on the hexadecimal notation of colors values (IMPORTANT: please note, Microsoft uses BGR ordering as opposed to the RGB notation explained below)

  2 Comments

What if the range is unknown? or to put it in a better way, if one wants to color only those cells that have specific strings characters in them. I did try xlsfont, but it does not work if I want to do
xlsfont('file.xls','Sheet1','Find','something','colorindex',3);
Can anyone help me on this?
Hi there, I have a follow-up question. I have data that has already been written to Excel, and would now want to mark certain cells (e.g. outlier values) by a different color. The above answer seems to apply only to new files that are created in the process - is it possible to adjust existing ones?

Sign in to comment.