Imports NPOI.HSSF.UserModelImports NPOI.XSSF.UserModel Public Shared Function DownLoadToExcel(ByVal dt As DataTable)        Dim book As HSSFWorkbook = New HSSFWorkbook()        Dim sheet As HSSFSheet = book.CreateSheet("Your_Sheet_Name")        Dim row As HSSFRow = sheet.CreateRow(0)        For i = 0 To dt.Columns.Count - 1            row.CreateCell(i).SetCellValue(dt.Columns(i).ColumnName)        Next        For i = 0 To dt.Rows.Count - 1            Dim row2 As HSSFRow = sheet.CreateRow(i + 1)            For j = 0 To dt.Columns.Count - 1                row2.CreateCell(j).SetCellValue(dt.Rows(i)(j).ToString)            Next        Next        Dim ms As System.IO.MemoryStream = New System.IO.MemoryStream()        book.Write(ms)        HttpContext.Current.Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}.xls", Now.ToString))        HttpContext.Current.Response.BinaryWrite(ms.ToArray())        book = Nothing        ms.Close()        ms.Dispose()    End Function

Public Shared Function GetTabelFromExcel(ByVal lofile As HtmlInputFile) As DataTable        Dim filepath As String = Path.Combine(HttpContext.Current.Server.MapPath("bin"), Path.GetFileName(lofile.PostedFile.FileName))        Dim fileExt As String = Path.GetExtension(lofile.PostedFile.FileName)        Try            lofile.PostedFile.SaveAs(filepath)            Dim tempname As String = Guid.NewGuid().ToString            Dim tempfilepath As String = Path.Combine(HttpContext.Current.Server.MapPath("bin"), tempname & fileExt)            File.Move(filepath, tempfilepath)            File.Delete(filepath)            filepath = tempfilepath            If fileExt = ".xlsx" Then ‘.xlsx 2007                 Try                    Dim wb As XSSFWorkbook                    Dim file As System.IO.FileStream = New System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read)                    Using file                        wb = New XSSFWorkbook(file)                    End Using                    Dim sheet As XSSFSheet = CType(wb.GetSheetAt(0), XSSFSheet)                    Dim rows As System.Collections.IEnumerator = sheet.GetRowEnumerator()                    Dim dt As DataTable = New DataTable()                    For i As Integer = 0 To CInt(sheet.GetRow(0).LastCellNum) - 1                        dt.Columns.Add(System.Convert.ToChar(65 + i).ToString())                    Next                    While rows.MoveNext()                        Dim row As XSSFRow = CType(rows.Current, XSSFRow)                        Dim dr As DataRow = dt.NewRow()                        For j As Integer = 0 To CInt(row.LastCellNum) - 1                            Dim cell As XSSFCell = CType(row.GetCell(j), XSSFCell)                            If cell Is Nothing Then                                dr(j) = Nothing                            Else                                dr(j) = cell.ToString()                            End If                        Next                        dt.Rows.Add(dr)                    End While                    dt.Rows.RemoveAt(0)                    Return dt                Catch e As System.Exception                    Throw New Exception("文件第一行不允許有空白欄位或文件正在使用,請先關閉正在打開的文件!" & e.Message)                End Try            ElseIf fileExt = ".xls" Then ‘office 2003                Try                    Dim wb As HSSFWorkbook                    Dim file As System.IO.FileStream = New System.IO.FileStream(filepath, System.IO.FileMode.Open, System.IO.FileAccess.Read)                    Using file                        wb = New HSSFWorkbook(file)                    End Using                    Dim sheet As HSSFSheet = CType(wb.GetSheetAt(0), HSSFSheet)                    Dim rows As System.Collections.IEnumerator = sheet.GetRowEnumerator()                    Dim dt As DataTable = New DataTable()                    For i As Integer = 0 To CInt(sheet.GetRow(0).LastCellNum) - 1                        dt.Columns.Add(System.Convert.ToChar(65 + i).ToString())                    Next                    While rows.MoveNext()                        Dim row As HSSFRow = CType(rows.Current, HSSFRow)                        Dim dr As DataRow = dt.NewRow()                        For j As Integer = 0 To CInt(row.LastCellNum) - 1                            Dim cell As HSSFCell = CType(row.GetCell(j), HSSFCell)                            If cell Is Nothing Then                                dr(j) = Nothing                            Else                                dr(j) = cell.ToString()                            End If                        Next                        dt.Rows.Add(dr)                    End While                    dt.Rows.RemoveAt(0)                    Return dt                Catch e As System.Exception                    Throw New Exception("文件第一行不允許有空白欄位或文件正在使用,請先關閉正在打開的文件!" & e.Message)                End Try            Else                Throw New Exception("請選擇office excel文件!")            End If        Catch ex As Exception            Throw New Exception("請選擇office excel文件!" & ex.Message)        Finally            File.Delete(filepath)        End Try    End Function