在我的datagrid中共有34条记录,当我执行下列语句导出到EXECL时,记录被不连续导出,不知何因?
Private Sub 导出成excel格式_Click()
sql = "select * from 报销 "
Call dakailianjie(sql)
rstmember.Sort = "No"
Dim i As Integer
Dim j As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
Set xlSheet = xlBook.Worksheets(1)
For j = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, j + 1) = DataGrid1.Columns.Item(j).Caption
Next j
rstmember.MoveFirst
For i = 0 To rstmember.RecordCount - 1
DataGrid1.Row = i
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
'MsgBox DataGrid1.Text
If IsNull(DataGrid1.Text) = False Then
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
End If
Next j
Next i
End Sub导出结果:
No 日期 单据
1 2007-3-26 1
2 2007-3-26 2
3 2007-3-26 3
4 2007-3-29 1
5 2007-3-29 1
6 2007-3-29 1
7 2007-3-30 1
8 2007-3-30 1
9 2007-3-30 1
10 2007-4-2 1
11 2007-4-2 1
12 2007-4-2 1
13 2007-4-2 1
14 2007-4-2 1
15 2007-4-2 1
17 2007-4-3 1
20 2007-4-11 1
24 2007-4-12 1
29 2007-4-13 1
Private Sub 导出成excel格式_Click()
sql = "select * from 报销 "
Call dakailianjie(sql)
rstmember.Sort = "No"
Dim i As Integer
Dim j As Integer
Dim xlApp As New Excel.Application
Dim xlBook As New Excel.Workbook
Dim xlSheet As New Excel.Worksheet
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
Set xlSheet = xlBook.Worksheets(1)
For j = 0 To DataGrid1.Columns.Count - 1
xlSheet.Cells(1, j + 1) = DataGrid1.Columns.Item(j).Caption
Next j
rstmember.MoveFirst
For i = 0 To rstmember.RecordCount - 1
DataGrid1.Row = i
For j = 0 To DataGrid1.Columns.Count - 1
DataGrid1.Col = j
'MsgBox DataGrid1.Text
If IsNull(DataGrid1.Text) = False Then
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
End If
Next j
Next i
End Sub导出结果:
No 日期 单据
1 2007-3-26 1
2 2007-3-26 2
3 2007-3-26 3
4 2007-3-29 1
5 2007-3-29 1
6 2007-3-29 1
7 2007-3-30 1
8 2007-3-30 1
9 2007-3-30 1
10 2007-4-2 1
11 2007-4-2 1
12 2007-4-2 1
13 2007-4-2 1
14 2007-4-2 1
15 2007-4-2 1
17 2007-4-3 1
20 2007-4-11 1
24 2007-4-12 1
29 2007-4-13 1
就一句话,还N快
创建了excel的sheet以后
mysheet.Cells.CopyFromRecordset Recordset
这样就把datagrid的rs的记录都放到excel的sheet里面了
On Error Resume Next
Set xlBook = xlApp.Workbooks.Open("d:\text2.xls")
首先,你上面这段代码,既然下面要open ,上面就没必要add了,一add,就多了一个book1对象
如果你最后用代码退出的时候忘了处理,可能会提示是否要保存book1什么的
我觉得如果你的有问题,可能就是下面这里了
If IsNull(DataGrid1.Text) = False Then
xlSheet.Cells(i + 2, j + 1) = DataGrid1.Text
End If
另外,你说的好处应该就是填充标题吧?
但是你可以先把recordset放进去,然后再插入标题不是更好,循环少了N多
主要是省资源,循环操作excel很占资源的
很容易就能查出原因的
我这里没有你的数据库表,不好跟
目的是想提取datagrid中日期列月、日分别导出到EXECL的月、日列中。