xlsread

Read Microsoft Excel spreadsheet file

collapse all in page

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,'',processFcn)
    example

Description

example

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

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

example

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.

num = xlsread(filename,sheet,xlRange,'basic') reads data from the spreadsheet in basic import mode. If your computer does not have Excel for Windows®, 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').

example

[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.

___ = 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.

example

[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.

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. 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

Worksheet, specified as one of the following:

  • Character vector 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.

Rectangular range, specified as a character vector.

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

Flag to request reading in basic mode, specified as the character vector, '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.

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, 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.

More About

collapse all

Algorithms

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

See Also

| | | |

Introduced before R2006a

Was this topic helpful?
MATLAB Documentation
Other Documentation
Support