Working with Spreadsheets :: Data Import and Export (Programming)

Programming

Working with Spreadsheets

You can use MATLAB to import and export data to the following types of spreadsheets:

Microsoft Excel Spreadsheets

This section covers

See the xlsfinfo, xlswrite, and xlsread reference pages for more detailed information and examples.

Getting Information About the File

Use the xlsfinfo function to determine if a file contains a readable Microsoft Excel spreadsheet.

Inputs to xlsfinfo are

Name of the spreadsheet file

Outputs from xlsfinfo are

String 'Microsoft Excel Spreadsheet' if the file contains an Excel worksheet readable with the xlsread function. Otherwise, it contains an empty string ('').
Cell array of strings containing the names of each worksheet in the file.

Example -- Querying an XLS File. This example returns information about spreadsheet file tempdata.xls:

[type, sheets] = xlsfinfo('tempdata.xls')

type =
Microsoft Excel Spreadsheet
sheets = 
    'Locations'    'Rainfall'    'Temperatures'

Exporting to the File

Use the xlswrite function to export a matrix to an Excel spreadsheet file. With xlswrite, you can export data from the workspace to any worksheet in the file, and to any location within that worksheet.

Inputs to xlswrite are

Name of the spreadsheet file
Matrix to be exported
Name of the worksheet to receive the data
Range of cells on the worksheet in which to write the data

Outputs from xlswrite are

Pass or fail status
Any warning or error message generated along with its message identifier

Example -- Writing To an XLS File. This example writes a mix of text and numeric data to the file tempdata.xls. Call xlswrite, specifying a worksheet labeled Temperatures, and the region within the worksheet to write the data to. The 4-by-2 matrix is written to the rectangular region that starts at cell E1 in its upper-left corner:

d = {'Time', 'Temp'; 12 98; 13 99; 14 97}
d = 
    'Time'    'Temp'
    [  12]    [  98]
    [  13]    [  99]
    [  14]    [  97]

xlswrite('tempdata.xls', d, 'Temperatures', 'E1');

Adding a New Worksheet. If the worksheet being written to does not already exist in the file, MATLAB displays the following warning:

```
Warning: Added specified worksheet.
```

You can disable these warnings with the command

```
warning off MATLAB:xlswrite:AddSheet
```

Importing from the File

Use xlsread to import a matrix from an Excel spreadsheet file into the MATLAB workspace. You can import data from any worksheet in the file, and from any location within that worksheet. You can also optionally have xlsread open an Excel window showing the file and then interactively select the worksheet and range of data to be read by the function.

Inputs to xlsread are

Name of the spreadsheet file
Matrix to be imported
Name of the worksheet from which to read the data
Range of cells on the worksheet from which to read the data
Keyword that opens an Excel window, enabling you to interactively select the worksheet and range of data to read
Keyword that imports using basic import mode

Three separate outputs from xlsread are

Numeric data
String data
Any unprocessed cell content

Example -- Reading from an XLS File. Continuing with the previous example, to import only the numeric data, use xlsread with a single return argument. 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 numeric data and text data, specify two return values for xlsread:

[ndata, headertext] = xlsread('tempdata.xls', 'Temperatures')

headertext = 
    'Time'    'Temp'

ndata =
    12    98
    13    99
    14    97

Exporting Audio/Video Data Lotus 123 Spreadsheets