[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • Last Modified:

Exporting a Dataset into Excel Spreadsheet

Hi there,

I need to export a dataset i get back from an SQL server stored procedure into an excel spreadsheet. Ive looked on the net but cant find much. Can anyone help me.

Thanks

Kev
0
fevster
Asked:
fevster
4 Solutions
 
mmarinovCommented:
0
 
tusharashahCommented:
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
0
 
kolluCommented:
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");
0
 
Volkan VardarSoftware Team LeaderCommented:
this sample code is easy and very useful:
http://www.dotnetjohn.com/articles/articleid78.aspx

good luck
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now