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.