Your latest MATLAB masterpiece produced a goldmine of
incisive data and
now it’s time to present the results to management.
MATLAB Live Scripts
are an excellent reporting mechanism but these may not suit
an organization’s workflow or documentation standards.
Sometimes it’s necessary to fall back to showing results
in a basic tool like Microsoft Excel.
MATLAB + Excel = not easy
MATLAB’s ability to create Excel files is modest at best.
The writecell(), writematrix(), and
writetable() functions can populate
an Excel file with data, but only using the default text style.
If you want to change typefaces, fonts, colors, merge cells
and so on, you’re in for some hardship.
Custom formatting is only supported on Windows using the COM layer
Write Data to Excel Spreadsheets, bottom of the page)
and examples demonstrating this process are hard to find.
If you’re using MATLAB on macOS or Linux, you’re completely
out of luck writing rich Excel files.
MATLAB + Python + Excel = easy!
include Python with your MATLAB setup on the otherhand, creating richly-formatted Excel files on any platform
is easy. Excel files can be created in MATLAB by calling functions
from the Python
The first step is to check if you have openpyxl and
to install it if you don’t.
Check your Python installation to see if it has openpyxl
by starting the Python command line REPL and importing
the module. If it imports successfully you can also check
the version number:
If you get a ModuleNotFoundError, you’ll need to install it.
That can be done on an Anaconda Python installation with
> conda install openpyxl
> python -m pip install openpyxl
using other Python distributions.
Start a new .xlsx file
The small program below creates an empty Excel file,
my_analysis.xlsx, containing two worksheets, or tabs,
called “Amazing Results” and “Summary”.
For special emphasis, the Summary tab’s color is changed to green.
Unfortunately colors in openpyxl must be provided as RGB or aRGB
(‘a’ = alpha, to control transparency) hex values rather
than words like “green”.
The create_sheet() method’s arguments are the worksheet’s title
and an optional position in the tab sequence, where 0 is the leftmost tab.
Note the presence of the ‘Sheet’ tab. This was created by default and
was pushed to the right when our sheets were added.
The ‘Sheet’ tab can be deleted like this:
# Pythondel book['Sheet']
# - or -
existing_sheet = book['Sheet']
# - or -
existing_sheet = book.get_sheet_by_name('Sheet')
MATLAB won’t let us use [ ] to index the Python book object so only
the third option works there:
As noted above, MATLAB won’t let us index Python objects with brackets.
This prevents us from using openpyxl shortcuts such as
assigning a cell’s value using something like sheet['C8'] = 3.14159.
In Section 7.8 of
Python for MATLAB Development,
I suggested using a bridge
module to overcome this interface limitation.
After the book came out,
at the MathWorks informed me that a bridge
module is unnecessary since openpyxl offers method- and
attribute-based ways to achieve the same thing. Setting cell C8’s
value can also be done with
C8 = sheet.cell(3,8)
All examples below use method and attribute access methods
to accomodate MATLAB.
The code examples therefore
don’t demonstrate the most idiomatic use of openpyxl,
but they have the benefits that Python and MATLAB code samples
look almost identical, and that the MATLAB implementation won’t need a bridge module.
Fill a cell with a value
To populate a cell with a number or string, get a handle
to the cell using the worksheet’s .cell() method, pass
it the 1-based row and column indices, then set the
.value attribute with the number or string of interest.
These lines put the string “Total” in cell B9 which
corresponds to row 2, column 9:
The int64() type casts are necessary because numeric
literals in MATLAB have type double while the Python
function expects integers.
Typeface size, font, color
A cell’s font can be modified by creating a “font style”
then applying this style to the cell. The code example
below creates a style, ft_title, having a
14 point blue Arial typeface with bold italics font.
Text in a cell can be aligned vertically and horizontally,
rotated, indented, and shrunk to fit a given container using
Text in merged cells looks good when it is centered both
horizontally and vertically. That can be done with
% MATLAB 2021b and newer
B9.alignment = Alignment(pyargs("horizontal","center", ...
% MATLAB 2022a and newer
B9.alignment = Alignment(horizontal="center", vertical="center");
Currency, percent, and other numeric formats
A cell can be formatted to show numeric values as currency or a
percentage through the cell’s .number_format attribute.
The numerical format notation is rather unusual in that it
allows positive values to be formatted diffently than
negative values. If such a distinction is desired,
a semicolon and the negative value format are appended
to the postive value format.
Financial ledgers sometimes show negative values in red and/or in
parentheses so these variations are shown in the example below.
Oddly, one may use words like ‘Red’ or ‘Blue’ to define the color
within a .number_format but all other color definition
options only allow hex values.
The dollar symbol can be replaced with other currency symbols
simply by replacing "$" with the desired character — "€" for example.
Cell background color
Cell backgrounds can be set to a variety of colors and
patterns with the
class. Following the same technique as with fonts and number
styles, one first defines a pattern
style then applies this to a cell’s .fill attribute.
The style bg_green defines a solid lime green background
A block of adjacent cells can be coalessed into
a single cell using a worksheet’s .merge_cells() method.
The method takes a cell block range as an argument.
The example below merges the nine cells B2, B3, B4,
C2, C3, C4, D2, D3, and D4 into one.
After the merge, the cell’s handle is accessed using
the row and column coordinates of the top left cell:
% MATLAB 2022a and newer
B2 = sheet_data.cell(int64(2),int64(2));
B2.value = "A Title";
B2.font = ft_title;
B2.alignment = Alignment(horizontal="center", vertical="center");
Equations are easily added to spreasheets as they are merely cells
containing strings that begin with an equals sign and an operator.
For example, to add an equation that sums values in the cell
range E2:E50, one merely populates a cell with '=SUM(E2:E50)'.
Putting it all together
This spreadsheet includes all customizations described above: