Export DataSet to Excel

Export DataSet to Excel - Multiple Sheets - Max Row Check

Exporting to single sheet excel file is easier and it requires only few line of codes. Exporting to Multiple sheet is little bit complicated and it requires 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 a XML file which could be easily converted to an Excel file. In the code, I will just create an XML file which will store the each DataTable in each sheet of an excel file.

Maximum Row Size in Excel File

Each sheet will allow the user to store only upto 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 way of exporting to excel is much faster than any other method. If you are using 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 conversion process.

Code Explanation

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++)

You can put one more loop above this line in the code to export all the tables to multiple sheet. In this line you need to change the Tables[0] to Tables[i].

You can ask your questions, if you find any difficult in exporting your dataset.