How to autofit table using MATLAB/Activex?

47 views (last 30 days)
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.

Accepted Answer

Image Analyst
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
Megna Hari
Megna Hari on 17 Jul 2014
Thank you! I didn't realize you could do excelObject.Cells.EntireColumn.AutoFit. I'm still getting used to ActiveX related stuff.
Image Analyst
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.

Sign in to comment.

More Answers (1)

Megna Hari
Megna Hari on 18 Dec 2020
Edited: Image Analyst on 18 Dec 2020
Also since I keep searching how to do this in Word with tables and keep finding my own question for Excel 6 years later....
When you add a table in Word with MATLAB and want to autofit :
set(Word.Selection.Tables,'AutoFitBehavior','wdAutoFitContent') % Doesn't work -- you have to do this during table creation:
hdlActiveX.ActiveDocument.Tables.Add(hdlActiveX.Selection.Range, nbRows, nbCols, 1, 1);
% first 1 same as DefaultTableBehavior := wdWord9TableBehavior
% last 1 same as AutoFitBehavior := wdAutoFitContent
  2 Comments
Image Analyst
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?
Megna Hari
Megna Hari on 21 Dec 2020
The first line is what I assumed would work based on figuring out what the macro was doing in word. so in place of line 1 the solution is to do line 2 with setting that "1" in the table add command.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!