# 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 last tested by SheetJS users on 2023 November 27 in MATLAB R2023a.

MATLAB has limited support for processing spreadsheets through `readtable`

^{1}
and `writetable`

^{2}. At the time of writing, it lacked support for XLSB,
NUMBERS, and other common spreadsheet formats.

SheetJS libraries help fill the gap by normalizing spreadsheets to a form that MATLAB can understand.

## 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`

command^{5} 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:

### Write 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 was tested in macOS. The path names will differ in other platforms.

This demo uses the `pres.numbers`

test file.
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.

1) Create the standalone `xlsx-cli`

binary^{7}:

`cd /tmp`

npm i --save https://cdn.sheetjs.com/xlsx-0.20.1/xlsx-0.20.1.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

2) Move the generated `xlsx-cli`

to the MATLAB workspace folder. On macOS, this
folder is typically `~/Documents/MATLAB/`

:

`mkdir -p ~/Documents/MATLAB/`

mv xlsx-cli ~/Documents/MATLAB/

3) Download https://sheetjs.com/pres.numbers and save to Downloads folder:

`cd ~/Downloads/`

curl -LO https://sheetjs.com/pres.numbers

4) Save the following to `SheetJSMATLAB.m`

in the workspace folder:

`% Import data from NUMBERS file`

system("./xlsx-cli --xlsx ~/Downloads/pres.numbers");

tbl = readtable("~/Downloads/pres.numbers.xlsx");

% Process data (reverse sort)

sorted = sortrows(tbl,"Index", "descend");

% Export data to XLSB workbook

writetable(sorted,"~/Downloads/sorted.xlsx");

system("./xlsx-cli --xlsb ~/Downloads/sorted.xlsx");

5) In a MATLAB desktop session, run the `SheetJSMATLAB`

command:

`>> SheetJSMATLAB`

It will create the file `sorted.xlsx.xlsb`

in the `~/Downloads`

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:

`cd ~/Documents/MATLAB`

matlab -batch SheetJSMATLAB

- See
`readtable`

in the MATLAB documentation.↩ - See
`writetable`

in the MATLAB documentation.↩ - See
`readFile`

in "Reading Files"↩ - See
`writeFile`

in "Writing Files"↩ - See
`system`

in the MATLAB documentation.↩ - See "MATLAB Operators and Special Characters in the MATLAB documentation.↩
- See "Command-line Tools" for more details.↩