技术资料 > .Net专区 > 数据库应用 : 将DataGrid中数据倒出Excel文件并下载

将DataGrid中数据倒出Excel文件并下载

March 25,2004

Imports System
Imports System.Text
Namespace toExcel

'功能：将asp.net中DataGrid生成Excel文件下载。
'Mountains改进：1、支持中文 2、隐藏列不显示
'日期：2002.10.30
Public Class DataGridToCSV

      Public Function GenerateFile(ByRef Page As System.Web.UI.Page, ByVal MyDataGrid As System.Web.UI.WebControls.DataGrid, ByVal FileName As String) As String

         Dim resp As HttpResponse
         Dim colCount As Integer = MyDataGrid.Columns.Count - 1

         resp = Page.Response

         resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312") '解决中文乱码之关键
         'resp.Charset = "utf-8"
         'resp.AddFileDependency(FileName)
         'resp.ContentType = "Text/HTML"
         ''resp.AppendHeader("Content-Type", "text/html; charset=gb2312")

         resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName)  '必要，做成下载文件

         Dim colHeaders As String = ""
         Dim strItems As StringBuilder = New StringBuilder()

         Dim myCol As DataGridColumn

         Dim i As Integer

         For i = 0 To colCount
            myCol = MyDataGrid.Columns(i)
            If myCol.Visible = True Then
                  colHeaders = colHeaders & myCol.HeaderText.ToString & ","
            End If
         Next

         If colHeaders.Length > 0 Then
            colHeaders = colHeaders.Substring(0, colHeaders.LastIndexOf(","))
         End If

         colHeaders = colHeaders & Chr(13) & Chr(10)

         resp.Write(colHeaders)

         Dim colRow As String

         Dim item As DataGridItem

         For Each item In MyDataGrid.Items
            resp.Write(FormatExportRow(colCount, item, MyDataGrid))
         Next item

         resp.End()

      End Function

      Private Function FormatExportRow(ByVal colCount As Integer, ByVal Item As DataGridItem, ByVal MyDataGrid As System.Web.UI.WebControls.DataGrid) As String
         Dim strItem As String
         Dim i As Integer

         For i = 0 To colCount
            If MyDataGrid.Columns(i).Visible = True Then
                  If Item.Cells(i).Text Is System.DBNull.Value Then
                     Item.Cells(i).Text = ""
                  End If
                  If i = colCount Then
                     strItem += Item.Cells(i).Text.ToString & Chr(13) & Chr(10)
                  Else
                     strItem += Item.Cells(i).Text.ToString & ","
                  End If
            End If
         Next
         strItem = Replace(strItem, " ", " ")
         Return strItem
      End Function

End Class

End Namespace