MATLAB Function Reference

xlsread

Read Microsoft Excel spreadsheet file (.xls)

Syntax

• num = xlsread('filename')
  num = xlsread('filename', -1)
  num = xlsread('filename', sheet)
  num = xlsread('filename', 'range')
  num = xlsread('filename', sheet, 'range')
  num = xlsread('filename', sheet, 'range', 'basic')
  num = xlsread('filename', ..., functionhandle)
  [num, txt] = xlsread('filename', ...)
  [num, txt, raw] = xlsread('filename', ...)
  [num, txt, raw, X] = xlsread('filename', ..., functionhandle)
  xlsread filename sheet range basic
  

Description

num = xlsread('filename') returns numeric data in double array num from the first sheet in the Microsoft Excel spreadsheet file named filename. The xlsread ignores leading rows or columns of text. However, if a cell not in a leading row or column is empty or contains text, xlsread puts a NaN in its place in the return array, num.

Note    The full functionality of xlsread depends on the ability to start Excel as a COM server from MATLAB. If your system does not have this capability, the xlsread syntax that passes the 'basic' keyword is recommended.

num = xlsread('filename', -1) opens the file filename in an Excel window, enabling you to interactively select the worksheet to be read and the range of data on that worksheet to import. To import an entire worksheet, first select the sheet in the Excel window and then click the OK button in the Data Selection Dialog box. To import a certain range of data from the sheet, select the worksheet in the Excel window, drag and drop the mouse over the desired range, and then click OK. (See "COM Server Requirements" below.)

num = xlsread('filename', sheet) reads the specified worksheet, where sheet is either a positive, double scalar value or a quoted string containing the sheet name. To determine the names of the sheets in a spreadsheet file, use xlsfinfo.

num = xlsread('filename', 'range') reads data from a specific rectangular region of the default worksheet (Sheet1). Specify range using the syntax 'C1:C2', where C1 and C2 are two opposing corners that define the region to be read. For example, 'D2:H4' represents the 3-by-5 rectangular region between the two corners D2 and H4 on the worksheet. The range input is not case sensitive and uses Excel A1 notation. (See help in Excel for more information on this notation.) (Also, see "COM Server Requirements" below.)

num = xlsread('filename', sheet, 'range') reads data from a specific rectangular region (range) of the worksheet specified by sheet. See the previous two syntax formats for further explanation of the sheet and range inputs. (See "COM Server Requirements," below.)

num = xlsread('filename', sheet, 'range', 'basic') imports data from the spreadsheet in basic import mode. This is the mode used on UNIX platforms as well as on Windows when Excel is not available as a COM server. In this mode, xlsread does not use Excel as a COM server, and this limits import ability. Without Excel as a COM server, range is ignored and, consequently, the whole active range of a sheet is imported. (You can set range to the empty string ('')). Also, in basic mode, sheet is case-sensitive and must be a quoted string.

num = xlsread('filename', ..., functionhandle) calls the function associated with functionhandle just prior to obtaining spreadsheet values. This enables you to operate on the spreadsheet data (for example, convert it to a numeric type) before reading it in. (See "COM Server Requirements," below.)

You can write your own custom function and pass a handle to this function to xlsread. When xlsread executes, it reads from the spreadsheet, executes your function on the data read from the spreadsheet, and returns the final results to you. When xlsread calls your function, it passes a range interface from Excel to provide access to the data read from the spreadsheet. Your function must include this interface both as an input and output argument. Example 5 below shows how you might use this syntax.

[num, txt]= xlsread('filename', ...) returns numeric data in array nmu and text data in cell array txt. All cells in txt that correspond to numeric data contain the empty string.

If txt includes data that was previously written to the file using xlswrite, and the range specified for that xlswrite operation caused undefined data ('#N/A') to be written to the worksheet, then cells containing that undefined data are represented in the txt output as 'ActiveX VT_ERROR: '.

[num, txt, raw] = xlsread('filename', ...) returns numeric and text data in num and txt, and unprocessed cell content in cell array raw, which contains both numeric and text data. (See "COM Server Requirements" below.)

