Material UI

Material UI is a collection of ReactJS Components that follows the Google Material Design system

SheetJS is a JavaScript library for reading and writing data from spreadsheets.

This demo uses Material UI and SheetJS to pull data from a spreadsheet and display the data. We'll explore how to import data from spreadsheets and export data to spreadsheets. The following Material UI components will be tested:

"Table" is based on the core HTML TABLE element.
"Data Grid" is a data grid for larger datasets.

The ReactJS demo covers basic ReactJS concepts. It should be perused before reading this demo.

Integration Details

The "Frameworks" section covers installation in projects using Material UI.

After installing the SheetJS module in a ReactJS project, import statements can load relevant parts of the library.

import { read, utils, writeFileXLSX } from 'xlsx';

Material UI Table

The Table component abstracts the <table> element in HTML.

Importing Data

Starting from a SheetJS worksheet object¹, the sheet_to_json method² generates an array of row objects.

In the ReactJS "Array of Objects" demo, the array of objects is rendered by manually mapping over data. For example, starting from the following spreadsheet and data:

Spreadsheet	State
	`[ { Name: "Bill Clinton", Index: 42 }, { Name: "GeorgeW Bush", Index: 43 }, { Name: "Barack Obama", Index: 44 }, { Name: "Donald Trump", Index: 45 }, { Name: "Joseph Biden", Index: 46 } ]`

The HTML table elements map to MUI components:

HTML	MUI
`TABLE`	`Table`
`THEAD`	`TableHead`
`TBODY`	`TableBody`
`TR`	`TableRow`
`TD`	`TableCell`

The library requires a TableContainer container component.

The following example JSX shows a table using HTML and using MUI components:

ReactJS
Material UI

Example JSX for displaying arrays of objects
<table>
  {/* The `thead` section includes the table header row */}
  <thead><tr><th>Name</th><th>Index</th></tr></thead>
  {/* The `tbody` section includes the data rows */}
  <tbody>
    {/* generate row (TR) for each president */}
    {pres.map(row => (
      <tr>
        {/* Generate cell (TD) for name / index */}
        <td>{row.Name}</td>
        <td>{row.Index}</td>
      </tr>
    ))}
  </tbody>
</table>

Example JSX for displaying arrays of objects
<TableContainer><Table>
  {/* The `TableHead` section includes the table header row */}
  <TableHead><TableRow><TableCell>Name</TableCell><TableCell>Index</TableCell></TableRow></TableHead>
  {/* The `TableBody` section includes the data rows */}
  <TableBody>
    {/* generate row (TableRow) for each president */}
    {pres.map((row, idx) => (
      <TableRow key={idx}>
        {/* Generate cell (TableCell) for name / index */}
        <TableCell>{row.Name}</TableCell>
        <TableCell>{row.Index}</TableCell>
      </TableRow>
    ))}
  </TableBody>
</Table></TableContainer>

Exporting Data

The SheetJS table_to_book method³ can parse data from a DOM element. The MUI Table element is really a HTML TABLE element under the hood. A ref attached to the Table element can be processed by table_to_book.

The following snippet uses the writeFileXLSX method⁴ to generate and download a XLSX workbook:

Skeleton Component for exporting a Material UI Table
import { utils, writeFileXLSX } from "xlsx";
import { useRef } from "react";

export default function MUITableSheetJSExport() {
  /* This ref will be attached to the <Table> component */
  const tbl = useRef<HTMLTableElement>(null);

  const xport = () => {
    /* the .current field will be a TABLE element */
    const table_elt = tbl.current;
    /* generate SheetJS workbook */
    const wb = utils.table_to_book(table_elt);
    /* export to XLSX */
    writeFileXLSX(wb, "SheetJSMaterialUI.xlsx");
  };

  return ( <>
    <button onClick={xport}>Export</button>
    <TableContainer>
      <Table ref={tbl}>{/* ... */}</Table>
    </TableContainer>
  <>);
}

MUI Table Demo

Tested Deployments

This demo was tested in the following deployments:

Material UI	Emotion	Date
`5.15.20`	`11.11.4`	2024-06-12

Create a new app using vite:

