What is the problem?
There are cases in which large reports could not be exported to Excel due to an error:
'Export to Excel operation failed.
This error is usually thrown when the limitations of Excel’s version are exceeded. Most often it is about exceeding the limits for the total number of rows and columns on a worksheet. Older versions have lower limits, especially Excel 97 – 2003 which allows are up to is 65,536 and 256 columns.
How can you resolve the problem?
Check the Default format for AutoSave in Excel
Make sure that the default format for AutoSave in Excel is 'Excel Workbook.xlsx'.
To check the option open any spreadsheet and then open File => Options => AutoSave OneDrive and SharePoint Online files by default on Excel => Save files in this format.
Make sure you are not using an old version of Excel
If you are using an older version you should check its limits and if they do not meet your needs you should consider an update.
Workaround – export the report in several parts
You can divide the report into several parts, by loading it for smaller periods for example, and export each part separately.