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.

Answer 1

[Image Analyst]

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

[Megna Hari]

Thank you! I didn't realize you could do excelObject.Cells.EntireColumn.AutoFit. I'm still getting used to ActiveX related stuff.

[Image Analyst]

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.

Answer 2

[Megna Hari]

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

[Image Analyst]

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]

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.