Main Content

xlsread

(Not recommended) Read spreadsheet file

collapse all in page

xlsread is not recommended. Use readtable, readmatrix, or readcell instead. For more information, see Compatibility Considerations.

Syntax

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

Description

num = xlsread(filename) reads the first worksheet in the Microsoft® Excel® spreadsheet workbook named filename and returns the numeric data in a matrix.

example

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

num = xlsread(filename,xlRange) reads from the specified range of the first worksheet in the workbook. Use Excel range syntax, such as 'A1:C3'.

example

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

example

num = xlsread(filename,sheet,xlRange,'basic') reads data from the spreadsheet in basic import mode. If your computer does not have Excel for Windows® or if you are using MATLAB® Online™, xlsread automatically operates in basic import mode, which supports XLS, XLSX, XLSM, XLTX, and XLTM files.

If you do not specify all the arguments, use empty character vectors, '' , as placeholders, for example, num = xlsread(filename,'','','basic').

[num,txt,raw] = xlsread(___) additionally returns the text fields in cell array txt, and both numeric and text data in cell array raw, using any of the input arguments in the previous syntaxes.

example

___ = 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 computers with Microsoft Excel software installed.

[num,txt,raw,custom] = xlsread(filename,sheet,xlRange,'',processFcn), where processFcn is a function handle, reads from the spreadsheet, calls processFcn on the data, and returns the final results as numeric data in array num. The xlsread function returns the text fields in cell array txt, both the numeric and text data in cell array raw, and the second output from processFcn in array custom. The xlsread function does not change the data stored in the spreadsheet. This syntax is supported only on Windows computers with Excel software.

example

Examples

collapse all

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 numeric data from the first worksheet.

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

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 the second column from the Excel file in the first example.

filename = 'myExample.xlsx';

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

For better performance, include the row numbers in the range, such as 'B1:B3'.

Request the numeric data, text data, and combined data from the Excel file in the first example.

[num,txt,raw] = xlsread('myExample.xlsx')
num =
     1     2     3
     4     5   NaN
     7     8     9

txt = 
    'First'    'Second'    'Third'
    ''         ''          ''     
    ''         ''          'x'    

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

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

function [Data] = setMinMax(Data)

minval = 0.2; 
maxval = 0.8;
 
for k = 1:Data.Count
  v = Data.Value{k};
  if v > maxval
    Data.Value{k} = maxval;
  elseif v < minval
    Data.Value{k} = minval;
  end
end

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

A = rand(5);
xlswrite('myExample.xlsx',A,'MyData')

The worksheet named MyData contains values ranging from 0 to 1.

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

trim = xlsread('myExample.xlsx','MyData','','',@setMinMax);

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 = 0.2; 
maxval = 0.8;
indices = [];
 
for k = 1:Data.Count
  v = Data.Value{k};
  if v > maxval
    Data.Value{k} = maxval;
    indices = [indices k];
  elseif v < minval
    Data.Value{k} = minval;
    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);

Input Arguments

collapse all

File name, specified as a character vector or 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' or "myFile.xlsx"

Data Types: char | string

Worksheet, specified as one of the following:

  • Character vector or string that contains the worksheet name. The 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. This option is not supported for XLS files in basic mode.

Data Types: char | string | single | double | int8 | int16 | int32 | int64 | uint8 | uint16 | uint32 | uint64

Rectangular range, specified as a character vector or a string.

Specify xlRange using 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).

When the specified xlRange overlaps merged cells:

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

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

Data Types: char | string

Flag to request reading in basic mode, specified as the character vector or a string, 'basic'.

basic mode is the default for computers 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.

Data Types: char | string

Handle to a custom function. This argument is supported only on Windows computers 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 Execute a Function on a Worksheet)

Example: @myFunction

Output Arguments

collapse all

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.

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

For XLS files in basic import mode, the txt output contains empty character vectors, '', 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.

Numeric and text data from the worksheet, returned as a cell array.

On computers 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 computers or in MATLAB Online, undefined values appear as '#N/A'.

Second output of the function corresponding to processFcn. 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.

  • If your computer does not have Excel for Windows or if you are using MATLAB Online, xlsread automatically operates in basic import mode.

  • On Linux® and Mac platforms, xlsread cannot open spreadsheet files written by the writetable function.

Algorithms

  • xlsread imports formatted text representing dates (such as '10/31/96'), except when importing in basic mode.

Version History

Introduced before R2006a

collapse all

R2019a: xlsread is not recommended

xlsread is not recommended. Use readtable, readmatrix, or readcell instead. There are no plans to remove xlsread.

Starting in R2019a, import spreadsheet data as a table, a matrix, or a cell array by using readtable, readmatrix, or readcell respectively. The readtable, readmatrix, and readcell functions have these advantages over the xlsread function:

  • Better cross-platform support and performance

  • Automatic detection of data format and types

  • Ability to use import options to control the data import process, including the handling of errors and missing data

This table shows typical usages of xlsread and how to update your code to use readtable, readmatrix, or readcell instead.

Not Recommended

Recommended

Read spreadsheet data as a matrix using xlsread:

M = xlsread(filename)

Read spreadsheet data as a table:

T = readtable(filename)
However, to continue reading your data as a matrix, use:
M = readmatrix(filename)

Read spreadsheet data as a cell array using xlsread:

[~,~,C] = xlsread(filename)

Import spreadsheet data as a table:

T = readtable(filename)
However, to continue importing your data as a cell array, use:
C = readcell(filename)

Read a specific sheet and range as a matrix using xlsread:

M = xlsread(filename,sheet,range)

Read a specific sheet and range as a table:

T = readtable(filename,'Sheet',sheet,'Range',range)
However, to continue reading your data as a matrix, use:
M = readmatrix(filename,'Sheet',sheet,'Range',range)

Read a specific sheet and range as a cell array using xlsread:

[~,~,C] = xlsread(filename,sheet,range)

Read a specific sheet and range as a table:

T = readtable(filename,'Sheet',sheet,'Range',range)
However, to continue reading your data as a cell array:
C = readcell(filename,'Sheet',sheet,'Range',range)

See Also

| | | | | |

Topics