Link to home
Start Free TrialLog in
Avatar of allday
allday

asked on

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?
Avatar of gregoryyoung
gregoryyoung
Flag of Canada image

Avatar of Ramesh Srinivas
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
Make sure you change the grid name according to yours, here:

DataGrid1.RenderControl(oHtmlTextWriter)
Avatar of allday
allday

ASKER

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.
Avatar of allday

ASKER

what is 'Me.ClearControls?
Avatar of allday

ASKER

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 ?

"capture all my controls and put on the excel sheet"

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

Greg
Avatar of allday

ASKER

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

sorry, i did not understand what you mean

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
Avatar of allday

ASKER

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
       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
Avatar of allday

ASKER

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

?
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.
Avatar of allday

ASKER

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
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
Avatar of allday

ASKER

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
Avatar of allday

ASKER

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
Avatar of allday

ASKER

saleek ????????
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.
ASKER CERTIFIED SOLUTION
Avatar of nisarkhan
nisarkhan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of allday

ASKER

you are the man ... thank you thank you thank you :)