Export ASP.Net Repeater with Images to Excel using C# and VB.Net

Hi Tevin,

Please refer belew sample

DataBase

I have made use of a table named tblFiles whose schema is defined as follows.

You can download the database table SQL by clicking the download link below.

Download SQL file

I have inserted few records in the table.

HTML

<asp:Repeater ID="rpttblFiles" runat="server" OnItemDataBound="OnItemDataBound">
    <HeaderTemplate>
        <table>
            <tr>
                <th>id</th>
                <th>Name</th>
                <th>Data</th>
            </tr>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
            <td>
                <asp:Label ID="lblId" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"id") %>'></asp:Label></td>
            <td>
                <asp:Label ID="lblName" runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Name") %>'></asp:Label></td>
            <td>
                <asp:Image ID="Image1" runat="server" AlternateText='<%# Eval("Name") %>' Height="200" Width="150" CssClass="img-rounded" /></td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>
<asp:Button ID="btnExport" Text="Export" runat="server" OnClick="Export" />

Namespace

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

VB.Net

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        this.BindRepeater();
    }
}

protected void OnItemDataBound(object sender, RepeaterItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        Uri uri = Request.Url;
        string applicationUrl = string.Format("{0}://{1}{2}", uri.Scheme, uri.Authority, uri.AbsolutePath);
        applicationUrl = applicationUrl.Replace(Request.Url.Segments[Request.Url.Segments.Length - 1], "");
        int id = Convert.ToInt32((e.Item.FindControl("lblId") as Label).Text);
        string imageUrl = string.Format("{0}Handler.ashx?id={1}", applicationUrl, id);
        (e.Item.FindControl("Image1") as Image).ImageUrl = imageUrl;
    }
}

private void BindRepeater()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblFiles", con))
        {
            DataTable dt = new DataTable();
            sda.Fill(dt);
            rpttblFiles.DataSource = dt;
            rpttblFiles.DataBind();
        }
    }
}

protected void Export(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=Data.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        this.BindRepeater();
        this.rpttblFiles.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Me.BindRepeater()
    End If
End Sub

Protected Sub OnItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
    If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then
        Dim uri As Uri = Request.Url
        Dim applicationUrl As String = String.Format("{0}://{1}{2}", uri.Scheme, uri.Authority, uri.AbsolutePath)
        applicationUrl = applicationUrl.Replace(Request.Url.Segments(Request.Url.Segments.Length - 1), "")
        Dim id As Integer = Convert.ToInt32((TryCast(e.Item.FindControl("lblId"), Label)).Text)
        Dim imageUrl As String = String.Format("{0}Handler.ashx?id={1}", applicationUrl, id)
        TryCast(e.Item.FindControl("Image1"), Image).ImageUrl = imageUrl
    End If
End Sub

Private Sub BindRepeater()
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM tblFiles", con)
            Dim dt As DataTable = New DataTable()
            sda.Fill(dt)
            rpttblFiles.DataSource = dt
            rpttblFiles.DataBind()
        End Using
    End Using
End Sub

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=Data.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Using sw As StringWriter = New StringWriter()
        Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
        Me.BindRepeater()
        Me.rpttblFiles.RenderControl(hw)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
    End Using
End Sub

Handler

C#

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public class Handler : IHttpHandler
{
    public void ProcessRequest(HttpContext context)
    {
        int id = 0;
        int.TryParse(context.Request.QueryString["id"], out id);
        if (id > 0)
        {
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                string sql = "SELECT Data, ContentType FROM tblFiles WHERE Id =" + id;
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
                {
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    byte[] bytes = (byte[])dt.Rows[0]["Data"];
                    context.Response.ContentType = dt.Rows[0]["ContentType"].ToString();
                    context.Response.BinaryWrite(bytes);
                    context.Response.End();
                }
            }
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

VB.Net

<%@ WebHandler Language="VB" Class="Handler" %>

Imports System
Imports System.Web
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class Handler : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim id As Integer = 0
        Integer.TryParse(context.Request.QueryString("id"), id)
        If id > 0 Then
            Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using con As SqlConnection = New SqlConnection(constr)
                Dim sql As String = "SELECT Data, ContentType FROM tblFiles WHERE Id =" & id
                Using sda As SqlDataAdapter = New SqlDataAdapter(sql, con)
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Dim bytes As Byte() = CType(dt.Rows(0)("Data"), Byte())
                    context.Response.ContentType = dt.Rows(0)("ContentType").ToString()
                    context.Response.BinaryWrite(bytes)
                    context.Response.End()
                End Using
            End Using
        End If
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property
End Class

Screeshot

Exported Excel