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

Question

Hazem Kamel about 21 hours 前

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2140571-how-to-format-cell-font-size-font-color-and-alignment-in-excel-from-matlab-gui-program

评论： Hazem Kamel about 1 hour 前

采纳的回答： Milan Bansal

在 MATLAB Online 中打开

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:

control the font size and color.
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);

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

请先登录，再进行评论。

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

Answer 1

Milan Bansal about 20 hours 前

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2140571-how-to-format-cell-font-size-font-color-and-alignment-in-excel-from-matlab-gui-program#answer_1491241

在 MATLAB Online 中打开

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!

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Hazem Kamel about 1 hour 前

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 about 2 hours 前

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2140571-how-to-format-cell-font-size-font-color-and-alignment-in-excel-from-matlab-gui-program#answer_1491356

编辑：Image Analyst about 2 hours 前

在 MATLAB Online 中打开

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