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?
LVL 4
newbiealAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zkeownCommented:
Hmm... you aren't using AJAX are you?
0
newbiealAuthor Commented:
nope, can't do for this project.  I wish though...
0
Dustin HopkinsSenior Web DeveloperCommented:
Are you changing the content type or are you using someother method, could you please show any relevant code?

Thanks,
Dustin
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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 HopkinsSenior Web DeveloperCommented:
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 HopkinsSenior Web DeveloperCommented:
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 HopkinsSenior Web DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Browsers

From novice to tech pro — start learning today.