Autofill range in excel using Matlab
显示 更早的评论
Hi guys,
I have a formula in an excel cell. I am trying to open the excel file from matlab and autofill the column (equivalent to double clicking the bottom right hand corner of the cell in excel). I have tried to set up a com.excel.application but have run into some problems, "I get the error No appropriate method, property, or field constants for class COM.Excel_Application."
My code is
Excel = actxserver('Excel.Application');
ResultFile = [excelFilePath_2 excelFileName2 '.xlsx'];
Workbook = invoke(Excel.Workbooks,'Open', ResultFile);
resultsheet = 'strain';
try
sheet = get(Excel.Worksheets,'Item', resultsheet);
invoke(sheet, 'Activate');
catch
% If the Excel Sheet ‘ExperimentSheet’ is not found, throw an error message
errordlg([resultsheet 'not found']);
end
r(1) = Excel.ActiveSheet.Range('I2');
r(2) = Excel.ActiveSheet.Range('J2');
r(3) = Excel.ActiveSheet.Range(['J' int2str(size(time,1))]);
sourceRange = Excel.ActiveSheet.get('Range',r(1),r(2));
fillRange=Excel.ActiveSheet.get('Range',r(1),r(3));
sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)
invoke(Excel.ActiveWorkbook,'Save');
Excel.Quit
Excel.delete
clear Excel
Is there any other method to do what I am trying to achieve? Thanks
4 个评论
Guillaume
2014-12-2
When reporting an error message thrown by matlab, always report the entire error message including the part that shows the line where the error occurs.
Which line is at fault?
Yasha
2014-12-2
Yasha
2014-12-4
Guillaume
2014-12-4
Did you not spot the answer below?
Does it not answer your question?
采纳的回答
Most likely the error comes from this line:
sourceRange.AutoFill(fillRange,Excel.constants.xlFillDefault)
There's no namespace called Excel.constants. In general matlab struggles with COM enumerations, so you're better off using the numerical values of the constants. See here for the autofill constants. However, since you're using the default, you could just omit it.
sourceRange.AutoFill(fillRange, 0); %0 is xlFillDefault
sourceRange.AutoFill(fillRange); %does the same
----
Side note: rather than working on ActiveSheet / ActiveWorkbook, I would use the sheet / workbook reference you've previously obtained. I would also use sprintf instead of numeric conversion and string concatenation:
Excel = actxserver('Excel.Application');
ResultFile = fullfile(excelFilePath_2, sprintf('%s.xlsx', excelFileName2));
Workbook = Excel.Workbooks.Open(ResultFile); %or use invoke on older matlab
resultsheet = 'strain';
try
sheet = Workbook.Worksheets.Item(resultsheet); %or use get on older matlab
%no need to activate sheet if you use its reference
catch
% If the Excel Sheet ‘ExperimentSheet’ is not found, throw an error message
errordlg([resultsheet 'not found']);
end
r = sheet.Range('I2');
r(2) = sheet.Range('J2');
r(3) = sheet.Range(sprintf(J%d', size(time, 1))); %much clearer with sprintf
sourceRange = sheet.get('Range',r(1),r(2));
fillRange = sheet.get('Range',r(1),r(3));
sourceRange.AutoFill(fillRange,0)
Workbook.Save
Excel.Quit
clear Excel %this also does Excel.delete
3 个评论
Yasha
2014-12-4
Yasha
2014-12-4
Guillaume
2014-12-4
Yes, sorry, it should have read
sheet = Workbook.Worksheets.Item(resultsheet); % or get(Workbook.Worksheets, 'Item', resultsheet);
I would use the worksheets collection of Workbook rather than the one of Excel (in case the same worksheet name is found in more than one workbook).
更多回答（0 个）
类别
在 帮助中心 和 File Exchange 中查找有关 Data Import from MATLAB 的更多信息
Translated by 
