9.9: Spreadsheet File I/O

Modern MATLAB spreadsheet functions

For new MATLAB code, it is best to use the newer spreadsheet functions listed below. These functions are clearer than older Excel-specific functions because the function name tells you what kind of MATLAB variable will be created or written.

readmatrix and writematrix are useful when the spreadsheet contains mostly numeric data.

readcell and writecell are useful when the spreadsheet contains mixed data, such as text, numbers, and empty cells.

readtable and writetable are useful when the spreadsheet behaves like a data table with named columns.

How to Determine Which File IO functions to Use?

If the file contains only numbers, use readmatrix or writematrix.

If the file contains a mixture of text and numbers and you want to preserve each cell as a separate item, use readcell or writecell.

If the first row contains column headings and each column represents a variable, use readtable or writetable.

Reading and Writing Mixed Spreadsheet Data with readcell and writecell

The function readcell reads the contents of a spreadsheet into a cell array. This is a good choice when the spreadsheet contains mixed data types. For example, one column may contain names, another column may contain ages, and another column may contain weights. A regular numeric matrix cannot store all of those values together, but a cell array can.

Suppose we want to create a small spreadsheet containing patient information. The first row contains headers, and the rows below it contain data.

patientData = {'LastName', 'FirstName', 'Age', 'Height_cm', 'Weight_kg';
               'Grey',     'Chris',     24,   172,         75;
               'Hall',     'David',     31,   168,         68;
               'Giovanni', 'Luke',      28,   181,         87;
               'Cat',      'Donna',     35,   165,         72};

writecell(patientData, 'patients_cell.xlsx');

The function writecell writes the cell array to a spreadsheet file. This is useful because cell arrays can store text and numbers together. After the file has been created, we can read it back into MATLAB using readcell.

rawData = readcell('patients_cell.xlsx')

The variable rawData is a cell array. The contents of the spreadsheet are preserved cell by cell. This means the header row is stored as text, the names are stored as text, and the numerical values are stored as numbers.

To access one cell of the imported data, remember the cell array indexing rules from the previous chapter. Parentheses return a cell. Curly braces return the contents of a cell.

oneCell = rawData(2,1);     % returns a 1-by-1 cell array
lastName = rawData{2,1};    % returns the contents: 'Grey'

This is a good example of why cell arrays are important. When spreadsheet data contains a mixture of text and numbers, readcell gives us a flexible way to bring that information into MATLAB.

Adding a Summary Row with writecell

Suppose we read the patient data and want to calculate the average weight. We can add the result to a small summary cell array and write it to a second sheet.

rawData = readcell('patients_cell.xlsx');

weights = cell2mat(rawData(2:end,5));
avgWeight = mean(weights);

summary = {'Quantity', 'Value';
           'Average Weight (kg)', avgWeight};

writecell(summary, 'patients_cell.xlsx', 'Sheet', 'Summary');

The expression rawData(2:end,5) selects the weight values, starting from row 2 because row 1 contains the headers. The function cell2mat converts those numeric cells into a numeric array so that mean can calculate the average.

Reading and Writing Labeled Data with readtable and writetable

A table is often the best choice when a spreadsheet has column headings and each column represents a different variable. Tables are especially useful for data analysis because you can refer to columns by name.

For example, suppose a spreadsheet named patients.xlsx contains these column headings: LastName, FirstName, Age, Height_cm, and Weight_kg. We can read the spreadsheet into a MATLAB table using readtable.

T = readtable('patients.xlsx')

If the spreadsheet contains column headings in the first row, MATLAB uses those headings as the table variable names. This allows us to access each column using dot notation.

ages = T.Age;
weights = T.Weight_kg;
heights = T.Height_cm;

This style is very readable. The expression T.Weight_kg clearly means the Weight_kg column of the table T. This is easier to understand than trying to remember that weight is stored in column 5.

Example: Calculating BMI and Writing an Updated Table

Suppose we want to calculate body mass index, or BMI, for each patient. BMI is calculated using mass in kilograms divided by height in meters squared.

BMI = mass_kg / height_m^2

The following script reads the spreadsheet as a table, calculates BMI, adds BMI as a new table variable, and writes the updated table to a new spreadsheet file.

T = readtable('patients.xlsx');

height_m = T.Height_cm / 100;
T.BMI = T.Weight_kg ./ (height_m .^ 2);

writetable(T, 'patients_with_BMI.xlsx');

The table T now contains all of the original columns plus a new column named BMI. This is one of the main advantages of using tables: we can keep related variables together and add new calculated variables as the analysis develops.

We can also write summary information to a separate spreadsheet using writecell.

avgAge = mean(T.Age);
avgWeight = mean(T.Weight_kg);

summary = {'Quantity', 'Value';
           'Average Age', avgAge;
           'Average Weight (kg)', avgWeight};

writecell(summary, 'patient_summary.xlsx');

Writing to a Specific Sheet or Range

Spreadsheet files can contain multiple sheets. MATLAB allows us to specify which sheet to read from or write to. This is useful when one sheet stores raw data and another sheet stores processed results.

T = readtable('patients.xlsx', 'Sheet', 'PatientData');
writetable(T, 'patients_with_BMI.xlsx', 'Sheet', 'ProcessedData');

When writing cell arrays, we can also specify a sheet and a starting range.

summary = {'Quantity', 'Value'; 'Average Age', avgAge};
writecell(summary, 'patients_with_BMI.xlsx', 'Sheet', 'Summary', 'Range', 'A1');

The Range option tells MATLAB where to place the first cell of the output. In this example, the summary starts in cell A1 of the Summary sheet.