做直播导航网站有哪些,小程序开发费用分析,专业制作行驶证,京东商城官方网站1、情景點擊“瀏覽”按鈕#xff0c;尋找要上傳的Excel。按下上傳按鈕#xff0c;資料寫入資料庫#xff08;database#xff09;並且顯示在畫面上。然後#xff0c;按下“保存”按鈕#xff0c;Datagrid上的資料寫入DB。2、分析按下上傳按鈕#xff0c;先將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 SubExcel 操作方法部分Public Class ModuleExcel summary 上傳Excel文件 /summary param nameInputFile上傳控件/param param nameFilename文件名稱(主文件名擴展名)/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 nameexcelPathExcel文件物理路徑/param param namesheetIndexExcel文件索引(從1開始)/param param namecolumnNameExcel欄位名稱(大寫)逗號分隔。/param param namestartRowIndexExcel數據行索引(從1開始)/param param namecanNotNullColumnExcel不可空白欄位索引/param param namecolumnCountExcel欄位總數/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 nameworkSheetExcel sheet/param param namedataTable存放資料的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 namedataTable資料表/param param namecolumnIndex列索引/param param namerowValue值/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 ClassEnd Class 注意使用office Excel元件操作Excel需要設定DCOM的權限。1“控制臺”--“系統管理工具”--“元件服務”然後找到“DCOM設定”。如下圖 转载于:https://www.cnblogs.com/htht66/archive/2010/10/29/1864442.html
