Solved

Exporting a Dataset into Excel Spreadsheet

Posted on 2004-11-02
587 Views
Last Modified: 2008-01-09
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
Question by:fevster
    5 Comments
     
    LVL 28

    Accepted Solution

    by:
    0
     
    LVL 18

    Assisted Solution

    by:tusharashah
    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
     
    LVL 5

    Assisted Solution

    by:kollu
    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
     
    LVL 18

    Expert Comment

    by:tusharashah
    0
     
    LVL 6

    Assisted Solution

    by:vardium
    this sample code is easy and very useful:
    http://www.dotnetjohn.com/articles/articleid78.aspx

    good luck
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

     Java Android Coding Bundle

    Whether you're an Apple user or Android addict, learning to code for the Android platform is an extremely valuable, in-demand skill. It all starts with Java, the language behind the apps and games that make Android the top platform it is today.

    I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
    A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.

    857 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now