- function [] = functionname(..) is the same as function functionname(..)
- All your functions have varargin as input but never use it. Might as well have function functionname()
- Use fullfile instead of strcat to build paths.
- The use of length on something that is very likely to be a 2D array makes your code fragile. Use size with the proper dimension.
- You're transposing S.colorindex in a loop while you're filling it. S.colorindex will end up as a column vector for an odd number of iterations and as a row vector for an even number of iteration. I assume that's a bug. In any case, why not create it as a vector in the correct direction to start with.
- esheet1 will be the same reference as s_worksheet1 (unless the user clicked on another workbook while the code is running, completely breaking your code). Using Activexxx is not a good idea, particularly if you make excel visible
- The reason why some variables are stored as field of the structure S while others are just normal variables is unclear.
actxserver copy and paste iteratively
14 次查看(过去 30 天)
显示 更早的评论
Hi everyone,
The question relates to the use of the use of the actxserver for an excel application.
I'm creating a a standalone to compare two excel tables. The Tables are nearly equal. Every week I'm getting a new excel table. I have to check if there are rows that were cleared.
The actual problem is in the second part of the code but I wanted to show you also the first part to help you to understand what I'm trying to do.
%#########################
%## FIRST PART ##
%#########################
function [] = exc_1_call (varargin)
[S.filename_xlsx_1, S.pathname_xlsx_1] = uigetfile({'*.xlsx'},'');
file = strcat(S.pathname_xlsx_1, S.filename_xlsx_1);
[S.num_1,S.txt_1,S.raw_1] = xlsread(file); % first spreadsheet
S.excel_1 = actxserver('Excel.Application'); % open Excel Server
S.excel_1.Visible = true; % make excel visible
S.workbook_1 = S.excel_1.Workbooks.Open(file); %open excel file
S.worksheet_1 = S.workbook_1.Worksheets.Item(1); %get worksheet reference
% the next part is giving me the color index for every cell in column 'M'
% I need to do it because the color of the cells will be compared
N = length(S.raw_1);
all_i = 6:N;
S.ColorIndex = zeros(size(all_i));
for k = 1:numel(all_i)
i = all_i(k);
m = strcat('M',num2str(i));
S.ColorIndex_1(k) = S.worksheet_1.Range(m).Interior.ColorIndex;
S.ColorIndex_1 = transpose(S.ColorIndex_1); % in my case it's a 1x5301 double
end
eSheet = S.excel_1.ActiveWorkbook.Sheets; % adding a new sheet
eSheet1 = Item(eSheet,1);
eNewSheet = Add(eSheet,[],eSheet1);
S.workbook_1.Worksheets.Item(2).Name = 'Sheet_2';
end
function [] = exc_2_call (varargin)
[S.filename_xlsx_2, S.pathname_xlsx_2] = uigetfile({'*.xlsx'},'');
file = strcat(S.pathname_xlsx_2, S.filename_xlsx_2);
[S.num_2,S.txt_2,S.raw_2] = xlsread(file); % first spreadsheet
S.excel_2 = actxserver('Excel.Application'); % open Excel Server
S.excel_2.Visible = true; % make excel visible
S.workbook_2 = S.excel_2.Workbooks.Open(file); %open excel file
S.worksheet_2 = S.workbook_2.Worksheets.Item(1); %get worksheet reference
N = length(S.raw_2);
all_i = 6:N;
S.ColorIndex_2 = zeros(size(all_i));
for k = 1:numel(all_i)
i = all_i(k);
m = strcat('M',num2str(i));
S.ColorIndex_2(k) = S.worksheet_2.Range(m).Interior.ColorIndex;
S.ColorIndex_2 = transpose(S.ColorIndex_2);
end
end
Now we're coming to the part where I can't find a solution.
%#########################
%## SECOND PART ##
%#########################
function [] = df_call (varargin)
S.ID_1 = S.raw_1(6:end,12); % that's a certain column from table 1 with information that have to be compared
S.ID_2 = S.raw_2(6:end,12); % same column from the second excel table
S.ID_find = ismember(S.ID_1, S.ID_2); % here I'm checking if there are missing cells in table 2
S.ID_find = double(S.ID_find); % convert to double because S.ID_find was 'logical'
[ID_row_zeros] = find(S.ID_find == 0); % looking for zeros (missing row)
ID_row_zeros_str = string(num2str(ID_row_zeros)); % convert to string to concatenate strings
ID_deleted_cells = strcat('M', ID_row_zeros_str); % concatenate strings
% copy the first six rows from the first table (first sheet) and paste it to the first excel table NEW SECOND sheet
S.worksheet_1.Rows.Item(1).Copy;
S.workbook_1.Worksheets.Item(2).Range('A1').PasteSpecial(13);
S.worksheet_1.Rows.Item(2).Copy;
S.workbook_1.Worksheets.Item(2).Range('A2').PasteSpecial(13);
S.worksheet_1.Rows.Item(3).Copy;
S.workbook_1.Worksheets.Item(2).Range('A3').PasteSpecial(13);
S.worksheet_1.Rows.Item(4).Copy;
S.workbook_1.Worksheets.Item(2).Range('A4').PasteSpecial(13);
S.worksheet_1.Rows.Item(5).Copy;
S.workbook_1.Worksheets.Item(2).Range('A5').PasteSpecial(13);
%
% NOW HERE COMES THE "PROBLEM"
% here I want to copy the missing rows and paste it to the NEW SECOND sheet of the first table
% for example: ID_row_zeros contains three missing rows. that means i have ID_row_zeros (3x1 double) or ID_row_zeros_str (3x1 string)
S.worksheet_1.Rows.Item(ID_row_zeros(1,1)).Copy; % that works fine. I'm copying the first missing row
S.workbook_1.Worksheets.Item(2).Range('A6').PasteSpecial(13); % and paste it to the 2. sheet to 'A6'
S.worksheet_1.Rows.Item(ID_row_zeros(2,1)).Copy; % copy second missing row
S.workbook_1.Worksheets.Item(2).Range('A7').PasteSpecial(13); % and paste it to the 2. sheet to 'A7'
S.worksheet_1.Rows.Item(ID_row_zeros(3,1)).Copy; % copy third missing row
S.workbook_1.Worksheets.Item(2).Range('A8').PasteSpecial(13); % and paste it to the 2. sheet to 'A8'
The thing is that I have to do it iteratively because there could be missing more than just three rows. I never know how many rows will be missing. So somehow I have to copy and paste it depending on the number of missing rows.
I hope I gave enough information to understand my problem. It's not that easy to explain such a problem. If you need some more information please let me know.
Any help/direction would be most appreciated.
Thanks in advance.
6 个评论
Guillaume
2020-3-12
编辑:Guillaume
2020-3-12
I haven't got the time to look at this in depth right now, I'll come back to it probably tonight. A few things:
"Wouldn't you recommend to code on my own?" That's entirely up to you. You don't seem to be doing to bad with building the GUI from scratch but you may want to look at the App designer which may make it easier for you. However, note that the App designer is OOP so very different from the way you've developped your GUI. Your GUI is more similar to what GUIDE would create but I wouldn't recommend using GUIDE. It's been deprecated now.
One important thing, when you define a callback with:
.. = uicontrol(.., 'Callback', {@somecallback, handles});
the variable that is passed to the callback is the handles in the exact same state as it is at the moment you create the callback. Subsequent changes/additions to that variable won't affect that variable. The {} creates a cell array whose content is a function handle and a copy of the handles.
One way to fix this is to indeed use guidata. So define the callbacks as:
.. = uicontrol(.., 'Callback', @somecallback);
at the end of the GUI creation:
guidata(handles.fh, handles); %store the handles in the figure
and in your callbacks
function somecallback(hobject, eventdata)
handles = guidata(hobject); %get the latest version of handles
%...
guidata(hobject, handles); %update the handles
end
edit: Oh, just noticed that your callback functions are nested functions (indenting them would have made this clearer) so this changes the scoping rules a bit. Ironically, if you hadn't passed at all the handles to the callback, your code would work properly and you wouldn't need guidata either. In the editor, handles would turn blue to indicate that the variable spans functions scope. So that's another option, define the callback as:
.. = uicontrol(.., 'Callback', @somecallback);
and the callback function
function somecallback(hobject, eventdata) %nested function, so has access to variables of the main function
%use handles variable as is
handles = dosomethingwith(handles); %uses the same handles as the main function.
end
Guillaume
2020-3-18
Christian Hermann's comment mistakenly posted as an answer moved here:
Hi again,
so now I'm able to use my GUI and all the functions. I will to upload the m-file and tables that will work. The first tables were wrong. Maybe you can test it.
But I still couldn't figure it out how to copy and paste iteratively.
I will summarize the problem again.
I want to compare two tables that should be equal. But there could miss some rows in the 2nd sheet. I have to check which rows are missing. I know how to find the missing rows. This works fine. I want to copy the missing rows, paste them into the new created 2nd sheet and save it as a new excel-table. As I said this works. But just for the rows that I define. Like in the code down below --> (ID_row_zeros(1,1))
%########## copy and paste deleted rows to the 2nd sheet ##############
handles.worksheet_1.Rows.Item(ID_row_zeros(1,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A6').PasteSpecial(13);
handles.worksheet_1.Rows.Item(ID_row_zeros(2,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A7').PasteSpecial(13);
handles.worksheet_1.Rows.Item(ID_row_zeros(3,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range('A8').PasteSpecial(13);
As you can see I define three rows that have to be copied to the second sheet from 'A6' to 'A8'.
But what if ID_row_zeros contains 400 rows that are missing in the second table. I have to copy and paste them iteratively to the second sheet.
回答(1 个)
Guillaume
2020-3-18
I'm afraid it's been a while so i don't remember the whole discussion.
It sounds like you want to merge two tables which have some common columns and may have some rows missing from either. If so, I wouldn't do that with Excel (although it's certainly possible). I'd use something like this:
%importing the excel files into matlab using MODERN techniques:
opts = detectImportOptions('table_1_1.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t1 = readtable('table_1_1.xlsx', opts);
opts = detectImportOptions('table_2_2.xlsx', 'VariableNamesRange', '5:5', 'TextType', 'string');
t2 = readtable('table_2_2.xlsx', opts);
%replace missing strings by "" so they're considered equal by the join (<missing> values are never equal)
t1 = fillmissing(t1, 'constant', "", 'DataVariables', @isstring);
t2 = fillmissing(t2, 'constant', "", 'DataVariables', @isstring);
%join the two tables, merging identical rows and keeping missing rows from both table (full outer join)
merged = outerjoin(t1, t2, 'MergeKeys', true, 'Keys', 1:12)
The above use columns 1:12 of both tables as keys for the merge whereas your original code just used column 12. It makes more sense to me to use all the columns, but there's a lot of non-identical values in columns 1:11 for the same ID in column 12 (eg ID 123_554_747 has Info6 as yes in one file but no in the other). If you just used column 12:
merged = outerjoin(t1, t2, 'MergeKeys', true, 'Keys', 'ID')
I'm not sure how you reconcile the differences between the mismatches in table 1 and 2.
In any case, you can just export the merged table to a new excel file with writetable.
10 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Text Files 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!