[data grid] `valueFormatter` or `valueGetter` are not applying for excel file exports

[jbaccarelli-equinix]

The problem in depth

I have a colDef config that applies a timezone format depending on what the user has picked, this works fine for the table display and for exporting CVS, but for excel it seems the valueGetter and valueFormatter are both ignored and i can't apply the timezone formatting when exporting excel files.

Any other way i could apply this formatting to excel exports?

import { formatDateTimeFriendly, formatDateTimeLocal } from '@goe/utils'

import { currentUserVar } from '@/hooks'

import { GRID_DATE_TIME_BETWEEN_COL_DEF } from '../../colDef'

const GRID_DATETIME_FRIENDLY_COL_DEF = {
  minWidth: 280,
  resizable: false,
  valueFormatter(value: Date) {
    if (value) {
      const date = new Date(value)
      const { timezone } = currentUserVar()
      return formatDateTimeFriendly(date, timezone)
    }
    return null
  },
  valueGetter: (value: Date) => value && new Date(value),
  ...GRID_DATE_TIME_BETWEEN_COL_DEF,
  dateType: 'friendly',
}

const GRID_DATETIME_LOCAL_COL_DEF = {
  minWidth: 280,
  resizable: false,
  valueFormatter(value: Date) {
    if (value) {
      const date = new Date(value)
      const { timezone } = currentUserVar()
      return formatDateTimeLocal(date, timezone)
    }
    return null
  },
  valueGetter: (value: Date) => value && new Date(value),
  ...GRID_DATE_TIME_BETWEEN_COL_DEF,
  dateType: 'local',
}

export const dateTimeBaseColumnTypes = {
  dateTimeFriendly: GRID_DATETIME_FRIENDLY_COL_DEF,
  dateTimeLocal: GRID_DATETIME_LOCAL_COL_DEF,
}

Your environment

`npx @mui/envinfo`

``` Don't forget to mention which browser you used. Output from `npx @mui/envinfo` goes here. ```

Search keywords: excel,export,date Order ID: 86073

[dosubot[bot]]

Hello @jbaccarelli-equinix! 👋 I'm here to assist you with any bugs, questions, or contributions you have in mind. Let's tackle this together!

To apply timezone formatting to Excel exports in the Data Grid, you can use the exceljsPreProcess or exceljsPostProcess properties to manipulate the data before or after it is added to the Excel workbook. The valueGetter and valueFormatter are not directly used in Excel exports, so you need to handle the formatting within these pre/post-processing functions.

Here's an example of how you can achieve this:

1. Define the pre-processing function:

   function exceljsPreProcess({ workbook, worksheet }) {
    // Iterate over each row and apply the timezone formatting
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   }

2. Use the pre-processing function in the GridToolbarExport:

   <GridToolbarExport
    excelOptions={{
      exceljsPreProcess,
    }}
   />

Replace yourDateColumnIndex with the actual index of your date column. This approach ensures that the date values are formatted according to the user's timezone before being exported to Excel.

By using the exceljsPreProcess function, you can customize the formatting of the data specifically for the Excel export, bypassing the limitations of valueGetter and valueFormatter in this context ^[1][2].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

Hello @jbaccarelli-equinix! 👋 I'm here to assist you with any bugs, questions, or contributions you have in mind. Let's tackle this together!

To apply timezone formatting to Excel exports in the Data Grid, you can use the exceljsPreProcess or exceljsPostProcess properties to manipulate the data before or after it is added to the Excel workbook. The valueGetter and valueFormatter are not directly used in Excel exports, so you need to handle the formatting within these pre/post-processing functions.

Here's an example of how you can achieve this:

1. Define the pre-processing function:

   function exceljsPreProcess({ workbook, worksheet }) {
    // Iterate over each row and apply the timezone formatting
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   }

2. Use the pre-processing function in the GridToolbarExport:

   <GridToolbarExport
    excelOptions={{
      exceljsPreProcess,
    }}
   />

Replace yourDateColumnIndex with the actual index of your date column. This approach ensures that the date values are formatted according to the user's timezone before being exported to Excel.

By using the exceljsPreProcess function, you can customize the formatting of the data specifically for the Excel export, bypassing the limitations of valueGetter and valueFormatter in this context [1][2].

To continue talking to Dosu, mention @dosu.

@dosu i'm using a custom export button and menu, how do i apply this in such case

[dosubot[bot]]

To apply the exceljsPreProcess function when using a custom export button and menu, you need to pass the excelOptions to the GridExcelExportMenuItem within your custom export button component. Here's how you can integrate it:

1. Define the pre-processing function:

   function exceljsPreProcess({ workbook, worksheet }) {
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   }

2. Update the CustomExportButton to include GridExcelExportMenuItem with excelOptions:

   function CustomExportButton(props) {
    return (
      <GridToolbarExportContainer {...props}>
        <GridCsvExportMenuItem options={{ delimiter: ';' }} />
        <GridExcelExportMenuItem options={{ exceljsPreProcess }} />
        <JsonExportMenuItem />
      </GridToolbarExportContainer>
    );
   }

