vb.net导出Excel文件的方法

选择字号： 超大 标准 发布时间：2008-9-11 10:42:13 | 作者：admin | 0个评论 | 人浏览

Public Function DataGridToExcel(ByVal DS As System.Web.UI.WebControls.DataGrid, ByVal Title As String)
        Dim sqlstr, TName, Sql, S, TT As String
        Dim I, J As Integer
        Dim Col As DataColumn
        Dim sw As New System.IO.StringWriter
        S = ""
        For J = 0 To DS.Columns.Count - 1
            If DS.Columns(J).Visible = True Then
                TT = DS.Columns(J).HeaderText.ToString.ToUpper.Replace("ID", "'ID")
                If TT = "" Then
                    TT = "无标题"
                End If
                S += TT + Chr(9)
            End If

        Next
        sw.WriteLine(S)
        For I = 0 To DS.Items.Count - 1
            S = ""
            For J = 0 To DS.Columns.Count - 1
                If DS.Columns(J).Visible = True Then
                    S += CStr(DS.Items(I).Cells(J).Text.ToString).ToString + Chr(9)
                End If

            Next
            sw.WriteLine(S)
        Next
        'S = Chr(9) + "系统生成表:〖" + Title + "〗" + " 生成时间:" + Now.ToString + Chr(9)
        'sw.WriteLine(S)
        sw.Close()
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Me.NowStr.ToLower + ".xls")
        Response.ContentType = "application/ms-excel"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
        EnableViewState = False
        htm = htm&(sw)
        Response.End()
    End Function
    Public Function DataTableToExcel(ByVal DS As DataTable, ByVal Title As String)
        Dim sqlstr, TName, Sql, S, TT As String
        Dim I, J As Integer
        Dim Col As DataColumn
        Dim sw As New System.IO.StringWriter
        S = ""
        sw.WriteLine(S)
        For J = 0 To DS.Columns.Count - 1

            TT = DS.Columns(J).ColumnName.ToString.ToUpper.Replace("ID", "'ID")
            If TT = "" Then
                TT = "无标题"
            End If
            If J = 0 Then
                TT = "编号"
            ElseIf J = 1 Then
                TT = "学号"
            ElseIf J = 2 Then
                TT = "姓名"
            End If
            S += TT + Chr(9)
        Next
        sw.WriteLine(S)
        For I = 0 To DS.Rows.Count - 1
            S = ""
            For J = 0 To DS.Columns.Count - 1

                S += CStr(DS.Rows(I)(J)).ToString + Chr(9)
            Next
            sw.WriteLine(S)
        Next
        'S = Chr(9) + "系统生成表:〖" + Title + "〗" + " 生成时间:" + Now.ToString + Chr(9)
        'sw.WriteLine(S)
        sw.Close()
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Me.NowStr.ToLower + ".xls")
        Response.ContentType = "application/ms-excel"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
        EnableViewState = False
        htm = htm&(sw)
        Response.End()
    End Function
    Public Function DatasToExcel(ByVal DS As DataTable, ByVal Title As String)
        Dim sqlstr, TName, Sql, S, TT, AT As String
        Dim I, J As Integer
        Dim Col As DataColumn
        Dim sw As New System.IO.StringWriter
        S = ""
        For I = 0 To DS.Rows.Count - 1
            S = ""
            For J = 0 To DS.Columns.Count - 1
                If DS.Columns(J).ColumnName.ToLower = "InUserType".ToLower Then
                    AT = CStr(DS.Rows(I)(J)).ToString
                    If AT = "1" Then
                        AT = "学生"
                    Else
                        AT = "教师"
                    End If
                    S += AT + Chr(9)
                Else
                    S += CStr(DS.Rows(I)(J)).ToString + Chr(9)
                End If

            Next
            sw.WriteLine(S)
        Next
        'S = Chr(9) + "系统生成表:〖" + Title + "〗" + " 生成时间:" + Now.ToString + Chr(9)
        'sw.WriteLine(S)
        sw.Close()
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Me.NowStr.ToLower + ".xls")
        Response.ContentType = "application/ms-excel"
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
        EnableViewState = False
        htm = htm&(sw)
        Response.End()
    End Function

标签：

猜你喜欢