从DataGrid导出到EXCEL文件的有关问题
从DataGrid导出到EXCEL文件的问题
我已经用程序实现从DataGrid导出到EXCEL文件,但现在有个问题是在DataGrid导出的速度快于excel写入的速度。当数据量大的时候,DataGrid已经走到记录的最末一笔,而excel还在写入前面的数据时,就会重复的写入最后一笔至到记录数。
请问各位高手,有没有办法控制一下,必须写完一行才执行下一行。
------解决方案--------------------
这个是不是导入速度过快的问题我不太清楚 据我了解是只能导出当前显示的距离 给你个方法看看
Private Sub btnPrint_Click()
Dim i, r, c As Integer
Dim newxls As New Excel.Application '声明application 对象
Dim newbook As New Excel.Workbook
Dim newsheet As New Excel.Worksheet
Set newbook = newxls.Workbooks.Add '创建工作薄
Set newsheet = newbook.Worksheets(1) '创建工作表
With CommonDialog1
.CancelError = False '在对话框中,按cancel键不出现错误
.Flags = cdlOFNHideReadOnly & cdlOFNOverwritePrompt
.Filter = "Excel Files (*.xls)
------解决方案--------------------
*.xls" '对话框底部“保存文件类型“下拉匡显示的内容"
.FilterIndex = 1 '选择"保存类型"的第一个值为默认值,即"Excel Files (*.xls)
------解决方案--------------------
*.xls"
.ShowSave
'设置Excel内容的格式,现在暂时不用
'With newsheet
' .Cells.Font.Name = "System"
' .Cells.Font.Size = 12
' .Name = "数据列表"
'
' Range(Cells(1, 1), Cells(1, DataGrid1.Columns.Count)).Select
' Selection.HorizontalAlignment = xlCenter
' Selection.VerticalAlignment = xlCenter
' Selection.Merge '合并居中
' .Cells(1, 1) = "导出excel文件"
'End With
If rs.RecordCount > 0 Then
For i = 0 To DataGrid1.Columns.Count - 1 '循环添加表头
newsheet.Cells(2, i + 1) = DataGrid1.Columns(i).Caption
Next
rs.MoveFirst
Do Until rs.EOF
r = rs.AbsolutePosition '读取recordset对象当前记录的序号位置
For c = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = c '读取地c列数据
newsheet.Cells(r + 2, c + 1) = DataGrid1.Columns(c)
Next
rs.MoveNext
Loop
End If
newsheet.SaveAs FileName:=CommonDialog1.FileName
newbook.Close
newxls.Quit
Set newbook = Nothing
Set newsheet = Nothing
Set newxls = Nothing
Set conn = Nothing
我已经用程序实现从DataGrid导出到EXCEL文件,但现在有个问题是在DataGrid导出的速度快于excel写入的速度。当数据量大的时候,DataGrid已经走到记录的最末一笔,而excel还在写入前面的数据时,就会重复的写入最后一笔至到记录数。
请问各位高手,有没有办法控制一下,必须写完一行才执行下一行。
For i = 0 To Adodc1.Recordset.RecordCount - 1
For j = 0 To Adodc1.Recordset.Fields.Count - 1
On Error Resume Next
DataGrid1.Row = i
DataGrid1.Col = j
Range("i:j").NumberFormatLocal = "@"
newsheet.Cells(i + 1, j + 1) = DataGrid1.Text
Next j
Next i
------解决方案--------------------
这个是不是导入速度过快的问题我不太清楚 据我了解是只能导出当前显示的距离 给你个方法看看
Private Sub btnPrint_Click()
Dim i, r, c As Integer
Dim newxls As New Excel.Application '声明application 对象
Dim newbook As New Excel.Workbook
Dim newsheet As New Excel.Worksheet
Set newbook = newxls.Workbooks.Add '创建工作薄
Set newsheet = newbook.Worksheets(1) '创建工作表
With CommonDialog1
.CancelError = False '在对话框中,按cancel键不出现错误
.Flags = cdlOFNHideReadOnly & cdlOFNOverwritePrompt
.Filter = "Excel Files (*.xls)
------解决方案--------------------
*.xls" '对话框底部“保存文件类型“下拉匡显示的内容"
.FilterIndex = 1 '选择"保存类型"的第一个值为默认值,即"Excel Files (*.xls)
------解决方案--------------------
*.xls"
.ShowSave
'设置Excel内容的格式,现在暂时不用
'With newsheet
' .Cells.Font.Name = "System"
' .Cells.Font.Size = 12
' .Name = "数据列表"
'
' Range(Cells(1, 1), Cells(1, DataGrid1.Columns.Count)).Select
' Selection.HorizontalAlignment = xlCenter
' Selection.VerticalAlignment = xlCenter
' Selection.Merge '合并居中
' .Cells(1, 1) = "导出excel文件"
'End With
If rs.RecordCount > 0 Then
For i = 0 To DataGrid1.Columns.Count - 1 '循环添加表头
newsheet.Cells(2, i + 1) = DataGrid1.Columns(i).Caption
Next
rs.MoveFirst
Do Until rs.EOF
r = rs.AbsolutePosition '读取recordset对象当前记录的序号位置
For c = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = c '读取地c列数据
newsheet.Cells(r + 2, c + 1) = DataGrid1.Columns(c)
Next
rs.MoveNext
Loop
End If
newsheet.SaveAs FileName:=CommonDialog1.FileName
newbook.Close
newxls.Quit
Set newbook = Nothing
Set newsheet = Nothing
Set newxls = Nothing
Set conn = Nothing