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