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

24 次查看（过去 30 天）
Hazem Kamel
Hazem Kamel 2024-7-26
编辑： Image Analyst 2024-7-28
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);

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

采纳的回答

Milan Bansal
Milan Bansal 2024-7-26
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!
  4 个评论
显示 2更早的评论
Hazem Kamel
Hazem Kamel 2024-7-28
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
Image Analyst 2024-7-28
编辑：Image Analyst 2024-7-28
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.

请先登录，再进行评论。

更多回答（1 个）

Image Analyst
Image Analyst 2024-7-27
编辑：Image Analyst 2024-7-27
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')

类别

在 Help Center 和 File Exchange 中查找有关 ActiveX 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by