将数据从gridview导出到excel的最佳方法 [英] Best way to export data from gridview to excel
问题描述
Hello Codeproject兄弟姐妹
我有这个问题,客户想要从gridview导出10,000行数据到excel。我知道这很疯狂,但这是要求。
我设法在gridview中显示10,000行数据而没有内存异常,但问题是当我导出它时到了excel。
我有这个代码,但它在这行上抛出内存异常
< pre lang =vb> datagridview1.RenderControl(hw)
我试图做一个解决方法。这个解决方法在我的本地工作正常但不是在现场机器上。
_ datagridview1.RenderBeginTag(hw)
_datagridview1.HeaderRow.RenderControl(hw)
对于 每个行作为 GridViewRow < span class =code-keyword>在 _datagridview1.Rows
row.RenderControl(hw)
Next
_datagridview1.FooterRow.RenderControl(hw)
_datagridview1.RenderEndTag(hw)
仍然是抛出内存异常。
我可以在应用程序池中做些什么吗?
或者有更好的方法吗?
我推荐3000行数据,但客户不需要。
谢谢大家
Mico
< span class =code-preprocessor>#RegionGridView Export
公共 Sub ExportGridView( ByVal gridview As GridView)
PrepareGridViewForExport(gridview)
RenderGridView(gridview)
结束 Sub
私有 Sub RenderGridView( ByVal gridview As GridView)
Dim allowSorting As Boolean = gridview.AllowSorting
gridview.AllowSorting = False
Dim attachment 作为 字符串 = < span class =code-string> attachment; filename = Export.xls
Page.Response.ClearContent()
Page.Response.AddHeader( content-disposition,附件)
Page.Response.ContentType = application / ms-excel
Dim sw As IO.StringWriter = 新 IO.StringWriter
Dim htw As HtmlTextWriter = 新 HtmlTextWriter(sw)
' 创建一个包含网格的表单
Dim frm As HtmlForm = 新 HtmlForm()
gridview.Parent.Controls.Add(frm)
frm.Attributes(跨度> < span class =code-string> runat)= server
frm.Controls.Add(gridview)
frm.RenderControl(htw)
' gridview .RenderControl(htw)
Page.Response.Write(sw.ToString)
Page.Response。 End ()
gridview.AllowSorting = allowSorting
结束 Sub
< span class =code-keyword>私有 Sub PrepareGridViewForExport( ByVal gv As Control)
Dim lb As LinkButton = 新 LinkButton
Dim l As Literal = 新 Literal
Dim name As String = 字符串 .Empty
Dim i 作为 整数 = 0
(i< gv.Controls.Count)
如果(gv.Controls(i)。 GetType GetType (LinkButton))或(gv。控件(i)。 GetType GetType (HyperLink))或(gv.Controls(i)。 GetType GetType (HyperLinkField))然后
l.Text = < span class =code-keyword> CType (gv.Controls(i),LinkButton).Text
gv.Controls.Remove(gv.Controls(i))
gv.Controls .AddAt(i,l)
ElseIf (gv.Controls(i)。 GetType < span class =code-keyword> GetType (DropDownLis t))然后
l.Text = CType (gv.Controls(i),DropDownList ).SelectedItem.Text
gv.Controls.Remove(gv.Controls(i))
gv.Controls.AddAt(i,l)
ElseIf (gv.Controls(i)。 GetType GetType (CheckBox))然后
l.Text = CType (gv.Controls(i),CheckBox).Checked
gv.Controls.Remove(gv.Controls(i))
gv.Controls.AddAt(i,l)
结束 如果
如果 gv.Controls( i).HasControls 然后
PrepareGridViewForExport(gv.Controls(i))
结束 如果
i + = 1
循环
结束 Sub
#End Region
Hello Codeproject brothers and sisters
I have this issue that the client wants to export 10,000 rows of data from gridview to excel. I know this is crazy but it is the requirement.
I managed to display the 10,000 rows of data in gridview without having memory exception but the problem is when i exported it to the excel.
I have this code but it is throwing memory exception on this line
datagridview1.RenderControl(hw)
I tried to do a workaround.This workaround works fine on my local but not on live machine.
_datagridview1.RenderBeginTag(hw)
_datagridview1.HeaderRow.RenderControl(hw)
For Each row As GridViewRow In _datagridview1.Rows
row.RenderControl(hw)
Next
_datagridview1.FooterRow.RenderControl(hw)
_datagridview1.RenderEndTag(hw)
Still it is throwing memory exception.
Is there anything that i can do in the application pool?
Or is there a better way to do this?
I recommend 3000 rows of data but client doesn't want.
Thanks Guys
Mico
#Region "GridView Export" Public Sub ExportGridView(ByVal gridview As GridView) PrepareGridViewForExport(gridview) RenderGridView(gridview) End Sub Private Sub RenderGridView(ByVal gridview As GridView) Dim allowSorting As Boolean = gridview.AllowSorting gridview.AllowSorting = False Dim attachment As String = "attachment; filename=Export.xls" Page.Response.ClearContent() Page.Response.AddHeader("content-disposition", attachment) Page.Response.ContentType = "application/ms-excel" Dim sw As IO.StringWriter = New IO.StringWriter Dim htw As HtmlTextWriter = New HtmlTextWriter(sw) 'Create a form to contain the grid Dim frm As HtmlForm = New HtmlForm() gridview.Parent.Controls.Add(frm) frm.Attributes("runat") = "server" frm.Controls.Add(gridview) frm.RenderControl(htw) 'gridview.RenderControl(htw) Page.Response.Write(sw.ToString) Page.Response.End() gridview.AllowSorting = allowSorting End Sub Private Sub PrepareGridViewForExport(ByVal gv As Control) Dim lb As LinkButton = New LinkButton Dim l As Literal = New Literal Dim name As String = String.Empty Dim i As Integer = 0 Do While (i < gv.Controls.Count) If (gv.Controls(i).GetType Is GetType(LinkButton)) Or (gv.Controls(i).GetType Is GetType(HyperLink)) Or (gv.Controls(i).GetType Is GetType(HyperLinkField)) Then l.Text = CType(gv.Controls(i), LinkButton).Text gv.Controls.Remove(gv.Controls(i)) gv.Controls.AddAt(i, l) ElseIf (gv.Controls(i).GetType Is GetType(DropDownList)) Then l.Text = CType(gv.Controls(i), DropDownList).SelectedItem.Text gv.Controls.Remove(gv.Controls(i)) gv.Controls.AddAt(i, l) ElseIf (gv.Controls(i).GetType Is GetType(CheckBox)) Then l.Text = CType(gv.Controls(i), CheckBox).Checked gv.Controls.Remove(gv.Controls(i)) gv.Controls.AddAt(i, l) End If If gv.Controls(i).HasControls Then PrepareGridViewForExport(gv.Controls(i)) End If i += 1 Loop End Sub #End Region
这篇关于将数据从gridview导出到excel的最佳方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!