How to format cell font size, font color, and alignment in Excel from Matlab GUI program

Dear Matlab users,

I wrote these lines to export some data from my Matlab GUI to an Excel sheet. But, I need to do the following:

1. control the font size and color.
2. control the cell alignment.

I've read a lot of generous explanations that discuss a lot of issues, but could not find an answer to what I simply need.

Here is the code. Would you tell me what to add and where exactly?

header={'Radar Set', 'Antenna Height' 'Tilting Angle', 'Target Type', 'Long', 'Lat', 'Elevation', 'Max. Range', 'Area', 'Date', 'Time'};
xlswrite('SavedData.xlsx',header);
Data = {radar_set, get(handles.ant_height, 'String'), get(handles.tilt_ang, 'String'), target, get(handles.long,'string'), get(handles.lat,'string'), get(handles.alt,'string'), get(handles.maxrange,'string'), get(handles.area,'string'), datestr(clock, 'dd/mm/YYYY'), datestr(clock, 'HH:MM:SS')};
[number, strings, row] = xlsread('SavedData.xlsx');
lastRow = size(row,1);
nextRow = lastRow+1;
cellReference = sprintf('A%d', nextRow);
xlswrite('SavedData.xlsx', Data, 'Sheet1', cellReference);

Answer 1

[Milan Bansal]

Hi Hazem Kamel,

To format the cells in an excel sheet using MATLAB, you can use the actxserver. This will open an instace of Excel and then you can interact with Excel from MATLAB code. Please see to the following example code for reference:

% Create a sample table
data = {'Header1', 'Header2', 'Header3'; 1, 2, 3; 4, 5, 6; 7, 8, 9};
xlswrite('formatted_file.xlsx', data);

% Create an ActiveX server to interact with Excel
excel = actxserver('Excel.Application');

% Open the saved Excel file, Get the first worksheet
workbook = excel.Workbooks.Open(fullfile(pwd, 'formatted_file.xlsx'));
sheet = workbook.Sheets.Item(1);

% Specify the range of cells you want to format
range = sheet.Range('A1:C4'); 

% set formatting
range.Font.Size = 14;
range.Font.Color = hex2dec('FF0000'); 
range.HorizontalAlignment = -4108; % -4108 is the constant for center alignment in Excel
range.VerticalAlignment = -4108; % -4108 is the constant for center alignment in Excel

% Save the workbook
workbook.Save;
workbook.Close(false);

% Quit the ActiveX server
excel.Quit;
delete(excel);

Please refer to the documentation to learn more about actxserver.

https://www.mathworks.com/help/matlab/ref/actxserver.html

Hope this helps!

[Hazem Kamel]

Dear Milan Bansal

Thanks a lot for your concern and kind help.

The code you sent is really helpful. Does it work for Matlab code generated with a GUI??

Answer 2

[Image Analyst]

I'm attaching a class, Excel_utils.m, that has all kinds of functions for formatting all kinds of things in Excel. I use it a lot. It also does things like find the first blank row in a column, etc.

Alternatively, if your data always goes into the same locations, you can just make a template workbook with all the custom formatting you want and save it to disk. Then make a copy of it in your code. Then write to the new copy you just made and it will have all the custom formatting you created.

I'm also attaching a demo on how to use ActiveX to open Excel, write stuff to it, and save it.

And a demo where it converts the row and column to the 'A1' cell reference that Excel prefers. For example row 3, column 27 would be 'AA3'.

These are the functions in the class file:

% 	Methods for class Excel_utils:
% 	Static methods:
% 
% 	ActivateSheet                          DeleteEmptyExcelSheets                 FormatDecimalPlaces                    LeftAlignSheet                         
% 	AlignCells                             DeleteExcelSheets                      FormatLeftBorder                       WrapText                               
% 	AutoSizeAllSheets                      DuplicateExcelSheet                    FormatRightBorder                      
% 	CenterCellsAndAutoSizeColumns          FormatBottomBorder                     GetNumberOfExcelSheets                 
% 	CenterCellsAndAutoSizeSpecificColumns  FormatCellColor                        GoToNextRowInColumn                    
% 	ClearCells                             FormatCellFont                         InsertComments      	
% 	AutoSizeColumns

Each function also has a sample line of code on how to call the function. For example:

%--------------------------------------------------------------------------------------------------------------------------------------------------------------------
% DeleteEmptyExcelSheets: deletes all empty sheets in the active workbook.
% This function loops through all sheets and deletes those sheets that are empty.
% Can be used to clean a newly created xls-file after all results have been saved in it.
% Sample call
% 		Excel = actxserver('Excel.Application');
% 		excelWorkbook = Excel.Workbooks.Open(excelFullFileName);
% 		Excel_utils.DeleteEmptyExcelSheets(Excel);
% 		Excel.ActiveWorkbook.Save;
% 		Excel.ActiveWorkbook.Close(false); % The 'false' argument prevents the popup window from showing, forcing the closure without user intervention.
% 		Excel.Quit;
% 		delete(Excel);
% 		clear('Excel')