Skip to main content

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.

Tested Deployments

This demo was tested by SheetJS users in the following deployments:

ArchitectureVersionDate
darwin-x64R2024a2024-06-09
win10-x64R2024a2024-06-09

MATLAB has limited support for processing spreadsheets through readtable1 and writetable2. 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 readFile3 and writeFile4 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:

flowchart LR
  subgraph MATLAB `system` invocation
    file[(workbook\nunknown type)]
    xlsx(XLSX\nNormalized Data)
  end
  data[(table)]
  file --> |`xlsx-cli`\nSheetJS| xlsx
  xlsx --> |`readtable`\nMATLAB| data

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:

flowchart LR
  subgraph MATLAB `system` invocation
    file[(XLSB\nworkbook)]
    xlsx(XLSX\nNormalized Data)
  end
  data[(table)]
  data --> |`writetable`\nMATLAB| xlsx
  xlsx --> |`xlsx-cli`\nSheetJS| file

Complete Demo​

This demo was tested in macOS. The path names will differ in other platforms.

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.

flowchart LR
  ifile[(NUMBERS)]
  ixlsx(XLSX)
  ofile[(XLSB)]
  oxlsx(XLSX)
  data[(table)]
  ifile --> |`xlsx-cli`\nSheetJS| ixlsx
  ixlsx --> |`readtable`\nMATLAB| data
  data -.-> |Data Processing| data
  data --> |`writetable`\nMATLAB| oxlsx
  oxlsx --> |`xlsx-cli`\nSheetJS| ofile

  1. Create the standalone xlsx-cli binary7:
cd /tmp
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
  1. 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/
  1. Download https://docs.sheetjs.com/pres.numbers and save to Downloads folder:
cd ~/Downloads/
curl -LO https://docs.sheetjs.com/pres.numbers
  1. Save the following to SheetJSMATLAB.m in the workspace folder:
SheetJSMATLAB.m
% 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");
  1. 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

Footnotes​

  1. See readtable in the MATLAB documentation. ↩

  2. See writetable in the MATLAB documentation. ↩

  3. See readFile in "Reading Files" ↩

  4. See writeFile in "Writing Files" ↩

  5. See system in the MATLAB documentation. ↩

  6. See "MATLAB Operators and Special Characters in the MATLAB documentation. ↩

  7. See "Command-line Tools" for more details. ↩