| Author |
 Topic  |
|
|
malgoska
Poland
36 Posts |
 Posted - 07/20/2018 : 06:31:49 AM
|
Origin Ver. 2018 and Service Release 1 b9.5.1.195 (Select Help-->About Origin):
Operating System: Win10
Hello,
I'm not an experienced user of Origin COM Automation Server, but I used to succesfully use a script based on ImportOrigin downloaded from matlab fileexchange site (https://www.mathworks.com/matlabcentral/fileexchange/43116-importorigin)
tempdata = struct('name',[],'xy',[]);
tempdata(1) = [];
originObj = actxserver('Origin.ApplicationSI');
workbooksHandle = invoke(originObj,'WorksheetPages');
nbooks = get(workbooksHandle,'Count') % Number of workbooks in project
for b = 0:nbooks-1 % Loop all workbooks
%workbookHandle = get(workbooksHandle,'Parent',b)
workbookHandle = get(workbooksHandle,'Item',b); % Handle to workbook b
workbookName = get(workbookHandle,'Name'); % Short name of workbook b
workbookLongName = get(workbookHandle,'LongName') % Long name of workbook b
% Identify worksheets
worksheetsHandle = get(workbookHandle,'layers'); % Handle to worksheets of workbook b
nsheets = get(worksheetsHandle,'Count'); % Number of sheets in workbook b
for s = 0:nsheets-1 % Loop all worksheets
worksheetHandle = get(worksheetsHandle,'Item',s); % Handle to sheet s
worksheetName = get(worksheetHandle,'Name'); % Short name of sheet s
worksheetLongName = get(worksheetHandle,'LongName'); % Long name of sheet s
worksheetData = invoke(originObj,'GetWorksheet',sprintf('[%s]%s',workbookName,worksheetName)); % Get worksheet data
fh = @(x) all(isnan(x(:)));
worksheetData(cellfun(fh, worksheetData)) = {[]}; % Remove all NaN from cell array
fh = @(x) all(ischar(x(:)));
worksheetData(cellfun(fh, worksheetData)) = {[]}; % Remove all strings from cell array
if ~iscell(worksheetData) || isempty(worksheetData)
% If there is no worksheet data found, continue to next
continue
end
% Identify columns
columnsHandle = get(worksheetHandle,'Columns'); % Handle to columns of worksheet s
ncolumns = get(columnsHandle,'Count'); % Number of columns in sheet s
x = [];
y = [];
for c = 0:ncolumns-1 % Loop all columns
columnHandle = get(columnsHandle,'Item',c); % Handle to column c
columnName = get(columnHandle,'Name'); % Name of column c
columnType = get(columnHandle,'Type'); % Column type: 0 (Y), 3 (X) or 5 (Z)
columnLongName = get(columnHandle,'LongName'); % Long name of column c
columnsUnits = get(columnHandle,'Units'); % Units specified in column c (not actually used here)
columnComment = get(columnHandle,'Comments'); % Comments specified in column c (not actually used here)
% Store column data
if columnType==3 % If x-vector
x = [worksheetData{:,c+1}]';
x(isnan(x)) = []; % Remove NaN
elseif columnType==0 % If y-vector
y = [worksheetData{:,c+1}]';
y(isnan(y)) = []; % Remove NaN
% Check x,y data
if ~isnumeric(x) || ~isnumeric(y) || ismember(1,isnan(x)) || ismember(1,isnan(y)) || isempty(x) || isempty(y) % || length(x)<5 || length(y)<5
% If x and y are not actual data, skip to next
continue
end
% Put (x,y) pair into tempdata structure
tempdata(end+1).name = sprintf('Book[%s]: Sheet[%s]: Col(%s): Name(%s)',workbookLongName,worksheetName,columnName,columnLongName);
if length(x)>length(y)
tempdata(end).xy = [x(1:length(y)) y]; % Cut x
elseif length(x)<length(y)
tempdata(end).xy = [x y(1:length(x))]; % Cut y
else
tempdata(end).xy = [x y]; % Equal sizes of x and y
end
else
% If z-vector, skip
continue
end
end
end
end
tempdata.xy
It is supposed to loop over the whole project and load data from workbooks to matlab workspace variable tempdata.xy.
It was working fine with origin 2017, but since last update to v.2018 it does not load the data (matlab and origin data files are the same). The command in the very beginning seems to be the problem:
workbooksHandle = invoke(originObj,'WorksheetPages')
as I've noticed it gives different output in comparison with older version of origin. Do you have any suggestion what to do to solve the problem?
Best regards,
Malgorzata |
|
|
YimingChen
1606 Posts |
Posted - 07/20/2018 : 4:41:09 PM
|
Hi,
Couple of things need to be changed in your script:
1. change iterator index b, c to int32(b), int32(c), etc. (Line 9, 18, 38)
2. Column type returns strings, need to change line 46, 49.
Please try the following script:
tempdata = struct('name',[],'xy',[]);
tempdata(1) = [];
originObj = actxserver('Origin.ApplicationSI');
workbooksHandle = invoke(originObj,'WorksheetPages');
nbooks = get(workbooksHandle,'Count') % Number of workbooks in project
for b = 0:nbooks-1 % Loop all workbooks
%workbookHandle = get(workbooksHandle,'Parent',b)
workbookHandle = get(workbooksHandle,'Item',int32(b)); % Handle to workbook b
workbookName = get(workbookHandle,'Name'); % Short name of workbook b
workbookLongName = get(workbookHandle,'LongName') % Long name of workbook b
% Identify worksheets
worksheetsHandle = get(workbookHandle,'layers'); % Handle to worksheets of workbook b
nsheets = get(worksheetsHandle,'Count'); % Number of sheets in workbook b
for s = 0:nsheets-1 % Loop all worksheets
worksheetHandle = get(worksheetsHandle,'Item',int32(s)); % Handle to sheet s
worksheetName = get(worksheetHandle,'Name'); % Short name of sheet s
worksheetLongName = get(worksheetHandle,'LongName'); % Long name of sheet s
worksheetData = invoke(originObj,'GetWorksheet',sprintf('[%s]%s',workbookName,worksheetName)); % Get worksheet data
fh = @(x) all(isnan(x(:)));
worksheetData(cellfun(fh, worksheetData)) = {[]}; % Remove all NaN from cell array
fh = @(x) all(ischar(x(:)));
worksheetData(cellfun(fh, worksheetData)) = {[]}; % Remove all strings from cell array
if ~iscell(worksheetData) || isempty(worksheetData)
% If there is no worksheet data found, continue to next
continue
end
% Identify columns
columnsHandle = get(worksheetHandle,'Columns'); % Handle to columns of worksheet s
ncolumns = get(columnsHandle,'Count'); % Number of columns in sheet s
x = [];
y = [];
for c = 0:ncolumns-1 % Loop all columns
columnHandle = get(columnsHandle,'Item',int32(c)); % Handle to column c
columnName = get(columnHandle,'Name'); % Name of column c
columnType = get(columnHandle,'Type'); % Column type: 0 (Y), 3 (X) or 5 (Z)
columnLongName = get(columnHandle,'LongName'); % Long name of column c
columnsUnits = get(columnHandle,'Units'); % Units specified in column c (not actually used here)
columnComment = get(columnHandle,'Comments'); % Comments specified in column c (not actually used here)
% Store column data
if columnType=='COLTYPE_X' % If x-vector
x = [worksheetData{:,c+1}]';
x(isnan(x)) = []; % Remove NaN
elseif columnType=='COLTYPE_Y' % If y-vector
y = [worksheetData{:,c+1}]';
y(isnan(y)) = []; % Remove NaN
% Check x,y data
if ~isnumeric(x) || ~isnumeric(y) || ismember(1,isnan(x)) || ismember(1,isnan(y)) || isempty(x) || isempty(y) % || length(x)<5 || length(y)<5
% If x and y are not actual data, skip to next
continue
end
% Put (x,y) pair into tempdata structure
tempdata(end+1).name = sprintf('Book[%s]: Sheet[%s]: Col(%s): Name(%s)',workbookLongName,worksheetName,columnName,columnLongName);
if length(x)>length(y)
tempdata(end).xy = [x(1:length(y)) y]; % Cut x
elseif length(x)<length(y)
tempdata(end).xy = [x y(1:length(x))]; % Cut y
else
tempdata(end).xy = [x y]; % Equal sizes of x and y
end
else
% If z-vector, skip
continue
end
end
end
end
tempdata.xy
Thank you,
Yiming
|
 |
|
|
malgoska
Poland
36 Posts |
Posted - 07/23/2018 : 08:31:42 AM
|
| Yes, now it works! Thank you very much. |
|
|
| |
Topic |
|
|
|
matlab loading data from origin workbooks