Read excel comments into Matlab

Is there a way to read the comments attached to the cells of excel files (red corners) in Matlab? By now I'm using xlsread to import the excel data but the comments are not read by this function.

[ANKUR KUMAR]

Please attach your code and excel files too.

"comments of excel cells",what do you mean by this??

xlsread import everything which is in the file.

I think, I am writing these comments twice in the last half an hour. You might have deleted your last question.

[Jonas S]

You can comment individual cells in excel. That is what i mean. And I want to import these in Matlab. xlsread is not doing this.

[Dennis]

I came up with half of a solution:

name=fullfile(pwd,'test.xls');  %filename
e=actxserver('Excel.Application');
wb=e.Workbooks;
file=wb.Open(name);
sheet=file.Sheets.Item('Sheet1');  %Change sheetname if necessary

datafield = sheet.Range('D8');    %cell with comment
mycomment = datafield.Comment.Text;

 Close(wb)
 delete(e)

There is probably a way to get all comments in 1 readout instead of getting them one by one - but thats the missing 50% :)

[ANKUR KUMAR]

Please attach the file.

[Jonas S]

Thanks a lot Dennis, this helped me out!

Answer 1

[dpb]

Try this as starter--it's a routine I built to process some data stored as comments on a worksheet for the local community college foundation...

function [cmnts]=xlsComments(Excel, rangeObject)
  % return comments from Excel sheet in range given

    % Input called rangeObject definition--for reference 
    % UsedRange is user input range or if not given as found area used
    % rangeObject = Excel.Application.ActiveSheet.UsedRange;

      % Allocate space same size a the input range object to place comments into
      cmnts=cell(rangeObject.Rows.Count,rangeObject.Columns.Count);
      rowOffset=rangeObject.Row-1;                      % Get first row in range
      colOffset=rangeObject.Column-1;                   % Get column row in range
      % Get Handle to Comments collection -- this is, unfortunately, worksheet global
      commentsCollection=Excel.Application.ActiveSheet.Comments;
      nComments=commentsCollection.Count;
      for i=1:nComments                                 % Iterate over the collection
        comment=invoke(commentsCollection,'Item',i);    % Get handle to each in turn
        parent=comment.Parent;                          % Handle to parent cell
        addr=parent.Address;                            % Address string of cell
        rnge=parent.Cells;                              % Parent cell range object
        if ~xlsInRange(Excel,rangeObject,rnge),continue,end  % Skip if not in range
        [r,c]=xlsRowCol(addr,-rowOffset,-colOffset);    % Convert to row,col indices
        cmnts{r,c}=comment.Text;                        % Save each in cell array
      end  
    return
  end

function is=xlsInRange(EXCEL,rngeA,rngeB)
% xlsInRange(EXCELAPP,RngeA,RngeB) returns TRUE if range B contained in A

    is=~isempty(invoke(EXCEL,'intersect',rngeA,rngeB));
  end

function [row,col]=xlsRowCol(rnge,r1,c1)
% Return row, column from Excel range address and optional offset
%
% [ROW,COL]=XLSADDR(RNGE) will return a ROW,COL array index values
% formed from the input Excel cell range expression.  Default addressing
% is one-based array indexing.
%
% [ROW,COL]=XLSADDR(RNGEA:RNGEB) will return a ROW,COL array index values
% formed from the input Excel cell range expression as 2D array by row.
%
% [ROW,COL]=XLSADDR(RNGE,R1,C1) will use optional R1, C1 values as base
% indices for the returned ROW,COL array index values
%
% See Also:  xlsAddr, xlsread, xlswrite

    switch nargin
      case 1
        r1 = 0;
        c1 = 0;
      case 2
        c1 = 0;
    end

    rnge=char(split(rnge,':'));         % split out the ranges if exist
    m=size(rnge,1);
    row=zeros(m,1); col=zeros(m,1);
    for i=1:m
      cstr=rnge(i,isletter(rnge(i,:)));    % pull out column letters only
      if length(cstr)>2, error('Input Column Too Long'), end
      bArr=[1 26];                  % hardcode base vector since not general
      b=bArr(1:length(cstr)).';     % base vector for specific input length
      col(i)=(cstr-'@')*b;

      rstr=rnge(i,ismember(rnge(i,:),'0':'9'));  % pull out row numbers only
      row(i)=str2num(rstr);                  % and convert to numeric row
    end
    % convert to reference origin before returning
    row=row+r1;
    col=col+c1;
  end