actxserver copy and paste iteratively

34 views (last 30 days)
Christian Hermann
Christian Hermann on 11 Mar 2020
Commented: Christian Hermann on 24 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 Comments
Guillaume
Guillaume on 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.

Sign in to comment.

Answers (1)

Guillaume
Guillaume on 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 Comments
Christian Hermann
Christian Hermann on 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.

Sign in to comment.

Community Treasure Hunt

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

Start Hunting!

Translated by