Modern Spreadsheets in MATLAB
MATLAB is a numeric computing
platform. It has a native table
type with limited support for spreadsheets.
SheetJS is a JavaScript library for reading and writing data from spreadsheets.
This demo uses SheetJS to pull data from a spreadsheet for further analysis within MATLAB. We'll explore how to run an external tool to convert complex spreadsheets into simple XLSX files for MATLAB.
This demo was tested by SheetJS users in the following deployments:
Architecture | Version | Date |
---|---|---|
darwin-x64 | R2024a | 2024-06-09 |
win11-x64 | R2024b | 2024-12-21 |
Integration Details
MATLAB does not currently provide a way to parse a CSV string or a character
array representing file data. readtable
, writetable
, csvread
, and
csvwrite
work with the file system directly. strread
and textscan
are
designed specifically for reading numbers.
The current recommendation involves a dedicated command-line tool that leverages SheetJS libraries to to perform spreadsheet processing.
The SheetJS NodeJS module can be loaded in NodeJS scripts and bundled in standalone command-line tools.
Command-Line Tools
The "Command-Line Tools" demo creates xlsx-cli
, a
command-line tool that reads a spreadsheet file and generates output. The
examples in the "NodeJS" section are able to generate XLSX spreadsheets using
the --xlsx
command line flag:
$ xlsx-cli --xlsx ./pres.numbers ## generates pres.numbers.xlsx
The command-line tool supports a number of formats including XLSB (--xlsb
).
The tools pair the SheetJS readFile
3 and writeFile
4 methods to read
data from arbitrary spreadsheet files and convert to XLSX:
const XLSX = require("xlsx"); // load the SheetJS library
const wb = XLSX.readFile("input.xlsb"); // read input.xlsb
XLSX.writeFile(wb, "output.xlsx"); // export to output.xlsx
MATLAB commands
The MATLAB system
command5 can run command-line tools in M-files. For
example, if the xlsx-cli
tool is placed in the workspace folder and the
test file pres.numbers
is in the Downloads folder, the following command
generates the XLSX file pres.numbers.xlsx
:
% generate ~/Downloads/pres.numbers.xlsx from ~/Downloads/pres.numbers
system("./xlsx-cli --xlsx ~/Downloads/pres.numbers");
In an interactive session, the exclamation point operator !
6 can be used:
% generate ~/Downloads/pres.numbers.xlsx from ~/Downloads/pres.numbers
!./xlsx-cli --xlsx ~/Downloads/pres.numbers
Reading Files
Starting from an arbitrary spreadsheet, xlsx-cli
can generate a XLSX workbook.
Once the workbook is written, the XLSX file can be parsed with readtable
:
% `filename` points to the file to be parsed
filename = "~/Downloads/pres.numbers";
% generate filename+".xlsx"
system("./xlsx-cli --xlsx " + filename)
% read using `readtable`
tbl = readtable(filename + ".xlsx");
The following diagram depicts the workbook waltz:
Writing Files
Starting from an MATLAB table, writetable
can generate a XLSX workbook. Once
the workbook is written, xlsx-cli
can translate to NUMBERS or other formats:
% tbl is the table
tbl = table({"Sheet";"JS"}, [72;62], 'VariableNames', ["Name", "Index"])
% `filename` points to the file to be written
filename = "~/Downloads/sorted.xlsx";
% write using `writetable`
writetable(tbl, filename);
% generate filename+".xlsb"
system("./xlsx-cli --xlsb " + filename);
The following diagram depicts the workbook waltz:
Complete Demo
This demo processes pres.numbers
.
There are 3 parts to the demo:
A) "Import": SheetJS tooling will read the test file and generate a clean XLSX
file. MATLAB will read the file using readtable
.
B) "Process": Using sortrows
, MATLAB will reverse the table order.
C) "Export": The modified table will be exported to XLSX using writetable
.
SheetJS tooling will convert the file to XLSB.
- Launch MATLAB and run the following command to print the workspace folder:
pwd
This folder is typically MATLAB
within the Documents
folder for the account.
-
Open a new macOS Terminal or Windows PowerShell window.
-
Navigate to the workspace folder displayed in Step 0.
- Windows
- macOS
In Windows, the folder is typically C:\Users\username\Documents\MATLAB
. Since
PowerShell sessions start from the user folder, the command is:
cd Documents\MATLAB
In macOS, the folder is typically ~/Documents/MATLAB
so the command is:
cd ~/Documents/MATLAB
- Create the standalone
xlsx-cli
binary7. The following commands should be run in the macOS Terminal or Windows PowerShell:
npm i --save https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz exit-on-epipe commander@2
curl -LO https://docs.sheetjs.com/cli/xlsx-cli.js
npx nexe -t 14.15.3 xlsx-cli.js
- Download https://docs.sheetjs.com/pres.numbers to the workspace folder:
curl -LO https://docs.sheetjs.com/pres.numbers
- Save the following to
SheetJSMATLAB.m
in the workspace folder:
- Windows
- macOS
% Import data from NUMBERS file
system(".\xlsx-cli.exe --xlsx pres.numbers");
tbl = readtable("pres.numbers.xlsx");
% Process data (reverse sort)
sorted = sortrows(tbl,"Index", "descend");
% Export data to XLSB workbook
writetable(sorted,"sorted.xlsx");
system(".\xlsx-cli.exe --xlsb sorted.xlsx");
% Import data from NUMBERS file
system("./xlsx-cli --xlsx pres.numbers");
tbl = readtable("pres.numbers.xlsx");
% Process data (reverse sort)
sorted = sortrows(tbl,"Index", "descend");
% Export data to XLSB workbook
writetable(sorted,"sorted.xlsx");
system("./xlsx-cli --xlsb sorted.xlsx");
- In a MATLAB desktop session, run the
SheetJSMATLAB
command:
>> SheetJSMATLAB
It will create the file sorted.xlsx.xlsb
in the MATLAB workspace folder. Open
the file and confirm that the table is sorted by Index in descending order:
Name Index
Joseph Biden 46
Donald Trump 45
Barack Obama 44
GeorgeW Bush 43
Bill Clinton 42
If the matlab
command is available on the system PATH
, the "headless"
version of the command is:
matlab -batch SheetJSMATLAB
Footnotes
-
See
writetable
in the MATLAB documentation. ↩ -
See "MATLAB Operators and Special Characters in the MATLAB documentation. ↩
-
See "Command-line Tools" for more details. ↩