We have discussed a number of programming tips especially on report generation using Microsoft Excel using Matlab. If you have not seen the article, you can refer to my articles on how to read and write a Microsoft Excel file. In this tutorial, we will see how to insert an Microsoft Excel formula through Matlab.
We have often seen, we need to do lot of report generation, when doing data analysis using Matlab and use Microsoft Excel for report generation. I have noticed that lot of people, just compute the result in Matlab and enter the result as data in Microsoft Excel file. However, it is also possible to insert Excel formula through Matlab. We will consider a simple example where we enter the data from Matlab into an Excel file and insert formula.
Example : Let’s say we have the data from some experiment and we will write this data in Excel and then insert the formula ( MAX) using Matlab.
Steps
Step 1: The first step is to create a COM server which runs the Excel Application:
1
2
3

%Create a COM server running the Excel application
Excel = actxserver('Excel.Application');

Step 2: In this step, we will open an Excel file “ResultFile.xls” in the current directory and set the visibility to true.
1
2
3

ResultFile = [pwd '\ResultFile.xlsx'];
Workbook = invoke(Excel.Workbooks,'Open', ResultFile);
set(Excel, 'Visible', 1);

Step 3: In this step, we will insert the data saved in the Matlab array ‘ResultData’. The following code will insert the data in column A.
1
2
3
4
5
6
7
8
9

resultsheet = Excel.ActiveSheet.Name;
ResultData = [12.34 12.56 12.74 11.66 13.34 12.56 13.56 12.45 12.01 12.98];
Location = ['A1:A' int2str(length(ResultData))];
[status, message] = xlswrite1(ResultFile,num2cell(ResultData'), resultsheet, Location);
if status == 0,
errordlg(message.message);
end

Insert Excel formula through Matlab
We need to insert the Excel formula to compute the maximum of the experimental values. You can also use the max command in Matlab to obtain the maximum of the array of values. The Excel formula to compute the Maximum of the range of data is given by
1

=MAX(A1:A10)

Step 4: In this step, we will insert the Excel formula which will compute the Maximum of the data range from cell A1 to A10.
1
2
3
4
5
6
7
8

Max_formula = {['=MAX(' 'A1:' ExcelCol(1) int2str(length(ResultData)) ')']};
Location = ['A' int2str(length(ResultData)+2) ':' 'A' int2str(length(ResultData)+2) ];
[status, message] = xlswrite1(ResultFile, Max_formula, resultsheet, Location);
if status == 0,
errordlg(message.message);
end

Here xlcolumn() function converts between column name and number for Excel representation. The xlcolumn() module can be downloaded from here.
Download
The complete source can be downloaded from here.
I hope, you find this article very useful. If you have any doubts or have any comments concerning this tutorial, Please leave a comment or contact me. I would be glad to help.
Hi there, I have noticed that sometimes this webpage renders a 404 server error. I thought that you would be keen to know. All the best
Thanks a lot. I will have a look at it.
Thanks for a marvelous posting! I definitely enjoyged reading it, you are a great author.
I will remember to bookmark your blog and will eventually come back someday.
I wanjt to encourage you to definitely continue your great job, have a nicde afternoon!