SQL Server Excel operations

1. query Excel Data

Select *

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

'Excel 5.0; HDR = yes; database = C:/aa.xls ', 'select * from [sheet1 $]')

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/aa.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]

2. Export the queried data into an Excel file

A. BCP

Exec master.. xp_mongoshell 'bcp database name. DBO. Table name out C:/temp.xls-C-Q-s "servername"-U "sa"-P ""'

Parameter: s indicates the SQL server name, u indicates the user, and P indicates the password.

B. Proc

/* -- Data export Excel

Export the queried data to excel, including the field name. The file is a real Excel file.

If the file does not exist, the file is automatically created.

If the table does not exist, the table is automatically created.

Only standard data types can be exported for the sake of universality.

-- Producer build 2003.10 --

-- Flystone fixed creation failure when the file exists

*/

/* -- Call example

P_exporttb @ sqlstr = 'select * From region information'

, @ Path = 'C:/',@fname='aa.xls', @ sheetname = 'region information'

--*/

If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sp_exporttb] ') and objectproperty (ID, n' isprocedure') = 1)

Drop procedure [DBO]. [sp_exporttb]

Go

Create proc sp_exporttb

@ Sqlstr varchar (8000), -- query statement. If order by is used in the query statement, add Top 100 percent. Note that if the table/view is exported, use the following stored procedure.

@ Path nvarchar (1000), -- file storage directory

@ Fname nvarchar (250), -- file name

@ Sheetname varchar (250) = ''-- Name of the worksheet to be created. The default value is the file name.

As

Declare @ err int, @ SRC nvarchar (255), @ DESC nvarchar (255), @ out int

Declare @ OBJ int, @ constr nvarchar (1000), @ SQL varchar (8000), @ fdlist varchar (8000)

-- Parameter detection

If isnull (@ fname, '') = ''set @fname='temp.xls'

If isnull (@ sheetname, '') ='' set @ sheetname = Replace (@ fname ,'.','#')

-- Check whether the file already exists

If right (@ path, 1) <> '/'set @ Path = @ path + '/'

Create Table # Tb (a bit, B bit, C bit)

Set @ SQL = @ path + @ fname

Insert into # TB exec master .. xp_fileexist @ SQL

-- Database creation statement

Set @ SQL = @ path + @ fname

-- If exists (select 1 from # TB where a = 1)

-- Set @ constr = 'driver = {Microsoft Excel Driver (*. xls)}; DSN = '''; readonly = false'

-- + '; Create_db = "' + @ SQL + '"; DBQ =' + @ SQL

-- Else

Set @ constr = 'provider = Microsoft. Jet. oledb.4.0; extended properties = "Excel 5.0; HDR = Yes'

+ '; Database =' + @ SQL + '"'

-- Connect to the database

Exec @ err = sp_oacreate 'ADODB. connection', @ OBJ out

If @ err <> 0 goto lberr

Exec @ err = sp_oamethod @ OBJ, 'open', null, @ constr

If @ err <> 0 goto lberr

-- SQL statement used to create a table

Declare @ tbname sysname

Set @ tbname = '# TMP _' + convert (varchar (38), newid ())

Set @ SQL = 'select * into ['+ @ tbname +'] From ('+ @ sqlstr +')'

Exec (@ SQL)

Select @ SQL = '', @ fdlist =''

Select @ fdlist = @ fdlist + ',' + A. Name

, @ SQL = @ SQL + ', [' + A. Name + ']'

+ Case when B. Name in ('Char ', 'nchar', 'varchar ', 'nvarchar') then

'Text ('+ Cast (case when a. length> 255 then 255 else A. Length End as varchar) + ')'

When B. Name in ('tynyint', 'int', 'bigint', 'tinyint') Then 'int'

When B. Name in ('smalldatetime', 'datetime') Then 'datetime'

When B. Name in ('money', 'smallmoney') Then 'money'

Else B. Name end

From tempdb .. syscolumns a left join tempdb .. policypes B on A. xtype = B. xusertype

Where B. name not in ('image', 'text', 'uniqueidentifier', 'SQL _ variant', 'ntext', 'varbinary ', 'binary', 'timestamp ')

And a. ID = (select ID from tempdb .. sysobjects where name = @ tbname)

If not exists (select 1 from # TB where a = 1)

Begin

Select @ SQL = 'create table ['+ @ sheetname

+ '] (' + Substring (@ SQL, 2,8000) + ')'

, @ Fdlist = substring (@ fdlist, 2,8000)

Exec @ err = sp_oamethod @ OBJ, 'execute ', @ out, @ SQL

If @ err <> 0 goto lberr

End

Else

Set @ fdlist = substring (@ fdlist, 2,8000)

Exec @ err = sp_oadestroy @ OBJ

-- Import data

Set @ SQL = 'openrowset (''microsoft. Jet. oledb.4.0 '', ''excel 5.0; HDR = Yes

; Database = '+ @ path + @ fname + ''', [' + @ sheetname + '$])'

Exec ('insert into '+ @ SQL +' ('+ @ fdlist +') select' + @ fdlist + 'from [' + @ tbname + ']')

Set @ SQL = 'drop table ['+ @ tbname +']'

Exec (@ SQL)

Return

Lberr:

Exec sp_oageterrorinfo 0, @ SRC out, @ DESC out

Lbexit:

Select cast (@ err as varbinary (4) as error code

, @ SRC as error source, @ DESC as error description

Select @ SQL, @ constr, @ fdlist

Go

3. insert data to 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)

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liaolian9948/archive/2010/02/04/5288229.aspx