Click here to Skip to main content
Click here to Skip to main content

Write to Excel Sheet through Matlab

By , 28 Jan 2013
Rate this:
Please Sign up or sign in to vote.

Introduction

This tutorial gives a complete overview as to how to write an Excel file from Matlab. Lot of times, we come across situations, where we need to save the data or read the data generated from the experimental simulation to an Excel file.   

Steps  

Step 1: The first step is to create a COM server which runs the Excel Application.   

Excel = actxserver('Excel.Application');  

This assumes that the Excel Application is installed in your system.  If Excel is not installed, this statement will give an error. You can put the above statement between a try-catch block: 

try
    Excel = actxserver('Excel.Application');
catch
    Excel = [];     
end     

Step 2 : In this step, we will check the version of Excel.

ExcelVersion = str2num(Excel.Version);  
If the Microsoft Excel Version installed in your system is 2007, 2010 or above, you would get ExcelVersion would be 12 or above. In such a case, you can handle all the Excel workbook files with extensions *.xls, *.xlsx and *.xlsm. If the version is less than 12, only *.xls file would be supported.

Step 3 : In this step, we will open an Excel file “ResultFile.xls” in the current directory.  

ResultFile = [pwd '\ResultFile.xls'];
Workbook = invoke(Excel.Workbooks,'Open', ResultFile);  
Step 4:  By default, the visibility of the Excel file is set to FALSE. You can make the Excel file visible using the command: 
set(Excel, 'Visible', 1);   

Step 5: In this step, let us say, we will add some data  in the sheet titled “ExperimentSheet” and take the average of the data.  

 

Let us assume, we are taking the experimental results from 10 different Experiments and we need to populate the results in Column A and Column B as shown in the figure. Finally, we need to take the Average of the Experimental Results using the Excel formula: 

=AVERAGE(B2:B11)  

The first step is to open the  Excel Workbook and activate the 'ExperimentSheet' Sheet. The following code tries to open the 'ExperimentSheet' in ResultFile.xls. If the sheet is not found, it adds the Excel sheet and renames it to 'ExperimentSheet'. 

resultsheet = 'ExperimentSheet'
try
    sheet = get(Excel.Worksheets,'Item', resultsheet);
    invoke(sheet, 'Activate');
catch
    % If the Excel Sheet ‘ExperimentSheet’ is not found, Add an 
    % Excel Sheet, and rename the new sheet
    % to ‘ExperimentSheet’
    ExcelWorkbook = Excel.workbooks.Add;
    set(ExcelWorkbook, 'Name',  resultsheet);
end 

Add the Header:

In order to write to an Excel File, we are using xlswrite() function. It has a number of advantages over the xlswrite function in Matlab. The xlswrite1 function can be downloaded from here. As seen from the Excel sheet, we need to first add two headers, “Experiment and Result” in A1 and  B1, respectively. 

%% Add the Header
Location = 'A1:B1';
[status, message] = xlswrite1(ResultFile,{'Experiment','Result'}, resultsheet, Location);
if status == 0,
   errordlg(message.message);
end

In order to make the header bold, we need to select the header:

% Make the header bold
HeaderRange = get(Excel.Activesheet, 'Range', Location);
HeaderRange.Select; 
Excel.Selection.Font.Bold = true; 

In this step, we need to add the iteration number in the first column: 

iter = (1:10)';
Location = 'A2:A11';
[status, message] = xlswrite1(ResultFile num2cell(iter), resultsheet,
Location);
if status == 0,
    errordlg(message.message);
end  

Similarly, we need to add the Experimental Results to Column B. Let us say, the experimental result is saved in the array ‘ExpResults’. For example, we can add the experimental results in the second column as,  

ExpResults = [12.34 12.56 12.74 11.66 13.34 12.56 13.56 12.45 12.01 12.98];
Location = 'B2:B11';
[status, message] = xlswrite1(ResultFile num2cell(ExpResults’), resultsheet, Location);
if status == 0,
    errordlg(message.message);
end

Computing the Average

We need to add the row AVERAGE on 14th Row.

Location = 'A14:A14';
[status, message] = xlswrite1(ResultFile,{'AVERAGE'}, resultsheet,
Location);
if status == 0,
    errordlg(message.message);
end  

Entering the Average Formula 

Location = 'B14:B14';
Avg_formula = {['=AVERAGE(' xlcolumn(2) '2:' xlcolumn(2) int2str(11) ')']};
[status, message] = xlswrite1(ResultFile, Avg_formula, resultsheet, Location);
if status == 0,
  errordlg(message.message);
end   

where xlcolumn() function converts between column name and number for Excel representation. The xlcolumn() module can be downloaded from here.   

function loc = xlcolumn(column)
 
if isnumeric(column)
    if column>256
        error('Excel is limited to 256 columns! Enter an integer number <256');
    end
    letters = {'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'};
    count = 0;
    if column-26<=0
        loc = char(letters(column));
    else
        while column-26>0
            count = count + 1;
            column = column - 26;
        end
        loc = [char(letters(count)) char(letters(column))];
    end
    
else
    letters = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
    if size(column,2)==1
        loc =findstr(column,letters);
    elseif size(column,2)==2
        loc1 =findstr(column(1),letters);
        loc2 =findstr(column(2),letters);
        loc = (26 + 26*loc1)-(26-loc2);
    end
end 

The last step is to save the Excel file and close the Excel application:

%Save and Close the Excel File
invoke(Workbook,'Save');
invoke(Excel,'Quit');
delete(Excel);

Download: The complete Matlab source can be downloaded below:  Download ComputeAverage.zip.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Kiran Chandrashekhar
Software Developer SapnaEdu, Inc
India India
I hope, you found my articles useful. My name is Kiran Chandrashekhar. I am the co-founder of QLab Technologies, Bangalore. I consider myself an expert in Matlab, C#, PHP/MySQL, Shell, Perl and Python. I do software consulting through QLab Technologies.
 
If you have any queries or need any assistance in programming, please write to me at Kiran.Chandrashekhar@googlemail.com
 
Website : http://www.sapnaedu.in, www.qlab-tech.com
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberVasastan25-Sep-13 22:33 
GeneralMy vote of 1 Pinmemberziloot12-Jul-13 23:22 
QuestionMissing Download PinmemberKiran Chandrashekhar28-Jan-13 7:27 
I just uploaded the missing image
AnswerRe: Missing Download PinstaffSmitha Vijayan28-Jan-13 7:34 
GeneralRe: Missing Download PinmemberKiran Chandrashekhar28-Jan-13 7:44 
GeneralRe: Missing Download PinstaffSmitha Vijayan28-Jan-13 7:46 
GeneralMissing download PinstaffSmitha Vijayan28-Jan-13 7:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 28 Jan 2013
Article Copyright 2013 by Kiran Chandrashekhar
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid