find strings in excel on different sheets

15 次查看（过去 30 天）

显示更早的评论

avram alter 2020-1-28

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/502472-find-strings-in-excel-on-different-sheets

评论： avram alter 2020-2-6

采纳的回答： Walter Roberson

examplesheet.xlsx

在 MATLAB Online 中打开

I found a question asking a basically the same thing as me: https://www.mathworks.com/matlabcentral/answers/502430-search-through-an-excel-file-and-save-sheet-and-cells-as-variables?s_tid=prof_contriblnk

basically, this finds a user inputted string, and returnes the row in which that string is found.

this is the code I adapted from there:

in = input('Enter code: ', 's');
[~,~,raw] = xlsread('examplesheet.xlsx');
p = strcmp(in,raw);% Compare user input string with entries in the Excel sheet
rowNum = find(p==1)%Get Row number

this works for my purpose as well, with 1 caveat: it can only search the first sheet in an excel sheet. is there any way to get this to look through multiple sheets, and return the row and sheet name in 2 different variabvles? I have included an example sheet here.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

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

采纳的回答

Walter Roberson 2020-1-28

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/502472-find-strings-in-excel-on-different-sheets#answer_412544

在 MATLAB Online 中打开

filename = 'examplesheet.xlsx';
[~, sheets] = xlsfinfo(filename);
rows_found = [];
sheets_found = {};
for K = 1 : length(sheets)
    this_sheet = sheets{K};
    [~, ~, raw] = xlsread(filename, this_sheet);
    [rowNum, colNum] = find( strcmp(in, raw));
    if ~isempty(rowNum)
        rows_found = [rows_found; rowNum];
        sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
    end
end

Output is a numeric matrix rows_found and a cell array of character strings sheets_found . rows_found(K) is arow number and sheets_found{K} is the corresponding sheet name.

27 个评论
显示 25更早的评论隐藏 25更早的评论

avram alter 2020-2-4

在 MATLAB Online 中打开

I've moved along in my code, and this code has stopped working properly. i have appended the new code here. instead of a user input, this code takes data from serial, and uses it to find the excel sheet. It doesn't get the correct sheet or row.

In plat of the variable name "in", I use "extracted_data".

addpath('C:\Users\Administrator\Dropbox (********)\******** Team Folder\Matlab\RFID chip reader\RfidChipData');
filename = 'CorrectedRFIDValues.xlsx';
[~, sheets] = xlsfinfo(filename);
% decision = fscanf(tags{portidx});
% [decision, receivedcount] = fscanf(tags{portidx});
delete(instrfind());
deciding_port = serialport('COM3', 9600);
deciding_data = readline(deciding_port)
while strlength(deciding_data) < 24
   deciding_data = readline(deciding_port)
   pause(.2)
end
% decision = waitfor(readline(deciding_port), strlength(readline(deciding_port)), 24);
% if receivedcount < 24
%     error('Received less data than expected. Received data was: %s', decision)
% end
%now we know we've got at least 24 characters. 
extracted_data = extractAfter(deciding_data, 11);
extracted_data = extractBefore(extracted_data, 14);
rows_found = [];
sheets_found = {};
for K = 1 : length(sheets)
    this_sheet = sheets{K};
    [~, ~, raw] = xlsread(filename, this_sheet);
    [rowNum, colNum] = find( strcmp(extracted_data, raw));
    if ~isempty(rowNum)
        rows_found = [rows_found; rowNum];
        sheets_found = [sheets_found; repmat({this_sheet}, length(rowNum), 1)];
    end
end

the first section reads from COM3, and loops until there is a tag present. once a tag is presented, it extracts only the tag, and then uses that to find the correct sheet and row.