[num, txt, raw, X] = xlsread('filename', ..., functionhandle) calls the function associated with functionhandle just prior to reading from the spreadsheet file. This syntax returns one additional output X from the function mapped to by functionhandle. Example 6 below shows how you might use this syntax. (See "COM Server Requirements," below.)

xlsread filename sheet range basic is the command format for xlsread, showing its usage with all input arguments specified. When using this format, you must specify sheet as a string, (for example, Income or Sheet4) and not a numeric index. If the sheet name contains space characters, then quotation marks are required around the string, (for example, 'Income 2002').

Remarks

Handling Excel Date Values

MATLAB imports date fields from Excel files in the format in which they were stored in the Excel file. If stored in string or date format, xlsread returns the date as a string. If stored in a numeric format, xlsread returns a numeric date.

Both Excel and MATLAB represent numeric dates as a number of serial days elapsed from a specific reference date. However, Excel uses January 1, 1900 as the reference date while MATLAB uses January 0, 0000. Due to this difference in the way Excel and MATLAB compute numeric date values, any numeric date imported from Excel into MATLAB must first be converted before being used in the MATLAB application.

You can do this conversion after the xlsread completes, as shown below:

•    excelDates = xlsread('filename')
     matlabDates = datenum('30-Dec-1899') + excelDates
     datestr(matlabDates,2)
  

You can also do this as part of the xlsread operation by writing a conversion routine that acts directly on the Excel COM Range object, and then passing a function handle for your routine as an input to xlsread. The description above for the following syntax, along with Examples 5 and 6, explain how to do this:

• [num, txt, raw, X] = xlsread('filename', ..., functionhandle)
  

COM Server Requirements

The following six syntax formats are supported only on computer systems capable of starting Excel as a COM server from MATLAB. They are not supported in basic mode.

• num = xlsread('filename', -1)
  num = xlsread('filename', 'range')
  num = xlsread('filename', sheet, 'range')
  [num, txt, raw] = xlsread('filename', ...)
  num = xlsread('filename', ..., functionhandle)
  [num, txt, raw, opt] = xlsread('filename', ..., functionhandle)
  

Examples

Example 1-- Reading Numeric Data

The Microsoft Excel spreadsheet file testdata1.xls contains this data:

•     1    6
      2    7
      3    8
      4    9
      5   10
  

To read this data into MATLAB, use this command:

• A = xlsread('testdata1.xls')
  A =
      1    6
      2    7
      3    8
      4    9
      5   10
  

Example 2 -- Handling Text Data

The Microsoft Excel spreadsheet file testdata2.xls contains a mix of numeric and text data:

•     1    6
      2    7
      3    8
      4    9
      5   text
  

xlsread puts a NaN in place of the text data in the result:

• A = xlsread('testdata2.xls')
  A =
      1    6
      2    7
      3    8
      4    9
      5   NaN
  

Example 3 -- Selecting a Range of Data

To import only rows 4 and 5 from worksheet 1, specify the range as 'A4:B5':

• A = xlsread('testdata2.xls', 1, 'A4:B5')
  
  A =
      4    9
      5   NaN
  

Example 4 -- Handling Files with Row or Column Headers

A Microsoft Excel spreadsheet labeled Temperatures in file tempdata.xls contains two columns of numeric data with text headers for each column:

• Time  Temp
  12     98
  13     99
  14     97
  

If you want to import only the numeric data, use xlsread with a single return argument. Specify the filename and sheet name as inputs.

xlsread ignores any leading row or column of text in the numeric result.

• ndata = xlsread('tempdata.xls', 'Temperatures')
  
  ndata =
      12    98
      13    99
      14    97
  

To import both the numeric data and the text data, specify two return values for xlsread:

• [ndata, headertext] = xlsread('tempdata.xls', 'Temperatures')
  
  ndata =
      12    98
      13    99
      14    97
  
  headertext = 
      'Time'    'Temp'
  

Example 5 -- Passing a Function Handle

