Introduction

I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets, and I couldn't find anything that actually works. So, I wrote one to help people who might be in the same situation. The complete code in ExcelHelper.cs is shown below.

This only works for Excel 2003 and later versions. If a table in the dataset has more than 65,000 rows, it will break it into multiple sheets for the table with sheet name (tableNameX). replaceXmlChar() function is added to escape XML reserved characters.

Limitations of the Code

It does not handle data tables with more than 256 columns (Excel 2003 column limit), and when data tables have very large rows count, it might throw OutOfMemory exception.

Using the Code

To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:

I tried utilizing your code in a project of mine, but I received the following error after doing the reponse.Flush and response.End

"ex = {Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.}"

Any clue as to why this could be happening? I tried substituting the dataset/datatables you were creating programmatically in place of mine, but I get the same result. Not sure how to overcome this issue.

Update 1:
Additonally, if I use HttpContext.Current.ApplicationInstance.CompleteRequest() instead Response.End(), I get the following,

"Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed."
Update 2:
I did not get any replies on this issue, but I did find a resolution. Simply do the following in your page load for the button that kicks off the export.

When I run this code in a machine which has Office 2010, it saves the file without the .xls file extension, and when i try to open in office 2010, we get warning message saying that the file is not in the correct format as the file extension.

i try your code.its fine. but i dont understand because my datagrid is call data from database.select command.so how to display the data.right now you just add value1,value2. my data is depend on what user key in.i cannot write every sngle column and row.