This is not necessarily a solution for your case yet would I did was created a SQL-Server table with bNumber as nvarchar, used SQL-Server Management Studio to export to Excel where I placed the Excel file in the bin\Debug folder of the project. Using the code below all rows returned properly (as we have a string column strings are returned).
The key here is using IMEX=1 in the connection string, Excel can be finicky, this may or may not resolve the issue.
Imports System.Data.OleDb Public Class Operations Public Function GetData(ByVal FileName As String) As List(Of String) Dim valueList As New List(Of String) Using cn As New OleDbConnection With { .ConnectionString = ConnectionString(FileName) } Using cmd As OleDbCommand = New OleDbCommand("SELECT bNumber FROM [Table_1$]", cn) cn.Open() Dim reader As OleDbDataReader = cmd.ExecuteReader While reader.Read valueList.Add(reader.GetString(0)) End While End Using End Using Return valueList End Function Public Function ConnectionString(ByVal FileName As String) As String Dim Builder As New OleDbConnectionStringBuilder If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then Builder.Provider = "Microsoft.Jet.OLEDB.4.0" Builder.Add("Extended Properties", "Excel 8.0;IMEX=1;HDR=Yes;") Else Builder.Provider = "Microsoft.ACE.OLEDB.12.0" Builder.Add("Extended Properties", "Excel 12.0;IMEX=1;HDR=Yes;") End If Builder.DataSource = FileName Return Builder.ConnectionString End Function End Class
Form code
Private Sub Button3_Click(sender As Object, e As EventArgs) _ Handles Button3.Click Dim ops As New Operations Dim fileName As String = IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, "Downloaded.xlsx") Dim valueList As List(Of String) = ops.GetData(fileName) For Each value As String In valueList Console.WriteLine(value) Next End Sub
Results in the IDE Output window
Table structure
Worksheet
EDIT: The following link points to a MSDN code sample for working with SpreadSheetLight library. Once downloaded, set startup form to StackOverFlow1QuestionMixedTypesForm. Right click on the solution in solution explorer and select "Restore NuGet Packages", now build and run the project. There are two buttons on the form, first does mixed types for Sheet1 while button two is a variant of the same data laid out differently in Sheet2.
Code sample https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2