How to autofit table using MATLAB/Activex?
조회 수: 43 (최근 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.
댓글 수: 0
채택된 답변
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
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?
참고 항목
카테고리
Help Center 및 File Exchange에서 Spreadsheets에 대해 자세히 알아보기
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!