SQL2000查询数据导出到EXCEL

 
 

  
  嘿嘿，网上找了篇导出excel的sql存储过程，执行了下。还不错可以导出excel，收藏下o(∩_∩)o...
 
 
 
 

 
 

  
  /*
  
  --数据导出EXCEL

导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10--
  
  */
  
  


  
  /*
  
  --调用示例
p_exporttb @sqlstr='select * from 地区资料'
,@path='c:/',@fname='aa.xls',@sheetname='地区资料'
--
  
  */
  
  


  
  if
  
   
  
  exists
  
   (
  
  select
  
   
  
  *
  
   
  
  from
  
   dbo.sysobjects 
  
  where
  
   id 
  
  =
  
   
  
  object_id
  
  (N
  
  '
  
  [dbo].[p_exporttb]
  
  '
  
  ) 
  
  and
  
   
  
  OBJECTPROPERTY
  
  (id, N
  
  '
  
  IsProcedure
  
  '
  
  ) 
  
  =
  
   
  
  1
  
  )

  
  drop
  
   
  
  procedure
  
   
  
  [
  
  dbo
  
  ]
  
  .
  
  [
  
  p_exporttb
  
  ]
  
  

  
  GO
  
  


  
  create
  
   
  
  proc
  
   p_exporttb

  
  @sqlstr
  
   sysname,
  
  --
  
  查询语句,如果查询语句中使用了order by ,请加上top 100 percent,注意,如果导出表/视图,用上面的存储过程
  
  

  
  @path
  
   
  
  nvarchar
  
  (
  
  1000
  
  ),
  
  --
  
  文件存放目录
  
  

  
  @fname
  
   
  
  nvarchar
  
  (
  
  250
  
  ),
  
  --
  
  文件名
  
  

  
  @sheetname
  
   
  
  varchar
  
  (
  
  250
  
  )
  
  =
  
  ''
  
  --
  
  要创建的工作表名,默认为文件名
  
  

  
  as
  
   

  
  declare
  
   
  
  @err
  
   
  
  int
  
  ,
  
  @src
  
   
  
  nvarchar
  
  (
  
  255
  
  ),
  
  @desc
  
   
  
  nvarchar
  
  (
  
  255
  
  ),
  
  @out
  
   
  
  int
  
  

  
  declare
  
   
  
  @obj
  
   
  
  int
  
  ,
  
  @constr
  
   
  
  nvarchar
  
  (
  
  1000
  
  ),
  
  @sql
  
   
  
  varchar
  
  (
  
  8000
  
  ),
  
  @fdlist
  
   
  
  varchar
  
  (
  
  8000
  
  )


  
  --
  
  参数检测
  
  

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

  
  if
  
   
  
  isnull
  
  (
  
  @sheetname
  
  ,
  
  ''
  
  )
  
  =
  
  ''
  
   
  
  set
  
   
  
  @sheetname
  
  =
  
  replace
  
  (
  
  @fname
  
  ,
  
  '
  
  .
  
  '
  
  ,
  
  '
  
  #
  
  '
  
  )


  
  --
  
  检查文件是否已经存在
  
  

  
  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
  
  


  
  --
  
  数据库创建语句
  
  

  
  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
  
  +
  
  '
  
  "
  
  '
  
  


  
  --
  
  连接数据库
  
  

  
  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
  
  

  
  declare
  
   
  
  @tbname
  
   sysname

  
  set
  
   
  
  @tbname
  
  =
  
  '
  
  ##tmp_
  
  '
  
  +
  
  convert
  
  (
  
  varchar
  
  (
  
  38
  
  ),
  
  newid
  
  ())

  
  set
  
   
  
  @sql
  
  =
  
  '
  
  select * into [
  
  '
  
  +
  
  @tbname
  
  +
  
  '
  
  ] from(
  
  '
  
  +
  
  @sqlstr
  
  +
  
  '
  
  ) a
  
  '
  
  

  
  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..systypes 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
  
  )

  
  select
  
   
  
  @sql
  
  =
  
  '
  
  create table [
  
  '
  
  +
  
  @sheetname
  
  

  
  +
  
  '
  
  ](
  
  '
  
  +
  
  substring
  
  (
  
  @sql
  
  ,
  
  2
  
  ,
  
  8000
  
  )
  
  +
  
  '
  
  )
  
  '
  
  
,
  
  @fdlist
  
  =
  
  substring
  
  (
  
  @fdlist
  
  ,
  
  2
  
  ,
  
  8000
  
  )


  
  exec
  
   
  
  @err
  
  =
  
  sp_oamethod 
  
  @obj
  
  ,
  
  '
  
  execute
  
  '
  
  ,
  
  @out
  
   out,
  
  @sql
  
  

  
  if
  
   
  
  @err
  
  <>
  
  0
  
   
  
  goto
  
   lberr


  
  exec
  
   
  
  @err
  
  =
  
  sp_oadestroy 
  
  @obj
  
  


  
  --
  
  导入数据
  
  

  
  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
  
   错误号
,
  
  @src
  
   
  
  as
  
   错误源,
  
  @desc
  
   
  
  as
  
   错误描述

  
  select
  
   
  
  @sql
  
  ,
  
  @constr
  
  ,
  
  @fdlist
  
  

  
  go
  
  



  
  /*
  
  p_exporttb @sqlstr='select * from dbo.AE_Equipment'
,@path='c:/',@fname='aa.xls',@sheetname='设备信息'
  
  */