Actxserver reading only first file

Question

Ghenji 2018-2-15

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/382859-actxserver-reading-only-first-file

评论： Ghenji 2018-2-20

working with this code-

get_files = cellstr(get(handles.listboxfiles,'String'));
len = length(get_files);     
for i = 1 : len                    
    excel = actxserver('Excel.Application');            
    workbook = excel.Workbooks.Open(get_files{i});     
    .....
    .....
    .....
    .....
    .....    
    .....
end

Am not getting any error but Actxserver reads only first file from list. Listboxfiles contains name of the files with pathname. Something like this "C:\Users\ERTW\Documents\Excel_file\ARAN1.xlsm". Is something missing with excel.Workbooks.Open?

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Guillaume 2018-2-15

编辑：Guillaume 2018-2-15

Note that your question has nothing to do with actxserver. actxserver is used to connect matlab to any COM/ActiveX component. It can be excel but it can be anything else such as other Office programs (Word, Access, Outlook, etc.), matlab itself, or anything that support scripting.

actxserver does not read files.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Guillaume 2018-2-15

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/382859-actxserver-reading-only-first-file#answer_305323

在 MATLAB Online 中打开

Have you verified that get_files actually contains more than one element. Is your listbox multiselect?

There is nothing wrong with your Workbooks.Open call and you'd get an error if excel failed to open the workbook. Of course, you overwrite the workbook variable on each step of the loop, so if you expected an array of workbooks after the loop is finished, you're not going to get one.

Note that your code starts a new instance of excel at each step of the loop. That's very inefficient. You would be better off with:

excel = actxserver('Excel.Application'); %start excel
%excel.Visible = true;   %uncomment for debugging
for fidx = 1:numel(get_files)     
  workbook = excel.Workbooks.Open(get_files{i});     
  %...
  workbook.Close();
end
excel.Quit();  %Don't forget to quit each excel instance or they'll be left running in the background invisible

The best way for you to figure out what is going on is to make excel visible and see how your code affects the excel window.

8 个评论
显示 6更早的评论隐藏 6更早的评论

Ghenji 2018-2-15

编辑：Ghenji 2018-2-15

在 MATLAB Online 中打开

Thanks Guillaume. You were the one who originally gave me this code. I made few changes as per my need. Here is the complete code and still this am getting result for only '1st' file in the list and not every file.

    [filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
    fullname = cellstr(fullfile(pathname, filename));
    len = length(fullname);      
    wanted_sheet = cell(1, len);
    excel = actxserver('Excel.Application');
    excel.Visible = true;
    for i = 1 : len                    
       workbook = excel.Workbooks.Open(fullname{i});     
       numsheet = workbook.Worksheets.Count;                
       namergb = cell(numsheet, 2); 
       for sheetidx = 1:numsheet
          sheet = workbook.Worksheets.Item(sheetidx);
          namergb{sheetidx, 1} = sheet.Name;
          bgr = sheet.Tab.Color;
          namergb{sheetidx, 2} = uint8([mod(bgr, 256), floor(mod(bgr/256, 
          256)), floor(bgr/65536)]);
        end
    t = cell2table(namergb, 'VariableNames', {'SheetName', 'RGB'});
    [wanted_sheet{i}] = t.SheetName(all(t.RGB == uint8([0 255 1]), 2));
    workbook.Close();
end
    excel.Quit()
    wanted_sheet = [wanted_sheet{:}];
    wanted_sheet = unique(wanted_sheet);

can you help me with changes to be made?

Guillaume 2018-2-16

编辑：Guillaume 2018-2-16

在 MATLAB Online 中打开

This would be slightly cleaner:

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = fullfile(pathname, filename));
wanted_sheets = cell(1, numel(fullfname));
required_rgb = [0 255 1];
required_bgr = polyval(fliplr(required_rgb), 256);
excel = actxserver('Excel.Application');
excel.Visible = true;  %for debugging only
for filenum = 1:numel(fullname)                  
   workbook = excel.Workbooks.Open(fullname{filenum});     
   for sheetidx = 1:workbook.Worksheets.Count; 
      sheet = workbook.Worksheets.Item(sheetidx);
      if required_bgr == sheet.Tab.Color
         wanted_sheets{filenum} = sheet.Name;
         break;
      end
   end
   if isempty(wanted_sheets{filenum})
      warning('File %s does not have a tab of the required colour', filename{filenum});
   end
   workbook.Close();
end
excel.Quit();

But I see no reason why your code would not go through all the files. Can't you see all the files opening in excel?

Of course, if a file does not have a tab with the exact rgb colour [0 255 1] then wanted_sheet will be empty for that file.

edit: bugfix with sheet.Name

Ghenji 2018-2-16

编辑：Ghenji 2018-2-16

在 MATLAB Online 中打开

firstly, few changes needed here Line

wanted_sheets{filenum} = sheet.SheetName;

Shouldn't it be like this -

wanted_sheets{filenum} = sheet.Name;

Now I get what the problem was with only one file being read. There is difference in what color excel passes and what is being read by code. For ex. I set [0 255 2] in excel but answer with matlab code was [0 255 3]. So I changed code to look for [0 255 3]. Now with my code it gave me error

Error using horzcat
Dimensions of matrices being concatenated are not consistent.

This was because out of 3 spreadsheets, 2 spreadsheets have 2 sheet labels of same color.

Now with your code it was not able to detect that color[0 255 3] thats why

Warning: File Copy of BMI260 WMR_A49-A56.xlsm does not have a tab of the required colour 
Warning: File BMI260 (004) with labels.xlsm does not have a tab of the required colour 
Warning: File Copy of BMI260 WMR V07-V09 Samples rev1.xlsm does not have a tab of the required colour.

In short What I need with wanted_sheet variable is result from all the sheets in single matrix. if there is repetition of name then just one name. Never understood why there is difference in reading colors.

Guillaume 2018-2-20

在 MATLAB Online 中打开

Well

unique([wanted_sheets{:}])

with your original code would have given you what you want

With a cleaner code:

[filename,pathname] = uigetfile('*.xls;*.xlsx;*.xlsm;*.xlsb', 'Multiselect', 'on');
fullname = fullfile(pathname, filename));
wanted_sheets = cell(1, numel(fullfname));
required_rgb = [0 255 1];
required_bgr = polyval(fliplr(required_rgb), 256);
excel = actxserver('Excel.Application');
excel.Visible = true;  %for debugging only
for filenum = 1:numel(fullname)                  
   workbook = excel.Workbooks.Open(fullname{filenum});
   numsheet = workbook.Worksheets.Count;
   names = cell(numsheet, 1);
   colours = zeros(numsheet, 1);     
   for sheetidx = 1:numsheet
      sheet = workbook.Worksheets.Item(sheetidx);
      names{sheetidx} = sheet.Name;
      colours(sheetidx) = sheet.Tab.Color;
   end
   wanted_sheets{filenum} = names(colours == required_bgr);
   if isempty(wanted_sheets{filenum})
      warning('File %s does not have a tab of the required colour', filename{filenum});
   end
   workbook.Close();
end
excel.Quit();
wanted_sheets = unique([wanted_sheets{:}]);