WebApp匯入資料之From Excel
1、情景
點擊“瀏覽”按鈕，尋找要上傳的Excel。按下上傳按鈕，資料寫入資料庫（database）並且顯示在畫面上。
然後，按下“保存”按鈕，Datagrid上的資料寫入DB。
2、分析
按下上傳按鈕，先將Excel上傳到Server上；再將Excel的內容讀入Datatable中；然後刪除Excel檔；最後檢查Datatable的內容正確性，寫入database。
3、實現
上傳按鈕部分
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
' check is has file
If Not upExcel.HasFile Then
JavascriptUtility.Alert(Me, "請先上傳檔案")
Exit Sub
End If
Dim filename As String = String.Empty
Dim savePath As String = String.Empty
Dim dt As DataTable = Nothing
Try
filename = Format(Now, "yyyyMMddHHmmss") & ".xls"
' 上傳檔案
savePath = ModuleExcel.SaveUploadFile(upExcel, filename)
' 讀取Excel的內容
dt = ModuleExcel.ReadExcel(savePath, 1, "I1ElM", 2, New Integer() {1}, 1)
' Delete Excel
System.IO.File.Delete(savePath)
' 計算Excel
'RunExcel(DataTable)
Catch ex As System.IO.DirectoryNotFoundException
JavascriptUtility.Alert(Me, ex.Message)
Catch ex As ModuleExcel.ExcelColumnDifferenceException
JavascriptUtility.Alert(Me, ex.Message)
Catch ex As Exception
JavascriptUtility.Alert(Me, ex.Message)
End Try
End Sub
Excel 操作方法部分
Public Class ModuleExcel
''' <summary>
''' 上傳Excel文件
''' </summary>
''' <param name="InputFile">上傳控件</param>
''' <param name="Filename">文件名稱(主文件名+擴展名)</param>
Public Shared Function SaveUploadFile(ByRef InputFile As WebControls.FileUpload, ByVal Filename As String) As String
Dim path As String = System.Configuration.ConfigurationManager.AppSettings("UploadPath")
path = System.Web.HttpContext.Current.Server.MapPath(path) & "\" & Filename
InputFile.SaveAs(path)
Return path
End Function
''' <summary>
''' 讀取Excel的內容
''' </summary>
''' <param name="excelPath">Excel文件物理路徑</param>
''' <param name="sheetIndex">Excel文件索引(從1開始)</param>
''' <param name="columnName">Excel欄位名稱(大寫)，逗號分隔。</param>
''' <param name="startRowIndex">Excel數據行索引(從1開始)</param>
''' <param name="canNotNullColumn">Excel不可空白欄位索引</param>
''' <param name="columnCount">Excel欄位總數</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ReadExcel(ByVal excelPath As String, ByVal sheetIndex As Integer, ByVal columnName As String, ByVal startRowIndex As Integer, _
ByVal canNotNullColumn As Integer(), ByVal columnCount As Integer) As DataTable
Dim excelApp As New Excel.Application
Dim workSheet As Excel.Worksheet
Dim workBook As Excel.Workbook
Dim Range1 As Excel.Range
Dim x As Integer
Dim dataTable As New Data.DataTable
'開啟Excel檔
workBook = excelApp.Workbooks.Open(excelPath)
Try
' 取Sheet
workSheet = workBook.Worksheets.Item(sheetIndex) 'workBook.Worksheets.Item(1)
' GetExcelcolumnName:取Excel欄位並且用逗號分隔
If columnName = GetExcelcolumnName(workSheet, dataTable).ToUpper() Then
For x = startRowIndex To workSheet.Rows.Count ' For x = 2 To workSheet.Rows.Count ' 數據行
Dim notNullCount As Integer = 0
For i As Integer = 0 To canNotNullColumn.Length - 1
Range1 = workSheet.Cells(x, canNotNullColumn(i)) ' Range1 = workSheet.Cells(x, 1)
If CType(Range1.Value, String) <> "" Then
notNullCount += 1
End If
Next
If notNullCount = canNotNullColumn.Length Then ' 不可空欄位都不是空
For j As Integer = 1 To columnCount 'Excel計數從1開始 ‘ 數據列
Range1 = workSheet.Cells(x, j)
AddDataRow(dataTable, j - 1, Range1.Value)
Next
Else ' 空則認為讀完
Exit For
End If
Next
Else
Throw New ExcelColumnDifferenceException("指定欄位與Excel實際欄位不一致")
End If
Catch ex As ExcelColumnDifferenceException
Throw ex
Catch ex As Exception
Throw New Exception("Read excel wrong", ex)
Finally
excelApp.Workbooks.Close()
excelApp.Quit()
excelApp = Nothing
End Try
Return dataTable
End Function
''' <summary>
''' 獲取Excel的欄位
''' </summary>
''' <param name="workSheet">Excel sheet</param>
''' <param name="dataTable">存放資料的table</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function GetExcelcolumnName(ByVal workSheet As Excel.Worksheet, ByRef dataTable As Data.DataTable) As String
If workSheet Is Nothing Then
Throw New ArgumentNullException("workSheet", "workSheet為空")
End If
If workSheet Is Nothing Then
Throw New ArgumentNullException("dataTable", "dataTable為空")
End If
Dim sCol As String = String.Empty
For i As Integer = 1 To workSheet.Columns.Count
Dim Range1 As Excel.Range
Dim cellV As String = String.Empty
Range1 = workSheet.Cells(1, i)
cellV = CType(Range1.Value, String)
If cellV <> String.Empty Then
' Join column
sCol &= cellV & ","
' Create column
dataTable.Clear()
dataTable.Columns.Add(cellV)
Else
Exit For
End If
Next
If sCol.Length > 0 Then
Dim _idx As Integer = sCol.LastIndexOf(",")
If _idx > -1 Then
sCol = sCol.Substring(0, _idx)
End If
End If
Return sCol
End Function
''' <summary>
''' 新增資料行
''' </summary>
''' <param name="dataTable">資料表</param>
''' <param name="columnIndex">列索引</param>
''' <param name="rowValue">值</param>
''' <remarks></remarks>
Public Shared Sub AddDataRow(ByRef dataTable As Data.DataTable, ByVal columnIndex As Integer, ByVal rowValue As String)
If dataTable Is Nothing Then
Throw New ArgumentNullException("dataTable", "dataTable為空")
End If
If columnIndex < 0 Then
Throw New ArgumentOutOfRangeException("columnIndex", "columnIndex超出Excel欄範圍")
End If
Dim dr As Data.DataRow
dr = dataTable.NewRow
dr(columnIndex) = rowValue
dataTable.Rows.Add(dr)
End Sub
Public Class ExcelColumnDifferenceException
Inherits System.ArgumentException
Public Sub New(ByVal message As [String])
MyBase.New(message)
End Sub
Public Sub New(ByVal message As [String], ByVal innerException As Exception)
MyBase.New(message, innerException)
End Sub
End Class
End Class
注意
使用office Excel元件操作Excel，需要設定DCOM+的權限。
1，“控制臺”-->“系統管理工具”-->“元件服務”，然後找到“DCOM設定”。如下圖
