Question

sql语句将数据库表中的数据导出到excel，怎么操作？？

insert into OPENROWSET (serialId,loginId,userName,logClass,logMethod,createTime,logLevel,infoMsg)
VALUES('Microsoft.Jet.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=d:\est.xls',
'SELECT * FROM [aa$]') select * from dbo.tbl_SysLogInfo
这样写哪里有问题？？？

Answer 1

首先要确定SQL语句的运行环境。以下是VB里的数据导出代码，已假设创建了一个adodc控件：
Dim i, j As Integer
Dim ex As Object
Dim exwbook As Object
Dim exsheet As Object

Set ex = CreateObject("Excel.Application") '创建EXCEL对象
Set exwbook = ex.Workbooks.Add '打开文件
ex.Visible = True
Set exsheet = exwbook.Worksheets("sheet1") '设定工作表

With Adodc1.Recordset
For j = 2 To .Fields.Count
exsheet.Cells(1, 1) = "序号"
exsheet.Cells(1, j) = .Fields(j - 1).Name
Next

.MoveFirst
For i = 1 To .RecordCount
For j = 2 To .Fields.Count
exsheet.Cells(i + 1, 1) = i
exsheet.Cells(i + 1, j) = .Fields(j - 1).Value
Next
.MoveNext
Next i
End With

Answer 2

INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/Temp.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...Sheet1$(A1,A2) VALUES (1,2)
--注：A1,A2,A3 为自己在Excel文档中添加的列名，而不是Excel 默认的 A,B,C,D等
--把values值改成select即可

Answer 3

office2007直接连接SQL数据库直接在office中引入。。

Answer 4

用过sqlserver的，sql不能换行。