DEV Community

Aashutosh Poudel
Aashutosh Poudel

Posted on

Exporting to excel with 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>
  );
}
Enter fullscreen mode Exit fullscreen mode

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>
  );
}
Enter fullscreen mode Exit fullscreen mode
  • ExportButton simply wraps a MenuItem in a container
export function ExportButton(props) {
  return (
    <GridToolbarExportContainer {...props}>
      <ExportMenuItem />
    </GridToolbarExportContainer>
  );
}

Enter fullscreen mode Exit fullscreen mode
  • 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>
  );
}
Enter fullscreen mode Exit fullscreen mode
  • 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 });
}
Enter fullscreen mode Exit fullscreen mode
  • 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;
}
Enter fullscreen mode Exit fullscreen mode

Resources:

Top comments (7)

Collapse
 
prettyblueberry profile image
Blueberry • 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 !

Collapse
 
sharmatanikesh profile image
Tanikesh Sharma

How to remove action column from that mui-datagrid-full-edit?

Collapse
 
prettyblueberry profile image
Blueberry

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

Thread Thread
 
sharmatanikesh profile image
Tanikesh Sharma

Thanks for the help.

Collapse
 
ankii2001 profile image
Ankit Luthra

How can we customize the, Tool bar where we have features to hide columns, Search, export etc.

Collapse
 
aalassv profile image
Israel Quintanilla

Thank you so much Aashutosh Poudel for sharing, it was so easy to follow your post.

Collapse
 
reacthunter0324 profile image
React Hunter

Great code example!