My export to excel has exceeded the 1,048,576 rows(which is the maximum length of excel file). Is there any work around or solution for this?
Excel has a maximum of 1048576 rows by 16384 columns (official doc).
You can either use a different file format or split it into sheets (if data allows it).
Or, maybe, that type of data is not for export and no one will ever look at it in an Excel.
Is there a logic to split data into different sheets?
You can split your data in the sheet based on your internal decision on how you go about it.
A plain answer would be just to wait till the max row count, and then any remaining records go in the next sheet. But a more meaningful separation would be to divide the records logically based on some data points, like some sort of segments that you see within your data.
Multiple sheets categorising some attribute of your whole data would also be an easier alternative for the end users of your sheet.
The criteria do split should come from the business.
If it is indifferent, just do it every million records.