asp.net com excel利用数组写入excel 效率提高
1.利用数组直接赋值给range,速度明显快多了。
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 & " 是从哪里开始写入,"C" 结束的列," intRows + 1"是数据结束的行数
Dim ObjRange As Object = ExcelSheet.Range("A" & 2 & ":" & "C" & intRows + 1)
'定义一个数组
Dim a(intRows + 1, 3) As String
'生成數據
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
'調整單元格的寬度:自動適應
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
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 & " 是从哪里开始写入,"C" 结束的列," intRows + 1"是数据结束的行数
Dim ObjRange As Object = ExcelSheet.Range("A" & 2 & ":" & "C" & intRows + 1)
'定义一个数组
Dim a(intRows + 1, 3) As String
'生成數據
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
'調整單元格的寬度:自動適應
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
ExcelSheet.Range("A1").Value = "SNO"
ExcelSheet.Range("B1").Value = "MaterialPN"
ExcelSheet.Range("C1").Value = "Qty"
ExcelSheet.Range("D1").Value = "KeepQty/ReSell"
End Sub