Can we have server-side export for CSV and Excel

[Takueg]

Duplicates

• [X] I have searched the existing issues

Latest version

• [X] I have tested the latest version

Summary 💡

Hi team 🙋🏾‍♂️ I am using DataGrid premium mostly because of the Excel export feature. However, in its current form, it seems heavily limiting. The table will display a large number of rows (in some cases 8 000 rows) so I am using server-side pagination, server-side filtering, and server-side sorting for better performance.

To me, it also makes sense to also have server-side export which allows us to fetch all rows to be exported instead of just exporting the current page.

I saw in this issue that it was suggested to create custom solutions. I do not think this is a great solution. Is there another solution that does not include installing a completely new library?

Examples 🌈

No response

Motivation 🔦

It is important for our product to export all the rows and not just the current page. At the moment I have a not pretty workaround in which I set the table's paginations to 'client' request all the rows; export; and turn it back to 'server'. please find attachment.

https://user-images.githubusercontent.com/78903016/188474008-c6bcc5d6-2479-487d-91e2-c67d58a42793.mov

Order ID 💳 (optional)

46510

[alexfauquette]

The export CSV and excel export all the rows loaded in the client. As you can see, your exported excel file does more than 35 lines whereas pages have 10 rows.

The only solution to export all the rows from the data grid itself is to download them, and set them into the DataGrid state before running the excel generation.

const exportAllRows = async () => {
	// fetch all the rows
	const allRows = await axios.get(....)
	// Set them in the stats
	apiRef.current.updateRows(allRows);
	// Get the excel file
	apiRef.current.exportDataAsExcel()
}

const GridExcelExportMenuItem = (props: GridExcelExportMenuItemProps) => {
  const apiRef = useGridApiContext();
  const { hideMenu, options, ...other } = props;

  return (
    <MenuItem
      onClick={() => {
        exportAllRows();
        hideMenu?.();
      }}
      {...other}
    >
      {apiRef.current.getLocaleText('toolbarExportExcel')}
    </MenuItem>
  );
};

The same should work for other formats (csv and print)

[Takueg]

props

@alexfauquette , thanks for the swift response.

I have tried this approach but apiRef.current.updateRows(allRows) loads all the downloaded rows on the dom even though pagination is true and pageSize is set to 10. This is also an issue because if I have too many rows - the page becomes unresponsive. Maybe there is something I am missing.

https://user-images.githubusercontent.com/78903016/188586285-3a60e0b3-5f89-4f8a-9e75-fd5080e0e039.mov

[alexfauquette]

Yes, I forget that with server-side pagination the rows in the internal state are the visible ones.

Making it work, would require modifying the exportDataAsExcel such that it could take extra rows in its argument. Maybe @m4theushw or @DanailH that worked on the server interaction will have an opinion on it.

If I really wanted to make it work regardless of wasting server resources, I would simply set the data and remove them the time to generate the file

onClick={async () => {
	// Save the row present before file generation
    const curretnRowIds = apiRef.current.getAllRowIds()

    // update data to set all the rows
    const allRows = await getAllRows()
    apiRef.current.updateRows(allRows);
    await apiRef.current.exportDataAsExcel() // notice that file generation is an async function
   
	// Delete rows added for the file generation
    const idsToDelete = allRows.map(row => row.id).filter((id) => !curretnRowIds.includes(id))
    apiRef.current.updateRows(idsToDelete.map(rowId => ({ id: rowId, _action: 'delete' })));
  }}

Here is the codesandbox with naive serve fetching mocking https://codesandbox.io/s/confident-sea-giz1rs?file=/demo.tsx

[m4theushw]

If the application needs to download all rows before exporting to Excel then it invalidates the idea of using server-side pagination. It would be better to generate the Excel file in the server and send to the user or use client-side pagination.

[Jashfaq]

Yes, I forget that with server-side pagination the rows in the internal state are the visible ones.

Making it work, would require modifying the exportDataAsExcel such that it could take extra rows in its argument. Maybe @m4theushw or @DanailH that worked on the server interaction will have an opinion on it.

If I really wanted to make it work regardless of wasting server resources, I would simply set the data and remove them the time to generate the file

onClick={async () => {
	// Save the row present before file generation
    const curretnRowIds = apiRef.current.getAllRowIds()

    // update data to set all the rows
    const allRows = await getAllRows()
    apiRef.current.updateRows(allRows);
    await apiRef.current.exportDataAsExcel() // notice that file generation is an async function
   
	// Delete rows added for the file generation
    const idsToDelete = allRows.map(row => row.id).filter((id) => !curretnRowIds.includes(id))
    apiRef.current.updateRows(idsToDelete.map(rowId => ({ id: rowId, _action: 'delete' })));
  }}

