actxserver copy and paste iteratively

28 visualizzazioni (ultimi 30 giorni)
Christian Hermann
Christian Hermann il 11 Mar 2020
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 Commenti
Guillaume
Guillaume il 12 Mar 2020
Modificato: Guillaume il 12 Mar 2020
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
Guillaume il 18 Mar 2020
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.

Accedi per commentare.

Risposte (1)

Guillaume
Guillaume il 18 Mar 2020
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 Commenti
Christian Hermann
Christian Hermann il 23 Mar 2020
I don't know why but your code doesn't work for me.
"To avoid these sorts of issue, I would really recommend you follow my initial answer of using matlab to do the processing and simply writing into a preformatted sheet."
You're right...I should try to do this but as I said I also have to check the color of the cells using this code:
N = length(handles.raw_1);
all_i = 6:N;
handles.ColorIndex = zeros(size(all_i));
for k = 1:numel(all_i)
i = all_i(k);
m = strcat('M',num2str(i));
handles.ColorIndex_1(k) = handles.worksheet_1.Range(m).Interior.ColorIndex;
handles.ColorIndex_1 = transpose(handles.ColorIndex_1);
end
But there is another option. I can also color the cells depending on the text which is inside of the cells. Maybe I can compare the rows and find the missing cells. Then I write this in a preformatted sheet. And in the next step I color the cells using the following code
S.workbook_1.Worksheets.Item(1).Range(S.cell).Interior.ColorIndex = 3;
Christian Hermann
Christian Hermann il 24 Mar 2020
In case someone will have the same problem I've found a solution.
for k = 1:handles.lenght_ID_row_zeros
handles.worksheet_1.Rows.Item(ID_row_zeros(k,1)).Copy;
handles.workbook_1.Worksheets.Item(2).Range(['A', num2str(k+5)]).PasteSpecial(13);
end
I can paste it using a for-loop. I know...it's a very special case but maybe there will be someone else who will have the same problem.
And thanks again Guillaume for all your help and energy.

Accedi per commentare.

Prodotti


Release

R2016b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by