Skip to main content

Material UI

This demo covers the traditional Material UI Table as well as the MUI Data Grid.

Integration Details

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

Material UI Table

The Table component abstracts the <table> element in HTML. table_to_book can process a ref attached to the Table element:

import TableContainer from '@mui/material/TableContainer';
import Table from '@mui/material/Table';
// ...
import { utils, writeFileXLSX } from "xlsx";
import { useRef } from "react";

// ...
export default function BasicTable() {
const tbl = useRef<HTMLTableElement>(null);
return ( <>
<button onClick={() => {
const wb = utils.table_to_book(tbl.current);
writeFileXLSX(wb, "SheetJSMaterialUI.xlsx");
}}>Export</button>
<TableContainer {...}>
<Table {...} ref={tbl}>
{/* ... material ui table machinations ... */}
</Table>
</TableContainer>
<>);
}

MUI Table Demo

Tested Deployments

This demo was last run on 2023 December 04 against Material UI 5.14.19 paired with Emotion 11.11.1

1) Create a new app using vite:

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

2) Install dependencies:

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

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

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

4) 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 last run on 2023 December 04 against MUI data grid 6.18.3 paired with Emotion 11.11.1

1) Create a new app using vite:

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

2) Install dependencies:

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

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

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

4) Start the development server:

npm run dev

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