Loading an excel file into a VB Data Table. Not getting all rows.

[Joanie Brows]

Hi,

Im using VB.net 2010 to load an Excel file that has over 60,000 rows into a data table. Im only getting around 7,200. There are no errors and the programs completes. I loaded the data table into a datagridview so I could see it and all rows ARE there in the DGV, it doesnt make any sense.. Its driving me crazy, I really cant figure out what the problem is. If anyone has any ideas Id really appreciate it.


TIA!

Joanie

Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop.Excel

Public Class Form1


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim openFileDialog1 As New OpenFileDialog()
openFileDialog1.InitialDirectory = "F:\primeproduction\primenxt\R212\_CSV-AcxiomRpts\_PNextRpts\"
openFileDialog1.Filter = "Excel files (*.csv)|*.csv|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.DataGridView1.DataSource = Nothing
Dim dataTable As System.Data.DataTable
dataTable = importExcelfileToDataTable(openFileDialog1.FileName)
Me.DataGridView1.DataSource = dataTable
Me.TextBox1.Text = dataTable.Rows.Count
End If
End Sub

Public Function importExcelfileToDataTable(ByVal xlsFileFullName As String) As System.Data.DataTable
Dim returnDT As New System.Data.DataTable()

If File.Exists(xlsFileFullName) Then

Microsoft.Office.Interop.Excel
Create new Application.
Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()

// Open Excel Workbook
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(xlsFileFullName) 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);


//Get sheet the first work sheet Get sheet, if not found return null;
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = xlWorkBook.Worksheets(1) get the first worksheet.

//Get range
Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.UsedRange

//Load all cels into Array

Dim arrayRanges(,) As Object = xlRange.Value

xlWorkBook.Close()
xlApp = Nothing
xlRange = Nothing
xlWorkSheet = Nothing

//** import into Datatable by converting the object array to a table
returnDT = convertToDataTable(arrayRanges)

End If
Return returnDT
End Function



Public Function convertToDataTable(ByVal array As Object(,)) As System.Data.DataTable
Dim row As Integer
Dim column As Integer
Dim intRows As Integer
Dim intCols As Integer
Dim dataTable As New System.Data.DataTable()
Dim dRow As DataRow



intRows = array.GetLength(0)

If intRows <> 0 Then
If intRows <> 0 Then
intCols = array.GetLength(1)
If intCols <> 0 Then
For column = 1 To intCols
trim the column if has leading spaces
Dim holdValue As Object = array(1, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If

dataTable.Columns.Add(new DataColumn((String)array[1, column]));
dataTable.Columns.Add(New DataColumn(holdString))
Next

Skip the first row, that should be used for the column heading
For row = 2 To intRows
dRow = dataTable.NewRow()
For column = 1 To intCols

need to assign row by column name so do the following

trim the column if has leading spaces
Dim holdValue As Object = array(1, column)
Dim holdString As [String]
If holdValue IsNot Nothing Then
holdString = DirectCast(holdValue, [String])
holdString = holdString.Trim()
Else
assign column name if column blank
holdString = "No_Name_Col" & column.ToString()
End If

dRow[(String)array[1, column]] = array[row, column];
dRow(holdString) = array(row, column)
Next
dataTable.Rows.Add(dRow)
Next
End If
End If
End If

dRow = Nothing
Return dataTable
End Function




End Class




-Joni

Continue reading...