Asp.net com Excel improves the efficiency of writing data into Excel using Arrays

1. assign a value directly to the range using arrays, which is much faster.

Public sub writetoexcelfile (byval filename as string, byval introws as integer, byval strsql as string)

Dim filepath as string =   " F: \ System \ SCS \ programes \ scsweb \ uploadkeepqty \ Download \ "
Dim filenamepathtype as string = Filepath + Filename +   " . Xls "
Dim filenamepath as string = Filepath + Filename

Fileexist (filenamepathtype, true)

Dim excelapp as new excel. Application

Excelapp. Visible=False

Dim excelworkbook as Excel. Workbook=Excelapp. workbooks. Add ()
'Dim excelsheet as Excel. worksheet = excelworkbook. worksheets. Add ()
Dim excelsheet as Excel. Worksheet=Excelworkbook. worksheets (1)

Createheader (excelsheet)
Dim I as integer
' "A" & 2 & "indicates where to start writing," C "indicates the ending column, and" introws + 1 "indicates the number of rows ending with data.
Dim objrange as object = Excelsheet. Range ( " A "   &   2   &   " : "   &   " C "   & Introws +   1 )

'Define an array
Dim A (introws+ 1,3) As string

' Generate data records
Dim Dr as sqldatareader = Daldb. getdatareader (strsql)
For I =   0 To introws -   1
Dr. Read ()
Dim strsno as string = Dr ( " SnO " ). Tostring. Trim
Dim strpn as string = Dr ( " Materialpn " ). Tostring. Trim
Dim strqty as string = Dr ( " Qty " ). Tostring. Trim
' Excelsheet. Range ("A" + ctype (I, string). value = strsno
' Excelsheet. Range ("B" + ctype (I, string). value = strpn
' Excelsheet. Range ("C" + ctype (I, string). value = strqty
Dim J as integer
For J =   0 To 2
If J =   0 Then
A (I, 0 ) = Strsno
End if
If J =   1 Then
A (I, 1 ) = Strpn
End if
If J =   2 Then
A (I, 2 ) = Strqty
End if
Next
Next
Objrange. Value = A
' Attention of the entire unit cell: auto Scaling
Excelapp. cells. Columns. autofit ()
Excelworkbook. saveas (filenamepath)
Excelworkbook. Close (dbnull. Value, dbnull. Value, dbnull. value)
Excelapp. workbooks. Close ()
Excelapp. Quit ()
System. runtime. interopservices. Marshal. releasecomobject (excelapp)
System. runtime. interopservices. Marshal. releasecomobject (excelsheet)
System. runtime. interopservices. Marshal. releasecomobject (excelworkbook)
Excelapp = Nothing
Excelworkbook = Nothing
Excelsheet = Nothing
GC. Collect ()

End sub

 

Private sub createheader (byval excelsheet as Microsoft. Office. InterOP. Excel. worksheet)
Excelsheet. Range ( " A1 " ). Value =   " SnO "
Excelsheet. Range ( " B1 " ). Value =   " Materialpn "
Excelsheet. Range ( " C1 " ). Value =   " Qty "
Excelsheet. Range ( " D1 " ). Value =   " Keepqty/resell "
End sub