Writing to excel is very slow

M

minaz_khan

I am writing a code in VB.NET and writing to it through a dataset.
But for every 25 seconds it writes only 100 rows to the excel.

Can any1 please comment on this.

Here is a piece of the code
For Each drXML As DataRowView In ViewToSort
rowCnt += 1
totCnt += 1

If rowCnt = 1 Then
objExcelWS = objExcelWB.Worksheets(sheetCnt)

For colCnt = 1 To xlsDS.Tables(0).Columns.Count
objExcelWS.Cells(rowCnt, colCnt) =
xlsDS.Tables(0).Columns(colCnt - 1).ColumnName
Next
End If

For colCnt = 1 To xlsDS.Tables(0).Columns.Count
'objExcelWS.Cells(rowCnt + 1, colCnt) = "'" &
Format(drXML(colCnt - 1).ToString())
objExcelWS.Cells(rowCnt + 1, colCnt) = drXML(colCnt
- 1).ToString()
Next

'if row count reaches 65K and not end of dataset, close
this worksheet
If rowCnt = 65000 And totCnt <>
xlsDS.Tables(0).Rows.Count Then
rowCnt = 0
sheetCnt += 1
End If

Next
 
Top