当前位置： 文江博客话题详情

将多个网格视图导出到 Excel

发布于 2024-10-15 16:41:15 字数 258 浏览 6 评论 0原文

我有多个网格视图要导出到 Excel。每个工作表 1 个网格视图。

怎么办呢？ （或者一般来说如何将 html 字符串写入工作表）

这是在服务器端 asp.net 中完成的。

我可以通过 epplus.codeplex.com 很好地创建多个工作表，但它适用于单元格级别。我可以导出DataTable，但是如何导出Gridview的HTML？

原文

I have multiple gridviews to be exported to excel. 1 gridview per worksheet.

How to do that ? ( or in general how to write an html string to a worksheet )

This is to be done in asp.net , server side.

I can create multiple worksheets via epplus.codeplex.com nicely, but it works on cell level. I could export DataTable, but how to export Gridview's HTML ?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（4）

俏︾媚 2024-10-22 16:41:15

我没有找到“clist”的定义地点和方式

For Each gView As GridView In cList
    CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
Next

I didn´t find where and how was "clist" defined

For Each gView As GridView In cList
    CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
Next
回复 原文
送你一个梦 2024-10-22 16:41:15

我不确定这是否适用于 GridView，但值得一试：

http:// www.codeproject.com/KB/office/ExcelDataTable.aspx

编辑：它仍然有效，用以下代码进行测试：

        Dim data As New DataTable("Sample-Data")
        data.Columns.Add("ID", GetType(Int32)).AutoIncrement = True
        data.Columns.Add("Text", GetType(String))
        For i As Int32 = 1 To 100
            Dim newRow As DataRow = data.NewRow
            newRow("Text") = i & ". Row"
            data.Rows.Add(newRow)
        Next

        Dim grid As New System.Web.UI.WebControls.GridView
        grid.HeaderStyle.Font.Bold = True
        grid.DataSource = data
        grid.DataMember = data.TableName
        grid.DataBind()

        ' render the GridView control to a file '
        Using sw As New IO.StreamWriter("c:\\Temp\test.xls")
            Using hw As New HtmlTextWriter(sw)
                grid.RenderControl(hw)
            End Using
        End Using

技巧是 Excel 可以显示 HTML 表格，而 GridView 则呈现为表格。

但是，如果您想生成多个工作表，则这不起作用。

I'm not sure if this works with GridView, but it's worth a try:

http://www.codeproject.com/KB/office/ExcelDataTable.aspx

Edit: it still works, tested with the following code:

        Dim data As New DataTable("Sample-Data")
        data.Columns.Add("ID", GetType(Int32)).AutoIncrement = True
        data.Columns.Add("Text", GetType(String))
        For i As Int32 = 1 To 100
            Dim newRow As DataRow = data.NewRow
            newRow("Text") = i & ". Row"
            data.Rows.Add(newRow)
        Next

        Dim grid As New System.Web.UI.WebControls.GridView
        grid.HeaderStyle.Font.Bold = True
        grid.DataSource = data
        grid.DataMember = data.TableName
        grid.DataBind()

        ' render the GridView control to a file '
        Using sw As New IO.StreamWriter("c:\\Temp\test.xls")
            Using hw As New HtmlTextWriter(sw)
                grid.RenderControl(hw)
            End Using
        End Using

The trick is that excel can display a HTML-Table and a GridView is rendered as a Table.

However this does not work if you want to generate multiple WorkSheets.

回复 原文
探春 2024-10-22 16:41:15

Excel 与 XML 完美配合。

将 Excel 中的一些工作簿另存为 XML 表并在记事本中打开它以查看其工作原理。

Excel perfectly works with XML.

Save some workbook from Excel as XML table and open it in notepad to see how it works.

回复 原文
凝望流年 2024-10-22 16:41:15

我刚刚做了同样的事情。每个数据库 talbe/gridview 都有一个 excel 工作表

您可以在 这篇博文。如果您需要更多帮助，请告诉我。

