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()
'conn.Dispose()
MsgBox("ＯＫ！生成Excel文件成功！", MsgBoxStyle.Information, "提示")
[解决办法]
将Excel中数据导入到DataGridView中
方法一:
Private Sub listExcel(ByVal dir)
' Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dir & ",Extended Properties=Excel 8.0;"
Dim strSQL As String = "SELECT * FROM [Sheet1$]"
Dim excelConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=" & dir & ";Extended Properties=Excel 8.0;")
Dim dbCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSQL, excelConnection)
Dim dataAdapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(dbCommand)
Dim ds As DataSet = New DataSet
Try
excelConnection.Open()
dataAdapter.Fill(ds, "Excel")
DataGridView1.DataSource = ds.Tables("Excel").DefaultView
Catch sqlException As Exception
MsgBox(sqlException.ToString() & Chr(13) & "出现异常，Excel文件打开失败")
Exit Sub
Finally
dataAdapter.Dispose()
dbCommand.Dispose()
excelConnection.Close()
excelConnection.Dispose()
End Try
End Sub
方法二:
Private Sub listExcel(ByVal dir)
Dim xlApp, xlBook, xlsheet
Dim y As Integer = 1
Dim i As Integer
Dim j As Integer
Dim rows() As String
xlApp = CreateObject("Excel.Application")
Try
xlBook = xlApp.Workbooks.Open(dir)
Catch ioException As Exception
MsgBox(ioException.ToString & Chr(13) & "出现异常，Excel文件打开失败")
Exit Sub
End Try
xlsheet = xlBook.Worksheets(1)
Dim LastColNum As Integer = xlsheet.UsedRange.Columns.Count '得到列数
Dim LastRowNum As Integer = xlsheet.UsedRange.Rows.Count '得到行数
ReDim rows(LastRowNum - 1)
While (Convert.ToString(xlsheet.Cells(y, 1).Value)) <> ""
y += 1
End While
With DataGridView1
.EditMode = DataGridViewEditMode.EditOnEnter
.ColumnCount = LastColNum
.RowHeadersVisible = False
End With
For i = 1 To LastRowNum
For j = 1 To LastColNum
rows(j - 1) = Convert.ToString(xlsheet.Cells(i, j).Value)
If rows(0) <> "" Then
rows(0) = xlsheet.Range("A" & i).Text '第一列如果是时间序列的话就补充此语句
End If
Next
Me.DataGridView1.Rows.Add(rows)
Next
End Sub
使用第一种方法速度非常快 *^_^*
[解决办法]
这个是我的导出CSV文件代码，供参阅
'输出文件 Private Sub btnOutput_Lzxt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOutput_Lzxt.Click '设置打开对话框，格式为文本格式 With sfdOutput .Filter = "CSV文件(*.csv)|*.csv" .FilterIndex = 1 .Title = "保存文件" End With Try '判断用户是否选择了文件 If sfdOutput.ShowDialog() = Windows.Forms.DialogResult.OK Then strFileName = sfdOutput.FileName End If Catch ex As Exception End Try Dim ds As DataSet = New DataSet Dim foxDA As SqlDataAdapter = New SqlDataAdapter '利用 SqlConnectionStringBuilder 对象来构建连接字符串 Dim strConnection As String = "Server=localhost;" & _ "DataBase=MIS;" & _ "Integrated Security=SSPI" ' 建立一个数据连接 Dim myConnection As New SqlConnection(strConnection) Dim sqlSelect As String = _ "SELECT G.XM AS 姓名,LEFT(G.GZ072,4)+'-'+RIGHT(G.GZ072,2)+'-16' AS 工资年月," & _ "J.LZXTID AS 身份证号码,'G6120640000' AS 部门名称,'02' AS 工资区划," & _ "G.GZ004 AS 保留工资,G.GZ021 AS 书报费,G.GZ017 AS 洗理费,G.GZ018 AS 女工卫生费," & _ "G.GZ016 AS 夜班费,G.GZ010 AS 苦累脏险,G.GZ007 AS 技术津贴," & _ "G.GZ006 AS 物价补贴,G.GZ012 AS 艰苦津贴,G.GZ013 AS 施工津贴," & _ "G.GZ008 AS 工长津贴,G.GZ009 AS 技师津贴,G.GZ015 AS 加班费," & _ "G.GZ023 AS 奖金,G.GZ024 AS 其他应得,G.GZ025 AS 补发," & _ "G.GZ027 AS 养老,G.GZ031 AS 储养,G.GZ029 AS 医保," & _ "G.GZ030 AS 失业,G.GZ028 AS 公积金,G.GZ014 AS 午餐费 " & _ " FROM [GZCW] AS G JOIN JHBS AS J " & _ " ON J.GZH=G.GZH AND G.GZ072=" & Format(DateTimePicker1.Value, "yyyyMM") Try ' 开启连接 myConnection.Open() With foxDA .MissingSchemaAction = MissingSchemaAction.AddWithKey .SelectCommand = New SqlCommand(sqlSelect, myConnection) .Fill(ds, "Gzcw") End With '将结果输出到文件 Dim sw As StreamWriter 'If Not File.Exists(strFileName) Then 'True 是指以追加的方式打开指定文件 sw = New StreamWriter(strFileName, True, System.Text.Encoding.Default) Dim strCol As String '写入标题 For i As Integer = 0 To ds.Tables("Gzcw").Columns.Count - 1 strCol &= ds.Tables("Gzcw").Columns(i).ColumnName & "," Next sw.WriteLine(strCol) '写入数据 For i As Integer = 0 To ds.Tables("Gzcw").Rows.Count - 1 strCol = Nothing For j As Integer = 0 To ds.Tables("Gzcw").Columns.Count - 1 strCol &= ds.Tables("Gzcw").Rows(i).Item(j) & "," Next sw.WriteLine(strCol) Next With sw .Flush() .Close() End With 'End If sw = Nothing myConnection.Close() MessageBox.Show("导出成功！") Catch ex As Exception MessageBox.Show(ex.ToString(), "出错啦～", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub