我这里有datagrid里的数据导入Excel中的代码,你可以参考一下. Dim Irow, Icol As Integer Dim Irowcount, Icolcount As Integer Dim Fieldlen() '存字段长度值 Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) With Ado_Output.Recordset .MoveLast If .RecordCount < 1 Then MsgBox ("Error 没有记录!") Exit Sub End If Irowcount = .RecordCount '"记录总数 Icolcount = .Fields.Count '"字段总数 ReDim Fieldlen(Icolcount) .MoveFirst For Irow = 1 To Irowcount + 1 For Icol = 1 To Icolcount Select Case Irow Case 1 '"在Excel中的第一行加标题 xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name Case 2 '"将数组FIELDLEN()存为第一条记录的字段长 If IsNull(.Fields(Icol - 1)) = True Then Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name) '"如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度 Else Fieldlen(Icol) = LenB(.Fields(Icol - 1)) End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol) '"Excel列宽等于字段长 xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1) '"向Excel的CellS中写入字段值 Case Else Fieldlen1 = LenB(.Fields(Icol - 1)) If Fieldlen(Icol) < Fieldlen1 Then xlSheet.Columns(Icol).ColumnWidth = Fieldlen1 '"表格列宽等于较长字段长 Fieldlen(Icol) = Fieldlen1 '"数组Fieldlen(Icol)中存放最大字段长度值 Else xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol) End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1) End Select Next If Irow <> 1 Then If Not .EOF Then .MoveNext End If Next With xlSheet .Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "宋体" '"设标题为黑体字 .Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True '"标题字体加粗 .Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous '"设表格边框样式 End With xlApp.Visible = True '"显示表格 xlBook.Save '"保存" Set xlApp = Nothing '"交还控制给Excel End With
Dim Irow, Icol As Integer
Dim Irowcount, Icolcount As Integer
Dim Fieldlen() '存字段长度值
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1) With Ado_Output.Recordset
.MoveLast If .RecordCount < 1 Then
MsgBox ("Error 没有记录!")
Exit Sub
End If Irowcount = .RecordCount '"记录总数
Icolcount = .Fields.Count '"字段总数 ReDim Fieldlen(Icolcount)
.MoveFirst For Irow = 1 To Irowcount + 1
For Icol = 1 To Icolcount
Select Case Irow
Case 1 '"在Excel中的第一行加标题
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1).Name
Case 2 '"将数组FIELDLEN()存为第一条记录的字段长 If IsNull(.Fields(Icol - 1)) = True Then
Fieldlen(Icol) = LenB(.Fields(Icol - 1).Name)
'"如果字段值为NULL,则将数组Filelen(Icol)的值设为标题名的宽度
Else
Fieldlen(Icol) = LenB(.Fields(Icol - 1))
End If xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
'"Excel列宽等于字段长
xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
'"向Excel的CellS中写入字段值
Case Else
Fieldlen1 = LenB(.Fields(Icol - 1)) If Fieldlen(Icol) < Fieldlen1 Then
xlSheet.Columns(Icol).ColumnWidth = Fieldlen1
'"表格列宽等于较长字段长
Fieldlen(Icol) = Fieldlen1
'"数组Fieldlen(Icol)中存放最大字段长度值
Else
xlSheet.Columns(Icol).ColumnWidth = Fieldlen(Icol)
End If xlSheet.Cells(Irow, Icol).Value = .Fields(Icol - 1)
End Select
Next
If Irow <> 1 Then
If Not .EOF Then .MoveNext
End If
Next
With xlSheet
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Name = "宋体"
'"设标题为黑体字
.Range(.Cells(1, 1), .Cells(1, Icol - 1)).Font.Bold = True
'"标题字体加粗
.Range(.Cells(1, 1), .Cells(Irow, Icol - 1)).Borders.LineStyle = xlContinuous
'"设表格边框样式
End With
xlApp.Visible = True '"显示表格
xlBook.Save '"保存"
Set xlApp = Nothing '"交还控制给Excel
End With