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?
how do i do that?
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("Conten t-Disposit ion", "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.HtmlTextWrit er = New System.Web.UI.HtmlTextWrit er(oString Writer)
Me.ClearControls(DataGrid1 )
DataGrid1.RenderControl(oH tmlTextWri ter)
Response.Write(oStringWrit er.ToStrin g())
Response.End()
regards,
KS
'export to excel
Dim filename = "myFile"
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Conten
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.HtmlTextWrit
Me.ClearControls(DataGrid1
DataGrid1.RenderControl(oH
Response.Write(oStringWrit
Response.End()
regards,
KS
Make sure you change the grid name according to yours, here:
DataGrid1.RenderControl(oH tmlTextWri ter)
DataGrid1.RenderControl(oH
ASKER
getting error on while rendering
dg1.RenderControl(oHtmlTex tWriter) <<<<<
Control 'dg1__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
dg1.RenderControl(oHtmlTex
Control 'dg1__ctl2__ctl0' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.
ASKER
what is 'Me.ClearControls?
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 ?
>>>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
have it use a new datagrid .... without formatting ....
Greg
ASKER
>>>have it use a new datagrid .... without formatting ....
sorry, i did not understand what you mean
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
As for the number of rows ... limit it in your binding
Greg
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.HtmlTextWrit er(stringW rite)
'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(ByV al sender As System.Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
'export to excel
Common.DataGridToExcel(dgC ontracts, Response)
end sub
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.HtmlTextWrit
'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
End Sub
==========
here is im calling my datagrid.
=================
Private Sub btnExportToExcel_Click(ByV
'export to excel
Common.DataGridToExcel(dgC
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
' 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
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
?
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.
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
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("Conten t-Disposit ion", "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.HtmlTextWrit er = New System.Web.UI.HtmlTextWrit er(oString Writer)
Me.ClearControls(DataGrid1 )
DataGrid1.RenderControl(oH tmlTextWri ter)
Response.Write(oStringWrit er.ToStrin g())
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.Cont rols(i))
Next i
If Not TypeOf control Is TableCell Then
If Not (control.GetType().GetProp erty("Sele ctedItem") Is Nothing) Then
Dim literal As New LiteralControl
control.Parent.Controls.Ad d(literal)
Try
literal.Text = CStr(control.GetType().Get Property(" SelectedIt em").GetVa lue(contro l, Nothing))
Catch
End Try
control.Parent.Controls.Re move(contr ol)
Else
If Not (control.GetType().GetProp erty("Text ") Is Nothing) Then
Dim literal As New LiteralControl
control.Parent.Controls.Ad d(literal)
literal.Text = CStr(control.GetType().Get Property(" Text").Get Value(cont rol, Nothing))
control.Parent.Controls.Re move(contr ol)
End If
End If
End If
Return
End Sub 'ClearControls
Try it and let me know.
regards,
KS
'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("Conten
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.HtmlTextWrit
Me.ClearControls(DataGrid1
DataGrid1.RenderControl(oH
Response.Write(oStringWrit
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.Cont
Next i
If Not TypeOf control Is TableCell Then
If Not (control.GetType().GetProp
Dim literal As New LiteralControl
control.Parent.Controls.Ad
Try
literal.Text = CStr(control.GetType().Get
Catch
End Try
control.Parent.Controls.Re
Else
If Not (control.GetType().GetProp
Dim literal As New LiteralControl
control.Parent.Controls.Ad
literal.Text = CStr(control.GetType().Get
control.Parent.Controls.Re
End If
End If
End If
Return
End Sub 'ClearControls
Try it and let me know.
regards,
KS
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
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
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you are the man ... thank you thank you thank you :)
http://www.dotnetjohn.com/articles.aspx?articleid=78 VB.NET