조회 수: 16(최근 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.

Guillaume
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.

Guillaume
20 Mar 2020

Yes, I tested the code before posting it.

Make sure that the workbook is not in preview mode (you may want to make excel visible from the start). It could also be due to your version of excel, I'm on Office 365. In any case, the problem is on the excel side. The range is perfectly valid.

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.

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

Start Hunting!
## 댓글 수: 6

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808433

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808433

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808654

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808654

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808666

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808666

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808962

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808962

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808980

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_808980

## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_811692

⋮## 이 댓글에 대한 바로 가기 링크

https://kr.mathworks.com/matlabcentral/answers/510286-actxserver-copy-and-paste-iteratively#comment_811692

댓글을 달려면 로그인하십시오.