VB.NET 将DataGrid1中的数据生成Excel 为何这么慢呢? 有没有更快生成Excel 的办法
VB.NET 将DataGrid1中的数据生成Excel 为什么这么慢呢? 有没有更快生成Excel 的办法?
以下是我的一个按据, 点击它时,它将查出来的DataGrid中的内容来生成Excel文件,但是我发现,当记录数不多时,还可以接受,快! 但是当记录数达到5万时,则非常慢了!
有没有更快的方法呀!
SaveFileDialog1.ShowDialog()
SaveFileDialog1.Filter = "EXCEL文件(*.xls)|*.xls"
'Dim filename As String = SaveFileDialog1.FileName
Dim ntmpfilename As String = SaveFileDialog1.FileName
Dim s As Integer = ntmpfilename.LastIndexOf(".")
Dim filepath As String
If s < 0 Then
filepath = ntmpfilename + ".xls"
Else
'Dim t As String = ntmpfilename.Substring(s, 4)
filepath = ntmpfilename
End If
Dim wkb As Excel.Workbook
Dim appExcel As New Excel.Application
Dim i, j As Integer
Dim ds As New DataSet
ds = mydataset
If File.Exists(filepath) Then
wkb = appExcel.Workbooks.Add(filepath)
wkb.Worksheets("sheet1").Activate()
Else
Dim exl As Object
exl = CreateObject("Excel.Application")
exl.Workbooks.add()
exl.worksheets("sheet1").Activate()
exl.ActiveWorkbook.SaveAs(filepath)
exl.quit()
End If
wkb = appExcel.Workbooks.Add(filepath)
wkb.Worksheets("sheet1").Activate()
'If File.Exists(filepath) Then File.Delete(filepath)
'wkb.SaveCopyAs(filepath)
'Try
For i = 0 To ds.Tables(0).Rows.Count - 4 'Excel文件添加行
appExcel.Range("A2").Select()
appExcel.Selection.EntireRow.Insert()
Next i
For i = 0 To ds.Tables(0).Columns.Count - 4 'Excel文件添加列
appExcel.Range("B2").Select()
appExcel.Selection.EntireColumn.Insert()
Next
'Excel文件写页眉
For i = 0 To ds.Tables(0).Columns.Count - 1
appExcel.Cells(3, i + 2) = ds.Tables(0).Columns(i).Caption.ToString
Next i
For i = 0 To ds.Tables(0).Rows.Count - 1 'Excel文件写数据
appExcel.Cells(i + 4, 1) = i + 1
For j = 0 To ds.Tables(0).Columns.Count - 1
appExcel.Cells(i + 4, j + 2) = ds.Tables(0).Rows(i)(j).ToString
Next j
Next i
With appExcel.Range("A1") '设置标题字体
'.Value = Trim(strTitle)
.Value = "XXX数据"
.Font.Name = "宋体"
.Font.Size = 22
'.Strikethrough = False
'.Superscript = False
'.Subscript = False
'.OutlineFont = False
'.Shadow = False
End With
If File.Exists(filepath) Then
File.Delete(filepath)
wkb.SaveCopyAs(filepath)
End If
wkb.Close(False, Nothing, Nothing)
appExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(wkb)
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel)
wkb = Nothing
appExcel = Nothing
GC.Collect()
'da.Dispose()
'mydataset.Dispose()
'conn.Close()
以下是我的一个按据, 点击它时,它将查出来的DataGrid中的内容来生成Excel文件,但是我发现,当记录数不多时,还可以接受,快! 但是当记录数达到5万时,则非常慢了!
有没有更快的方法呀!
SaveFileDialog1.ShowDialog()
SaveFileDialog1.Filter = "EXCEL文件(*.xls)|*.xls"
'Dim filename As String = SaveFileDialog1.FileName
Dim ntmpfilename As String = SaveFileDialog1.FileName
Dim s As Integer = ntmpfilename.LastIndexOf(".")
Dim filepath As String
If s < 0 Then
filepath = ntmpfilename + ".xls"
Else
'Dim t As String = ntmpfilename.Substring(s, 4)
filepath = ntmpfilename
End If
Dim wkb As Excel.Workbook
Dim appExcel As New Excel.Application
Dim i, j As Integer
Dim ds As New DataSet
ds = mydataset
If File.Exists(filepath) Then
wkb = appExcel.Workbooks.Add(filepath)
wkb.Worksheets("sheet1").Activate()
Else
Dim exl As Object
exl = CreateObject("Excel.Application")
exl.Workbooks.add()
exl.worksheets("sheet1").Activate()
exl.ActiveWorkbook.SaveAs(filepath)
exl.quit()
End If
wkb = appExcel.Workbooks.Add(filepath)
wkb.Worksheets("sheet1").Activate()
'If File.Exists(filepath) Then File.Delete(filepath)
'wkb.SaveCopyAs(filepath)
'Try
For i = 0 To ds.Tables(0).Rows.Count - 4 'Excel文件添加行
appExcel.Range("A2").Select()
appExcel.Selection.EntireRow.Insert()
Next i
For i = 0 To ds.Tables(0).Columns.Count - 4 'Excel文件添加列
appExcel.Range("B2").Select()
appExcel.Selection.EntireColumn.Insert()
Next
'Excel文件写页眉
For i = 0 To ds.Tables(0).Columns.Count - 1
appExcel.Cells(3, i + 2) = ds.Tables(0).Columns(i).Caption.ToString
Next i
For i = 0 To ds.Tables(0).Rows.Count - 1 'Excel文件写数据
appExcel.Cells(i + 4, 1) = i + 1
For j = 0 To ds.Tables(0).Columns.Count - 1
appExcel.Cells(i + 4, j + 2) = ds.Tables(0).Rows(i)(j).ToString
Next j
Next i
With appExcel.Range("A1") '设置标题字体
'.Value = Trim(strTitle)
.Value = "XXX数据"
.Font.Name = "宋体"
.Font.Size = 22
'.Strikethrough = False
'.Superscript = False
'.Subscript = False
'.OutlineFont = False
'.Shadow = False
End With
If File.Exists(filepath) Then
File.Delete(filepath)
wkb.SaveCopyAs(filepath)
End If
wkb.Close(False, Nothing, Nothing)
appExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(wkb)
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel)
wkb = Nothing
appExcel = Nothing
GC.Collect()
'da.Dispose()
'mydataset.Dispose()
'conn.Close()