blinkdagger

Microsoft Excel is a common data format, so it’s a good idea to learn how to work with it in the MATLAB environment. Exporting data to the Excel format can be incredibly easy, but there are also some nuances that will be discussed. There are a couple of ways to export data to Excel format, and we’ll discuss how to use the XLSWRITE command to do it.

Contents

• XLSWRITE - A Simple Example
• XLSWRITE - Specify Sheet and Range
• XLSWRITE - Writing Data Headers
• XLSWRITE - Writing Numerical and Text Data using Cell Arrays
• XLSWRITE - Writing Numerical and Text Data using Cell Arrays (Another Example)
• Deleting XLS Sheets
• Useful Links

XLSWRITE - A Simple Example

The simplest example of using XLSWRITE is when you are dealing strictly with numerical data. Let’s generate some sample data:

%generate the sample data
data = [
    17    24     1     8    15
    23     5     7    14    16
     4     6    13    20    22
    10    12    19    21     3
    11    18    25     2     9];

Now, using the following command:

%write the data to an Excel File
xlswrite('myDataFile.xls', data);

This command writes the data to myDataFile.xls into the current MATLAB directory. If you go there, and open up the file, you’ll see the following:

Alternatively, you can also specify the full path where you want to write the file too. This is useful so that you don’t have to change directories when writing data. See this post on avoiding the CD command for more information on this topic. Make sure that the directory where you want to write to exists, or else MATLAB will spit out an error

%write the data to a specific location
xlswrite('C:\blinkdagger\myDataFile.xls', data);

XLSWRITE - Specify Sheet and Range

The XLSWRITE command also allows you to specify which worksheet you want to write too. If you specify a sheet that doesn’t exist, than MATLAB will create a new sheet. In addition, it also has an argument that allows you to specify where on that worksheet to begin writing the data. Say, for example, that I wanted to start writing the data onto a sheet named Quan, starting at cell C3. Then I could do the following:

%write the data to a specific sheet, starting at cell C3
xlswrite('myDataFile.xls', data,'Quan', 'C3');

Your output will now look like this:

XLSWRITE - Including Column Headers

Now, let’s say you want to add a little more descriptive information for your data. You want to add some column identifiers, as shown in the image below:

One way of doing this is to use a cell array to store the column headers.

%create the cell array containing the column headers
columnHeader = {'Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'};

%write the column headers first
xlswrite('myDataFile.xls', columnHeader );

%write the data directly underneath the column headers
xlswrite('myDataFile.xls', data,'Sheet1','A2');

In the next section, we’ll discuss another way to do this, which is actually my preferred method.

XLSWRITE - Writing Numerical and Text Data using Cell Arrays

In the above example, we used the XLSWRITE function twice, first for the column headers, and the second time for the actual data. Since XLSWRITE takes in either a normal array OR a cell array as the data input, we can use this to our advantage. My preferred method is to create one cell array containing all the relevant data, and then executing a single call to the XLSWRITE function. As you can expect, this method can involve some cell array manipulation.

%create the cell array containing the column headers
columnHeader = {'Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'};

%change the numerical array into a cell array
numericalData = num2cell(data);

%combine the two cell arrays into one
allData = [columnHeader; numericalData];

%write the data to the Excel file
xlswrite('myDataFile.xls', allData );

The advantages of doing it this way is that you don’t have to specify where to start writing on a particular sheet because all the data is already arranged in the form that you want it to be in. Let’s say you wanted to write data to an Excel file such as shown below:

Using this example, you can see how using a single cell array to store all your data can be advantageous. If you prefer writing one segment of data at a time, you could have also done it the following way. You have to be careful that you’re writing data to the right place, or else some of it may get overwritten. Personally, I believe the method above is superior to the one shown below!

columnHeader = {'Column 1', 'Column 2', 'Column 3', 'Column 4', 'Column 5'};
numericalData = num2cell(data);
rowHeader = {''; 'Row 1'; 'Row 2'; 'Row 3'; 'Row 4'; 'Row 5'};

%write data to Excel File, specifying the placement
xlswrite('myDataFile.xls', columnHeader ,'Sheet1','A2');
xlswrite('myDataFile.xls', numericalData ,'Sheet1','B2');
xlswrite('myDataFile.xls', rowHeader ,'Sheet1','A1');

As you can see, there are plenty of advantages of gathering all your data into a single cell array. This way, you only need to make one XLSWRITE. But the downside is that you have to be pretty clever about your cell array manipulation. This sure beats specifying where to write data!

XLSWRITE - Writing Numerical and Text Data using Cell Arrays, Another Example

If one example wasn’t enough, here’s another example that is a bit more complicated! Let’s say you wanted to add a column of data that consisted of different colors. In addition, you also want to add some header lines to your data, as shown in the image below:

columnHeader = {'Column 1', 'Column 2', 'Column 3',...
 'Column 4', 'Column 5', 'Column 6'};

numericalData = num2cell(data); %convert into cell array
colorData = {'Red'; 'Blue'; 'Green'; 'Black';'Yellow'};

%create the row column
rowHeader = {''; 'Row 1'; 'Row 2'; 'Row 3'; 'Row 4'; 'Row 5'};
allData  = [numericalData  colorData ]; %combine cell arrays
allData = [columnHeader; allData]; %combine cell arrays
allData = [rowHeader allData]; %combine cell arrays

%these are the headerlines
headerLines = { 'Blinkdagger Tutorial on XLSWRITE'; ...
                'Written By Quan Quach'; ...
                'This is a sample Data set';...
                'All Rights Reserved'};

%location of where to put the actual data on excel sheet
dataPlacement = ['A' num2str(length(headerLines)+1)];

%write the header information first
xlswrite('myDataFile.xls', headerLines);

%write the actual data
xlswrite('myDataFile.xls', allData, 'Sheet1', dataPlacement);

Deleting XLS Sheets

By default, MATLAB creates 3 sheets everytime a new Excel file is created. Luckily, there’s a way to delete these sheets programatically.

You can automatically delete the standard excel sheets using this guide.

Useful Links

How to Import Excel Data with XLSREAD