Documentation Center

  • Trial Software
  • Product Updates

xlsread

Read Microsoft Excel spreadsheet file

Syntax

  • num = xlsread(filename) example
  • num = xlsread(filename,sheet)
  • num = xlsread(filename,xlRange) example
  • num = xlsread(filename,sheet,xlRange) example
  • num = xlsread(filename,sheet,xlRange,'basic')
  • [num,txt,raw] = xlsread(___) example
  • ___ = xlsread(filename,-1)
  • [num,txt,raw,custom] = xlsread(filename,sheet,xlRange,'',functionHandle) example

Description

example

num = xlsread(filename) reads data from the first worksheet in the Microsoft® Excel® spreadsheet file named filename and returns the numeric data in array num.

On Windows® systems with Microsoft Excel software, xlsread reads any file format recognized by your version of Excel.

If your system does not have Excel for Windows, xlsread operates in basic import mode, and reads only XLS, XLSX, XLSM, XLTX, and XLTM files.

num = xlsread(filename,sheet) reads the specified worksheet.

example

num = xlsread(filename,xlRange) reads data from the specified range, xlRange, of the first worksheet in the file.

example

num = xlsread(filename,sheet,xlRange) reads from the specified sheet and range, xlRange.

num = xlsread(filename,sheet,xlRange,'basic') reads data from the spreadsheet in basic mode, the default on systems without Excel for Windows. If you do not specify all the arguments, use empty strings as placeholders, for example, num = xlsread(filename,'','','basic').

example

[num,txt,raw] = xlsread(___) additionally returns the text fields in cell array txt, and the unprocessed data (numbers and text) in cell array raw using any of the input arguments in the previous syntaxes. If xlRange is specified, leading blank rows and columns in the worksheet that precede rows and columns with data are returned in raw.

___ = xlsread(filename,-1) opens an Excel window to interactively select data. Select the worksheet, drag and drop the mouse over the range you want, and click OK. This syntax is supported only on Windows systems with Excel software.

example

[num,txt,raw,custom] = xlsread(filename,sheet,xlRange,'',functionHandle) reads from the spreadsheet, executes the function associated with functionHandle on the data, and returns the final results as numeric data in array num. The xlsread function optionally returns the text fields in cell array txt, the unprocessed data (numbers and text) in cell array raw, and the second output from the function associated with functionHandle in array custom. The xlsread function does not change the data stored in the spreadsheet. This syntax is supported only on Windows systems with Excel software.

Examples

expand all

Read Data from First Worksheet Into Numeric Array

Create an Excel file named myExample.xlsx.

values = {1, 2, 3 ; 4, 5, 'x' ; 7, 8, 9};
headers = {'First','Second','Third'};
xlswrite('myExample.xlsx', [headers; values]);

Sheet1 of myExample.xlsx contains:

   First    Second    Third
       1         2        3
       4         5    x    
       7         8        9

Read data from the first worksheet.

filename = 'myExample.xlsx';
A = xlsread(filename)
A =
     1     2     3
     4     5   NaN
     7     8     9

xlsread returns the numeric data in array A.

Read a Specific Range of Data

Read a specific range of data from the Excel file in the previous example.

filename = 'myExample.xlsx';
sheet = 1;
xlRange = 'B2:C3';

subsetA = xlsread(filename, sheet, xlRange)
subsetA =
     2     3
     5   NaN

Read a Column of Data

Read the second column of data from the Excel file in the first example.

filename = 'myExample.xlsx';

columnB = xlsread(filename,'B:B')
columnB =
     2
     5
     8

For better performance, specify the row numbers in the range, as shown in the previous example.

Request Numeric, Text, and Unprocessed Data

Request the numeric data, text, and a copy of the unprocessed (raw) data from the Excel file in the first example.

[ndata, text, alldata] = xlsread('myExample.xlsx')
ndata =
     1     2     3
     4     5   NaN
     7     8     9

text = 
    'First'    'Second'    'Third'
    ''         ''          ''     
    ''         ''          'x'    

alldata = 
    'First'    'Second'    'Third'
    [    1]    [     2]    [    3]
    [    4]    [     5]    'x'    
    [    7]    [     8]    [    9]

xlsread returns numeric data in array ndata, text data in cell array text, and unprocessed data in cell array alldata.

Execute a Function on a Worksheet and Return Numeric Data

In the Editor, create a function to process data from a worksheet. In this case, set values outside the range [-3, 3] to -3 or 3.

function [Data] = setMinMax(Data)

  minval = -3; maxval = 3;
 
  for k = 1:Data.Count
    v = Data.Value{k};
    if v > maxval || v < minval
       if v > maxval
          Data.Value{k} = maxval;
       else
           Data.Value{k} = minval;
       end
    end
  end

In the Command Window, add data to myExample.xlsx.

misc = pi*gallery('normaldata',[10,3],1);
xlswrite('myExample.xlsx',misc,'MyData');

Worksheet MyData contains the following values, which range from -6.6493 to 3.4845:

    2.7156   -6.1744    1.8064
    0.2959   -2.3383   -2.7210
   -2.6764   -1.7351   -6.6493
    2.7442   -2.5752   -3.0300
   -1.3761    3.4845    0.6683
   -1.3498   -1.9319    1.5014
   -3.4643   -0.8000    0.3162
    1.2448   -0.8477    0.9344
   -3.0314   -5.2527    1.7912
    0.5292   -5.8938   -5.1035

Read the data from the worksheet, and reset any values outside the range [-3, 3]. Specify the sheet name, but use '' as placeholders for the xlRange and 'basic' inputs.

