How to autofit table using MATLAB/Activex?
显示 更早的评论
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.
采纳的回答
Image Analyst
2014-7-17
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 个评论
Megna Hari
2014-7-17
Image Analyst
2014-7-17
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.
更多回答（1 个）
Megna Hari
2020-12-18
编辑：Image Analyst
2020-12-18
2 个评论
Image Analyst
2020-12-18
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?
Megna Hari
2020-12-21
类别
在 帮助中心 和 File Exchange 中查找有关 ActiveX 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
