八咫烏的天空

最近剛好專案需要客戶上傳excel檔再做資料分析

使用傳統的方式當然還是可以處理

但是客戶的使用方式千百樣...舊方式有時候會遇到處理上有問題的情況

google了一下 就使用了NPOI來處理

MSDN的說明

大陸程式設計師寫的超詳細教程

Dim path As String = Server.MapPath("test.xls")
Dim file As FileStream = New FileStream(path, FileMode.Open, FileAccess.Read)
Dim workbook As HSSFWorkbook = New HSSFWorkbook(file)
Dim sheet As HSSFSheet = workbook.GetSheetAt(0)
Dim dt As DataTable = New DataTable()
Dim headerRow As HSSFRow = sheet.GetRow(0)
Dim cellCount As Integer = headerRow.LastCellNum
For i = headerRow.FirstCellNum To (cellCount - 1) Step 1
Dim column As DataColumn = New DataColumn(headerRow.GetCell(i).StringCellValue)
dt.Columns.Add(column)
Next
Dim rowCount As Integer = sheet.LastRowNum
For i = (sheet.FirstRowNum + 1) To (sheet.LastRowNum) Step 1
Dim row As HSSFRow = sheet.GetRow(i)
Dim dataRow As DataRow = dt.NewRow()
For j = row.FirstCellNum To (cellCount - 1) Step 1
If Not Convert.IsDBNull(row.GetCell(j)) Then
dataRow(j) = row.GetCell(j).ToString()
End If
Next
dt.Rows.Add(dataRow)
Next
sheet.Dispose()
file.Dispose()
GridView1.DataSource = dt
GridView1.DataBind()