TOPImports NPOI.HPSF Imports NPOI.HSSF Imports NPOI.HSSF.UserModel Imports NPOI.POIFS Imports NPOI.UtilNPOI 讀取Excel
Public Shared Function LoadExcel(Optional ByVal FilePath As String = "", _
Optional ByVal SheetName As String = "", _
Optional ByVal SheetIndex As Integer = 0)
Dim OpenXls As New OpenFileDialog
Dim table As New DataTable
OpenXls.Filter = "XLS File|*.xls"
If String.IsNullOrWhiteSpace(FilePath) = True Then
If OpenXls.ShowDialog = DialogResult.OK Then
FilePath = OpenXls.FileName
End If
End If
If File.Exists(FilePath) = False Then
Return table
End If
Dim tmpXs As String = Application.StartupPath & "\tmp.xls"
If File.Exists(tmpXs) = True Then
File.Delete(tmpXs)
End If
File.Copy(FilePath, tmpXs)
FilePath = tmpXs
Try
Dim Fs As FileStream = File.OpenRead(FilePath)
Dim workbook As HSSFWorkbook = New HSSFWorkbook(Fs)
Try
Dim sheet As HSSFSheet
If SheetName <> "" Then
sheet = workbook.GetSheet(SheetName)
Else
sheet = workbook.GetSheetAt(SheetIndex)
End If
Dim headerRow As HSSFRow = sheet.GetRow(0)
Dim cellCount As Int32 = headerRow.LastCellNum
For i As Int32 = headerRow.FirstCellNum To cellCount - 1
Dim column As DataColumn = New DataColumn(headerRow.GetCell(i).StringCellValue)
table.Columns.Add(column)
Next
Dim rowCount As Int32 = sheet.LastRowNum
For i As Int32 = (sheet.FirstRowNum + 1) To sheet.LastRowNum
Dim row As HSSFRow = sheet.GetRow(i)
Dim dataRow As DataRow = table.NewRow()
Dim NullCount As Integer = 0
For j As Int32 = row.FirstCellNum To cellCount - 1
If Not (row.GetCell(j) Is Nothing) Then
dataRow(j) = row.GetCell(j).ToString()
End If
If IsDBNull(dataRow(j)) = True Then
NullCount += 1
ElseIf String.IsNullOrWhiteSpace(dataRow(j)) = True Then
NullCount += 1
End If
Next
If NullCount <> table.Columns.Count Then
table.Rows.Add(dataRow)
End If
Next
sheet = Nothing
Catch ex As Exception
MsgBox(ex.Message)
Finally
Fs.Dispose()
Fs.Close()
End Try
workbook = Nothing
Return table
Catch ex As Exception
MessageBox.Show(ex.Message)
Return table
End Try
End Function
NPOI 寫入EXCEL檔
Public Shared Function SaveExcel(ByVal obj As Object, Optional ByVal FilePath As String = "") As Boolean
Try
Dim SaveXls As New SaveFileDialog
Dim i As Integer = 1
SaveXls.Filter = "Excel檔(*xls)|*.xls"
If FilePath <> "" Then
ElseIf SaveXls.ShowDialog = DialogResult.OK Then
FilePath = SaveXls.FileName
Else
Return False
End If
Dim Ds As New DataSet
Select Case obj.GetType
Case GetType(DataSet)
Ds = obj.copy
Case GetType(DataTable)
Ds.Tables.Add(obj.copy)
Case Else
Dim Json As String = JsonConvert.SerializeObject(obj)
Dim dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(Json)
Ds.Tables.Add(dt)
End Select
Dim workbook As HSSFWorkbook = New HSSFWorkbook()
Dim ms As MemoryStream = New MemoryStream()
For Each dt As DataTable In Ds.Tables
If dt.TableName = "" Then
dt.TableName = "sheet" & i
i = i + 1
End If
Dim sheet As HSSFSheet = workbook.CreateSheet(dt.TableName)
Dim headerRow As HSSFRow = sheet.CreateRow(0)
' 欄位資料
Dim column As DataColumn
For Each column In dt.Columns
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
Next column
' 資料內容
Dim rowIndex As Int32 = 1
Dim row As DataRow
For Each row In dt.Rows
Dim DataRow As HSSFRow = sheet.CreateRow(rowIndex)
For Each column In dt.Columns
If IsDBNull(row(column)) = False Then
DataRow.CreateCell(column.Ordinal).SetCellValue(row(column))
End If
Next column
rowIndex = rowIndex + 1
Next row
sheet.CreateFreezePane(0, 1, 0, 1) '凍結視窗
sheet = Nothing
headerRow = Nothing
Next
workbook.Write(ms)
ms.Flush()
ms.Position = 0
workbook = Nothing
Dim fs As FileStream = New FileStream(FilePath, FileMode.Create, FileAccess.Write)
Dim data As Byte() = ms.ToArray()
fs.Write(data, 0, data.Length)
fs.Flush()
fs.Close()
data = Nothing
ms = Nothing
fs = Nothing
Ds.Dispose()
Return True
Catch ex As Exception
Return False
End Try
End Function
