0

My MATLAB code produces data that I want to send to Excel. I use ActiveX Server in MATLAB to both produce the Excel file and to do the export (I can control more properties of the resulting Excel file this way than by using the 'writetable' command). In the produced Excel file, I manually have to chose the menu 'Data/Filter' to turn on the function letting me filter and sort each column (visible as the small triangles in the column headings as shown in the illustration below).

How can I turn on this function with ActiveX from MATLAB?

Excel screenshot showing sort/filter dropdowns

nekomatic
  • 5,988
  • 1
  • 20
  • 27
myotis
  • 393
  • 4
  • 16

2 Answers2

1

Call the AutoFilter method on an Excel Range object. For example, if you have a worksheet object MySheet:

rangeToFilter = get(MySheet,'Range','A1:B3');
rangeToFilter.AutoFilter
nekomatic
  • 5,988
  • 1
  • 20
  • 27
  • Thanks! It worked nicely. How can I set the range to include all present columns, without mentioning them specifically (e.g. 'A1:B3')? – myotis Feb 01 '22 at 13:13
  • I'm not sure sorry… in Excel the worksheet has a property `UsedRange` which is the rectangular range including all the cells that contain data, but I haven't worked out how to access that from MATLAB. – nekomatic Feb 07 '22 at 16:51
0

You can use a simple function that helps you to get the range depending on the size of the data that you will send to excel. For example:

idx2=size(data,2);
rangeToFilt=join(["A1:",idxtoexcel(idx2),"1"],"");
rangeToFilt.AutoFilter

function rexcel=idxtoexcel(idx)
% Función que permite obtener el número y letra de las filas y columnas que ocupará una matriz a imprimirse en un libro de excel.
alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
if idx < 27
    rexcel = alphabet(idx);
else
   idx2 = rem(idx,26);
    if idx2 == 0
        rexcel = [alphabet(floor(idx/26)-1),'Z'];
    else
        rexcel = [alphabet(floor(idx/26)),alphabet(idx2)];
    end
end
Santiago H Castillo
  • 13
  • 3
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 16 '22 at 11:06