Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Exporting a Dataset into Excel Spreadsheet

Posted on 2004-11-02
8
Medium Priority
?
591 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
Comment
Question by:fevster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 28

Accepted Solution

by:
mmarinov earned 128 total points
ID: 12474087
0
 
LVL 18

Assisted Solution

by:tusharashah
tusharashah earned 124 total points
ID: 12474595
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
kollu earned 124 total points
ID: 12474776
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
ID: 12477765
0
 
LVL 6

Assisted Solution

by:Volkan Vardar
Volkan Vardar earned 124 total points
ID: 12481443
this sample code is easy and very useful:
http://www.dotnetjohn.com/articles/articleid78.aspx

good luck
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

604 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