How to assign a function to an Excel cell

26 次查看（过去 30 天）
Ronald Stahl
Ronald Stahl 2024-5-28
回答： Shivani 2024-5-29
I have been using xlswrite to write Excel functions into cells in an Excel spreadsheet. When opening the spreadsheet the functions evaluate as expected. Now Mathworks is calling xlswrite "not recommended". I've been unable to find a way to accomplish the same thing without xlswrite. How can I use another builtin Matlab function to perform the same as the xlswrite?
% Ways to write to Excel
FN = 'TestFile.xlsx';
% Put some values into the test file
Vals = num2cell(rand(10,3));
writecell(Vals, FN, 'range', 'a1');
% Function for an Excel cell
XFunction = {'=SUM(B1:B10)'};
% This creates a function in Excel that works
xlswrite(FN, XFunction, 1, 'D1');
% This does not
writecell(XFunction, FN, 'Range', 'D2')
winopen(FN)

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

回答（1 个）

Shivani
Shivani 2024-5-29
Hello @Ronald Stahl
Based on my understading, there is no direct way to write Excel formulas into an Excel file using "writematrix", "writecell", or "writetable" in a way that Excel recognizes them as formulas. However, there is an alternate approach to writing into an Excel file without using the "xlswrite" function. This can be achieved by using ActiveX on Windows.
You can use ActiveX to interact directly with Excel, enabling you to write formulas into cells. This method works around the limitation of "writecell" and similar functions by programmatically controlling Excel to input the formula, ensuring that Excel correctly interprets it as a formula rather than a string.
Kindly refer to the following sample code on how ActiveX can be used to write the formula into an Excel cell:
FN = 'TestFile.xlsx';
Vals = num2cell(rand(10,3));
writecell(Vals, FN, 'Range', 'A1');
Excel = actxserver('Excel.Application');
Workbook = Excel.Workbooks.Open(fullfile(pwd, FN));
Sheet = Workbook.Sheets.Item(1);
XFunction = '=SUM(B1:B10)';
Range = Sheet.Range('D1');
Range.Formula = XFunction;
Workbook.Save;
Workbook.Close;
Excel.Quit;
delete(Excel);
winopen(FN);
Please note that this approach is Windows-specific due to the use of ActiveX controls, which are not available on macOS or Linux.
I hope this answers your question!

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

产品


版本

R2024a

Community Treasure Hunt

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

Start Hunting!

Translated by