在Project-References中选中Microsoft Excel 10.0 Ojbect Library我写的一个例子: '******************************************************************************** '* 功能 描述:将检索结果得到的临时表内容导出到Excel表格中( '* 参数 说明: '* 输入:None '* 输出:None '* 返回值说明:成功-1,失败-0 '* 作 者:阿九 '* 更 新: '* 创建 日期:2004/3/10 '* 更新 日期: '******************************************************************************** Public Function ExportToExcel() As Long Dim uExcel As Excel.Application Dim uExcelBook As Excel.Workbook Dim adoCmm As Command Dim adoRec As Recordset Dim strSQL, strTemp As String Dim intList, intI, intJ As Integer 'intRow 行,intList 列 Dim intRow As Long
On Error GoTo ErrorHandler
Set adoCmm = GetCommand strSQL = "select count(*) as TotalCount from " & gTempTable '临时表存在的记录数 adoCmm.CommandText = strSQL Set adoRec = adoCmm.Execute If Not adoRec.BOF And Not adoRec.EOF Then intRow = adoRec("TotalCount") '取得行数 intList = 11 '列数固定为11列 adoRec.Close strSQL = "select TestCode,PatCode,OpeUser from " & gTempTable & " order by TestCode" adoCmm.CommandText = strSQL Set adoRec = adoCmm.Execute If intRow > 0 Then Set uExcel = New Excel.Application uExcel.Visible = True uExcel.SheetsInNewWorkbook = 1 Set uExcelBook = uExcel.Workbooks.Add '打开Excel '边框设置 With uExcel.ActiveSheet.Range("A1:K" & (intRow + 1) & "").Borders .LineStyle = 1 .Weight = xlThin .ColorIndex = 1 End With '字体设置(第一行以粗体显示) 高度设为 20 'With uExcel.ActiveSheet.Range("A1:K1").Font '.Size = 14 '.Bold = True '.Italic = True '.ColorIndex = 3 'End With uExcel.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter '水平居中 uExcel.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter '垂直居中 '设置第一行标题 With uExcel.ActiveSheet .Cells(1, 1).Value = "测试编号1" .Cells(1, 2).Value = "测试编号2" .Cells(1, 3).Value = "操作人员" '…… End With End If '填充数据行 intI = 2 Do While Not adoRec.EOF With uExcel.ActiveSheet .Cells(intI, 1).Value = adoRec("TestCode") .Cells(intI, 2).Value = adoRec("PatCode") .Cells(intI, 3).Value = adoRec("OpeUser") End With intI = intI + 1 adoRec.MoveNext Loop adoRec.Close
'uExcel.ActiveSheet.PageSetup.Orientation = xlPortrait 'xlLandscape 'uExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4 '适应于A4纸 'uExcel.ActiveSheet.PrintOut'打印输出 'uExcel.DisplayAlerts = False '不保存后退出 'uExcel.Quit 'uExcel.DisplayAlerts = True 'uExcel.Quit Set uExcel = Nothing Set uExcelBook = Nothing ExportToExcel = 1 Exit Function ErrorHandler: mvarErrorInfo = Err.Description ExportToExcel = 0 End Function
EXEC master..xp_cmdshell 'bcp TESTDB.dbo.tmp out C:\test.xls -c -q -S "(local)" -U "sa" -P ""'
'********************************************************************************
'* 功能 描述:将检索结果得到的临时表内容导出到Excel表格中(
'* 参数 说明:
'* 输入:None
'* 输出:None
'* 返回值说明:成功-1,失败-0
'* 作 者:阿九
'* 更 新:
'* 创建 日期:2004/3/10
'* 更新 日期:
'********************************************************************************
Public Function ExportToExcel() As Long
Dim uExcel As Excel.Application
Dim uExcelBook As Excel.Workbook
Dim adoCmm As Command
Dim adoRec As Recordset
Dim strSQL, strTemp As String
Dim intList, intI, intJ As Integer 'intRow 行,intList 列
Dim intRow As Long
On Error GoTo ErrorHandler
Set adoCmm = GetCommand
strSQL = "select count(*) as TotalCount from " & gTempTable '临时表存在的记录数
adoCmm.CommandText = strSQL
Set adoRec = adoCmm.Execute
If Not adoRec.BOF And Not adoRec.EOF Then intRow = adoRec("TotalCount") '取得行数
intList = 11 '列数固定为11列
adoRec.Close
strSQL = "select TestCode,PatCode,OpeUser from " & gTempTable & " order by TestCode"
adoCmm.CommandText = strSQL
Set adoRec = adoCmm.Execute
If intRow > 0 Then
Set uExcel = New Excel.Application
uExcel.Visible = True
uExcel.SheetsInNewWorkbook = 1
Set uExcelBook = uExcel.Workbooks.Add '打开Excel
'边框设置
With uExcel.ActiveSheet.Range("A1:K" & (intRow + 1) & "").Borders
.LineStyle = 1
.Weight = xlThin
.ColorIndex = 1
End With
'字体设置(第一行以粗体显示) 高度设为 20
'With uExcel.ActiveSheet.Range("A1:K1").Font
'.Size = 14
'.Bold = True
'.Italic = True
'.ColorIndex = 3
'End With
uExcel.ActiveSheet.Rows.HorizontalAlignment = xlVAlignCenter '水平居中
uExcel.ActiveSheet.Rows.VerticalAlignment = xlVAlignCenter '垂直居中
'设置第一行标题
With uExcel.ActiveSheet
.Cells(1, 1).Value = "测试编号1"
.Cells(1, 2).Value = "测试编号2"
.Cells(1, 3).Value = "操作人员"
'……
End With
End If
'填充数据行
intI = 2
Do While Not adoRec.EOF
With uExcel.ActiveSheet
.Cells(intI, 1).Value = adoRec("TestCode")
.Cells(intI, 2).Value = adoRec("PatCode")
.Cells(intI, 3).Value = adoRec("OpeUser")
End With
intI = intI + 1
adoRec.MoveNext
Loop
adoRec.Close
'uExcel.ActiveSheet.PageSetup.Orientation = xlPortrait 'xlLandscape
'uExcel.ActiveSheet.PageSetup.PaperSize = xlPaperA4 '适应于A4纸
'uExcel.ActiveSheet.PrintOut'打印输出
'uExcel.DisplayAlerts = False '不保存后退出
'uExcel.Quit
'uExcel.DisplayAlerts = True
'uExcel.Quit
Set uExcel = Nothing
Set uExcelBook = Nothing
ExportToExcel = 1
Exit Function
ErrorHandler:
mvarErrorInfo = Err.Description
ExportToExcel = 0
End Function