Importing data from excel with formula

Question

Akash 2014-5-14

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/129642-importing-data-from-excel-with-formula

回答： Image Analyst 2019-11-26

I want to import the data from excel sheet ensuring that the logic behind the sheet is not lost. So the formula remains intact. What is the procedure for that?

File attached.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2014-5-15

My guess is you can't...

请先登录，再进行评论。

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

Answer 1

Eric 2014-5-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/129642-importing-data-from-excel-with-formula#answer_136921

在 MATLAB Online 中打开

Here's a snippet of code to get you started. I created a spreadsheet called delme.xlsx. In cell A1 I put the value 1. In cell A2 I used the formula "=PI()". I named cell A1 "Value1" and cell A2 "Value2". In cell A3 I used the formula "=sum(Value1,Value2)".

fname = 'c:\temp\delme.xlsx';
xlObj = actxserver('Excel.Application');%Start Excel
xlObj.Visible = 1;%Make Excel visible
wsObj = xlObj.Workbooks.Open(fname);%Open workbook
Sheet = wsObj.Sheets.Item(1);%Assume we're using the first sheet
A3_value = Sheet.Range('A3').Value
A3_formula = Sheet.Range('A3').Formula

A3_value is equal to 4.141592653589793 and A3_formula is the string "=SUM(Value1,Value2)".

Hopefully this helps,

Eric

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

Image Analyst 2019-11-26

Eric hasn't been here in quite some time. If you're willing to accept help from others, I suggest you post your question as a new thread in answers after you read this.

dpb 2019-11-26

在 MATLAB Online 中打开

I've not tried but I'd think simply

A3_formula = '=SUM(A1,A2)';
Sheet.Range('A3').Formula=A3_formula;

should do the trick.

Salt to suit, of course...

请先登录，再进行评论。

Answer 2

Image Analyst 2014-5-15

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/129642-importing-data-from-excel-with-formula#answer_136850

I'm sure you can do it using ActiveX, though I'm not sure which ActiveX command is the correct one to use. You can pretty much do absolutely anything with Office apps using ActiveX. It's the figuring out which method to use that is the hard part since there are thousands of methods.

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2014-5-15

Yeah, should've added the caveat to "can't" -- I was limiting to the "import" idea using a higher-level function.

请先登录，再进行评论。

Answer 3

Image Analyst 2019-11-26

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/129642-importing-data-from-excel-with-formula#answer_403456

在 MATLAB Online 中打开

Try this well commented, fully functioning demo I made up. Change the name of the workbook to whatever you're using instead of "Example.xlsx".

% Launch an Excel server using ActiveX (Windows ONLY).
excelObject = actxserver('Excel.Application');
% Create the filename of the existing workbook.
fullFileName = fullfile(pwd, 'Example.xlsx');
% Make sure the file exists.
if ~isfile(fullFileName)
	errorMessage = sprintf('The workbook file does not exist:\n%s', fullFileName);
	uiwait(errordlg(errorMessage));
	return;
end
% Open the workbook from disk.
excelWorkbook = excelObject.workbooks.Open(fullFileName);
% Excel is invisible so far.  Make it visible.
excelObject.Visible = true;
% Create a string with the formula just like you'd have it in Excel.
yourFormula = '=SUM(A1..A100)'; % No spaces allowed.
% Assign the formula to the cell "B1".
excelWorkbook.ActiveSheet.Range('B1').Formula = yourFormula;
% Save the current state of the workbook.
excelWorkbook.Save;
% Close the workbook.  Excel will stay open but be hidden.
% You can still see it as "Microsoft Excel" in Task Manager.
excelWorkbook.Close;
% Shut down the Excel server instance.
excelObject.Quit;
% Even after quitting, you can still see it as "Microsoft Excel" in Task Manager.
% Clear the excel object variable from MATLAB's memory.
clear('excelObject', 'excelWorkbook', 'yourFormula');
% The clear finally shuts down the server and it no longer appears in Task Manager.
fprintf('Done interacting with Excel.\n');