Display an ASP.NET DataGrid in Microsoft Excel

I was putting the finishing touches on a web application tonight when I discovered something really cool...

I've got a form where a user can select a whole slew of parameters to build a dynamic query and, once the results have been fetched I needed to render them as either:

  1. A plain, sortable, pageable DataGrid
  2. Rendered in a dynamically generated GUI
  3. Opened in a Microsoft Excel worksheet

I had already done the plain grid and the dynamic UI stuff - which, in itself was pretty cool - and tonight set about the task of doing the Excel part. The first place that I went to was the ASP.NET Support Center. This is an incredible resource and, if you haven't already done so, I'd suggest that you spend some time scouring through the "How To's" on that site. Anyways, I plugged the term: "Excel" into the "Search (KB) ASP.NET" form and submitted. I slid my eyes down the page about 10 items until they landed upon: "HOW TO: Export Data in a DataGrid on an ASP . NET WebForm to Microsoft Excel"... VOILA! Perfection. This was my resultant code to render the Excel Worksheet:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    BindGrid()
    RenderGrid()
End Sub

Private Sub BindGrid()
    Dim dtResults As DataTable = CType(Session("DynamicFormResults"), DataTable)
    If Not dtResults Is Nothing Then
        DataGrid1.DataSource = dtResults.DefaultView
        DataGrid1.DataBind()
    End If
End Sub

Private Sub RenderGrid()
    Response.ContentType = "application/vnd.ms-excel"
    ' Remove the charset from the Content-Type header.
    Response.Charset = ""
    ' Turn off the view state.
    Me.EnableViewState = False
    Dim tw As New System.IO.StringWriter
    Dim hw As New System.Web.UI.HtmlTextWriter(tw)
    ' Get the HTML for the control.
    DataGrid1.RenderControl(hw)
    ' Write the HTML back to the browser.
    Response.Write(tw.ToString())
    ' End the response.
    Response.End()
End Sub

