YiJyun's 研究小間

1.必須先到NPOI的官網下載2.0以上版本(http://npoi.codeplex.com/releases)

2.將下載回來的dll加入參考

    Private Function RenderDataTableToExcel(ByRef SourceTable As DataTable) As Stream
        Dim workbook As HSSFWorkbook = New HSSFWorkbook()
        Dim ms As MemoryStream = New MemoryStream()
        Dim sheet As HSSFSheet = workbook.CreateSheet()
        Dim headerRow As HSSFRow = sheet.CreateRow(0)
        For Each column As DataColumn In SourceTable.Columns
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
        Next
        Dim rowIndex As Int32 = 1
        For Each row As DataRow In SourceTable.Rows
            Dim dataRow As HSSFRow = sheet.CreateRow(rowIndex)
            For Each column2 As DataColumn In SourceTable.Columns
                dataRow.CreateCell(column2.Ordinal).SetCellValue(row(column2).ToString)
            Next
            rowIndex = rowIndex + 1
        Next
        workbook.Write(ms)
        ms.Flush()
        ms.Position = 0
        sheet = Nothing
        headerRow = Nothing
        workbook = Nothing
        Return ms
    End Function

    Public Overridable Sub DataTableToExcel(ByRef dt As DataTable, ByRef filename As String)
        Dim ms As IO.MemoryStream = RenderDataTableToExcel(dt)
        Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"
        Web.HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("UTF-8")
        Web.HttpContext.Current.Response.AddHeader("Content-Disposition", String.Format("attachment; filename=" & filename & ""))
        Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray())
        ms.Close()
        ms.Dispose()
    End Sub