MATLAB: How to automatically collapse grouped columns in a spreadsheet

MATLAB

How can I automatically collapse grouped columns in a spreadsheet which is created using Excel's COM Automation Server?
The excel spreadsheet generated using the following code snippet:
excelObj = actxserver('Excel.Application');
wb = excelObj.workbooks.Add;
worksheets = excelObj.Sheets;
for ii = 1 : worksheets.Count - 1
    worksheets.Item(1).Delete;
end
sheetObject = wb.Sheets.Item(1);
sheetObject.Range('A1:A2').Value = {'1';'2'};
sheetObject.Range('A1:AX50').Value = num2cell(magic(50));
sheetObject.Range('G:K').Columns.Group;
wb.SaveAs([pwd,'\example.xlsx']);
wb.Close(false);
excelObj.Quit;
delete(excelObj);
creates a group including the columns G, H, I, J, K but this group is shown as expanded when opening the generated excel spreadsheet.

Best Answer

This question is not purely related to MATLAB as it involves the Excel's COM Automation Server. As such, the underlying commands are specific to Excel and therefore the following website can be used for documentation,
https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model
Nevertheless, the answer to this question can be found in the following documentation link,
https://www.mrexcel.com/board/threads/vba-to-collapse-groups.89127/#post-437549
where the following command needs to be used,
>> sheetObject.get('Columns','G').ShowDetail = false;
Note that the first column of the group 'G:K' is used in order to programmatically collapse the group of the generated excel spreadsheet.
Moreover, it is recommended to use the command,
>> sheetObject.get('Columns','G:K').Group;
instead of,
>> sheetObject.Range('G:K').Columns.Group;
to group a particular set of columns.
The suggested replacement to the aforementioned code snippet is then the following one:
sheetObject = wb.Sheets.Item(1);
sheetObject.Range('A1:A2').Value = {'1';'2'};
sheetObject.Range('A1:AX50').Value = num2cell(magic(50));
sheetObject.get('Columns','G:K').Group;
sheetObject.get('Columns','G').ShowDetail = false;
Related Question