DataTable 導出Excel



   ''' <summary>
    ''' 由DataTable导出Excel
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="fileName"></param>
    Public Sub CreateExcel(dt As DataTable, fileName As String)
        Try
            Dim resp As HttpResponse
            resp = Page.Response

            resp.Buffer = True
            resp.ClearContent()
            resp.ClearHeaders()
            resp.Charset = "GB2312"
            'resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8")
            resp.AppendHeader("Content-Disposition", "attachment;filename=" & HttpUtility.UrlEncode(fileName, Encoding.UTF8).ToString)
            'resp.ContentEncoding = System.Text.Encoding.UTF7
            resp.ContentEncoding = System.Text.Encoding.[Default]
            '设置输出流为简体中文  
            resp.ContentType = "application/ms-excel"
            '设置输出文件类型为excel文件。

            Dim colHeaders As String = "", ls_item As String = ""

            '定义表对象与行对象，同时用DataSet对其值进行初始化
            'DataTable dt = ds.Tables[0];
            Dim myRow As DataRow() = dt.[Select]()
            '可以类似dt.Select("id>10")之形式达到数据筛选目的
            Dim i As Integer = 0
            Dim cl As Integer = dt.Columns.Count

            '取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            For i = 0 To cl - 1
                If i = (cl - 1) Then
                    '最后一列，加n
                    colHeaders += dt.Columns(i).Caption.ToString().Trim() & vbLf
                Else
                    colHeaders += dt.Columns(i).Caption.ToString().Trim() & vbTab

                End If
            Next
            resp.Write(colHeaders)
            '向HTTP输出流中写入取得的数据信息

            '逐行处理数据
            For Each row As DataRow In myRow
                '当前行数据写入HTTP输出流，并且置空ls_item以便下行数据  
                For i = 0 To cl - 1
                    Dim a As String = ""
                    Select Case dt.Columns(i).DataType.ToString()
                        Case "System.DateTime"
                            If row(i).ToString() <> "" Then
                                a = DirectCast(row(i), DateTime).ToString("yyyy-MM-dd").Trim()
                            End If
                        Case Else
                            a = row(i).ToString().Trim()
                    End Select

                    If i = (cl - 1) Then
                        '最后一列，加n
                        ls_item += a & vbLf
                        'ls_item += row(i).ToString().Trim() & vbLf
                    Else
                        ls_item += a & vbTab
                        'ls_item += row(i).ToString().Trim() & vbTab
                    End If
                Next
                resp.Write(ls_item)

                ls_item = ""
            Next
            resp.End()
            '   #region 强行杀死最近打开的Excel进程
            '   System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            '   System.DateTime startTime = new DateTime();
            '   int m, killId = 0;
            '   for (m = 0; m < excelProc.Length; m++)
            '   {
            '       if (startTime < excelProc[m].StartTime)
            '       {
            '           startTime = excelProc[m].StartTime;
            '           killId = m;
            '       }
            '   }
            '   if (excelProc[killId].HasExited == false)
            '   {
            '       excelProc[killId].Kill();
            '   }
            '   #endregion
        Catch ex As Exception
            'MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub toExcel(ByVal tb As DataTable)

        Dim dgrid As System.Web.UI.WebControls.DataGrid = Nothing

        Dim context As System.Web.HttpContext = System.Web.HttpContext.Current

        Dim strOur As System.IO.StringWriter = Nothing

        Dim htmlWriter As System.Web.UI.HtmlTextWriter = Nothing

        If Not IsNothing(tb) Then

            If tb.Rows.Count = 0 Then Exit Sub

            context.Response.ContentType = "application/vnd.ms-excel "

            context.Response.ContentEncoding = System.Text.Encoding.UTF7

            context.Response.Charset = " "

            strOur = New IO.StringWriter

            htmlWriter = New System.Web.UI.HtmlTextWriter(strOur)

            dgrid = New DataGrid

            dgrid.DataSource = tb.DefaultView

            dgrid.AllowPaging = False

            dgrid.DataBind()

            dgrid.RenderControl(htmlWriter)

            context.Response.Write(strOur.ToString)

            context.Response.End()

        End If

    End Sub