Exporting to excel with mui datagrid

#mui #datagrid

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 a MenuItem in a container

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.

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

Resources:

Top comments (6)

Blueberry • Apr 10 '23 • Edited

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 !