VB高手请进.使用VB6.0的DATAGRID控件输出到EXCEL里时只显示可见行,请问如何显示所有行,请高手给我代码好吗

Question

我的关键代码如下.

Private Sub Command1_Click()
Dim i, j As Integer
Dim xlApplication As Excel.Application, xlWorkbook As Excel.Workbook, xlSheet
On Error Resume Next
Set xlApplication = GetObject(, "Excel.Application")
If Err.Number <> 0 Then Set xlApplication = CreateObject("Excel.Application")
Set xlWorkbook = xlApplication.Workbooks.Add
Set xlSheet = xlWorkbook.ActiveSheet
For i = 1 To DataGrid1.Columns.Count
xlSheet.Cells(1, i) = DataGrid1.Columns(i - 1).Caption
For j = 0 To DataGrid1.VisibleRows - 1
xlSheet.Cells(j + 2, i) = DataGrid1.Columns(i - 1).CellText(DataGrid1.RowBookmark(j))
Next j
Next i

我知道肯定是这里的VISIBLEROWS的问题,但是DATAGRID里没有DATAGRID1.ROWS这个属性啊,有什么办法能输出所有行的,我快疯了.跪求代码...

Answer 1

我是直接对ADOC操作的，因此它不会受rows 的visible属性影响
因一般来说，每个DataGrid会对应一个ADODC，或RecordSet对象作为数据源。
直接对它们操作就行了。这样有个好处就是可以根据字段的属性格式化相应的列，如将调整日期或日期的显示格式。

'输出表头
For i = 0 To Me.Adodc1.Recordset.Fields.Count - 1
'设置列名
strColName1 = Chr(65 + i) & "1"
oSheet.Range(strColName1).Value = Me.DataGrid1.Columns(i).Caption

'据DataGrid中的宽度设置EXCEL列宽
StrColName = Chr(65 + i) & ":" & Chr(65 + i)
oSheet.Columns(StrColName).ColumnWidth = Int(Me.DataGrid1.Columns(i).Width / 90)

Next i

'输出内容
Do Until Me.Adodc1.Recordset.EOF
j = j + 1

For i = 0 To Me.Adodc1.Recordset.Fields.Count - 1
StrColName = Chr(65 + i) & CStr(j + 1)
If Not IsNull(Me.DataGrid1.Columns(i)) And Me.DataGrid1.Columns(i) <> "" Then

'根据列的属性对表格数据作相应格式化处理
Select Case Me.Adodc1.Recordset.Fields(i).Type
Case adCurrency
oSheet.Range(StrColName).Value = CCur(Me.DataGrid1.Columns(i))
Case adNumeric
oSheet.Range(StrColName).Value = CLng(Me.DataGrid1.Columns(i))
Case adInteger
oSheet.Range(StrColName).Value = CInt(Me.DataGrid1.Columns(i))
Case adDate
oSheet.Range(StrColName).Value = CDate(Me.DataGrid1.Columns(i))
oSheet.Range(StrColName).NumberFormatLocal = "yyyy-mm-dd hh:mm"
Case Else
oSheet.Range(StrColName).Value = Me.DataGrid1.Columns(i)

End Select
Else
oSheet.Range(StrColName).Value = Me.DataGrid1.Columns(i)
End If
Next i

Me.Adodc1.Recordset.MoveNext
Loop

Answer 2

建议结合ADODC控件和DATAGRID控件的各自属性作将在DATA中显示的数据送EXCEL，代码如下：
Option Explicit
Dim i, j, k As Integer
Dim strConn As String
Dim pubConn As New ADODB.Connection
Dim rsTable As New ADODB.Recordset
Dim strSQL As String
Dim xlapp As Variant
Dim xlBook As Variant
Dim xlSHEET As Variant

Private Sub Command1_Click()
Set xlapp = CreateObject("excel.application")
Set xlBook = xlapp.workbooks.Add
Set xlSHEET = xlBook.worksheets(1)
xlapp.Visible = True
On Error Resume Next
If Err.Number <> 0 Then Set xlapp = CreateObject("Excel.Application")
Set xlBook = xlapp.workbooks.Add
Set xlSHEET = xlBook.ActiveSheet
For k = 1 To DataGrid1.Columns.Count
xlSHEET.Cells(1, k) = DataGrid1.Columns(k - 1).Caption
Next k
For i = 1 To Adodc1.Recordset.RecordCount + 1
For j = 0 To DataGrid1.Columns.Count
xlSHEET.Cells(i + 1, j + 1) = Adodc1.Recordset(j) '
Next j
Adodc1.Recordset.MoveNext
Next i
End Sub

Private Sub Form_Load()
strSQL = "select * from mdlk_sj where 批号='D012'"
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\hxrkgl.mdb;Persist Security Info=False"
Adodc1.RecordSource = strSQL
Adodc1.Refresh
End Sub