Tuesday, November 9, 2010

How to Export gridview or repeater data to Excel


ASP Repeater Control
The Repeater control is used to display a repeated list of items that are bound to the control.
The Repeater control may be bound to a database table, an XML file, or another list of items.
There will be different better ways to implement below code... I came up with this to share a sample..
Example: 
<body>
  <form id="frm1" method="post" runat="server">
  <div id="div1">
   <table align="center" width="98%" cellpadding="0" cellspacing="0" border="1">
    <tr><th colspan="5" height="20">Report</th></tr>
    <tr><td colspan="5">&nbsp;</td></tr>
    <tr>
       <td colspan="5"><input type="button" id="btnExport" runat="server" value="Export Report"></td>
    </tr>
    <tr>
       <td colspan="5">
       <%
            rptHistory.DataSource = Nothing
            rptHistory.DataBind()
            Dim dsDetails As DataSet
            ''Retrive Data from DB table to Dataset
            If dsDetails.Tables("User").Rows.Count = 0 Then
               dsDetails = Nothing
               Exit Sub
            Else
              rptHistory.DataSource = dsDetails.Tables("User")
              rptHistory.DataBind()       
              dsDetails = Nothing       
            End If
       %>
     </td>
    </tr>
    <asp:Repeater ID="rptHistory" Runat="server">
     <HeaderTemplate>
      <tr>
       <td align="center" style="BACKGROUND-COLOR: #ffcccc"><b>Name</b></td>
       <td align="center" style="BACKGROUND-COLOR: #ffcccc"><b>Age</b></td>
       <td align="center" style="BACKGROUND-COLOR: #ffcccc"><b>City</b></td>
       <td align="center" style="BACKGROUND-COLOR: #ffcccc"><b>School</b></td>
       <td align="center" style="BACKGROUND-COLOR: #ffcccc"><b>Class</b></td>
      </tr>
     </HeaderTemplate>
     <ItemTemplate>
      <tr id="trCnt" runat="server">
       <td>&nbsp;<%# DataBinder.Eval(Container.DataItem, "Name") %></td>
       <td align="center"><%# DataBinder.Eval(Container.DataItem, "Age") %></td>
       <td align="center"><%# DataBinder.Eval(Container.DataItem, "City") %></td>
       <td align="center"><%# DataBinder.Eval(Container.DataItem, "School") %></td>
       <td>&nbsp;<%# DataBinder.Eval(Container.DataItem, "Class") %></td>
      </tr>
     </ItemTemplate>
    </asp:Repeater>
      <%
        'Here am caching the Repeater control
        Cache.Remove("Report")
        Cache.Insert("Report", rptHistory)
      %>
   </table>
  </div>
 </form>
</body>
Export to Excel
Response.Clear()
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Response.AddHeader("content-disposition", "attachment;filename=" & "Report.xls")
Dim sw As New System.IO.StringWriter
Dim htw As New HtmlTextWriter(sw)
DirectCast(Cache("Report"), Repeater).RenderControl(htw)
Dim sb1 As New System.Text.StringBuilder
sb1 = sb1.Append("<table cellSpacing='0' cellPadding='0' width='100%' align='center' border='1'>" & sw.ToString() & _
     "</table>")
sw = Nothing
htw = Nothing
Response.Write(sb1.ToString())
sb1.Remove(0, sb1.Length)
Response.Flush()
Response.End()