Solved

Export Dataset to Excel

Posted on 2007-04-05
4
608 Views
Last Modified: 2012-06-27
I use the following script to export dataset to excel

Public Class DataSetToExcel

  Public Shared Sub Convert(ByVal ds As DataSet, ByVal response As HttpResponse)
    'first let's clean up the response.object
    response.Clear()
    response.Charset = ""
    'set the response mime type for excel
    response.ContentType = "application/vnd.ms-excel"
    'create a string writer
    Dim stringWrite As New System.IO.StringWriter
    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    'instantiate a datagrid
    Dim dg As New DataGrid
    'set the datagrid datasource to the dataset passed in
    dg.DataSource = ds.Tables(0)
    'bind the datagrid
    dg.DataBind()
    'tell the datagrid to render itself to our htmltextwriter
    dg.RenderControl(htmlWrite)
    'all that's left is to output the html
    response.Write(stringWrite.ToString)
    response.End()
  End Sub
End Class

It has been working fine except now that I move the script to a new SharePoint server, it stops working.  Instead of exporting the dataset to an excel, it displays the XML on the screen.  Help.  

0
Comment
Question by:chaochenga
  • 3
4 Comments
 
LVL 11

Accepted Solution

by:
ethoths earned 500 total points
ID: 18856444
Response.Write writes things to the response stream. That usually means it will be displayed on the screen. However, if the client does not know how to handle the fille type it will try to save it. When you installed Sharepoint you probably set IE to recognise xls files so that they could be displayed. If this is the case then changin the line response.ContentType = "application/vnd.ms-excel" will stop it from happening.
 
0
 

Author Comment

by:chaochenga
ID: 18857355
What do I need to change the line response.ContentType = "application/vnd.ms-excel" to?
0
 

Author Comment

by:chaochenga
ID: 18857826
I removed the line response.ContentType = "application/vnd.ms-excel" and it is now showing data on the screen.  However, I need the save as dialog to pop up.  Help.
0
 

Author Comment

by:chaochenga
ID: 18858049
I got it to work with the following

Response.AppendHeader( "content-disposition",  "attachment; filename=" + name )
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

867 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

22 Experts available now in Live!

Get 1:1 Help Now