--导出excel处理
declare @s nvarchar(4000)
set @s='bcp "'+
+N'select top 100 percent '
+N' case c.colid when 1 then o.name else N'''' end as 表名,'
+N' c.colid as 序号,'
+N' c.name as 字段名,'
+N' t.name 数据类型,'
+N' c.prec as 长度,'
+N' p.value as 字段说明,'
+N' m.text as 默认值'
+N' from '+quotename(@dbname)+N'.dbo.sysobjects o'
+N' inner join '+quotename(@dbname)+N'.dbo.syscolumns c on o.id=c.id'
+N' inner join '+quotename(@dbname)+N'.dbo.systypes t on c.xusertype=t.xusertype'
+N' left join '+quotename(@dbname)+N'.dbo.sysproperties p on c.id=p.id and c.colid = p.smallid'
+N' left join '+quotename(@dbname)+N'.dbo.syscomments m on c.cdefault=m.id'
+N' where o.xtype in(N''U'') and o.status>=0'
+N' order by c.id,c.colid'
+N'" queryout "'+@fname
+N'" /P"" /w'
exec master..xp_cmdshell @s,no_output
go


--查询
sqlserver中直接通过代码访问excel


SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

甚至可以：
DELETE 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions


你还可以：

EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\Temp\Part.xls',
   NULL,
   'Excel 5.0'
GO

EXEC sp_addlinkedsrvlogin 'excelsource', 'false', NULL, NULL, NULL

在excel中选定范围,选择菜单'插入'->'名称'->'定义'

示例

SELECT *
FROM EXCEL...SalesData  (整页使用 Sheet1$) 

如果碰到转换为NULL值请使用文本文件来做DTS转换

------解决方案--------------------
别人的 改的

你自己改改就可以了
SET QUOTED_IDENTIFIER ON  
GO
SET ANSI_NULLS ON  
GO



















/*--数据导出EXCEL
导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10(引用请保留此信息)--*/
/*--调用示例
p_exporttb @SqlTableName='地区资料',@filePath='c:\',@excelTableName='aa.xls'
--*/
ALTER                proc p_exporttb
@SqlTableName sysname,--要导出的表名
@filePath nvarchar(1000),--文件存放目录
@excelTableName nvarchar(250)='',--文件名
@excelSheetName nvarchar(250) = 'sheet1' , --表名  
@tmBgn datetime = '1970-1-1 8:00',
@tmEnd  datetime = '1970-1-1 8:00'
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
declare @ibgn int
declare @iend int
declare @filterstr nvarchar(255)
--参数检测
if isnull(@excelTableName,'')=''set @excelTableName=@SqlTableName+'.xls'

select @ibgn = datediff(ss,'1970-1-1 8:00',@tmBgn)
select @iend = datediff(ss,'1970-1-1 8:00',@tmend)
select @filterstr = ' where EventTime >' +cast(@ibgn as varchar(12))+ ' and EventTime <' + cast(@iend as varchar(12))
--检查文件是否已经存在
if right(@filePath,1)<>'\' set @filePath=@filePath+'\'
create table #tb(a bit,b bit,c bit)
set @sql=@filePath+@excelTableName
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句
set @sql=@filePath+@excelTableName
if exists(select 1 from #tb where a=1)
set @constr='DRIVE