Export Gridview data to Excel

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.

Answer 1

User139975600 posted

Hi EssCee,

I have provided code and explaination at the following location:

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

Hope this helps!

Answer 2

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.

Answer 3

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.

 

Answer 4

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.

Answer 5

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?

Answer 6

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!

Answer 7

User139975600 posted

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

Answer 8

User-1093373388 posted

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

Answer 9

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>

Answer 10

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.

Answer 11

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.

Answer 12

User636866211 posted

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

Answer 13

User-666286296 posted

 HI

Try this

http://exporttoexcel.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=10267

Thanks