Export DataSet To Excel - Multiple Sheets - Max Row Check
Exporting to a single sheet excel file is easier and it requires only a few lines of codes. Exporting to Multiple sheets is a little bit complicated and it requires a little bit of extra effort. Mostly we will go for third party controls or dlls to achieve that. Here is the simple way to export your dataset to multiple sheets in an Excel file.
How the Excel files are internally stored?
Most of the excel files are generally stored in the format of an XML file internally. To achieve our goal, we need to create an XML file that could be easily converted to an Excel file. In the code, I will just create an XML file that will store each DataTable in each sheet of an excel file.
Maximum Row Size in Excel File
Each sheet will allow the user to store only up to 64000 rows, if you try to export your data without checking this condition, surely your code will throw the error. It's mandatory to check the condition while exporting the data to an excel file. It's clearly handled in this code. When the rows count reaches 64000, it will automatically store the next row in the next sheet as per the code.
XML way of Exporting to Excel
XML's way of exporting to excel is much faster than any other method. If you are using a third party dll to export an excel file, surely it will take more time in conversion. But this method is very much robust and faster in the conversion process.
Code Explanation
The attached code will just export only one table to one sheet and it checks the condition of max rows, if the row exceeds the limit of 64000, it will directly write the next row in the next sheet. You can apply the same logic to export all the tables in the dataset to multiple sheets. Just giving this work to the readers, to clearly understand the code rather than simply copy-pasting the code.
I will tell you the clue to implement your logic- for (int x = 0; x < dsInput.Tables[0].Columns.Count; x++)