How to autofit table using MATLAB/Activex?
17 views (last 30 days)
Show older comments
So I needed to make a table in word using a structure: I used struct2table and then did writetable to a file -> and then I read the file into numeric, text, and raw and then did:
ExAct = get(excel,'Activesheet');
ExActRange = get(ExAct,'Range',sprintf('A1:%c%d',col,nc));
set(ExActRange,'Value',raw);
I was able to copy this excel table and paste it into a word file but I want to autofit the tables to contents. How would I do this if I did the thing where I invoke(word.Selection.... in order to paste into word?
ALSO if you know an easier way to save a structure into an activesheet let me know - it would only let me set a matrix: not a table or a structure which is why I wrote to excel twice.
0 Comments
Accepted Answer
Image Analyst
on 17 Jul 2014
See this snippet from a class I wrote:
%================================================================================================
% Selects all cells in the current worksheet and auto-sizes all the columns
% and vertically and horizontally aligns all the cell contents.
% Leaves with cell A1 selected.
function CenterCellsAndAutoSizeColumns(excelObject)
try
% Select the entire spreadsheet.
excelObject.Cells.Select;
% Auto fit all the columns.
excelObject.Cells.EntireColumn.AutoFit;
% Center align the cell contents.
excelObject.Selection.HorizontalAlignment = 3;
excelObject.Selection.VerticalAlignment = 2;
% Put "cursor" or active cell at A1, the upper left cell.
excelObject.Range('A1').Select;
catch ME
errorMessage = sprintf('Error in function CenterCellsAndAutoSizeColumns.\n\nError Message:\n%s', ME.message);
fprintf('%s\n', errorMessage);
WarnUser(errorMessage);
end
return; % from CenterCellsAndAutoSizeColumns
end % of CenterCellsAndAutoSizeColumns
%-------------------------------------------------------------------------------------------------------
% Loops over all sheets in a workbook, auto-sizing columns and center-aligning all cells.
function AutoSizeAllSheets(excelObject)
try
% excelObject = actxserver('Excel.Application');
% excelWorkbook = excelObject.workbooks.Open(fileName);
worksheets = excelObject.sheets;
numSheets = worksheets.Count;
% Loop over all sheets
for currentSheet = 1 : numSheets
thisSheet = get(worksheets, 'Item', currentSheet);
invoke(thisSheet, 'Activate');
% Center data in cells, and auto-size all columns.
CenterCellsAndAutoSizeColumns(excelObject)
end
catch ME
errorMessage = sprintf('Error in function AutoSizeAllSheets.\n\nError Message:\n%s', ME.message);
fprintf('%s\n', errorMessage);
WarnUser(errorMessage);
end
return; % from AutoSizeAllSheets
end % of AutoSizeAllSheets
2 Comments
Image Analyst
on 17 Jul 2014
There are a bazillion ActiveX commands - no one can know even a fraction of them. A good strategy to figure out what methods you need to call, is to do the same thing in Excel while recording a macro. Then save the macro and edit it and look what commands the macro recorder put in there. Then copy those to your MATLAB code.
More Answers (1)
Megna Hari
on 18 Dec 2020
Edited: Image Analyst
on 18 Dec 2020
2 Comments
Image Analyst
on 18 Dec 2020
So does your second line of code "fix" the autofitting (since you say the first line does not work)? So after adding that second line, the column will now autofit?
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!