用sql直接操作excel表

用sql直接操作excel表

start from 李孟尝

INSERT INTO OutputCane(ctime,outvalue,area,vid,ctimeint)

Select ctime,buyprice as outvalue,area,vid,left(ctime,4) as ctimeint

From OpenRowSet('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=E:\workspace\brk\table_20100816\InsertGj.xls' ,

'select * from [巴西甘蔗产量$]')

end from

直接操作excel

select b.时间as etime ,广州as value ,a.id as areaid , 3 as varid from QH_Area a, OpenRowSet('Microsoft. Jet.OLEDB.4.0','Excel 8.0;Database=D:/xitong/糖现货价格.xls','select * from [Sheet1$] where 广州is not null ') b where ='广州'

--配置权限

EXEC sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;

GO

RECONFIGURE;

GO

SQL SERVER 和EXCEL的数据导入导出

1、在SQL SERVER里查询Excel数据:

-- ======================================================

SELECT*

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于Jet 的OLE DB 提供程序查询Excel 电子表格。

SELECT*

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactio ns

-------------------------------------------------------------------------------------------------

2、将Excel的数据导入SQL server :

-- ======================================================

SELECT*into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

实例:

SELECT*into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactio ns

-------------------------------------------------------------------------------------------------

3、将SQL SERVER中查询到的数据导成一个Excel文件

-- ======================================================

T-SQL代码：

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

参数：S 是SQL服务器名；U是用户；P是密码

说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" qu eryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码：

Dim cn As New ADODB.Connection

cn.open"Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

cn.execute"master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sserve rname -Usa -Ppassword'"

------------------------------------------------------------------------------------------------

4、在SQL SERVER里往Excel插入数据:

-- ======================================================

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values(1,2,3)

T-SQL代码：

INSERT INTO

OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',

'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]

(bestand, produkt) VALUES(20, 'Test')