[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

datagrid - export to excel?

Posted on 2005-05-03
21
Medium Priority
?
2,257 Views
Last Modified: 2008-03-25
i have a datagrid and whatever the data i have in my datagrid i want to export to Excel

how do i do that?
0
Comment
Question by:allday
  • 11
  • 5
  • 4
  • +1
21 Comments
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 13925001
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
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 13925003
Make sure you change the grid name according to yours, here:

DataGrid1.RenderControl(oHtmlTextWriter)
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:allday
ID: 13948727
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
 

Author Comment

by:allday
ID: 13948729
what is 'Me.ClearControls?
0
 

Author Comment

by:allday
ID: 13948744
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13948821
"capture all my controls and put on the excel sheet"

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

Greg
0
 

Author Comment

by:allday
ID: 13951921
>>>have it use a new datagrid .... without formatting ....

sorry, i did not understand what you mean

0
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13952194
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
 

Author Comment

by:allday
ID: 13970404
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
 
LVL 37

Expert Comment

by:gregoryyoung
ID: 13970453
       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
 

Author Comment

by:allday
ID: 13970655
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
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 13975881
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
 

Author Comment

by:allday
ID: 13980834
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
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 13984323
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
 

Author Comment

by:allday
ID: 13999955
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
 

Author Comment

by:allday
ID: 14000199
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
 

Author Comment

by:allday
ID: 14011865
saleek ????????
0
 
LVL 11

Expert Comment

by:Ramesh Srinivas
ID: 14016811
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
 
LVL 7

Accepted Solution

by:
nisarkhan earned 2000 total points
ID: 14032946
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
 

Author Comment

by:allday
ID: 14033406
you are the man ... thank you thank you thank you :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month19 days, 11 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question