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