[RESOLVED] bizarre issue importing Excel data

[silverblatt]

My application needs to import Excel data and drop it into a ListView control; the data may be in .xls, .xlsx, .csv or .psv formats. I'm using ReadExcelSheetData() to import .xls and .xlsx files, and ReadTextFile() to import .csv and .psv files. Everything works as it should with one bizarre exception: when I import .xls or .xlsx files, about 95% of the time, the data in the 7th and 8th cells of the first row (i.e., cells G1 and H1) is coming in as blank, regardless of the actual content of the cells. When I save the .xls or .xlsx file to either .csv or .psv formats and then import the .csv or .psv file, everything comes in exactly as it should.

At first, I thought it was some issue with a particular Excel file being corrupted, or with items in the first row containing pound signs, so I've spent hours and hours creating fresh files with nothing but letters in the first rows, but the problem persists. I am not applying any formatting to the Excel files - I'm just entering (or in some cases copying and pasting) the data into the cells and saving the files in the normal way.

It also occurred to me that the data may be coming in properly, but perhaps the issue is due to how I'm handling it or loading it into the ListView control, so I used the 5 lines (now commented out) in ReadExcelSheetData() to see exactly what's coming in before I load it into the ListView or do anything else with it. Doing so confirmed that, for whatever reason, ONLY the data from cells G1 and H1 is coming in blank from the .xls and .xlsx files, even though those cells are all properly populated with text when I view the files in Excel.

I'm about at wit's end, and so I'll greatly appreciate any suggestions.

    Private Sub ReadExcelSheetData()
        Dim I As Int32, J As Int32, clbi As ComboListBoxItem, dr As DataRow, lvi As ListViewItem
        Try
            InitializeListView()
            'read the data from the sheet and load it into the listview
            Using cn As New OleDb.OleDbConnection(ConnectionString)
                clbi = cboWorksheet.SelectedItem
                Using cmd As New OleDb.OleDbCommand("SELECT * FROM [" & clbi.URL & "]", cn)
                    cmd.CommandType = CommandType.Text
                    Using da As New OleDb.OleDbDataAdapter(cmd)
                        Using dt As New DataTable
                            da.Fill(dt)
                            For I = 0 To dt.Rows.Count - 1
                                'add the row number to the listview
                                lvi = New ListViewItem((I + 1).ToString)
                                lvi.ForeColor = SystemColors.WindowText
                                lvi.BackColor = ListViewItemBackColor
                                lvi.UseItemStyleForSubItems = False
                                lvwMain.Items.Add(lvi)
                                'add all the columns in the row as individual subitems
                                dr = dt.Rows(I)
                                For J = 0 To dr.ItemArray.Count - 1
                                    'If I = 0 Then
                                    '    Dim ob As Object = dr.ItemArray(J)
                                    '    Dim st As String = ob.ToString
                                    '    MsgBox("J: " & J.ToString & "; Item: " & st)
                                    'End If
                                    AddListViewSubItem(lvwMain, EmptyStringIfNull(dt.Rows(I).Item(J)).ToString.Trim)
                                Next
                            Next
                        End Using
                    End Using
                End Using
            End Using
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub
    Private Sub ReadTextFile()
        Dim FileSpec As String = lblImportFileSpec.Text, RowCount As Int32 = 0, LineText As String(), lvi As ListViewItem, I As Int32
        Try
            InitializeListView()
            Using tfp As New Microsoft.VisualBasic.FileIO.TextFieldParser(FileSpec)
                tfp.TextFieldType = FileIO.FieldType.Delimited
                tfp.SetDelimiters(If(GetFileNameExtension(FileSpec) = "csv", Comma, Pipe))
                While Not tfp.EndOfData
                    RowCount += 1
                    LineText = tfp.ReadFields
                    lvi = New ListViewItem((RowCount).ToString)
                    lvi.ForeColor = SystemColors.WindowText
                    lvi.BackColor = ListViewItemBackColor
                    lvi.UseItemStyleForSubItems = False
                    lvwMain.Items.Add(lvi)
                    For I = LineText.GetLowerBound(0) To LineText.GetUpperBound(0)
                        AddListViewSubItem(lvwMain, EmptyStringIfNull(LineText(I)).ToString.Trim)
                    Next
                End While
            End Using
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub
    Private Sub InitializeListView()
        Dim I As Int32
        Try
            'clear the listview and set up its columns
            lvwMain.Clear()
            lvwMain.BackColor = Color.LightBlue
            AddListViewColumn(lvwMain, "Row #")
            lvwMain.Columns(0).Width = 50
            For I = 1 To ColumnCount
                AddListViewColumn(lvwMain, ExcelColumns(I))
                lvwMain.Columns(I).Width = 66
            Next
        Catch ex As Exception
            DisplayException(ex)
        End Try
    End Sub