Actxserver reading only first file
显示 更早的评论
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 个评论
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.
采纳的回答
Guillaume
2018-2-15
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 个评论
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
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-16
Yes, there may be some small mistakes in my code, it's obviously untested. I don't even have matlab on this computer. Indeed it should be sheet.Name.
The modified code above stops iterating through the sheets at the first sheet with the required tab colour, so it already does what you want.
As for the off by one error, no idea where it comes from. I'm fairly confident that my conversion from bgr to rgb triplet is correct. In any case, the modified code above converts the desired rgb triplet into the required bgr value instead, using a simpler method. Does it solve the issue?
No i don't want it to stop at first iteration it should look for all the sheets of same color and if there is repetition it should give me just one name. here is ex. with file and sheets inside
sheet2 and sheet5 are repeating in file1 and file3. end answer should be -
wanted_sheets = sheet2, sheet3, sheet4, sheet5, sheet7
Guillaume
2018-2-20
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{:}]);
Oh, yes sorry, it needs to be a vertcat instead of horzcat (aka []) since it's a cell array of column vectors
wanted_sheets = unique(vertcat(wanted_sheets{:}));
Ghenji
2018-2-20
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Translated by 
