下面是我讀取excel填充到datatable中的代碼.. 在網(wǎng)上看了很也沒有找到答案,希望博客園高手給個(gè)答案 Error: strConn不能正常連接 則拋出Could not decrypt file??? '讀取Excel中Sheet名稱??? Private Function GetExcelSheetName(ByVal FilePath As String) As DataTable??????? Dim FileextensionIndex = FilePath.LastIndexOf(".")??????? Dim filetenName = FilePath.Substring(FileextensionIndex, FilePath.Length - FileextensionIndex)??????? Dim conn As OleDbConnection = Nothing??????? Dim strConn As String??????? Try??????????? If filetenName = ".xls" Then??????????????? strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"??????????? ElseIf filetenName = ".xlsx" Then??????????????? strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0;"??????????? Else??????????????? txtMsg.Text = "Please choose execl files"??????????? End If??????????? ' Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"??????????? 'public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;"; --xlsx, use this:??????????? 'Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" + path + ";Extended Properties=""Excel 8.0;HDR=YES;"""? -xls, use:??????????? conn = New OleDbConnection(strConn)??????????? If conn.State = ConnectionState.Closed Then conn.Open()??????????? Return conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})??????? Catch ex As Exception??????????? Throw ex??????? Finally??????????? If conn Is Nothing Then conn.Close()??????????? If conn Is Nothing Then conn.Dispose()??????? End Try??? End Function ?'讀取指定Sheetname的數(shù)據(jù)??? Private Function GetExcelDataBySheetName(ByVal FilePath As String, ByVal SheetName As String) As DataSet??????? Dim conn As OleDbConnection = Nothing??????? Try??????????? Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"??????????? conn = New OleDbConnection(strConn)??????????? If conn.State = ConnectionState.Closed Then conn.Open()??????????? Dim myCommand As OleDbDataAdapter = Nothing??????????? Dim ds As DataSet = Nothing??????????? Dim CmdText As String = "select * from [" + SheetName.Replace("'", "") + "]"??????????? myCommand = New OleDbDataAdapter(CmdText, strConn)??????????? ds = New DataSet??????????? myCommand.Fill(ds)??????????? Return ds??????? Catch ex As Exception??????????? Throw ex??????? Finally??????????? If conn Is Nothing Then conn.Close()??????????? If conn Is Nothing Then conn.Dispose()??????? End Try??? End Function ? '綁定數(shù)據(jù)??? Private Sub BindData(ByVal FileName As String)??????? '讀取sheetName??????? Dim dtData As DataTable??????? Dim dr As DataRow??????? dtSizeLabel.Columns.Add("Size") '添加Size??????? dtSizeLabel.Columns.Add("Quantity") '添加Size??????? Dim dtSN As DataTable = GetExcelSheetName(FileName) '得到數(shù)據(jù)表名??????? For i As Integer = 0 To dtSN.Rows.Count - 1? '表示循環(huán)取表名??????????? Dim strSheetName As String = dtSN.Rows(i)("TABLE_NAME").ToString().Replace("'", "") '取得表名的方法??????????? Dim strTableNameHz As String = strSheetName.Substring(strSheetName.Length - 1)??????????? If (strSheetName.Substring(strSheetName.Length - 1) = "$") Then? '得到后綴名必須是$??????????????? dtData = GetExcelDataBySheetName(FileName, strSheetName).Tables(0) '得到數(shù)據(jù)??????????????? Dim intColIndex As Integer = 0? '列索引從0開始???????????? ???????????????? For k As Integer = 0 To dtData.Rows.Count - 1 '開始循環(huán)表??????????????????? Dim strValue As String = ConvertToString(dtData.Rows(k)(0)) '第i行第0列 找到Size??????????????????? If strValue.IndexOf("Size") > -1 Then? '表示找到這一行???????????????????? ???????????????????????? For j As Integer = 1 To dtSN.Columns.Count - 1 '取列值??????????????????????????? If ConvertToString(dtData.Rows(k)(j)) <> "" Then??????????????????????????????? 'dtSizeLabel.Columns.Item(j).ColumnName = dtSN.Rows(k)(j).GetType().ToString()? '第k行第0列為Size??????????????????????????????? dr = dtSizeLabel.NewRow '創(chuàng)建行??????????????????????????????? dr("Size") = dtData.Rows(k)(j)??????????????????????????????? dr("Quantity") = dtData.Rows(k + 1)(j)??????????????????????????????? dtSizeLabel.Rows.Add(dr)??????????????????????????? End If??????????????????????? Next??????????????????? End If??????????????????? '---------------------------------??????????????????? 'If strValue.IndexOf("Quantity") > -1 Then? '表示找到這一行??????????????????? '??? dtSizeLabel.Columns.Add("Quantity") '添加Size??????????????????? '??? For j As Integer = 1 To dtSN.Columns.Count - 1 '取列值??????????????????? '??????? If ConvertToString(dtData.Rows(k)(j)) <> "" Then??????????????????? '??????????? dr2 = dtSizeLabel.NewRow??????????????????? '??????????? dr2("Quantity") = dtData.Rows(k)(j)??????????????????? '??????????? dtSizeLabel.Rows.Add(dr2)??????????????????? '??????? End If??????????????????? '??? Next??????????????????? 'End If??????????????? Next??????????? End If??????? Next??????? dgvSizeLabel.DataSource = dtSizeLabel??????? dgvSizeLabel.DataBind()??? End Sub