How to autofit table using MATLAB/Activex?

18 次查看(过去 30 天)
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
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
Megna Hari 2014-7-17
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 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
Megna Hari 2020-12-18
编辑:Image Analyst 2020-12-18
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 个评论
Image Analyst
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
Megna Hari 2020-12-21
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.

请先登录,再进行评论。

Community Treasure Hunt

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

Start Hunting!

Translated by