Community version of mui doesn't support excel export. This example shows adding excel export to mui datagrid using SheetJS.
TL;DR: https://stackblitz.com/edit/react-ts-3kav3v?file=App.tsx
Steps
- Replace the default toolbar in datagrid with a custom implementation
export default function App() {
return (
<div style={{ height: '100vh', width: '100%' }}>
<DataGrid
columns={data.columns}
rows={data.rows}
components={{
Toolbar: CustomToolbar,
}}
/>
</div>
);
}
For now we remove everything from the default toolbar and only include our custom button that exports to excel
function CustomToolbar(props) {
return (
<GridToolbarContainer {...props}>
<ExportButton />
</GridToolbarContainer>
);
}
-
ExportButton
simply wraps aMenuItem
in a container
export function ExportButton(props) {
return (
<GridToolbarExportContainer {...props}>
<ExportMenuItem />
</GridToolbarExportContainer>
);
}
-
ExportMenuItem
when clicked downloads the data present in the datagrid. It uses theuseGridApiContext()
to access the data.
export function ExportMenuItem(props) {
const apiRef = useGridApiContext();
const { hideMenu } = props;
return (
<MenuItem
onClick={() => {
handleExport(apiRef);
// Hide the export menu after the export
hideMenu?.();
}}
>
Download Excel
</MenuItem>
);
}
-
handleExport
does some basic data mappings, creates a worksheet, appends the worksheet to a workbook, adds column names and finally makes the file available for download.
function handleExport(apiRef) {
const data = getExcelData(apiRef);
const rows = data.map((row) => {
const mRow = {};
for (const key of config.keys) {
mRow[key] = row[key];
}
return mRow;
});
const worksheet = XLSX.utils.json_to_sheet(rows);
XLSX.utils.sheet_add_aoa(worksheet, [[...config.columnNames]], {
origin: 'A1',
});
const workbook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(workbook, worksheet, config.sheetName);
XLSX.writeFile(workbook, config.fileName, { compression: true });
}
-
getExcelData
uses the reference to datagrid and combines the rows and columns to get data from the datagrid
function getExcelData(apiRef) {
// Select rows and columns
const filteredSortedRowIds = gridFilteredSortedRowIdsSelector(apiRef);
const visibleColumnsField = gridVisibleColumnFieldsSelector(apiRef);
// Format the data. Here we only keep the value
const data = filteredSortedRowIds.map((id) => {
const row = {};
visibleColumnsField.forEach((field) => {
row[field] = apiRef.current.getCellParams(id, field).value;
});
return row;
});
return data;
}
Top comments (7)
Hi!
I have published a node module for easy usage of mui-data-grid.
It's name is "mui-datagrid-full-edit" and includes exporting as Excel using your code.
If someone want to use mui-data-grid simply, he/she can use it.
Here is its links.
View on github.com
View on npmjs.com
View a Sample on codesandbox.io
Thanks for your sharing !
How to remove action column from that mui-datagrid-full-edit?
I have just updated the module.
Now you can hide the action column using "noActionColumn" prop.
The latest version is 1.2.7
github.com/prettyblueberry/mui-dat...
Thank you very much
Thanks for the help.
How can we customize the, Tool bar where we have features to hide columns, Search, export etc.
Thank you so much Aashutosh Poudel for sharing, it was so easy to follow your post.
Great code example!