npm create vite@latest sheetjs-mui -- --template react-ts
cd sheetjs-mui

Install dependencies:

npm i -S https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @mui/[email protected] @emotion/[email protected] @emotion/[email protected]

Download App.tsx and replace src/App.tsx.

curl -L -o src/App.tsx https://docs.sheetjs.com/mui/table/App.tsx

Start the development server:

npm run dev

The script should open the live demo in a web browser. Click the "Export" button to save the file. Open the generated file in a spreadsheet editor.

Material UI Data Grid

A complete example is included below.

Rows and Columns State

MUI Data Grid state consists of an Array of column metadata and an Array of row objects. Typically both are defined in state:

import { DataGrid, GridColDef } from "@mui/x-data-grid";

export default function App() {
  const [rows, setRows] = useState([]);
  const [columns, setColumns] = useState([]);

  return ( <DataGrid columns={columns} rows={rows} /> );
}

The most generic data representation is an array of arrays. To sate the grid, columns must be objects whose field property is the index converted to string:

import { WorkSheet, utils } from 'xlsx';
import { GridColDef } from "@mui/x-data-grid";

type Row = any[];
type RowCol = { rows: Row[]; columns: GridColDef[]; };

function ws_to_muidg(ws: WorkSheet): RowCol {
  /* create an array of arrays */
  const rows = utils.sheet_to_json(ws, { header: 1 });

  /* create column array */
  const range = utils.decode_range(ws["!ref"]||"A1");
  const columns = Array.from({ length: range.e.c + 1 }, (_, i) => ({
    field: String(i), // MUIDG will access row["0"], row["1"], etc
    headerName: utils.encode_col(i), // the column labels will be A, B, etc
    editable: true // enable cell editing
  }));

  return { rows, columns }; // these can be fed to setRows / setColumns
}

In the other direction, a worksheet can be generated with aoa_to_sheet:

x-data-grid does not properly preserve row array objects, so the row arrays must be re-created. The snippet defines a arrayify function.

import { WorkSheet, utils } from 'xlsx';

type Row = any[];

function arrayify(rows: any[]): Row[] {
  return rows.map(row => {
    var length = Object.keys(row).length;
    for(; length > 0; --length) if(row[length-1] != null) break;
    return Array.from({length, ...row});
  });
}

function muidg_to_ws(rows: Row[]): WorkSheet {
  return utils.aoa_to_sheet(arrayify(rows));
}

Editing Cells

The processRowUpdate callback prop receives the new row data. An event handler can mutate state:

import { GridRowModel } from "@mui/x-data-grid";

export default function App() {
  // ...

  const processRowUpdate = useCallback((rowNew: GridRowModel, rowOld: GridRowModel) => {
    /* scan each column and manually set state entries */
    for(var j = 0; j < columns.length; ++j) if(rowNew[j] != null) {
      rows[rowNew.id][j] = isNaN(+rowNew[j]) ? rowNew[j] : +rowNew[j];
    }
    /* force a state update */
    setRows(rows);
    /* commit the new row */
    return rowNew;
  }, [columns, rows]);

  return ( <DataGrid columns={columns} rows={rows} processRowUpdate={processRowUpdate} /> );
}

MUIDG Demo

Tested Deployments

This demo was tested in the following deployments:

Data Grid	Emotion	Date
`7.6.2`	`11.11.4`	2024-06-12

Create a new app using vite:

npm create vite@latest sheetjs-muidg -- --template react-ts
cd sheetjs-muidg

Install dependencies:

npm i -S https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz @mui/[email protected] @emotion/[email protected] @emotion/[email protected]

Download App.tsx and replace src/App.tsx.

curl -L -o src/App.tsx https://docs.sheetjs.com/mui/dg/App.tsx

Start the development server:

npm run dev

When the page loads, it will process https://docs.sheetjs.com/pres.numbers

See "Sheet Objects" ↩
See sheet_to_json in "Utilities" ↩
See table_to_book in "HTML" Utilities ↩
See writeFileXLSX in "Writing Files" ↩

Integration Details​

Material UI Table​

Importing Data​

Exporting Data​

MUI Table Demo​

Material UI Data Grid​

MUIDG Demo​

Footnotes​