Here is the codesandbox with naive serve fetching mocking https://codesandbox.io/s/confident-sea-giz1rs?file=/demo.tsx

Do you know how we can export all the rows. Currently it is exporting the only selected row.

[alexfauquette]

Do you know how we can export all the rows. Currently it is exporting the only selected row.

That's the default behavior of apiRef.current.exportDataAsExcel(). If some rows are selected, only selected rows are exported

You can pass the parameter getRowsToExport

/**
   * Function that returns the id of the rows to export on the order they should be exported.
   * @param {GridGetRowsToExportParams} params With all properties from [[GridGetRowsToExportParams]].
   * @returns {GridRowId[]} The id of the rows to export.
   */
  getRowsToExport?: (params: GridGetRowsToExportParams<Api>) => GridRowId[];

If you still face problems please open an new issue, or ask on stackoverflow

[oleksandrfomin5995]

I'm rewriting legacy front-end code to React and I'm having an issue with page freezing and erroring when trying to export DataGrids that has many pages (some up to 2 thousand pages). I understand that this happens because we're adding all the elements to the DOM. So my question is, is there a way to generate Excel without actually rendering everything in the DataGrid ?

I've got no access to backend to create Excel export functionality on the server. It would also kinda defeat the purpose of buying the priciest MUI bundle.

[alexfauquette]

I understand that this happens because we're adding all the elements to the DOM. So my question is, is there a way to generate Excel without actually rendering everything in the DataGrid ?

You are not adding elements to the DOM, because the data grid has virtualization. It only renders the visible rows such that your DOM stays light. So except if you've disabled virtualization, this part is OK.

But for sure you will need to download the data, and then create the Excel file in the browser. If you have 2k pages and so I assume something like 200k lines, It could take some time.

If the bottleneck is at the downloading of data, there is nothing to do. If it's because the Excel generation is freezing the main thread, you can use web workers

[oleksandrfomin5995]

I understand that this happens because we're adding all the elements to the DOM. So my question is, is there a way to generate Excel without actually rendering everything in the DataGrid ?

You are not adding elements to the DOM, because the data grid has virtualization. It only renders the visible rows such that your DOM stays light. So except if you've disabled virtualization, this part is OK.

But for sure you will need to download the data, and then create the Excel file in the browser. If you have 2k pages and so I assume something like 200k lines, It could take some time.

If the bottleneck is at the downloading of data, there is nothing to do. If it's because the Excel generation is freezing the main thread, you can use web workers

It actually does try to render everything when paginationMode is server and the DataGrid autoHeight props is true . So I took the solution you suggested above and added state variable to toggle between server and client modes.

const [gridFeatureMode, setGridFeatureMode] = useState<GridFeatureMode>('server');

 const handleExcelExport = async () => {
    const curretnRowIds = apiRef.current.getAllRowIds();

    ...

    setGridFeatureMode('client');
    apiRef.current.updateRows((data as any).data);

    await apiRef.current.exportDataAsExcel({
      fileName,
    });

    const idsToDelete = (data as any).data
      .map((row: any) => row.id)
      .filter((id: any) => !curretnRowIds.includes(id));

    apiRef.current.updateRows(idsToDelete.map((rowId: any) => ({ id: rowId, _action: 'delete' })));
    setGridFeatureMode('server');
  };

It seems to work fine and the freezing problem is gone. But I'm not 100% sure if it won't cause any undesired sideeffects that I'm not aware of right now.

[alexfauquette]

Thanks for the clarification. By curiosity, is it also freezing without autoHeight activated?

I don't know what's are the plan of the data grid team on this aspect. But I feel there is an issue with serverside+export management.

All the export methods assume rows is the entire dataset, and the server-side grid assumes rows is the displayed data. Leading to the trick of setting toe client and adding the extra rows.

Maybe the export methods such as exportDataAsExcel should have an extra parameter rows or getRows({ filterModel, page, ... }) that allows fetching the data set to export without modifying the rendered HTML.

@cherniavskii do you have rethought on this topic?

[oleksandrfomin5995]

Thanks for the clarification. By curiosity, is it also freezing without autoHeight activated?

My bad. It renders all the rows regardless of the autoHeight prop.

[cherniavskii]

Let's start with adding a recipe with server-side pagination and an Excel export button in the toolbar that would fetch all the rows, then use the updateRows method to update the grid state and export all the rows to Excel. This will let us understand the problem better and then we can work on the missing parts (e.g. overriding the rows data in the exportDataAsExcel method).

[adamduren]

@cherniavskii what if there was an prepareForExportFn prop that took a function that returns a promise containing a list of rows to be exported? If provided that would be used vs the current logic. Would that be a viable option?