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??

[Walter Roberson]

Does it work for Matlab code generated with a GUI??

No, MATLAB code generated from a GUI does not know anything about actxserver

[Hazem Kamel]

Now it makes sense and that is why I did everything and every single advice without getting any results.

Thanks a lof for your concern and your help.

[Image Analyst]

Not sure what GUI you were talking about. Was it a MATLAB applet like Color Thresholder or Classification Learner where you can export the code to a file by clicking on the Export button? No, those built-in GUIs won't use ActiveX. But obviously if the GUI is your own GUI you can write whatever code you want into your functions including calls to ActiveX functions.

Not sure you saw my Answer below but I guess you didn't like the suggestion of creating 'SavedDataTemplate.xlsx' in advance with all the desired formatting and then making a copy of it and writing to that. It's easier than all the ActiveX stuff if your data always goes into the same cells. For example:

% Create output file with all the desired formatting.
copyfile('SavedDataTemplate.xlsx', 'SavedData.xlsx');
% Write our data to the nicely formatted workbook.
xlswrite('SavedData.xlsx', Data, 'Sheet1', cellReference);

Can't get much simpler than that, which is why I suggested it.

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')