trim = xlsread('myExample.xlsx','MyData','','',@setMinMax)
trim =
    2.7156   -3.0000    1.8064
    0.2959   -2.3383   -2.7210
   -2.6764   -1.7351   -3.0000
    2.7442   -2.5752   -3.0000
   -1.3761    3.0000    0.6683
   -1.3498   -1.9319    1.5014
   -3.0000   -0.8000    0.3162
    1.2448   -0.8477    0.9344
   -3.0000   -3.0000    1.7912
    0.5292   -3.0000   -3.0000

Request Custom Output

Execute a function on a worksheet and display the custom index output.

In the Editor, modify the function setMinMax from the previous example to return the indices of the changed elements (custom output).

function [Data,indices] = setMinMax(Data)

  minval = -3; maxval = 3;
  indices = [];
  
  for k = 1:Data.Count
    v = Data.Value{k};
    if v > maxval || v < minval
       if v > maxval
          Data.Value{k} = maxval;
       else
           Data.Value{k} = minval;
       end
       indices = [indices k];
    end
  end

Read the data from the worksheet MyData, and request the custom index output, idx.

[trim,txt,raw,idx] = xlsread('myExample.xlsx',...
'MyData','','',@setMinMax);
disp(idx)
7     9    11    15    19    20    23    24    30

Input Arguments

expand all

filename — Name of file to readstring

Name of the file to read, specified as a string. If you do not include an extension, xlsread searches for a file with the specified name and a supported Excel extension. xlsread can read data saved in files that are currently open in Excel for Windows.

Example: 'myFile.xlsx'

Data Types: char

sheet — Worksheet to readstring | positive integer

Worksheet to read, specified as one of the following:

  • String that contains the worksheet name. Cannot contain a colon (:). To determine the names of the sheets in a spreadsheet file, use xlsfinfo. For XLS files in basic mode, sheet is case sensitive.

  • Positive integer that indicates the worksheet index. Not supported for XLS files in basic mode.

xlRange — Rectangular portion of the worksheet to readstring

Rectangular portion of the worksheet to read, specified as a string.

Specify xlRange using the syntax 'C1:C2', where C1 and C2 are two opposing corners that define the region to read. For example, 'D2:H4' represents the 3-by-5 rectangular region between the two corners D2 and H4 on the worksheet. The xlRange input is not case sensitive, and uses Excel A1 reference style (see Excel help).

Range selection is not supported when reading XLS files in basic mode. In this case, use '' in place of xlRange.

If you do not specify sheet, then xlRange must include both corners and a colon character, even for a single cell (such as 'D2:D2'). Otherwise, xlsread interprets the input as a worksheet name (such as 'sales' or 'D2').

If you specify sheet, then xlRange:

  • Does not need to include a colon and opposite corner to describe a single cell.

  • Can refer to a named range that you defined in the Excel file (see the Excel help).

Data Types: char

'basic' — Flag to request reading in basic modeliteral string

Flag to request reading in basic mode, specified as the literal string, 'basic'.

basic mode is the default for systems without Excel for Windows. In basic mode, xlsread:

  • Reads XLS, XLSX, XLSM, XLTX, and XLTM files only.

  • Does not support an xlRange input when reading XLS files. In this case, use '' in place of xlRange.

  • Does not support function handle inputs.

  • Imports all dates as Excel serial date numbers. Excel serial date numbers use a different reference date than MATLAB® date numbers.

functionHandle — Handle to a custom functionfunction handle

Handle to a custom function, starting with the symbol @. Supported only on Windows systems with Excel software. xlsread reads from the spreadsheet, executes your function on a copy of the data, and returns the final results. xlsread does not change the data stored in the spreadsheet.

When xlsread calls the custom function, it passes a range interface from the Excel application to provide access to the data. The custom function must include this interface both as an input and output argument. (See the Examples.)

Example: @myFunctionHandle

Output Arguments

expand all

num — Numeric datamatrix

Numeric data, returned as a matrix of double values. The array does not contain any information from header lines, or from outer rows or columns that contain nonnumeric data. Text data in inner spreadsheet rows and columns appear as NaN in the num output.

txt — Text datacell array

Text data, returned as a cell array. Numeric values in inner spreadsheet rows and columns appear as empty strings, '', in txt.

For XLS files in basic import mode, the txt output contains empty strings, '', in place of leading columns of numeric data that precede text data in the spreadsheet. In all other cases, txt does not contain these additional columns.

Undefined values (such as '#N/A') appear in the txt output as '#N/A', except for XLS files in basic mode.

raw — Unprocessed datacell array

Unprocessed data from the worksheet, returned as a cell array. Contains both numeric and text data.

On systems with Excel for Windows, undefined values (such as '#N/A') appear in the raw output as 'ActiveX VT_ERROR:'. For XLSX, XLSM, XLTX, and XLTM files on other systems, undefined values appear as '#N/A'.

custom — Second output of the function corresponding to functionHandledefined by the function

Second output of the function corresponding to functionHandle. The value and data type of custom are determined by the function.

Limitations

  • xlsread reads only 7-bit ASCII characters.

  • xlsread does not support non-contiguous ranges.

More About

expand all

Algorithms

  • If you do not specify xlRange, then xlsread ignores leading blank rows and columns in the worksheet that precede your data.

  • If you specify xlRange, then leading blank rows and columns in the worksheet that precede your data are returned in raw, but not in num or txt.

  • When the specified xlRange overlaps merged cells:

    • On Windows systems with Excel, xlsread expands the range to include all merged cells.

    • On systems without Excel for Windows, xlsread returns data for the specified range only, with empty or NaN values for merged cells.

  • xlsread imports formatted dates as strings (such as '10/31/96'), except in basic mode and on systems without Excel for Windows.

See Also

| | | | |

Was this topic helpful?