We help IT Professionals succeed at work.

Exporting data in Multiple excel Sheet from asp.net

Mahesh Yadav
Mahesh Yadav asked
on
I have requirement, I need to call a store procedure which returns me a data set.

Now my client does not want to use any third party tools and they want to export this store proc data into multiple sheet in a single excel file and then allow user to download it.

Any one please what are best options for me to go ahead on this.
Comment
Watch Question

Why not use Excel to connect to your Oracle datasource?  Then query thru Excel directly?

Author

Commented:
I have SQL server as a back end and I can only call the store procedure and get the data from it.
I have no rights to do any thing extra
Commented:
Excel xlsx files are actually .zip file containing several files in a directory structure.
Try renaming one to .zip and unpacking.
You will find multiple sheet xml files.
If you can try this:
Take the output data and massage it into the appropriate XML for each sheet
1. use a blank unpacked xlsx file
2. replace the contents of the blanks sheets with your own sheets from the data
3. if you have a variable number of sheets to be created, tweek the entries in the main work book XML.
4. Zip, copy and reset your template folder structure.

Microsoft provide some useful tools for examing the contents of MS Office OPC documents. http://openxmlviewer.codeplex.com/

Look here for the serious tools you need to really pull apart Open XML documents http://msdn.microsoft.com/en-us/library/bb448854.aspx

Here is some useful information from the MS Office Developer center
http://msdn.microsoft.com/en-us/office/ee330857

I have successfully written several cross platform (using open source libs only) tools to manipulate MS Office OPC Open XML documents. It's