3. Ensure your CustomToolbar uses the updated CustomExportButton:

   function CustomToolbar(props) {
    return (
      <GridToolbarContainer {...props}>
        <CustomExportButton />
      </GridToolbarContainer>
    );
   }

4. Use the CustomToolbar in your main component:

   export default function CustomExport() {
    const { data, loading } = useDemoData({
      dataSet: 'Commodity',
      rowLength: 4,
      maxColumns: 6,
    });
   
    return (
      <div style={{ height: 300, width: '100%' }}>
        <DataGrid {...data} loading={loading} slots={{ toolbar: CustomToolbar }} />
      </div>
    );
   }

By following these steps, you ensure that the exceljsPreProcess function is applied when exporting to Excel using your custom export button and menu ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

@dosu im getting a TS error: "Type '({ worksheet }: GridExceljsProcessInput) => void' is not assignable to type '(processInput: GridExceljsProcessInput) => Promise\<void>'. Type 'void' is not assignable to type 'Promise\<void>'"

[dosubot[bot]]

To resolve the TypeScript error, you need to ensure that the exceljsPreProcess function returns a Promise<void>. Here's how you can modify your function:

1. Define the pre-processing function as an async function:

   const exceljsPreProcess = async ({ workbook, worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the pre-processing function in your custom export button:

   function CustomExportButton(props) {
    return (
      <GridToolbarExportContainer {...props}>
        <GridCsvExportMenuItem options={{ delimiter: ';' }} />
        <GridExcelExportMenuItem options={{ exceljsPreProcess }} />
        <JsonExportMenuItem />
      </GridToolbarExportContainer>
    );
   }

By defining the exceljsPreProcess function as async, it inherently returns a Promise<void>, which should resolve the TypeScript error ^[1][2][3].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

@dosu This works for formatting the value but for some reason the cell.value is different from what i see. When i see "Today at 10:00 AM" in the cell, then i click export i see while debugging that the cell.value is two hours ahead

[dosubot[bot]]

The cell.value is two hours ahead of the displayed value "Today at 10:00 AM" when exporting to Excel because the code creates a date using UTC instead of the local timezone. This is done to ensure that Excel does not perform any timezone conversion.

Here is the relevant code snippet:

case 'date':
case 'dateTime': {
  // Excel does not do any timezone conversion, so we create a date using UTC instead of local timezone
  // Solution from: https://github.com/exceljs/exceljs/issues/486#issuecomment-432557582
  // About Date.UTC(): https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/UTC#exemples
  const value = apiRef.current.getCellParams<any, Date>(id, column.field).value;
  // value may be `undefined` in auto-generated grouping rows
  if (!value) {
    break;
  }
  const utcDate = new Date(
    Date.UTC(
      value.getFullYear(),
      value.getMonth(),
      value.getDate(),
      value.getHours(),
      value.getMinutes(),
      value.getSeconds(),
    ),
  );
  row[column.field] = utcDate;
  break;
}

This code converts the local date and time to a UTC date and time, which can result in a time difference if your local timezone is not UTC ^[1].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

The cell.value is two hours ahead of the displayed value "Today at 10:00 AM" when exporting to Excel because the code creates a date using UTC instead of the local timezone. This is done to ensure that Excel does not perform any timezone conversion.

Here is the relevant code snippet:

case 'date':
case 'dateTime': {
  // Excel does not do any timezone conversion, so we create a date using UTC instead of local timezone
  // Solution from: https://github.com/exceljs/exceljs/issues/486#issuecomment-432557582
  // About Date.UTC(): https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/UTC#exemples
  const value = apiRef.current.getCellParams<any, Date>(id, column.field).value;
  // value may be `undefined` in auto-generated grouping rows
  if (!value) {
    break;
  }
  const utcDate = new Date(
    Date.UTC(
      value.getFullYear(),
      value.getMonth(),
      value.getDate(),
      value.getHours(),
      value.getMinutes(),
      value.getSeconds(),
    ),
  );
  row[column.field] = utcDate;
  break;
}

This code converts the local date and time to a UTC date and time, which can result in a time difference if your local timezone is not UTC [1].

To continue talking to Dosu, mention @dosu.

@dosu any way to bypass this conversion?

[dosubot[bot]]

To bypass the UTC conversion and keep the local timezone for Excel exports, you can modify the exceljsPreProcess function to format the date values directly without converting them to UTC. Here's how you can do it:

1. Define the pre-processing function:

   const exceljsPreProcess = async ({ workbook, worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the pre-processing function in your custom export button:

   function CustomExportButton(props) {
    return (
      <GridToolbarExportContainer {...props}>
        <GridCsvExportMenuItem options={{ delimiter: ';' }} />
        <GridExcelExportMenuItem options={{ exceljsPreProcess }} />
        <JsonExportMenuItem />
      </GridToolbarExportContainer>
    );
   }

By using this approach, you can ensure that the date values retain their local timezone formatting when exported to Excel, bypassing the default UTC conversion ^[1][2].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

To bypass the UTC conversion and keep the local timezone for Excel exports, you can modify the exceljsPreProcess function to format the date values directly without converting them to UTC. Here's how you can do it:

1. Define the pre-processing function:

   const exceljsPreProcess = async ({ workbook, worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        if (colNumber === yourDateColumnIndex) { // Replace with your date column index
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the pre-processing function in your custom export button:

   function CustomExportButton(props) {
    return (
      <GridToolbarExportContainer {...props}>
        <GridCsvExportMenuItem options={{ delimiter: ';' }} />
        <GridExcelExportMenuItem options={{ exceljsPreProcess }} />
        <JsonExportMenuItem />
      </GridToolbarExportContainer>
    );
   }

By using this approach, you can ensure that the date values retain their local timezone formatting when exported to Excel, bypassing the default UTC conversion [1][2].

To continue talking to Dosu, mention @dosu.

@dosu when i use exceljsPreProcess the row.eachCell is nevery looping because the worksheet seems to be empty, it only works with the exceljsPostProcess, but then i think the UTC conversion happens

[dosubot[bot]]

Could you provide the code for your custom export button and how you are integrating the exceljsPreProcess function? This will help in understanding how the worksheet is being populated and why it might be empty during the pre-processing step.

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

Could you provide the code for your custom export button and how you are integrating the exceljsPreProcess function? This will help in understanding how the worksheet is being populated and why it might be empty during the pre-processing step.

To continue talking to Dosu, mention @dosu.

@dosu

export function exceljsPreProcess({ worksheet }: GridExceljsProcessInput) {
  worksheet.eachRow((row) => {
    row.eachCell((cell) => {
      if (isDate(cell.value)) {
        const date = new Date(cell.value)
        const { timezone } = currentUserVar()
        cell.value = changeTimezone(date, timezone)
      }
    })
  })
  return Promise.resolve()
}

a snippet of where im using the preprocessor

const CustomExportButton = ({
  dataKey,
  totalCount,
  document,
  filters,
  exportState,
  disableTotalRecordsDownload,
}: {
  dataKey: string
  totalCount: number
  document: DocumentNode
  filters: {
    condition: Record<string, unknown>
    filter: Record<string, unknown>
  }
  exportState?: string
  disableTotalRecordsDownload?: boolean
}) => {
  ...

  return (
    <GridToolbarExportContainer>

      <GridCsvExportMenuItem key="csv" options={exportOptions} />
      <GridExcelExportMenuItem
        key="excel"
        options={{
          exceljsPreProcess,
        }}
      />

    </GridToolbarExportContainer>
  )
}

export { CustomExportButton }

[dosubot[bot]]

It looks like the exceljsPreProcess function is correctly defined and integrated into your custom export button. However, the worksheet might be empty during the pre-processing step because the data hasn't been populated yet.

To address this, you can use the exceljsPostProcess function instead, which ensures that the data is already populated in the worksheet. Here's how you can modify your code:

1. Define the post-processing function:

   const exceljsPostProcess = async ({ worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        if (isDate(cell.value)) {
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = changeTimezone(date, timezone);
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the post-processing function in your custom export button:

   const CustomExportButton = ({
    dataKey,
    totalCount,
    document,
    filters,
    exportState,
    disableTotalRecordsDownload,
   }: {
    dataKey: string;
    totalCount: number;
    document: DocumentNode;
    filters: {
      condition: Record<string, unknown>;
      filter: Record<string, unknown>;
    };
    exportState?: string;
    disableTotalRecordsDownload?: boolean;
   }) => {
    // Your existing code...
   
    return (
      <GridToolbarExportContainer>
        <GridCsvExportMenuItem key="csv" options={exportOptions} />
        <GridExcelExportMenuItem
          key="excel"
          options={{
            exceljsPostProcess,
          }}
        />
      </GridToolbarExportContainer>
    );
   };
   
   export { CustomExportButton };

By using the exceljsPostProcess function, you ensure that the data is already populated in the worksheet before applying your custom timezone formatting. This should resolve the issue with the worksheet being empty during the pre-processing step.

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

It looks like the exceljsPreProcess function is correctly defined and integrated into your custom export button. However, the worksheet might be empty during the pre-processing step because the data hasn't been populated yet.

To address this, you can use the exceljsPostProcess function instead, which ensures that the data is already populated in the worksheet. Here's how you can modify your code:

1. Define the post-processing function:

   const exceljsPostProcess = async ({ worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        if (isDate(cell.value)) {
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = changeTimezone(date, timezone);
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the post-processing function in your custom export button:

   const CustomExportButton = ({
    dataKey,
    totalCount,
    document,
    filters,
    exportState,
    disableTotalRecordsDownload,
   }: {
    dataKey: string;
    totalCount: number;
    document: DocumentNode;
    filters: {
      condition: Record<string, unknown>;
      filter: Record<string, unknown>;
    };
    exportState?: string;
    disableTotalRecordsDownload?: boolean;
   }) => {
    // Your existing code...
   
    return (
      <GridToolbarExportContainer>
        <GridCsvExportMenuItem key="csv" options={exportOptions} />
        <GridExcelExportMenuItem
          key="excel"
          options={{
            exceljsPostProcess,
          }}
        />
      </GridToolbarExportContainer>
    );
   };
   
   export { CustomExportButton };

By using the exceljsPostProcess function, you ensure that the data is already populated in the worksheet before applying your custom timezone formatting. This should resolve the issue with the worksheet being empty during the pre-processing step.

To continue talking to Dosu, mention @dosu.

@dosu yes this works to have a worksheet with data, but then the UTC conversion happens and breaks the date value

[dosubot[bot]]

To prevent the UTC conversion from breaking the date value during the exceljsPostProcess step, you can modify the date values directly in the post-processing function. This way, you can ensure that the dates are formatted correctly according to the user's timezone without being converted to UTC.

Here's how you can do it:

1. Define the post-processing function:

   const exceljsPostProcess = async ({ worksheet }: GridExceljsProcessInput): Promise<void> => {
    worksheet.eachRow((row) => {
      row.eachCell((cell) => {
        if (isDate(cell.value)) {
          const date = new Date(cell.value);
          const { timezone } = currentUserVar();
          cell.value = formatDateTimeFriendly(date, timezone); // or formatDateTimeLocal
        }
      });
    });
   
    // Ensure the function returns a Promise
    return Promise.resolve();
   };

2. Use the post-processing function in your custom export button:

   const CustomExportButton = ({
    dataKey,
    totalCount,
    document,
    filters,
    exportState,
    disableTotalRecordsDownload,
   }: {
    dataKey: string;
    totalCount: number;
    document: DocumentNode;
    filters: {
      condition: Record<string, unknown>;
      filter: Record<string, unknown>;
    };
    exportState?: string;
    disableTotalRecordsDownload?: boolean;
   }) => {
    // Your existing code...
   
    return (
      <GridToolbarExportContainer>
        <GridCsvExportMenuItem key="csv" options={exportOptions} />
        <GridExcelExportMenuItem
          key="excel"
          options={{
            exceljsPostProcess,
          }}
        />
      </GridToolbarExportContainer>
    );
   };
   
   export { CustomExportButton };

By using the exceljsPostProcess function, you can ensure that the date values are formatted correctly according to the user's timezone after the data has been populated in the worksheet, thus bypassing the default UTC conversion ^[1][2].

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

@dosu im trying to apply the same logic to a place where im programatically calling exportDataAsExcel but both the pre processing and post processing are not being triggered

 await apiRef.current.exportDataAsExcel({
          exceljsPostProcess, // function never called
          exceljsPreProcess: exceljsPostProcess // function never called
          worker: () =>
            new Worker(new URL('./worker/muiExcelWorker.ts', import.meta.url), {
              type: 'module',
            }),
          fileName: `${fileName}`,
        })

[dosubot[bot]]

Could you provide the code for how you are programmatically calling exportDataAsExcel and how you are defining and using the apiRef? This will help in understanding why the pre-processing and post-processing functions are not being triggered.

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

Could you provide the code for how you are programmatically calling exportDataAsExcel and how you are defining and using the apiRef? This will help in understanding why the pre-processing and post-processing functions are not being triggered.

To continue talking to Dosu, mention @dosu.

@dosu here is the full component

import { DocumentNode } from '@goe/graphql'
import { EqxButton } from '@goe/lib'
import { dateStringIsValid, shouldNotForwardProp, toTimeZone } from '@goe/utils'
import {
  MenuItem,
  Stack,
  styled,
  ToggleButton,
  ToggleButtonGroup,
  Tooltip,
} from '@mui/material'
import {
  GridCsvExportMenuItem,
  GridCsvGetRowsToExportParams,
  GridExcelExportMenuItem,
  GridFileExportOptions,
  GridPrintExportMenuItem,
  GridRowId,
  gridSortedRowIdsSelector,
  GridToolbarExportContainer,
  useGridApiContext,
} from '@mui/x-data-grid-premium/'
import { startCase } from 'lodash'
import { useCallback, useEffect, useState } from 'react'
import Swal from 'sweetalert2'

import { currentUserVar, useBatchedCalls, useToast } from '@/hooks'
import { getNestedResponseData } from '@/hooks/table/useEqxTableQuery/helpers'

import { exceljsPostProcess } from '..'

type ExportOptions = 'CSV' | 'EXCEL'

const applyTimezoneToDateFields = (rows: Record<string, unknown>[]) => {
  const { timezone } = currentUserVar()
  return rows.map((row) => {
    const newRow = { ...row }
    Object.keys(newRow).forEach((key) => {
      if (dateStringIsValid(newRow[key] as string)) {
        newRow[key] = toTimeZone(new Date(newRow[key] as string), timezone)
      }
    })
    return newRow
  })
}

const UnstyledMenuItem = styled(MenuItem, {
  shouldForwardProp: (prop) => shouldNotForwardProp(prop, ['hideMenu']),
})`
  background: transparent;
  &:hover,
  &:focus {
    background: transparent;
  }

  .toggleBtn {
    text-transform: none;
  }
`

const StyledMenuItem = styled(MenuItem, {
  shouldForwardProp: (prop) => shouldNotForwardProp(prop, ['hideMenu']),
})``

const getFilteredRows = ({ apiRef }: GridCsvGetRowsToExportParams) =>
  gridSortedRowIdsSelector(apiRef)

const exportOptions: GridFileExportOptions = {
  getRowsToExport: getFilteredRows,
}

const categories = ['only_displaying', 'total_records']

const MAX_RECORDS_AT_ONCE = 10000
const MAX_BATCHES_AT_ONCE = 4

interface DownloadProps {
  exportAs: ExportOptions
  rows: Record<string, unknown>[]
  fileName: string
}

const CustomExportButton = ({
  dataKey,
  totalCount,
  document,
  filters,
  exportState,
  disableTotalRecordsDownload,
}: {
  dataKey: string
  totalCount: number
  document: DocumentNode
  filters: {
    condition: Record<string, unknown>
    filter: Record<string, unknown>
  }
  exportState?: string
  disableTotalRecordsDownload?: boolean
}) => {
  const [activeTab, setActiveTab] = useState(categories[0])

  const { makeToast, doneLoading, cancelLoading, updateProgress } =
    useToast('loading')
  const { batchCalls, abortCall, loading } = useBatchedCalls({
    maxRecordsAtOnce: MAX_RECORDS_AT_ONCE,
    maxBatchesAtOnce: MAX_BATCHES_AT_ONCE,
    totalCount,
    document,
    filters,
  })

  const apiRef = useGridApiContext()

  useEffect(() => {
    if (exportState === 'finished') {
      doneLoading()
    }
  }, [exportState, doneLoading])

  const download = useCallback(
    async ({ exportAs, rows, fileName }: DownloadProps) => {
      const prevRows = Array.from(apiRef.current.getRowModels().values())

      apiRef.current.setRows(rows)
      if (exportAs === 'EXCEL') {
        await apiRef.current.exportDataAsExcel({
          exceljsPostProcess,
          worker: () =>
            new Worker(new URL('./worker/muiExcelWorker.ts', import.meta.url), {
              type: 'module',
            }),
          fileName: `${fileName}`,
        })
      } else {
        apiRef.current.exportDataAsCsv({
          getRowsToExport: () => rows.map(({ id }) => id) as GridRowId[],
          fileName: `${fileName}`,
        })
        doneLoading()
      }
      apiRef.current.setRows(prevRows)
    },
    [apiRef, doneLoading],
  )

  const getNestedRows = useCallback(
    (data: unknown) => {
      let rawRows: Record<string, unknown>[] = []
      const dataNode = getNestedResponseData<Record<string, unknown>>(
        data,
        dataKey,
      )

      if (dataNode) {
        const { edges } = dataNode

        rawRows = edges.map((v) => v.node)
      }

      return rawRows
    },
    [dataKey],
  )

  const onAbort = useCallback(() => {
    abortCall()
    cancelLoading()
  }, [cancelLoading, abortCall])

  const makeCall = useCallback(
    async (exportAs: ExportOptions, currenDataKey: string) => {
      makeToast(
        <Stack direction="row" spacing={2} alignItems="center">
          <span>Downloading, please wait...</span>
          <EqxButton
            sx={{
              height: '35px',
              whiteSpace: 'nowrap',
            }}
            variant="outlined"
            onClick={onAbort}
            data-testid="cancel-btn"
          >
            Cancel
          </EqxButton>
        </Stack>,
      )

      let composedRows: Record<string, unknown>[] = []

      await batchCalls({
        onCallDone: ({ progress }) => updateProgress(progress / 100),
        onBatchDone: (batchResults) => {
          for (const group of batchResults) {
            const { data: resultBatch, error } = group || {}
            if (resultBatch) {
              composedRows = [...composedRows, ...getNestedRows(resultBatch)]
            } else if (error) {
              composedRows = []
              onAbort()
              Swal.fire(
                'Oops!',
                `Something went wrong ${error.message}`,
                'error',
              )
              break
            }
          }
        },
      })

      updateProgress(0.99, 'Compiling file, this might take a while...')

      download({
        exportAs,
        rows: composedRows,
        fileName: `${startCase(currenDataKey)}_Export`,
      })
    },
    [makeToast, onAbort, batchCalls, updateProgress, download, getNestedRows],
  )

  const fetchAll = useCallback(
    async (exportAs: ExportOptions) => {
      if (totalCount > 1000000) {
        Swal.fire(
          'Oops!',
          'Cannot download more than 1 million records at a time, please refine your search',
          'warning',
        )
      } else {
        makeCall(exportAs, dataKey)
      }
    },
    [dataKey, makeCall, totalCount],
  )

  return (
    <GridToolbarExportContainer>
      {totalCount > 0 ? (
        [
          <UnstyledMenuItem key="menu-item">
            <ToggleButtonGroup
              value={activeTab}
              exclusive
              onChange={(_event, newValue) =>
                newValue && setActiveTab(newValue)
              }
            >
              {categories.map((cat, ix) =>
                disableTotalRecordsDownload && cat === 'total_records' ? (
                  <Tooltip
                    disableInteractive
                    title="Disable current values to enable downloading of total records."
                    key={ix}
                  >
                    <ToggleButton
                      className="toggleBtn"
                      color="primary"
                      size="small"
                      value={cat}
                      data-testid={`${cat}-btn`}
                      sx={{
                        cursor: 'default',
                        color: 'lightgray',
                      }}
                      onClick={(event) => event.preventDefault()}
                    >
                      {startCase(cat)}
                    </ToggleButton>
                  </Tooltip>
                ) : (
                  <ToggleButton
                    className="toggleBtn"
                    color="primary"
                    size="small"
                    key={ix}
                    value={cat}
                    data-testid={`${cat}-btn`}
                  >
                    {startCase(cat)}
                  </ToggleButton>
                ),
              )}
            </ToggleButtonGroup>
          </UnstyledMenuItem>,
          ...(activeTab === categories[0]
            ? [
                <GridCsvExportMenuItem key="csv" options={exportOptions} />,
                <GridExcelExportMenuItem
                  key="excel"
                  options={{
                    exceljsPostProcess,
                  }}
                />,
              ]
            : [
                <StyledMenuItem
                  disabled={loading}
                  onClick={() => fetchAll('CSV')}
                  key="csv"
                  data-testid="all-csv-btn"
                >
                  Download as CSV
                </StyledMenuItem>,
                <StyledMenuItem
                  disabled={loading}
                  onClick={() => fetchAll('EXCEL')}
                  key="excel"
                >
                  Download as Excel
                </StyledMenuItem>,
              ]),
          activeTab === categories[0] && (
            <GridPrintExportMenuItem key="export-item" />
          ),
        ]
      ) : (
        <UnstyledMenuItem>No records to export</UnstyledMenuItem>
      )}
    </GridToolbarExportContainer>
  )
}

export { CustomExportButton }

[dosubot[bot]]

The issue with the exceljsPreProcess and exceljsPostProcess functions not being triggered when programmatically calling exportDataAsExcel might be due to how the options are being passed or how the apiRef is being used.

Here are a few steps to ensure that the pre-processing and post-processing functions are correctly triggered:

1. Ensure Correct Typing and Usage of apiRef: Make sure that apiRef is correctly typed and used. The useGridApiContext hook should provide the correct API reference.

2. Check the Options Passed to exportDataAsExcel: Ensure that the options passed to exportDataAsExcel are correctly formatted and include the necessary functions.

Here is the updated code snippet for your component:

import { DocumentNode } from '@goe/graphql';
import { EqxButton } from '@goe/lib';
import { dateStringIsValid, shouldNotForwardProp, toTimeZone } from '@goe/utils';
import {
  MenuItem,
  Stack,
  styled,
  ToggleButton,
  ToggleButtonGroup,
  Tooltip,
} from '@mui/material';
import {
  GridCsvExportMenuItem,
  GridCsvGetRowsToExportParams,
  GridExcelExportMenuItem,
  GridFileExportOptions,
  GridPrintExportMenuItem,
  GridRowId,
  gridSortedRowIdsSelector,
  GridToolbarExportContainer,
  useGridApiContext,
} from '@mui/x-data-grid-premium/';
import { startCase } from 'lodash';
import { useCallback, useEffect, useState } from 'react';
import Swal from 'sweetalert2';

import { currentUserVar, useBatchedCalls, useToast } from '@/hooks';
import { getNestedResponseData } from '@/hooks/table/useEqxTableQuery/helpers';

import { exceljsPostProcess } from '..';

type ExportOptions = 'CSV' | 'EXCEL';

const applyTimezoneToDateFields = (rows: Record<string, unknown>[]) => {
  const { timezone } = currentUserVar();
  return rows.map((row) => {
    const newRow = { ...row };
    Object.keys(newRow).forEach((key) => {
      if (dateStringIsValid(newRow[key] as string)) {
        newRow[key] = toTimeZone(new Date(newRow[key] as string), timezone);
      }
    });
    return newRow;
  });
};

const UnstyledMenuItem = styled(MenuItem, {
  shouldForwardProp: (prop) => shouldNotForwardProp(prop, ['hideMenu']),
})`
  background: transparent;
  &:hover,
  &:focus {
    background: transparent;
  }

  .toggleBtn {
    text-transform: none;
  }
`;

const StyledMenuItem = styled(MenuItem, {
  shouldForwardProp: (prop) => shouldNotForwardProp(prop, ['hideMenu']),
})``;

const getFilteredRows = ({ apiRef }: GridCsvGetRowsToExportParams) =>
  gridSortedRowIdsSelector(apiRef);

const exportOptions: GridFileExportOptions = {
  getRowsToExport: getFilteredRows,
};

const categories = ['only_displaying', 'total_records'];

const MAX_RECORDS_AT_ONCE = 10000;
const MAX_BATCHES_AT_ONCE = 4;

interface DownloadProps {
  exportAs: ExportOptions;
  rows: Record<string, unknown>[];
  fileName: string;
}

const CustomExportButton = ({
  dataKey,
  totalCount,
  document,
  filters,
  exportState,
  disableTotalRecordsDownload,
}: {
  dataKey: string;
  totalCount: number;
  document: DocumentNode;
  filters: {
    condition: Record<string, unknown>;
    filter: Record<string, unknown>;
  };
  exportState?: string;
  disableTotalRecordsDownload?: boolean;
}) => {
  const [activeTab, setActiveTab] = useState(categories[0]);

  const { makeToast, doneLoading, cancelLoading, updateProgress } =
    useToast('loading');
  const { batchCalls, abortCall, loading } = useBatchedCalls({
    maxRecordsAtOnce: MAX_RECORDS_AT_ONCE,
    maxBatchesAtOnce: MAX_BATCHES_AT_ONCE,
    totalCount,
    document,
    filters,
  });

  const apiRef = useGridApiContext();

  useEffect(() => {
    if (exportState === 'finished') {
      doneLoading();
    }
  }, [exportState, doneLoading]);

  const download = useCallback(
    async ({ exportAs, rows, fileName }: DownloadProps) => {
      const prevRows = Array.from(apiRef.current.getRowModels().values());

      apiRef.current.setRows(rows);
      if (exportAs === 'EXCEL') {
        await apiRef.current.exportDataAsExcel({
          exceljsPostProcess,
          worker: () =>
            new Worker(new URL('./worker/muiExcelWorker.ts', import.meta.url), {
              type: 'module',
            }),
          fileName: `${fileName}`,
        });
      } else {
        apiRef.current.exportDataAsCsv({
          getRowsToExport: () => rows.map(({ id }) => id) as GridRowId[],
          fileName: `${fileName}`,
        });
        doneLoading();
      }
      apiRef.current.setRows(prevRows);
    },
    [apiRef, doneLoading],
  );

  const getNestedRows = useCallback(
    (data: unknown) => {
      let rawRows: Record<string, unknown>[] = [];
      const dataNode = getNestedResponseData<Record<string, unknown>>(
        data,
        dataKey,
      );

      if (dataNode) {
        const { edges } = dataNode;

        rawRows = edges.map((v) => v.node);
      }

      return rawRows;
    },
    [dataKey],
  );

  const onAbort = useCallback(() => {
    abortCall();
    cancelLoading();
  }, [cancelLoading, abortCall]);

  const makeCall = useCallback(
    async (exportAs: ExportOptions, currenDataKey: string) => {
      makeToast(
        <Stack direction="row" spacing={2} alignItems="center">
          <span>Downloading, please wait...</span>
          <EqxButton
            sx={{
              height: '35px',
              whiteSpace: 'nowrap',
            }}
            variant="outlined"
            onClick={onAbort}
            data-testid="cancel-btn"
          >
            Cancel
          </EqxButton>
        </Stack>,
      );

      let composedRows: Record<string, unknown>[] = [];

      await batchCalls({
        onCallDone: ({ progress }) => updateProgress(progress / 100),
        onBatchDone: (batchResults) => {
          for (const group of batchResults) {
            const { data: resultBatch, error } = group || {};
            if (resultBatch) {
              composedRows = [...composedRows, ...getNestedRows(resultBatch)];
            } else if (error) {
              composedRows = [];
              onAbort();
              Swal.fire(
                'Oops!',
                `Something went wrong ${error.message}`,
                'error',
              );
              break;
            }
          }
        },
      });

      updateProgress(0.99, 'Compiling file, this might take a while...');

      download({
        exportAs,
        rows: composedRows,
        fileName: `${startCase(currenDataKey)}_Export`,
      });
    },
    [makeToast, onAbort, batchCalls, updateProgress, download, getNestedRows],
  );

  const fetchAll = useCallback(
    async (exportAs: ExportOptions) => {
      if (totalCount > 1000000) {
        Swal.fire(
          'Oops!',
          'Cannot download more than 1 million records at a time, please refine your search',
          'warning',
        );
      } else {
        makeCall(exportAs, dataKey);
      }
    },
    [dataKey, makeCall, totalCount],
  );

  return (
    <GridToolbarExportContainer>
      {totalCount > 0 ? (
        [
          <UnstyledMenuItem key="menu-item">
            <ToggleButtonGroup
              value={activeTab}
              exclusive
              onChange={(_event, newValue) =>
                newValue && setActiveTab(newValue)
              }
            >
              {categories.map((cat, ix) =>
                disableTotalRecordsDownload && cat === 'total_records' ? (
                  <Tooltip
                    disableInteractive
                    title="Disable current values to enable downloading of total records."
                    key={ix}
                  >
                    <ToggleButton
                      className="toggleBtn"
                      color="primary"
                      size="small"
                      value={cat}
                      data-testid={`${cat}-btn`}
                      sx={{
                        cursor: 'default',
                        color: 'lightgray',
                      }}
                      onClick={(event) => event.preventDefault()}
                    >
                      {startCase(cat)}
                    </ToggleButton>
                  </Tooltip>
                ) : (
                  <ToggleButton
                    className="toggleBtn"
                    color="primary"
                    size="small"
                    key={ix}
                    value={cat}
                    data-testid={`${cat}-btn`}
                  >
                    {startCase(cat)}
                  </ToggleButton>
                ),
              )}
            </ToggleButtonGroup>
          </UnstyledMenuItem>,
          ...(activeTab === categories[0]
            ? [
                <GridCsvExportMenuItem key="csv" options={exportOptions} />,
                <GridExcelExportMenuItem
                  key="excel"
                  options={{
                    exceljsPostProcess,
                  }}
                />,
              ]
            : [
                <StyledMenuItem
                  disabled={loading}
                  onClick={() => fetchAll('CSV')}
                  key="csv"
                  data-testid="all-csv-btn"
                >
                  Download as CSV
                </StyledMenuItem>,
                <StyledMenuItem
                  disabled={loading}
                  onClick={() => fetchAll('EXCEL')}
                  key="excel"
                >
                  Download as Excel
                </StyledMenuItem>,
              ]),
          activeTab === categories[0] && (
            <GridPrintExportMenuItem key="export-item" />
          ),
        ]
      ) : (
        <UnstyledMenuItem>No records to export</UnstyledMenuItem>
      )}
    </GridToolbarExportContainer>
  );
};

export { CustomExportButton };

In this code, ensure that the exceljsPostProcess function is correctly defined and passed to the exportDataAsExcel method. Additionally, make sure that the apiRef is correctly used to call the exportDataAsExcel method.

If the issue persists, you might want to check if there are any errors or warnings in the console that could provide more insights into why the pre-processing and post-processing functions are not being triggered.

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

@dosu still not working ,the postprocessing function is never called

[dosubot[bot]]

Are there any errors or warnings in the console when you call exportDataAsExcel? Additionally, could you provide the version of @mui/x-data-grid-premium you are using? This information will help in diagnosing the issue further.

To continue talking to Dosu, mention @dosu.

[jbaccarelli-equinix]

@dosu no warnings, "@mui/x-data-grid-premium": "^7.7.1",

[dosubot[bot]]

To ensure that the exceljsPostProcess function is called when programmatically exporting data as Excel using exportDataAsExcel in @mui/x-data-grid-premium version 7.7.1, you need to pass the exceljsPostProcess function as part of the options to the exportDataAsExcel method.

Here is an example of how you can do this:

import { exportDataAsExcel } from '@mui/x-data-grid-premium';

// Define your exceljsPostProcess function
const exceljsPostProcess = async ({ workbook, worksheet }) => {
  // Your custom post-processing logic here
  console.log('Post-processing Excel workbook');
};

// Call exportDataAsExcel with the exceljsPostProcess option
await apiRef.current.exportDataAsExcel({
  exceljsPostProcess,
  // other options...
});

The exceljsPostProcess function will be called after the workbook and worksheet have been created and populated with data. This allows you to perform any additional modifications or customizations to the Excel file before it is finalized.

Note: The exceljsPostProcess option is not supported when a web worker is used. If you are using a web worker, you should pass the callback to the same option in setupExcelExportWebWorker ^[1][2][3].

If you are using a web worker, you need to set up the exceljsPostProcess function within the worker setup. Here’s how you can do it:

1. Define the worker setup function:

   import { setupExcelExportWebWorker } from '@mui/x-data-grid-premium';
   
   const setupWorker = () => {
    const worker = new Worker(new URL('./worker/muiExcelWorker.ts', import.meta.url), {
      type: 'module',
    });
   
    setupExcelExportWebWorker(worker, {
      exceljsPostProcess,
    });
   
    return worker;
   };

2. Use the worker setup function in your export call:

   await apiRef.current.exportDataAsExcel({
    worker: setupWorker,
    fileName: `${fileName}`,
   });

By following these steps, you should be able to ensure that the exceljsPostProcess function is called during the Excel export process.

To continue talking to Dosu, mention @dosu.

[michelengelen]

Hey @jbaccarelli-equinix could you share a stripped down version of your component that does not use any external components/packages?

This makes it hard for us to find the error when trying to run the component! Thanks!

[github-actions[bot]]

The issue has been inactive for 7 days and has been automatically closed.