Exporting a Dataset into Excel Spreadsheet

One of the way of doing that is:
--------------------------------------------------------------------------------------------------------------------------------
DataSet DS = somevalueDataSet();
XmlDataDocument xdd = new XmlDataDocument(DS);
XslTransform  xt = new XslTransform();
string workingdir = AppDomain.CurrentDomain.BaseDirectory + "/" + "xslSheet.xsl";
xt.Load(workingdir);
string dirPath = AppDomain.CurrentDomain.BaseDirectory +  "/" + "GeneratedFR";
string filePath = AppDomain.CurrentDomain.BaseDirectory +  "/" + "GeneratedFR" + "/" + "Test1.xls";
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
File.Delete(filePath);
FileStream fs = new FileStream(filePath, FileMode.Create);
using(fs)
{
xt.Transform(xdd,null,fs);
fs.Close();
}
--------------------------------------------------------------------------------------------------------------------------------

-tushar

You can write a separate page for excel export.
You can build all the html code using StringBuilder which is little bit faster for large data)as you do in asp page and follow this.

OracleConnection dbCon      = myUtils.openAppsDB();
                  DataSet ds      = myUtils.GetDataSetFromAppsOrPPPP(Sql,"APPS",dbCon);
                  System.Text.StringBuilder sbBody = new System.Text.StringBuilder();  
                  sbBody.Append("<TABLE width='100%' border=1 cellspacing=2 cellpadding=2>");
                  sbBody.Append("<TR valign=Top>");
                  sbBody.Append("<td align=Center valign=Middle rowspan=2><B>PO&nbsp;No</B></td>");
                  sbBody.Append("<td align=Center valign=Middle rowspan=2><B>PO&nbsp;Date</B></td>");
                  sbBody.Append("</TR>");
                  foreach(DataTable myTable in ds.Tables)
                  {
                        if (myTable.Rows.Count > 0)
                        {
                              foreach(DataRow myRow in myTable.Rows)
                              {
                                    sbBody.Append("<TR valign=Top>");
                                    sbBody.Append("<TD valign=Top NOWRAP>"+ myRow["PO_NUMBER"].ToString() +"</TD>");
                                    sbBody.Append("<TD valign=Top>"+ myRow["PO_CREATION_DATE"].ToString() +"</TD>");

                              }
                        }
                        else
                        {
                              sbBody.Append("<TR><TD>No records found with your your search criteria.Please try again</TD></TR>");
                        }
                  }
                  sbBody.Append("</TABLE>");                  
                  Response.Clear();
                  Response.Buffer = true;                  
                  Response.ContentType = "application/vnd.ms-excel";                  
                  this.EnableViewState = false;                  
                  Response.Write(sbBody.ToString());
                  ds.Dispose();
                  dbCon.Close();      
                  ds.Dispose();
                  dbCon.Close();
                  Response.End ();            

You can also save this to ~ (or ,)separated file and can opne in excel as follows
      Response.ContentType = "text/plain";(instead of "application/vnd.ms-excel"; from above)
                  
                  Response.AddHeader ("Content-Disposition","inline;filename=BuyerPartlist.txt");