36 Comments

  • How about adding a content disposition as file attachement after removing the charset ?

  • I agree this is something really cool. However I came across one issue where when I try it with a datagrid that contains server side controls (such as an edit button for each row) then I get "Control '_ctl2_EditButton' of type 'ImageButton' must be placed inside a form tag with runat=server".



    There are two ways I thought this could be solved:



    1. delimit the datagrid in form tags and send that to htmltextwriter

    2. remove the server controls from the datagrid to avoid the error



    Anybody know of an easy way to achieve the 2nd? Both seem to be a real pain.



    Thanks

  • Thanks for all of the useful feedback guys. Very helpful!

  • i've tried that code converted to VB, but i still got the same error "xxx must be placed inside a form tag with runat=server". any ideas? here's my ClearControls function:





    Private Sub ClearControls(ByVal control As Control)

    Dim i As Integer

    For i = control.Controls.Count - 1 To i >= 0

    ClearControls(control.Controls(i))

    Next



    If Not (control.GetType().GetProperty("SelectedItem") Is Nothing) Then

    Dim literal As New LiteralControl

    control.Parent.Controls.Add(literal)

    Try

    literal.Text = control.GetType().GetProperty("SelectedItem").GetValue(control, Nothing)

    Catch

    End Try

    control.Parent.Controls.Remove(control)

    Else

    If Not (control.GetType().GetProperty("Text") Is Nothing) Then

    Dim literal As New LiteralControl

    control.Parent.Controls.Add(literal)

    literal.Text = control.GetType().GetProperty("Text").GetValue(control, Nothing)

    control.Parent.Controls.Remove(control)

    End If

    End If

    End Sub





  • wrong code, here it is...i get an empty grid in excel:





    Private Sub ClearControls(control As Control)

    Dim i As Integer

    For i = control.Controls.Count - 1 To 0 Step -1

    ClearControls(control.Controls(i))

    Next i



    If Not TypeOf control Is TableCell Then

    If Not (control.GetType().GetProperty("SelectedItem") Is Nothing) Then

    Dim literal As New LiteralControl()

    control.Parent.Controls.Add(literal)

    Try

    literal.Text = CStr(control.GetType().GetProperty("SelectedItem").GetValue(control, Nothing))

    Catch

    End Try

    control.Parent.Controls.Remove(control)

    Else

    If Not (control.GetType().GetProperty("Text") Is Nothing) Then

    Dim literal As New LiteralControl()

    control.Parent.Controls.Add(literal)

    literal.Text = CStr(control.GetType().GetProperty("Text").GetValue(control, Nothing))

    control.Parent.Controls.Remove(control)

    End If

    End If

    End If

    Return

    End Sub 'ClearControls

  • If we clear controls, that looks fine, but still exports current webpage to Excel only. How can we export all pages to Excel?

    Meantime, after exported the DataGrid to Excel, all mouse click event on the webpage cannot works any more, and generated errors on the browser's left bottom corner. How to solve it?

  • hi,



    make sure the form tag of the aspx page has the runar="server" attribute.



    sometimes, when we alter the form tag properties, or manually write the form tag, we might miss this attribute, which generates similar errors.



    hope this helps

  • no, not really.



    I've been playing around with the ClearControls sub, but indeed, that renders an empty aspx page.



    I wrote my own sub, which checks the servercontrol types, and replaces them. Columns with a Linkbutton are removed.



    However, apparently this (removing columns) can no longer be done once the grid is bound. This means you need to tell the function in advance what columns should be dropped?



    Who can give me a workable sollution post-binding?



    My code : [quote]

    Private Sub Replace_WebControls()

    'dimension integers to loop through the datagrid

    '+ a string to hold the controlType

    Dim iRows As Integer

    Dim iCols As Integer

    Dim iCtls As Integer



    Dim sCtlType As String



    Dim lblTemp As Label

    Dim txtTemp As TextBox

    Dim chkTemp As CheckBox

    Dim rbtTemp As RadioButton

    Dim hrefTemp As HyperLink

    Dim lbtTemp As LinkButton

    Dim tmpCol As DataGridColumn



    'dimension a boolean to define column removal

    Dim blDeleteCol(dtgExcel.Columns.Count - 1) As Boolean



    For iRows = dtgExcel.Items.Count - 1 To 0 Step -1

    With dtgExcel.Items(iRows)

    For iCols = dtgExcel.Columns.Count - 1 To 0 Step -1

    For iCtls = .Cells(iCols).Controls.Count - 1 To 0 Step -1

    sCtlType = .Cells(iCols).Controls(iCtls).GetType.ToString.ToLower

    'myResponse.Write("col " & iCols & " - ctl " & iCtls & " : " & sCtlType & "<BR>")

    If InStr(sCtlType, "webcontrols") > 0 Then

    Select Case sCtlType

    Case "system.web.ui.webcontrols.label"

    Dim lcTemp As New LiteralControl

    lblTemp = CType(.Cells(iCols).Controls(iCtls), Label)

    lcTemp.Text = lblTemp.Text

    .Cells(iCols).Controls.Remove(lblTemp)

    .Cells(iCols).Controls.Add(lcTemp)

    Case "system.web.ui.webcontrols.textbox"

    Dim lcTemp As New LiteralControl

    txtTemp = CType(.Cells(iCols).Controls(iCtls), TextBox)

    lcTemp.Text = txtTemp.Text

    .Cells(iCols).Controls.Remove(txtTemp)

    .Cells(iCols).Controls.Add(lcTemp)

    Case "system.web.ui.webcontrols.checkbox"

    Dim lcTemp As New LiteralControl

    chkTemp = CType(.Cells(iCols).Controls(iCtls), CheckBox)

    If CBool(chkTemp.Checked) Then

    lcTemp.Text = "Yes"

    Else

    lcTemp.Text = "No"

    End If

    .Cells(iCols).Controls.Remove(chkTemp)

    .Cells(iCols).Controls.Add(lcTemp)

    Case "system.web.ui.webcontrols.radiobutton"

    Dim lcTemp As New LiteralControl

    rbtTemp = CType(.Cells(iCols).Controls(iCtls), RadioButton)

    If CBool(rbtTemp.Checked) Then

    lcTemp.Text = "Yes"

    Else

    lcTemp.Text = "No"

    End If

    .Cells(iCols).Controls.Remove(rbtTemp)

    .Cells(iCols).Controls.Add(lcTemp)

    Case "system.web.ui.webcontrols.hyperlink"

    Dim lcTemp As New LiteralControl

    hrefTemp = CType(.Cells(iCols).Controls(iCtls), HyperLink)

    If "" & hrefTemp.NavigateUrl <> "" Then

    lcTemp.Text = "<a href=" & hrefTemp.NavigateUrl & ">" & hrefTemp.Text & "</a>"

    Else

    lcTemp.Text = hrefTemp.Text

    End If

    .Cells(iCols).Controls.Remove(hrefTemp)

    .Cells(iCols).Controls.Add(lcTemp)

    Case "system.web.ui.webcontrols.linkbutton"

    tmpCol = CType(dtgExcel.Columns(iCols), DataGridColumn)

    dtgExcel.Columns.Remove(tmpCol)

    End Select

    End If

    Next iCtls

    Next iCols

    End With

    Next iRows

    End Sub

    [/quote]

  • lol, 12:16 AM. over here (Belgium) it is only 15:17 PM, on the 29th :-)

  • My problem seems to have to do with the binding, indeed. If I add "dtgExcel.databind" at the very end of the above code, the error "Control 'grid_ctl1_ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server" is no longer cast. However, now my page is completely blank (because all literals are unbound, of course).

  • can somebody please help me display a histogram chart in the excel sheet on which we are displaying data from the datagrid.



    please reply to my ID: Brajabhusan_Panda@infosys.com

    thanks and regards

  • Yes, I've figured out how to successfully export to excel but I only get the,

    'DataGridLinkButton' must be placed inside a form tag with runat=server error when paging is allowed and you have more than one page of data within your grid. I did find that the error does not occur if you allow the number of lines per page to be large enough not to have to page. Not sure how to handle this for all instances.

  • I got the code to return an aspx file instead of a .xls file.. Any Ideas?

  • You can remove paging from datagrid. After that no error will found.

    Other way you create same grig without button and paging. While you send to excel, visible grid , when complete write to excel invisible to grid.

  • Dan, The xls being returned as an aspx page typically occurs if you do not have Excel installed, or do not have the xls type tied to excel on your CLIENT computer.



    I have several machines, but excel only on one. And It works fine on that box, but all the others think they are getting an aspx page, since they have no idea what xls is.

  • Hi,

    I tried your sample.

    When the excel sheet opens in the ie, no data is displayed. I have office 2000 installed in my PC

  • I'm using the same code to display datalist in Excel. But somwhow i couldnt managed to display correctly . My datalist contains Datagrid. Because Datagrid is a control, it clears the datagrid control. so i check if the control is DataGrid then do nothing. it works well. this time the order of the datalist items is wrong. Before the Datagrid there are tables and label but the layout of the excel is datagrid and table-label...

  • hi,

    i tried the code



    Private Sub RenderGrid()

    Response.ContentType = "application/vnd.ms-excel"

    ' Remove the charset from the Content-Type header.

    Response.Charset = ""

    ' Turn off the view state.

    Me.EnableViewState = False

    Dim tw As New System.IO.StringWriter

    Dim hw As New System.Web.UI.HtmlTextWriter(tw)

    ' Get the HTML for the control.

    DataGrid2.RenderControl(hw)

    ' Write the HTML back to the browser.

    Response.Write(tw.ToString())

    ' End the response.

    Response.End()

    End Sub





    but i get an error









    Server Error in '/application' Application.

    --------------------------------------------------------------------------------



    Control 'DataGrid2__ctl3__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

  • I have created a link on one of my pages to export the datagrid to excel .

    when I click the link the first time , i get an empty sheet , when I click the link again , it shows the grid with the records .



    can anyone tell what I am doing wrong.



    thanks in advance

    Sanjay

  • When you get this:



    Control 'DataGrid2__ctl3__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.



    Make sure that in your aspx page your control is inside a form tag that has the attribute runat=server (just like it says)



    like this ...



    <form id='frmMain' runat='server'>

  • The datagrid has successfully emport to excel.but when it can reaponse to any mouse event(like format cell, delete) .Can anyone tell me why and how to solve it

  • I am able to display all records in my datagrid on an excel file. I am using a dataview that displays 10 records at a time for each page. I want to display just one page of the dataview (the one they are currently viewing) when the "export to excel" button is clicked. how can i do this with a dataview?

  • If you want to get rid of the errors like "Control 'DataGrid2__ctl3__ctl0' of type 'DataGridLinkButton' " Just set the properties on the grid that display these. You don't want them in the Excel Sheet anyways.



    IE dg.AllowPaging = False



    Do this before you send to the sub routine or do it in the sub routine.



    Any one know how to do this same thing in a new window?



    Mark

  • datagrid.columns(index).visible =false

    Please try this for Server control columns in datagrid

    Thanks

    pradeep

  • Problem rectified,
    Add
    datagrid.AllowSorting = false;

    datagrid.AllowPaging = false;
    on the export button

  • thanks a ton!
    this was really useful to me.

  • I saw all the posts for exporting a datagrid to excel
    this code works fine for me but I have an issue on formating the output to excell it autoformats data is there a way to turn that off?
    Dim attachment As String = "attachment; filename=Information.xls"

    Response.ClearContent()
    Response.AddHeader("content-disposition", attachment)
    Response.ContentType = "application/ms-excel"


    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    Dim htW As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
    'HtmlTextWriter(htw = New HtmlTextWriter(sw))
    'Create a form to contain the grid
    Dim frm As HtmlForm = New HtmlForm()
    GridView2.Parent.Controls.Add(frm)

    frm.Attributes("runat") = "server"
    frm.Controls.Add(GridView2)
    frm.RenderControl(htW)
    Response.Write(sw.ToString())

    Response.End()

  • That's great, I never thought about Display an ASP.NET DataGrid in Microsoft Excel like that before.

  • Wow, I never knew that Display an ASP.NET DataGrid in Microsoft Excel. That's pretty interesting...

  • Good post, but have you thought about Display an ASP.NET DataGrid in Microsoft Excel before?

  • It seems can not run properly in HTTPS server. The Error show that can not find the attachment..

  • How can we export all data means including all page data into excel?

  • Wonderful. Zer eta definitly nire eskerrak eman nahi duzu duzu merezi!

  • Cheers for the post, definitly something which i found fascinating, enough to make me drop this comment.

  • Bravo! A zer definitly merezi iruzkin bat merezi, eskerrik asko.

  • Bravo! A zer definitly merezi iruzkin bat merezi, eskerrik asko.

Comments have been disabled for this content.