關於NPOI的一點補充和示例

最近看到很多人分享NPOI的用法.

但是很多都不是完整示例或者並沒有實戰效果.

剛好最近有個VB.NET的項目有升級原有的oledb select sheet$的做法.

很明顯,NPOI有更好的穩定性和兼容性.

2進制的處理方式排除了server和client端有沒有office excel的諸多影響和限制.如office版本,系統編碼問題.以及server端是否需要安裝office excel.

2.0版本之後更是支持.xlsx格式等等..

那直接上代碼:

 

要先引用HSSF和XSSF(for .xlsx support).

 

 

下載Excel.沒有寫下載成xlsx的格式了.沒必要.如果一定要download成xlsx.使用對應的XSSF命名空間下的XSSFWorkbook等即可

Imports NPOI.HSSF.UserModel
Imports 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

 

上傳EXCEL.

通常我們實際做項目的時候,需要通過導入excel再進行處理而不是單純的重複insert DB.

那我們直接獲取datatable.

支持xls和.xlsx格式上傳. 這裏的lofile 就是HtmlInputFile控件.調用方法的時候直接將這個對象傳過來即可.

我這裏是web項目.如果是winform.lofile完全不用save之後再處理.直接傳path過來就可以.同樣請將對應的HttpContext改成Context.

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

得到datatable之後請自行處理.