[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1003
  • Last Modified:

export gridview results to excel

I've got a gridview that when loaded in IE the user has the option to show all records (meaning paging is disabled) or show only 10 records (paging = true).  If the user displays all records, they should be able to take advantage of the 'export to excel' feature in IE.  Right click on gridview, select export-to-excel, and for some reason it only exports the first 10 records, not all of the 100+ records, why is that?
0
newbieal
Asked:
newbieal
  • 4
  • 4
2 Solutions
 
zkeownCommented:
Hmm... you aren't using AJAX are you?
0
 
newbiealAuthor Commented:
nope, can't do for this project.  I wish though...
0
 
Dustin HopkinsManaging MemberCommented:
Are you changing the content type or are you using someother method, could you please show any relevant code?

Thanks,
Dustin
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
newbiealAuthor Commented:
I've just got a gridview and a button.  Gridview is set for paging being true and displays 10 recs per page.

Button when clicked just does this:

Gridview1.AllowPaging = False

This displays all of the records on one page.  But when I go to export (right-click on grid, export-to-excel).  It exports only first 10 recs.
0
 
Dustin HopkinsManaging MemberCommented:
Are you databinding after you change the paging? if you could show the code we could look it over to find where an error may be generated.
Without that all I could tell you to try is when the user clicks the export to excel before you send the gridview to the function change allow paging to false and do a databind. Here is an example:
 '//Bind with paging disabled
GridView1.AllowPaging = False
GridView1.DataBind()
            
 '// Send to Excel export function
ExportGridView(GridView1)
 '// Rebind with paging enabled
GridView1.AllowPaging = True
GridView1.DataBind()

Open in new window

0
 
Dustin HopkinsManaging MemberCommented:
I'm sorry I just realized what you were saying...The code obove is for use with a content type change. As you are relying on IE/Office functionallity to handle it.

Is there a way you can change to using your own export function instead of the office built in stuff? As you lose formatting and such in this method and requires the user have IE and office?
0
 
newbiealAuthor Commented:
Your code above is very similar to what I have.  I bind the data after I set allowpaging=false, but it still just exports 10 records.  I was hoping that I didn't have to implement my own, but it looks like that's the route I'm going with.  I have though about using the report write, dropping a table into it and dragging the fields on it.  This way the user can also export in pdf, not just excel.   Report writer is a pretty nice feature.
0
 
Dustin HopkinsManaging MemberCommented:
I did some testing of the built in stuff and it seems it will only export the initial data only, if you decide to use an export function instead of report writer. You could the code below. All of this can go in the code behind...
' // This is for the button click
Protected Sub lnkExport_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkExport.Click
        If GridView1.HasControls = True Then
            '// Disable paging
            GridView1.AllowPaging = False
            GridView1.DataBind()
            '// exluded columns arraylist
            Dim defaultExcludedColumns As New ArrayList()
 
            '// Always exclude these columns
            defaultExcludedColumns.Add("") '// Removes columns without headers from the output.
 
            '// Send to base Excel export method 
            ExportGridView(GridView1, "ExcelFileName", defaultExcludedColumns)
 
            '// Rebind with paging enabled
            GridView1.AllowPaging = True
            GridView1.DataBind()
        End If
    End Sub
 
' This will export the gridview...
'/// <summary>
    '/// Export GridView data to Excel.
    '/// </summary>
    '/// <param name="grdView">GridView control to export.</param>
    '/// <param name="filename">Filename of excel spreadsheet.</param>
    '/// <param name="excludedColumnList">ArrayList of columns to exlude.</param>
    Sub ExportGridView(ByVal grdView As GridView, ByVal filename As String, ByVal excludedColumnList As ArrayList)
 
        '// Clear response content & headers
        Response.Clear()
        Response.ClearContent()
        Response.ClearHeaders()
 
        '// Add header
        Response.AddHeader("content-disposition", "attachment;filename=" + filename + ".xls")
        Response.Charset = String.Empty
        Response.Cache.SetCacheability(System.Web.HttpCacheability.Public)
        Response.ContentType = "application/vnd.xls"
        '// Start Excel Write
        '// Create stringWriter
        Dim stringWrite As New System.IO.StringWriter()
 
        '// Create HtmlTextWriter
        Dim htmlWrite As New HtmlTextWriter(stringWrite)
 
        '// Remove controls from Column Headers
        If (grdView.HeaderRow IsNot Nothing And grdView.HeaderRow.Cells IsNot Nothing) Then
            Dim ct As Integer
            For ct = 0 To grdView.HeaderRow.Cells.Count - 1 Step ct + 1
                ' Save initial text if found
                Dim headerText As String = grdView.HeaderRow.Cells(ct).Text
 
                ' Check for controls in header
                If grdView.HeaderRow.Cells(ct).HasControls() Then
                    ' Check for link button
                    If grdView.HeaderRow.Cells(ct).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.DataControlLinkButton" Then
                        ' link button found, get text
                        headerText = (CType(grdView.HeaderRow.Cells(ct).Controls(0), LinkButton)).Text
                    End If
 
                    ' Remove controls from header
                    grdView.HeaderRow.Cells(ct).Controls.Clear()
                End If
 
                ' Reassign header text
                grdView.HeaderRow.Cells(ct).Text = headerText
            Next
 
        End If
        '// Remove footer
        If (grdView.FooterRow IsNot DBNull.Value) Then
 
            grdView.FooterRow.Visible = False
        End If
        
        '// Remove unwanted columns (header text listed in removeColumnList arraylist)
        Dim field As DataControlField
        For Each field In grdView.Columns
 
            If (excludedColumnList.Contains(field.HeaderText)) Then
 
                field.Visible = False
            End If
        Next
 
        '// Call gridview's renderControl
        grdView.RenderControl(htmlWrite)
 
        '// Write Response to browser
        Response.Write(stringWrite.ToString())
 
        Response.End()
    End Sub

Open in new window

0
 
newbiealAuthor Commented:
Ok, I found the solution that I was looking for.  I decided to change allowpaging to equals to false instead of true.  This caused loading of all data at start up.  Then I clicked on Export to Excel from the right-click menu in IE and it exported all of the records...exactly what I wanted and didn't require any coding.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now