datagrid导成电子表格的问题？
请各位看下面的代码：
Private Sub Command1_Click()
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
SetxlApp = 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
xlSheet.Cells(6, 1) = "i"
Adodc1.Recordset.MoveFirst
For i = 0 To Adodc1.Recordset.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
Private Sub Form_Load()
Adodc1.ConnectionString = "Provider=SQLOLEDB.1;Password=material2000;Persist Security Info=True;User ID=materialadmin;Initial Catalog=material;Data Source=10.63.208.71"
Adodc1.RecordSource = "select * from material where materialcode like '0352%'"
Adodc1.Refresh
Set DataGrid1.DataSource = Adodc1
End Sub
运行以后，可以导出电子表格，但是发现有跳过记录的现象
就是recordset并非按顺序走，莫名其妙的跳过几个，然后用最后一条记录补齐剩下的记录数，非常奇怪，现在已经被搞糊涂了！还请各位高手指点一下！
问题点数：100、回复次数：11Top
1 楼daisy8675（莫依 沉迷）回复于 2005-07-18 17:50:57 得分 20
你这个方法不好。给你看个方法，可以把连接混成pubs中的jobs表
Option Explicit
Public Rs As New ADODB.Recordset
Public Conn As New ADODB.Connection
Public strConn As String
Private Sub Command1_Click()
ExporToExcel strConn
End Sub
Private Sub Form_Load()
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
Conn.CursorLocation = adUseClient
Conn.Open strConn
If Rs.State <> adStateClosed Then Rs.Close
Rs.Open "Select * from jobs", Conn, adOpenStatic, adLockOptimistic
Set DataGrid1.DataSource = Rs
End Sub
Public Function ExporToExcel(strOpen As String)
Dim Rs_Data As New ADODB.Recordset
Dim Irowcount As Integer
Dim Icolcount As Integer
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlQuery As Excel.QueryTable
With Rs_Data
If Rs_Data.State <> adStateClosed Then Rs_Data.Close
.Open "Select * from jobs", Conn, adOpenStatic, adLockOptimistic
End With
With Rs_Data
If .RecordCount < 1 Then
MsgBox ("没有记录!")
Exit Function
End If
Irowcount = .RecordCount
Icolcount = .Fields.Count
End With
Set xlApp = CreateObject("Excel.Application")
Set xlBook = Nothing
Set xlSheet = Nothing
Set xlBook = xlApp.Workbooks().Add
Set xlSheet = xlBook.Worksheets("sheet1")
xlApp.Visible = True
Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
With xlQuery
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With
xlQuery.FieldNames = True
xlQuery.Refresh
xlApp.Application.Visible = True
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing
End Function
Top
2 楼daisy8675（莫依 沉迷）回复于 2005-07-18 17:59:45 得分 10
你的那段代码我用pubs中的jobs试了下没发现跳号现象呀Top
3 楼verious（曾经嘲笑自寻烦恼，现在无奈自找烦恼--平民的苦恼！）回复于 2005-07-18 18:01:50 得分 35
晕，不用这么复杂吧？
我来个简单点的
Dim myexcel As New Excel.Application
Dim mybook As New Excel.Workbook
Dim mysheet As New Excel.Worksheet
Set mybook = myexcel.Workbooks.Add '添加一个新的BOOK
Set mysheet = mybook.Worksheets.Add '添加一个新的SHEET
Dim rows As Integer
rows = 1
mysheet.Cells(rows, 1).Value = "数据项"
mysheet.Cells(rows, 2).Value = "数据项"
... ...
rows = rows + 1
mysheet.Cells(rows, 1).CopyFromRecordset Adodc1.Recordset
myexcel.Visible = True
'使用应用程序对象的 Quit 方法关闭 Excel。
myexcel.Quit
'释放该对象变量
Set myexcel = Nothing
Set mybook = Nothing
Set mysheet = NothingTop
4 楼verious（曾经嘲笑自寻烦恼，现在无奈自找烦恼--平民的苦恼！）回复于 2005-07-18 18:04:12 得分 0
我是直接将ADODC的数据直接导出到EXCEL表中Top
5 楼haitao5676（笑纹）回复于 2005-07-18 18:07:11 得分 0
daisy8675(莫依)：
这句话出错：Set xlQuery = xlSheet.QueryTables.Add(Rs_Data, xlSheet.Range("a1"))
无效的过程或参数调用！Top
6 楼haitao5676（笑纹）回复于 2005-07-18 18:10:46 得分 0
datagrid导出后有个好处，可以利用datagrid每列的标题信息填充字段说明，不用显示原来表里的英文字段，所以我只需要导出datagrid，然后再excel表的头一行用datagrid的标题信息填充，从而形成报表形式，可惜我的代码总是在跳跃记录Top
7 楼verious（曾经嘲笑自寻烦恼，现在无奈自找烦恼--平民的苦恼！）回复于 2005-07-18 18:13:27 得分 5
我那个可以哦
我现在正在用Top
8 楼haitao5676（笑纹）回复于 2005-07-18 18:20:13 得分 0
那就怪了，在我的机器上，到处的数据怪得离谱，头5、6条记录很正常，7、8条开始每读完一条记录指针突然空出4、5条记录，然后读一条，然后再跳，一直到结尾，然后被跳过的记录用最后一条记录补齐数量，真是气死我了！Top
9 楼jxgzay（jxgzay）回复于 2005-07-18 18:25:55 得分 30
我的实例：
Dim newapp As Excel.Application
Dim newbook As Excel.Workbook
Dim newsheet As Excel.Worksheet
Set newapp = CreateObject("excel.application")
Set newbook = newapp.Workbooks.Add
Set newsheet = newbook.ActiveSheet
newapp.Visible = True
m = Adodc1.Recordset.Fields.Count
n = Adodc1.Recordset.RecordCount
'填写标题
For i = 1 To m
newsheet.Cells(1, i) = Adodc1.Recordset.Fields(i - 1).Name
Next
Adodc1.Recordset.MoveFirst
'填写内容
If n <> 0 Then
For i = 1 To n
For j = 1 To m
newsheet.Cells(i + 1, j) = Adodc1.Recordset(j - 1)
Next
Adodc1.Recordset.MoveNext
Next
End If
。。。
Top
10 楼haitao5676（笑纹）回复于 2005-07-18 18:25:58 得分 0
我换了台机器仍然是一个问题，难道我们的机器都有毛病？Top
11 楼haitao5676（笑纹）回复于 2005-07-18 18:34:09 得分 0
好了，用adodc还是挺方便的，谢谢各位，我决定用adodc的方法，稍后加分，人人有份Top
