Getting Data Into and Out of Excel

Introduction

Recently, I needed to assemble a report which is to be generated quarterly and distributed to those unfortunate enough not to have MATLAB. Currently, such reports are distributed as Excel workbooks. Nearly everyone who produces such reports where I work does so by generating tables of numbers and cutting-and-pasting them into Excel (yuck!). As I have a number of these reports to produce, I was motivated to construct a more automatic solution.

Happily, MATLAB can easily get data from or send data to Excel documents. The mechanics of this are not difficult, but I thought that readers might not be aware that this facility exists and just how easy this is to accomplish.

There is just one catch: For users without Excel to act as a COM server (such as UNIX users), 'basic' mode is required, and functionality will be limited: See help xlsread for details.


Getting Data From Excel in MATLAB

MATLAB's function for extracting data from Excel documents is xlsread. Using it is as simple as this:

[NumericData TextData] = xlsread(FileName,SheetName,CellRange)

...where NumericData and TextData contain the numeric and text data read from the workbook, respectively; and FileName, SheetName and CellRange are the names of the Excel document, sheet name and cell range from which to read.

Often, I find myself needing to read data from growing ranges of cells within Excel spreadsheets. Think of daily rainfall data stored in a single column within a spreadsheet, which periodically has data appended to it. To load such data, simply set the range of cells to be much larger than the existing range: xlsread will ignore the extra empty spreadsheet cells.


Getting Data Into Excel in MATLAB

Writing data to Excel documents is also quite simple. Just use xlswrite:

xlswrite(FileName,DataArray,SheetName,CellRange)

...where FileName, SheetName and CellRange are the names of the Excel document, sheet name and cell range to which to write, and DataArray contains the data to be written.


Final note

Refer to the help facility for diagnostic and other capabilities of both of these functions. See also wk1read and wk1write to handle the old Lotus 1-2-3 .WK1 format.