Public Shared Sub CreateWorkBook(ByVal cList As Object, ByVal wbName As String, ByVal CellWidth As Integer)
    Dim attachment As String = "attachment; filename=""" & wbName & ".xml"""

    HttpContext.Current.Response.ClearContent()

    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"

    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    sw.WriteLine("<?xml version=""1.0""?>")

    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")

    sw.WriteLine("xmlns:o=""urn:schemas-microsoft-com:office:office""")
    sw.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")

    sw.WriteLine("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
    sw.WriteLine("xmlns:html=""http://www.w3.org/TR/REC-html40"">")

    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
    sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>")

    sw.WriteLine("<Created>2010-05-15T19:14:19Z</Created>")
    sw.WriteLine("<Version>11.9999</Version>")

    sw.WriteLine("</DocumentProperties>")
    sw.WriteLine("<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")

    sw.WriteLine("<WindowHeight>9210</WindowHeight>")
    sw.WriteLine("<WindowWidth>19035</WindowWidth>")

    sw.WriteLine("<WindowTopX>0</WindowTopX>")
    sw.WriteLine("<WindowTopY>90</WindowTopY>")

    sw.WriteLine("<ProtectStructure>False</ProtectStructure>")
    sw.WriteLine("<ProtectWindows>False</ProtectWindows>")

    sw.WriteLine("</ExcelWorkbook>")
    sw.WriteLine("<Styles>")

    sw.WriteLine("<Style ss:ID=""Default"" ss:Name=""Normal"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom""/>")

    sw.WriteLine("<Borders/>")
    sw.WriteLine("<Font/>")

    sw.WriteLine("<Interior/>")
    sw.WriteLine("<NumberFormat/>")

    sw.WriteLine("<Protection/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s22"">")
    sw.WriteLine("<Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("<Font ss:Bold=""1""/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s23"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("</Style>")
    sw.WriteLine("<Style ss:ID=""s24"">")

    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")
    sw.WriteLine("<Borders>")

    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("</Borders>")
    sw.WriteLine("<Font ss:Color=""#FFFFFF""/>")

    sw.WriteLine("<Interior ss:Color=""#FF6A6A"" ss:Pattern=""Solid""/>") 'set header colour here
    sw.WriteLine("</Style>")

    sw.WriteLine("</Styles>")
    For Each gView As GridView In cList
        CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
    Next
    sw.WriteLine("</Workbook>")
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

End Sub
Private Shared Sub CreateWorkSheet(ByVal wsName As String, ByVal sw As System.IO.StringWriter, ByVal gv As GridView, ByVal cellwidth As Integer)
    If IsNothing(gv.HeaderRow) = False Then
        sw.WriteLine("<Worksheet ss:Name=""" & wsName & """>")
        Dim cCount As Integer = gv.HeaderRow.Cells.Count
        Dim rCount As Long = gv.Rows.Count + 1
        sw.WriteLine("<Table ss:ExpandedColumnCount=""" & cCount & """ ss:ExpandedRowCount=""" & rCount & """ x:FullColumns=""1""")
        sw.WriteLine("x:FullRows=""1"">")
        For i As Integer = (cCount - cCount) To (cCount - 1)
            sw.WriteLine("<Column ss:AutoFitWidth=""1"" ss:Width=""" & cellwidth & """/>")
        Next

        GridRowIterate(gv, sw)
        sw.WriteLine("</Table>")
        sw.WriteLine("<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")

        sw.WriteLine("<Selected/>")
        sw.WriteLine("<DoNotDisplayGridlines/>")

        sw.WriteLine("<ProtectObjects>False</ProtectObjects>")
        sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>")

        sw.WriteLine("</WorksheetOptions>")
        sw.WriteLine("</Worksheet>")
    End If
End Sub
Private Shared Sub GridRowIterate(ByVal gv As GridView, ByVal sw As System.IO.StringWriter)
    sw.WriteLine("<Row>")

    For Each tc As TableCell In gv.HeaderRow.Cells
        Dim tcText As String = tc.Text

        Dim tcWidth As String = gv.Width.Value
        Dim dType As String = "String"

        If IsNumeric(tcText) = True Then

            dType = "Number"

        End If
        sw.WriteLine("<Cell ss:StyleID=""s24""><Data ss:Type=""String"">" & tcText & "</Data></Cell>")

    Next
    sw.WriteLine("</Row>")

    For Each gr As GridViewRow In gv.Rows
        sw.WriteLine("<Row>")

        For Each gc As TableCell In gr.Cells
            Dim gcText As String = gc.Text
            Dim dType As String = "String"

            If IsNumeric(gcText) = True Then

                dType = "Number"
                gcText = CDbl(gcText)

            End If
            sw.WriteLine("<Cell ss:StyleID=""s23""><Data ss:Type=""" & dType & """>" & gcText & "</Data></Cell>")

        Next
        sw.WriteLine("</Row>")
    Next

End Sub

I've just done exactly the same thing. One excel worksheet for each database talbe/gridview

You'll find everything in this blog post. Let me know if you need more help.

Public Shared Sub CreateWorkBook(ByVal cList As Object, ByVal wbName As String, ByVal CellWidth As Integer)
    Dim attachment As String = "attachment; filename=""" & wbName & ".xml"""

    HttpContext.Current.Response.ClearContent()

    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"

    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    sw.WriteLine("<?xml version=""1.0""?>")

    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")

    sw.WriteLine("xmlns:o=""urn:schemas-microsoft-com:office:office""")
    sw.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")

    sw.WriteLine("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
    sw.WriteLine("xmlns:html=""http://www.w3.org/TR/REC-html40"">")

    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
    sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>")

    sw.WriteLine("<Created>2010-05-15T19:14:19Z</Created>")
    sw.WriteLine("<Version>11.9999</Version>")

    sw.WriteLine("</DocumentProperties>")
    sw.WriteLine("<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")

    sw.WriteLine("<WindowHeight>9210</WindowHeight>")
    sw.WriteLine("<WindowWidth>19035</WindowWidth>")

    sw.WriteLine("<WindowTopX>0</WindowTopX>")
    sw.WriteLine("<WindowTopY>90</WindowTopY>")

    sw.WriteLine("<ProtectStructure>False</ProtectStructure>")
    sw.WriteLine("<ProtectWindows>False</ProtectWindows>")

    sw.WriteLine("</ExcelWorkbook>")
    sw.WriteLine("<Styles>")

    sw.WriteLine("<Style ss:ID=""Default"" ss:Name=""Normal"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom""/>")

    sw.WriteLine("<Borders/>")
    sw.WriteLine("<Font/>")

    sw.WriteLine("<Interior/>")
    sw.WriteLine("<NumberFormat/>")

    sw.WriteLine("<Protection/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s22"">")
    sw.WriteLine("<Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("<Font ss:Bold=""1""/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s23"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("</Style>")
    sw.WriteLine("<Style ss:ID=""s24"">")

    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")
    sw.WriteLine("<Borders>")

    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("</Borders>")
    sw.WriteLine("<Font ss:Color=""#FFFFFF""/>")

    sw.WriteLine("<Interior ss:Color=""#FF6A6A"" ss:Pattern=""Solid""/>") 'set header colour here
    sw.WriteLine("</Style>")

    sw.WriteLine("</Styles>")
    For Each gView As GridView In cList
        CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
    Next
    sw.WriteLine("</Workbook>")
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

End Sub
Private Shared Sub CreateWorkSheet(ByVal wsName As String, ByVal sw As System.IO.StringWriter, ByVal gv As GridView, ByVal cellwidth As Integer)
    If IsNothing(gv.HeaderRow) = False Then
        sw.WriteLine("<Worksheet ss:Name=""" & wsName & """>")
        Dim cCount As Integer = gv.HeaderRow.Cells.Count
        Dim rCount As Long = gv.Rows.Count + 1
        sw.WriteLine("<Table ss:ExpandedColumnCount=""" & cCount & """ ss:ExpandedRowCount=""" & rCount & """ x:FullColumns=""1""")
        sw.WriteLine("x:FullRows=""1"">")
        For i As Integer = (cCount - cCount) To (cCount - 1)
            sw.WriteLine("<Column ss:AutoFitWidth=""1"" ss:Width=""" & cellwidth & """/>")
        Next

        GridRowIterate(gv, sw)
        sw.WriteLine("</Table>")
        sw.WriteLine("<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")

        sw.WriteLine("<Selected/>")
        sw.WriteLine("<DoNotDisplayGridlines/>")

        sw.WriteLine("<ProtectObjects>False</ProtectObjects>")
        sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>")

        sw.WriteLine("</WorksheetOptions>")
        sw.WriteLine("</Worksheet>")
    End If
End Sub
Private Shared Sub GridRowIterate(ByVal gv As GridView, ByVal sw As System.IO.StringWriter)
    sw.WriteLine("<Row>")

    For Each tc As TableCell In gv.HeaderRow.Cells
        Dim tcText As String = tc.Text

        Dim tcWidth As String = gv.Width.Value
        Dim dType As String = "String"

        If IsNumeric(tcText) = True Then

            dType = "Number"

        End If
        sw.WriteLine("<Cell ss:StyleID=""s24""><Data ss:Type=""String"">" & tcText & "</Data></Cell>")

    Next
    sw.WriteLine("</Row>")

    For Each gr As GridViewRow In gv.Rows
        sw.WriteLine("<Row>")

        For Each gc As TableCell In gr.Cells
            Dim gcText As String = gc.Text
            Dim dType As String = "String"

            If IsNumeric(gcText) = True Then

                dType = "Number"
                gcText = CDbl(gcText)

            End If
            sw.WriteLine("<Cell ss:StyleID=""s23""><Data ss:Type=""" & dType & """>" & gcText & "</Data></Cell>")

        Next
        sw.WriteLine("</Row>")
    Next

End Sub
回复 原文
~没有更多了~

关于作者

作死小能手

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

佚名

文章 0 评论 0

羁客

文章 0 评论 0

天天爱笑的徐老师

文章 0 评论 0

星

文章 0 评论 0

夏日落

文章 0 评论 0

隐诗

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文