与

来源：互联网发布：350模板和淘宝什么区别编辑：程序博客网时间：2024/06/10 15:22

/*
存储过程名称：导出数据到Excel
功能描述：导出数据到Excel

EXECExportToExcel@server='.',
                  @uname='sa',
                  @pwd='',
                  @QueryText='SELECT*FROMdldata..bbbbbb',
                  @filename='d:/ImportToExcel.xls'
*/

IFOBJECT_ID('ExportToExcel')ISNOTNULLDROPPROCExportToExcel
GO

CREATEPROCEDUREExportToExcel(
@serversysname=null,
@unamesysname=null,
@pwdsysname=null,
@QueryTextvarchar(200)=null,
@filenamevarchar(200)='d:/ImportToExcel.xls'
)
AS
DECLARE@SQLServerint,--SQLDMO.SQLServer对象
       @QueryResultsint,--QueryResults对象
       @CurrentResultSetint,
       @objectint,--Excel.Application对象
       @WorkBooksint,
       @WorkBookint,
       @Rangeint,
       @hrint,
       @Columnsint,
       @Rowsint,
       @indColumnint,
       @indRowint,
       @off_Columnint,
       @off_Rowint,
       @code_strvarchar(100),
       @result_strvarchar(255)

IF@QueryTextISNULL
BEGIN
  PRINT'Setthequerystring'
  RETURN
END

--设置服务器名为本地服务器(@@servername返回运行SQLServer的本地服务器名称)
IF@serverISNULLSELECT@server=@@servername

--设置用户名为当前系统用户名(使用SYSTEM_USER返回当前系统用户名)
IF@unameISNULLSELECT@uname=SYSTEM_USER

SETNOCOUNTON

--创建SQLDMO.SQLServer对象
EXEC@hr=sp_OACreate'SQLDMO.SQLServer',@SQLServerOUT
IF@hr<>0
BEGIN
  PRINT'errorcreateSQLDMO.SQLServer'
  RETURN
END

--连接到SQLServer系统
IF@pwdISNULL
BEGIN
  EXEC@hr=sp_OAMethod@SQLServer,'Connect',null,@server,@uname
  IF@hr<>0
  BEGIN
     PRINT'errorConnect'
     RETURN
  END
END
ELSE
BEGIN
  EXEC@hr=sp_OAMethod@SQLServer,'Connect',null,@server,@uname,@pwd
  IF@hr<>0
  BEGIN
     PRINT'errorConnect'
     RETURN
  END
END

--TheExecuteWithResultsmethodexecutesaTransact-SQLcommandbatch
--returningbatchresultsetsinaQueryResultsobject共3页  第1页
<script type="text/javascript"></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>