datagrid - export to excel?

i have a datagrid and whatever the data i have in my datagrid i want to export to Excel

how do i do that?
alldayAsked:
Who is Participating?
 
nisarkhanCommented:
this code can be run outside of page, or can be converted to run in and extended datagrid control

imports System.Drawing
Imports System.Web.UI
Imports System.Web.UI.WebControls


Namespace demetz


    Public Class DemExportGridExcel

        Sub RenderGridToExcelFormat(ByVal grid As DataGrid, ByVal saveAsFile As String)
            ' check Excel rows limit
            If grid.Items.Count.ToString + 1 < 65536 Then
                HttpContext.Current.Response.Clear()
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
                HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" & saveAsFile & ".xls")
                ' Remove the charset from the Content-Type header.
                HttpContext.Current.Response.Charset = ""
                'HttpContext.Current.Response.WriteFile("style.txt")
                ' Turn off the view state.
                grid.EnableViewState = False
                Dim tw As New System.IO.StringWriter()
                Dim hw As New System.Web.UI.HtmlTextWriter(tw)
                ' Get the HTML for the control.
                grid.HeaderStyle.ForeColor = Color.Black
                grid.HeaderStyle.BackColor = Color.Red
                grid.ItemStyle.ForeColor = Color.Black
                grid.BorderColor = Color.White
                ClearControls(grid)
                grid.RenderControl(hw)
                ' Write the HTML back to the browser.
                HttpContext.Current.Response.Write(tw.ToString())
                ' End the response.
                HttpContext.Current.Response.End()
            Else
               
                HttpContext.Current.Response.Write("Too many rows - Export to Excel not possible")
            End If
        End Sub

        Sub ClearControls(ByVal control As Control)
            Dim i As Integer
            For i = control.Controls.Count - 1 To 0 Step -1
                ClearControls(control.Controls(i))
            Next i

            If TypeOf control Is System.Web.UI.WebControls.Image Then
                control.Parent.Controls.Remove(control)
            End If

            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

    End Class

End Namespace


0
 
