locked
Export Gridview data to Excel RRS feed

  • Question

  • User-284642143 posted

    Ive searched around the internet for this answer and found some but i would really like to learn the process of exporting the data from a gridview to excel. As in what libraries i need etc etc and why.

    From what i understand i need to

    1. Verify Excel is on the PC, if not advise the user.
    2. If Excel is on the PC then the page being viewed (with the gridview) is exported and displayed in excel.

    The data is being retrieved from SQL database. So can anyone guide me what to do or refer me to a site so i can understand this? targetting Visual Basic .Net 2008 with ASP .Net

    Thanks in advance.

    Tuesday, April 1, 2008 9:46 AM

Answers

All replies

  • User139975600 posted

    Hi EssCee,

    I have provided code and explaination at the following location:

    http://forums.asp.net/t/1239899.aspx

    Hope this helps!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 1, 2008 10:28 AM
  • User-284642143 posted

    Thanks for that PixelSyndicate i eventually got it working. However i have some questions about the code you have used and an additional question [:)]:

    1.
    Response.ClearContent()
    Response.AddHeader("content-disposition", ("attachment; filename=" + fileName))
    Response.ContentType = "application/excel"

    What do the 3 lines of code do here and why have you used it?


    2.
     ' disables the paging so no data is lost in conversion
        Private Sub DisableControls(ByVal gv As Control)
            Dim lb As LinkButton = New LinkButton
            Dim l As Literal = New Literal
            Dim name As String = String.Empty
            Dim i As Integer = 0
            Do While (i < gv.Controls.Count)
                If gv.Controls(i).HasControls Then
                    DisableControls(gv.Controls(i))
                End If
                i = (i + 1)
            Loop
        End Sub
    What do you mean disable the paging and again why use it? same goes with link button and literal. I dont have a link button in my form so i think it should be ok to delete - would you agree? and why disable the controls for?

    3.

    Dim style As String = "< style> .text { mso-number-format:\@; } < /script> "
            If (GridView1.AllowPaging = True) Then

    What is the first line doing? and why set AllowPaging to true?

    4. I also had to use the VerifyRenderingInServerForm overrides method - Why use this and what does it do?

    If you have any links i can read up on to get a better understanding of this that would be great.

    Finally any idea how i can have the first (header row) of the excel row to have a background colour and freeze the first line?

    Thanks again.

    Tuesday, April 1, 2008 3:25 PM
  • User139975600 posted

    1. That is what tells the web browser the contents of the document is of an Excel format, and is essentually a requirement to 'create' an excel document without a bunch of additional code.

    2. Gridviews are FULL of controls.  The idea of object oriented programming is to allow your code to grow (or shrink) to your needs without re-write for each separate instance.  So it would make sense to eliminate potential objects in your gridview which would turn into unwanted items in your excel document.  For instance: If you like to allow your users to sort your gridviews by a clickable column header, then you have a linkbutton in your gridview. That translates to a hyperlink in your excel document with javascript and html code being triggered when the excel user clicks them. You wouldn't want that. Even if you dont do it with THIS gridview, why change the code when you could just call on it genericly each time you needed it in the future?

    3a. Setting up the styles in excel... this helps to translate your gridview color scheme into one which isn't limited to web browsers. That style overriding the .text property in particular is ensuring that excel knows how to display your excel table.

    3b. AllowPaging to true is a setting in your gridview which indicates that you want some autogenerated clickable links in the bottom part of your grid so you can 'page' through your data in pieces, instead of all of the rows at once. You may not have it in one gridview, but in the future you may.

    4. Its a requirement to enact a workaround to allow you to change the page definition and to avoid future errors that come if you do not do it. Good nuff reason (some of these things are done through trial and error).

    finally. I dont know how to freeze an excel row programmatically though this process, though I can do it in a webpage (gridview) with stylesheets.

     

    Tuesday, April 1, 2008 8:18 PM
  • User-1093373388 posted

    Hi, My name is Sabu and a newcomer to vs2005(vb). thanks for this great post. I used the code and working fine with small datasets. Now I have a gridview with 59000 records(less than excel limit) and the code not working for this. Do you have any clue? Really appreciate it. Thanks in advance.

    Wednesday, April 16, 2008 10:34 AM
  • User139975600 posted

    Hi, My name is Sabu and a newcomer to vs2005(vb). thanks for this great post. I used the code and working fine with small datasets. Now I have a gridview with 59000 records(less than excel limit) and the code not working for this. Do you have any clue? Really appreciate it. Thanks in advance.

    What tells you it isn't working? Error message? Time-out? just hangs? The code is going to loop through all of the records and attempts to send that data to your web browser, which sees it as not being a webpage, but an excel document, at this point it attempts to tell you and allow you to open it with excel.  So is the web server chewing on all of the records and it's just taking a long time?

    Wednesday, April 16, 2008 10:59 AM
  • User-1093373388 posted

    Thanks for your quick response.

    For smaller datasets when I clicked on the Button_Click event(for exporting the gridview), a popup will ask you to open or save or cancel options and I can open or save the result. But when I clicked on the Button_Click for larger gridview, the broweser will stay for few seconds and then come up with "The Page Cannot be displayed". I can see at the status bar "Opening page res// c:\windows\system32\shdoclc.dll\dnserror.htm". I tried to put a BreakPoint  at Button_Click event and it's not coming there. Thanks!

    Wednesday, April 16, 2008 11:24 AM
  • User139975600 posted

    it appears that that browser is unable to consume that much data. ;*(

    Wednesday, April 16, 2008 11:33 AM
  • User-1093373388 posted

    If you can find a solution, please post it here. I'm also looking. Thanks for your quick responses[:)]

    Wednesday, April 16, 2008 11:46 AM
  • User-1093373388 posted

    I think it was a timeout error. I added the following line in the web.config, and the export working fine. Thanks!

    <httpRuntime maxRequestLength="2097151" executionTimeout="3600"/><?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    Wednesday, April 16, 2008 3:36 PM
  • User139975600 posted

    by default, I believe the maximum upload filesize is 4 megs, so by setting the maxRequestLength in the web.config, you can up that.

    Wednesday, April 16, 2008 5:22 PM
  • User1289875885 posted

    I read your article mentioned above.

    I want to export html data into excel sheet.
    I used the code
    <script language="vbscript">

    Sub exportbutton_onclick

    Dim sHTML, oExcel, oBook

    sHTML = document.all.item("DataGrid1").outerhtml

    Set oExcel = CreateObject("Excel.Application")

    Set oBook = oExcel.Workbooks.Add

    oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML

    oBook.HTMLProject.RefreshDocument

    oExcel.Visible = true

    oExcel.UserControl = true

    End Sub

    </script>

    to export the html data.
    But in my gridview's data also contain one column as account number that is alphanumeric. If E is coming in the account number then it is picking it as a exponential and displaying numeric data. ex: if Ac# is 123456E89 then it is showing 12345.0000 something like this.
    But i want to display the account number as it is in the excel sheet.
    How to do this?
    I was using oBook.ActiveSheet.Columns("A:A").numberformat = "@"
    But above is giving me error.
    Kindly send me any prompt suggestion it will be helpful to me.

    Tuesday, July 8, 2008 1:44 AM
  • User636866211 posted

    Thank you so much, this solves my problem, too!

    Laughing

    Tuesday, August 25, 2009 10:29 AM
  • Tuesday, August 25, 2009 10:32 AM