MATLAB Answers

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

252 views (last 30 days)
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.

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 4 Oct 2017
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

KRUNAL
KRUNAL on 30 Jul 2014
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?
bloodymir
bloodymir on 29 Aug 2017
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.

More Answers (0)

Community Treasure Hunt

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

Start Hunting!