How to return an excel file or excel data from ASP.NET

Discussion in 'ASP .Net' started by Anonieko, Jan 29, 2006.

  1. Anonieko

    Anonieko Guest

    Here are some of the approaches.

    1. Transform DataGrid

    http://www.dotnetjohn.com/articles.aspx?articleid=36

    3. Use the Export approach

    http://www.aspnetpro.com/NewsletterArticle/2003/09/asp200309so_l/asp200309so_l.asp



    ---->


    This article will show how to create a class which does the export. The
    class contains a convert method which has three overloads so that we
    can pass in different kinds of information:

    Overload #1
    A Dataset
    The response object of the web page
    Overload #2
    A Dataset
    An index value of which table from the dataset
    The response object of the web page
    Overload #3
    A Dataset
    The table name of a table in the dataset
    The response object of the web page
    The methods will be shared methods so that we don't have to instaniate
    the class in order to use the method.

    What makes this task so straight forward is the elegance of the .NET
    Framework design. It turns out that most web controls have a
    RenderControl method which will write an html text stream. All we need
    to do is set up the response object, call the RenderControl method of a
    datagrid and tell the response object to output the "rendering". Pretty
    simple!

    Here's the code for the class (DataSetToExcel.vb):

    'Class to convert a dataset to an html stream which can be used to
    display the dataset
    'in MS Excel
    'The Convert method is overloaded three times as follows
    ' 1) Default to first table in dataset
    ' 2) Pass an index to tell us which table in the dataset to use
    ' 3) Pass a table name to tell us which table in the dataset to use

    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

    Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableIndex As
    Integer, ByVal response As HttpResponse)
    'lets make sure a table actually exists at the passed in value
    'if it is not call the base method
    If TableIndex > ds.Tables.Count - 1 Then
    Convert(ds, response)
    End If
    'we've got a good table so
    '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(TableIndex)
    '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

    Public Shared Sub Convert(ByVal ds As DataSet, ByVal TableName As
    String, ByVal response As HttpResponse)
    'let's make sure the table name exists
    'if it does not then call the default method
    If ds.Tables(TableName) Is Nothing Then
    Convert(ds, response)
    End If
    'we've got a good table so
    '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(TableName)
    '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

    Editor's Note: The class above was compiled using the following Visual
    Basic Compiler directive:

    vbc /t:library /r:system.dll /r:system.web.dll /r:system.data.dll
    /r:system.xml.dll DataSetToExcel.vb

    My example web page is based upon creating a dataset from SQL Server
    (the authors table from the pubs database), but it doesn't matter how
    you get the dataset created, so modify your page according to your
    methodology. The example simply creates the dataset and calls the class
    method from the Page_Load event handler of the page.

    Here's the code for the calling page. First the .aspx page which is
    really just a shell to give us something to call. As usual, all the
    work is done in the code-behind file.

    DataToExcel.aspx

    <%@ Page Language="vb" AutoEventWireup="false"
    Codebehind="DataToExcel.aspx.vb" Inherits="DataToExcel" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    <head>
    <title>DataSetToExcel</title>
    <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
    <meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
    <meta name="vs_defaultClientScript" content="JavaScript">
    <meta name="vs_targetSchema"
    content="http://schemas.microsoft.com/intellisense/ie5">
    </head>
    <body MS_POSITIONING="GridLayout">
    <form id="Form1" method="post" runat="server">

    </form>
    </body>
    </html>

    DataToExcel.aspx.vb

    Public Class DataToExcel
    Inherits System.Web.UI.Page

    #Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub
    InitializeComponent()

    End Sub

    'NOTE: The following placeholder declaration is required by the Web
    Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Init
    'CODEGEN: This method call is required by the Web Form Designer
    'Do not modify it using the code editor.
    InitializeComponent()
    End Sub

    #End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
    System.EventArgs) Handles MyBase.Load
    Dim myConnection As New
    SqlClient.SqlConnection(ConfigurationSettings.AppSettings("PubsConnection"))
    Dim cmd As New SqlClient.SqlCommand("select * from authors",
    myConnection)
    Dim da As New SqlClient.SqlDataAdapter(cmd)
    'instantiate a dataset
    Dim ds As New DataSet
    Try
    'populate the dataset
    da.Fill(ds)
    Finally
    'check on connection status
    If myConnection.State = ConnectionState.Open Then
    myConnection.Close()
    End If
    'get rid of connection object
    myConnection.Dispose()
    End Try
    'call our class method
    DataSetToExcel.Convert(ds, Response)
    End Sub

    End Class

    This is really a simple solution to a common problem. Hope it works out
    for you!
    Anonieko, Jan 29, 2006
    #1
    1. Advertising

  2. Anonieko

    Anonieko Guest

    Re: How to return an excel file or excel data from ASP.NET

    Response.Clear();
    Response.Buffer= true;
    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition",
    "inline;filename=Clientes.xls");
    Response.Charset = "";
    this.EnableViewState = false;
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter oHtmlTextWriter = new
    System.Web.UI.HtmlTextWriter(oStringWriter);
    DataGrid1.RenderControl(oHtmlTextWriter);
    Response.Write(oStringWriter.ToString());
    Response.End();
    Anonieko, Jan 31, 2006
    #2
    1. Advertising

  3. Anonieko

    JackStraw73

    Joined:
    Feb 12, 2008
    Messages:
    1
    I have a question related to this post. My server uses Excel 2003 when using Datasettoexcel.Convert(ds, Response) but many of my client machines don't have excel 2003 and can't open the resulting file. Is there a way to force Datasettoexcel to output as an earlier version of excel like 98 or 2000?
    JackStraw73, Feb 12, 2008
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Sreedhar Vankayala
    Replies:
    2
    Views:
    13,539
    Sreedhar Vankayala
    Feb 25, 2004
  2. Replies:
    1
    Views:
    1,778
    extentechjohn
    Nov 21, 2006
  3. bienwell
    Replies:
    2
    Views:
    4,104
    bienwell
    Jun 17, 2006
  4. Robbe Morris [C# MVP]

    asp.net extracting excel charts from an Excel file

    Robbe Morris [C# MVP], Dec 15, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    467
    Robbe Morris [C# MVP]
    Dec 15, 2006
  5. Greenhorn
    Replies:
    15
    Views:
    797
    Keith Thompson
    Mar 6, 2005
Loading...

Share This Page