How to Improve the performance of reading data from excel and writing to datatable

[EDN Admin]

Hi ,
I want to import data from excel and move that to Datatable in VB.NET 2008.
I wrote and working but its taking too long time.. for 1500 records its taking 4.40 min.
My Code is here,
Dim xla As Object <br/>
Dim xlw As Object<br/>
Dim xls As Object
Dim dt1 As New DataTable()
xla = CreateObject("Excel.Application")
xlw = GetObject(FileName)<br/>
xls = xlw.sheets(_SheetNo)
_TotalRows = xls.Rows.Count
_StartingRow=1
_EndCol=32
For i = _StartingRow To _TotalRows<br/>
ColIndex = -1<br/>
Dim dataRow1 As DataRow = dt1.NewRow()<br/>
For j = _StartingCol To _EndCol<br/>
If Trim(xls.Cells(i, _StartingCol).Text) = "" Then If first column
is blank then stop reading further <br/>
GoTo EndPart<br/>
End If<br/>
<br/>
ColIndex = ColIndex + 1<br/>
<br/>
If dt1.Columns.Count < _EndCol Then<br/>
dt1.Columns.Add(New DataColumn())
Adding Column Dynamically <br/>
End If<br/>
<br/>
dataRow1.Item(ColIndex) = Trim(xls.Cells(i, j).Text)<br/>
Next<br/>
dt1.Rows.Add(dataRow1)<br/>
Next<br/>
EndPart:<br/>
xlw.close()<br/>
xla = Nothing<br/>
xlw = Nothing<br/>
xls = Nothing




So this code taking too long time for 1500 records, how can i change this code to improve the performance ?
or any other easy way to read from excel and write into datatable ?

<hr class="sig Thanks - Ravi

View the full article