actxserver copy and paste iteratively

Question

Christian Hermann 2020-3-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively

评论： Christian Hermann 2020-3-24

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 个评论
显示 4更早的评论隐藏 4更早的评论

Guillaume 2020-3-11

Your second part function cannot possibly run. S.raw_1 is undefined on the first line.

Anyway, rather than some code, it would be much better to tell give a more complete description of what you want to copy where and what the conditions are. Example files would be useful.

A few comments about your code:

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.

Guillaume 2020-3-12

编辑：Guillaume 2020-3-12

在 MATLAB Online 中打开

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

在 MATLAB Online 中打开

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.

请先登录，再进行评论。

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

Answer 1

Guillaume 2020-3-18

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#answer_420761

在 MATLAB Online 中打开

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 个评论
显示 8更早的评论隐藏 8更早的评论

Guillaume 2020-3-19

编辑：Guillaume 2020-3-19

在 MATLAB Online 中打开

"I use the following code I get an error which I don't understand"

I completely missed that you're using 2016b, which is the first version with detectImportOptions. It was a lot less developed than it is now and didn't yet have the 'VariableNamesRange' option.

As workaround you may be able to use the 'Range' with a much larger range than you'll ever need. The aim of this is to tell detectImportOptions to pick row 5 to names the variables (in my version it picks row 4 if left to work it out). So, try:

opts = detectImportOptions('table_1_1_1.xlsx', 'Range', '5:10000');  %assuming your files will always have less than 10,000 rows

This works in my version (2020a). No idea how it behaves in 2016b. There's been a lot of improvements to the detection capabilities in recent years.

edit: I realise that I haven't answered your question, which is how to copy multiple rows at once.

It's exactly as you have done. Just create a range that covers the rows you want to copy.

excel = actxserver('Excel.Application');
wbsource = excel.Workbooks.Open(fullfile(pwd, 'table_1_1_1.xlsx'));
wbdest = excel.Workbooks.Add;
wssource = wbsource.Sheets.Item(1);
wsdest = wbdest.Sheets.Item(1);
rgsource = wssource.Range('A4:C5,A13:C13,A27:C27,A35:C36');
rgdest = wsdest.Range('A1');
rgsource.Copy;
rgdest.PasteSpecial(13);  %13 => xlPasteAllUsingSourceTheme
excel.Selection.Clear;
excel.Visible = true;

Christian Hermann 2020-3-23

在 MATLAB Online 中打开

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 2020-3-24

在 MATLAB Online 中打开

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.

请先登录，再进行评论。

actxserver copy and paste iteratively

6 个评论
显示 4更早的评论隐藏 4更早的评论

回答（1 个）

10 个评论
显示 8更早的评论隐藏 8更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

actxserver copy and paste iteratively

6 个评论 显示 4更早的评论隐藏 4更早的评论

回答（1 个）

10 个评论 显示 8更早的评论隐藏 8更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

6 个评论
显示 4更早的评论隐藏 4更早的评论

10 个评论
显示 8更早的评论隐藏 8更早的评论