TomBecker.net

Exporting a Girdview to Excel

I needed a quick way to export either a Gridview to an Excel file. I wanted something quick that I could call on a button click. So I put the following function in a class and you can call it from anywhere. You can also specify the file name of the export if you wish. It defaults to export.xls if you don't. You can easily use this for file types of than excel by changing the MIME type, a full list can be found here.

The PrepareControlForExport sub will just remove the control and replace it with a literal so it will show up nice in your export.

Public Shared Sub Generate(ByVal Grid As GridView, Optional ByVal filename As String = "export")
    HttpContext.Current.Response.Clear()
    HttpContext.Current.Response.Buffer = True
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & filename & ".xls")
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
    HttpContext.Current.Response.Charset = ""

    Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter()
    Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

    '  Create a form to contain the grid
    Dim table As Table = New Table
    table.GridLines = Grid.GridLines

    '  add the header row to the table
    If Grid.ShowHeader Then
        PrepareControlForExport(Grid.HeaderRow.Controls(0))
        table.Rows.Add(Grid.HeaderRow)
    End If

    '  add each of the data rows to the table
    For Each row As GridViewRow In Grid.Rows
        PrepareControlForExport(row)
        table.Rows.Add(row)
    Next
  
    '  render the table into the htmlwriter
    table.RenderControl(oHtmlTextWriter)
  
    HttpContext.Current.Response.Write(oStringWriter.ToString())
    HttpContext.Current.Response.End()
End Sub

 ' Replace any of the contained controls with literals
Private Shared Sub PrepareControlForExport(ByVal control As Web.UI.Control)
    Dim i As Integer = 0
    Do While (i < control.Controls.Count)
        Dim current As Web.UI.Control = control.Controls(i)
        If (TypeOf current Is LinkButton) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, LinkButton).Text))
        ElseIf (TypeOf current Is ImageButton) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, ImageButton).AlternateText))
        ElseIf (TypeOf current Is HyperLink) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, HyperLink).Text))
        ElseIf (TypeOf current Is DropDownList) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, DropDownList).SelectedItem.Text))
        ElseIf (TypeOf current Is CheckBox) Then
            control.Controls.Remove(current)
            control.Controls.AddAt(i, New Web.UI.LiteralControl(CType(current, CheckBox).Checked))
        End If

        If current.HasControls Then
            PrepareControlForExport(current)
        End If
        i = (i + 1)
    Loop
End Sub

Just pass in your gridview and filename and you are good to go. It should prompt you whether or not you want to save the file or open it.

Labels: .NET

posted by Tom Becker at 11/25/2008 | 0 Comments

Creating a custom pager using Delegates

I wanted to create a way to handle the paging event of a gridview or datagrid with a few special requirements, I wanted to be able to

1. change the page size,
2. have previous, next, first and last buttons, and
3. jump to a specific page.

The inbuilt paging option in the gridview does not allow you to do all of this. And instead of writing this for each grid that I wanted to use it in I created a user control, that does everything for you.

I will save you the details of the most of the code, but will point out the interesting bits, you can download a working example at the end of the post.

First the delegates. The user control has two delegates defined one to change the page (PageChangedEventHandler) and one to handle the page size changing (ItemsPerPageChangedEventHandler). Notice that in the definition of the delegates I have a custom eventArg class that has a few extra properties that I am going use.

Public Delegate Sub PageChangedEventHandler(ByVal sender As Object, ByVal e As DataNavigatorEventArgs)

Public Delegate Sub ItemsPerPageChangedEventHandler(ByVal sender As Object, ByVal e As DataNavigatorEventArgs)

Both Delegates have an event associated with them:

Public Event PageChanged As PageChangedEventHandler
Public Event CountChanged As ItemsPerPageChangedEventHandler

The user control has 3 properties that we will need to use on the page where you are using the user control. And of course all the button clicks where the magic happens. When the user clicks a button, changes page size, or jumps to a page, we raise the corresponding event, below is the example from clicking the 'first' button.

 Private Sub btnFirst_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnFirst.Click
     Dim myE As New DataNavigatorEventArgs

     _Current_Page = 0

     myE.CurrentPage = _Current_Page
     myE.TotalPages = _Page_count

     ViewState("CurrentPage") = _Current_Page
     ViewState("PageCount") = _Page_count

     RaiseEvent PageChanged(sender, myE)
End Sub

The rest of the button clicks look pretty much the same just the logic of setting the current page is different.

So that is pretty much it for the user control.

In order to use it, you can just drag it on your page and in the code behind make sure you catch both events, example below.

Public Sub Paging(ByVal sender As Object, ByVal e As DataNavigatorEventArgs) Handles myPager.PageChanged
     Me.grdTest.PageIndex = e.CurrentPage
     'rebind grid
End Sub

Public Sub CountChanged(ByVal sender As Object, ByVal e As DataNavigatorEventArgs) Handles myPager.CountChanged
     Me.grdTest.PageSize = e.ItemsPerPage
     Me.grdTest.PageIndex = e.CurrentPage
     'rebind grid
End Sub

Since this bit of code is essentially the same you can just copy paste between the pages and change variables names as needed.

Only one other bit of code on the aspx page and you are set. And this goes where you are binding the grid. You must tell the pager control who many items there are and what the current page is.

Me.grdTest.DataSource = dt
Me.grdTest.DataBind()

Dim itemCount As Integer = dt.Rows.Count
Me.myPager.ItemCount = itemCount
Me.myPager.Currentpage = Me.grdTest.PageIndex

And you are all set.

Download the code

Labels: .NET

posted by Tom Becker at 11/19/2008 | 2 Comments

Delete/Backspace not working in SQL Developer

So I was merrily working along in Oracle SQL Developer and all of a sudden the delete and backspace buttons stopped working.

A quick Google search showed this post. Apparently this is a common problem in SQL Developer and here is the quick fix:

Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Easy as that.

Labels: Oracle

posted by Tom Becker at 11/06/2008 | 0 Comments