Exporting to excel with mui datagrid
Tags: mui, datagrid
Published: 2023-01-12 17:09:46

---
title: Exporting to excel with mui datagrid
published: true
description: Using sheetjs to create excel files in mui data grid
tags: mui, datagrid
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
# published_at: 2022-12-23 22:51 +0000
---

Community version of mui doesn't support excel export. This example shows adding excel export to mui datagrid using [SheetJS](https://docs.sheetjs.com/docs/).

TL;DR: https://stackblitz.com/edit/react-ts-3kav3v?file=App.tsx

## Steps
* Replace the default toolbar in datagrid with a custom implementation 
```jsx
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
```jsx
function CustomToolbar(props) {
  return (
    <GridToolbarContainer {...props}>
      <ExportButton />
    </GridToolbarContainer>
  );
}
```
* `ExportButton` simply wraps a `MenuItem` in a container
```jsx
export function ExportButton(props) {
  return (
    <GridToolbarExportContainer {...props}>
      <ExportMenuItem />
    </GridToolbarExportContainer>
  );
}

```
* `ExportMenuItem` when clicked downloads the data present in the datagrid. It uses the `useGridApiContext()` to access the data.
```jsx
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. 
```jsx
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 
```jsx
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;
}
```

## Resources: 
* https://mui.com/x/react-data-grid/export/
* https://docs.sheetjs.com/docs/getting-started/installation/frameworks
* https://docs.sheetjs.com/docs/getting-started/example