Ramesh SrinivasTechnical ConsultantCommented:
Add this code inside your button sub:

        'export to excel
        Dim filename = "myFile"
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")
        Response.ContentEncoding = System.Text.Encoding.UTF7
        Response.Charset = ""
        Me.EnableViewState = False

        Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
        Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

        Me.ClearControls(DataGrid1)
        DataGrid1.RenderControl(oHtmlTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()

regards,

KS
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Ramesh SrinivasTechnical ConsultantCommented:
Make sure you change the grid name according to yours, here:

DataGrid1.RenderControl(oHtmlTextWriter)
0
 
alldayAuthor Commented:
getting error on while rendering

dg1.RenderControl(oHtmlTextWriter) <<<<<
Control 'dg1__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
0
 
alldayAuthor Commented:
what is 'Me.ClearControls?
0
 
alldayAuthor Commented:
gregoryyoung:

>>>http://www.dotnetjohn.com/articles.aspx?articleid=78 VB.NET

i follow the above link and it open the excel but what i see is that it capture all my controls and put on the excel sheet

also,
is there a way i can give the number of rows from datagrid to export excel ?

0
 
gregoryyoungCommented:
"capture all my controls and put on the excel sheet"

have it use a new datagrid .... without formatting ....

Greg
0
 
alldayAuthor Commented:
>>>have it use a new datagrid .... without formatting ....

sorry, i did not understand what you mean

0
 
gregoryyoungCommented:
Instead of using the existant datagrid (with your controls) have it create a new DataGrid ... DataGrid = new DataGrid() without any formatting as yours has ...

As for the number of rows ... limit it in your binding

Greg
0
 
alldayAuthor Commented:
regardless whether i give the datagrid = new datagrid()

still its giving me the controls in my excel sheet

here is my code which im using

==========
Public Shared Sub DataGridToExcel(ByVal dgExport As DataGrid, ByVal response As HttpResponse)
        'clean up the response.object
        response.Clear()
        response.Charset = ""
        'set the response mime type for excel
        response.ContentType = "application/vnd.ms-excel"
        'create a string writer
        Dim stringWrite As New System.IO.StringWriter
        'create an htmltextwriter which uses the stringwriter
        Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

        'instantiate a datagrid
        Dim dg As New DataGrid
        ' just set the input datagrid = to the new dg grid
        dg = dgExport

        ' I want to make sure there are no annoying gridlines
        dg.GridLines = GridLines.None
        ' Make the header text bold
        dg.HeaderStyle.Font.Bold = True

        ' If needed, here's how to change colors/formatting at the component level
        dg.HeaderStyle.ForeColor = System.Drawing.Color.Black
        dg.ItemStyle.ForeColor = System.Drawing.Color.Black

        'bind the modified datagrid
        dg.DataBind()
        'tell the datagrid to render itself to our htmltextwriter
        dg.RenderControl(htmlWrite)
        'output the html
        response.Write(stringWrite.ToString)

    End Sub


==========
here is im calling my datagrid.
=================

  Private Sub btnExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
        'export to excel
        Common.DataGridToExcel(dgContracts, Response)
end sub
0
 
gregoryyoungCommented:
       Dim dg As New DataGrid
        ' just set the input datagrid = to the new dg grid
        dg = dgExport

Umm why are you using dg=dgExport after declaring a new datagrid? That datagrid has controls in it ... if you want to remove formatting then take that line out and use the New DataGrid()

Greg
0
 
alldayAuthor Commented:
i remove the dg= dgExport and what im getting now is

first few rows, displaying all my controls like

textbox, buttons and then

it display rows like 20 rows and then it also display the number of pages

in other words its just like print screen in excel

?
0
 
Ramesh SrinivasTechnical ConsultantCommented:
If you don't need to display it in the browser but just save it to disc - then you could loop through your dataset and insert row-by-row into excel. Its alot more flexible and you have greater control over formatting. Just a thought.
0
 
alldayAuthor Commented:
saleek:

i just want

when the user click on 'export to excel' then just open a pop window asking for where to save and save all my datagrid rows in the excel and i don't care the formarting what i care is just dump all my rows inside the excel

can i have the code for that?

thanks
0
 
Ramesh SrinivasTechnical ConsultantCommented:
Well in that case it the code i posted earlier:

'export to excel - add this in your button click sub
        Dim filename = "myFile"
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")
        Response.ContentEncoding = System.Text.Encoding.UTF7
        Response.Charset = ""
        Me.EnableViewState = False

        Dim oStringWriter As System.IO.StringWriter = New System.IO.StringWriter
        Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(oStringWriter)

        Me.ClearControls(DataGrid1)
        DataGrid1.RenderControl(oHtmlTextWriter)
        Response.Write(oStringWriter.ToString())
        Response.End()

You were getting an error - i remember i got this too, but i'm having trouble remembering how i got round it!

Doh! I didnt even mention the function ClearControls did i??? Add thefollowing also:

'add this anywhere
Private Sub ClearControls(ByVal 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

Try it and let me know.

regards,

KS
0
 
alldayAuthor Commented:
KS:

i have 10 pages in datagrid and everypage have 20 rows, so when i click on export to excel its only fetching 20 rows to excel sheet and its not fetching all which is 10*20 = 200 rows

how should i get around
0
 
alldayAuthor Commented:
also i find that 2 thing:

1) the dialog box popup to open a excel and i click open and then again i have to click open twice in order to open excel (dunno why?)

2)

i have a button called - export to excel

once the user click on that button, im disabling the whole page which means like disabled all controls on the page but whats happening here.. after i click on export-excel it opens the excel window and after i close the excel window my page still disabled all controls, how do i go back to normal page and also i see that response.end which means it will end there no more execution rite?


thanks
0
 
alldayAuthor Commented:
saleek ????????
0
 
Ramesh SrinivasTechnical ConsultantCommented:
allday,

This code (as far as I know) will only save to excel those rows which are visible in datagrid. The other rows are not in the datagrid, so they will not be exported.

Response.End means that the excel content type will end.

You should not have any problems going back and forth - i have not come across this - I will have a look and see if I can reproduce the problem.
0
 
alldayAuthor Commented:
you are the man ... thank you thank you thank you :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.