This example calls xlsread twice, the first time as a simple read from a file, and the second time requesting that xlsread execute some user-defined modifications on the data prior to returning the results of the read. These modifications are performed by a user-written function, setMinMax, that you pass as a function handle in the call to xlsread. When xlsread executes, it reads from the spreadsheet, executes the function on the data read from the spreadsheet, and returns the final results to you.

Note    The function passed to xlsread operates on the copy of the data read from the spreadsheet. It does not modify data in the spreadsheet itself.

Read a 10-by-3 numeric array from Excel spreadsheet testsheet.xls. with a simple xlsread statement that does not pass a function handle. Note that the values returned range from -587 to +4,149:

• arr = xlsread('testsheet.xls')
  arr =
    1.0e+003 *
      1.0020    4.1490    0.2300
      1.0750    0.1220   -0.4550
     -0.0301    3.0560    0.2471
      0.4070    0.1420   -0.2472
      2.1160   -0.0557   -0.5870
      0.4040    2.9280    0.0265
      0.1723    3.4440    0.1112
      4.1180    0.1820    2.8630
      0.9000    0.0573    1.9750
      0.0163    0.2000   -0.0223
  

In preparation for the second part of this example, write a function setMinMax that restricts the values returned from the read to be in the range of 0 to 2000. You will need to pass this function in the call to xlsread which will then execute the function on the data it has read before returning it to you.

When xlsread calls your function, it passes a range interface from Excel to provide access to the data read from the spreadsheet. This is shown as DataRange in this example. Your function must include this interface both as an input and output argument. The output argument allows your function to pass modified data back to xlsread:

• function [DataRange] = setMinMax(DataRange)
  maxval = 2000;  minval = 0;
   
  for k = 1:DataRange.Count
     v = DataRange.Value{k};
     if v > maxval || v < minval
        if v > maxval
           DataRange.Value{k} = maxval;
        else
           DataRange.Value{k} = minval;
        end
     end
  end
  

Now call xlsread, passing a function handle for the setMinMax function as the final argument. Note the changes from the values returned from the last call to xlsread:

• arr = xlsread('testsheet.xls', '', '', '', @setMinMax)
  arr =
    1.0e+003 *
      1.0020    2.0000    0.2300
      1.0750    0.1220         0
           0    2.0000    0.2471
      0.4070    0.1420         0
      2.0000         0         0
      0.4040    2.0000    0.0265
      0.1723    2.0000    0.1112
      2.0000    0.1820    2.0000
      0.9000    0.0573    1.9750
      0.0163    0.2000         0
  

Example 6 -- Passing a Function Handle with Additional Output

This example adds onto the previous one by returning an additional output from the call to setMinMax. Modify the function so that it not only limits the range of values returned, but also reports which elements of the spreadsheet matrix have been altered. Return this information in a new output argument, indices:

• function [DataRange, indices] = setMinMax(DataRange)
  maxval = 2000;  minval = 0;
  indices = [];
   
  for k = 1:DataRange.Count
     v = DataRange.Value{k};
     if v > maxval || v < minval
        if v > maxval
           DataRange.Value{k} = maxval;
        else
           DataRange.Value{k} = minval;
        end
     indices = [indices k];
     end
  end
  

When you call xlsread this time, account for the three initial outputs, and add a fourth called idx to accept the indices returned from setMinMax. Call xlsread again, and you will see just where the returned matrix has been modified:

• [arr txt raw idx] = xlsread('testsheet.xls', ...
                              '', '', '', @setMinMax);
  
  idx
  idx =
      3   5   8  11  13  15  16  17  22  24  25  28  30
  arr
  arr =
    1.0e+003 *
      1.0020    2.0000    0.2300
      1.0750    0.1220         0
           0    2.0000    0.2471
      0.4070    0.1420         0
      2.0000         0         0
      0.4040    2.0000    0.0265
      0.1723    2.0000    0.1112
      2.0000    0.1820    2.0000
      0.9000    0.0573    1.9750
      0.0163    0.2000         0
  

See Also

xlswrite, xlsfinfo, wk1read, textread, function_handle

xlsfinfo

xlswrite

© 1994-2005 The MathWorks, Inc.