导入/导出Excel

原创 2004年09月21日 10:25:00
从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:
 
/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
 
--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
 
 
/*===================================================================*/
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
select * from 表
 
 
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:/test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
 
--导出查询的情况
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:/test.xls" /c -/S"服务器名" /U"用户名" -P"密码"'
 
说明.
 
c:/test.xls  为导入/导出的Excel文件名.
sheet1$      为Excel文件的工作表名,一般要加上$才能正常使用.
 
 
 
下面是导出真正Excel文件的方法:
 
 
/*--数据导出EXCEL
 
 导出表中的数据到Excel,包含字段名,文件为真正的Excel文件
 ,如果文件不存在,将自动创建文件
 ,如果表不存在,将自动创建表
 基于通用性考虑,仅支持导出标准数据类型
---*/
 
/*--调用示例
 
 p_exporttb @tbname='地区资料',@path='c:/',@fname='aa.xls'
--*/
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
@tbname sysname,    --要导出的表名,注意只能是表名/视图名
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(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=@tbname+'.xls'
 
--检查文件是否已经存在
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
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 syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
 and object_id(@tbname)=id
select @sql='create table ['+@tbname
 +']('+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+''',['+@tbname+'$])'
 
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
 
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
 
 
 
*--数据导出EXCEL
 
 导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
 ,如果文件不存在,将自动创建文件
 ,如果表不存在,将自动创建表
 基于通用性考虑,仅支持导出标准数据类型
--*/
 
/*--调用示例
 
 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
版权声明:本文为博主原创文章,未经博主允许不得转载。

超实用的PHPExcel[导入][导出]实现方法总结

首先需要去官网https://github.com/PHPOffice/PHPExcel/下载PHPExcel,下载后只需要Classes目录下的文件即可。 1、PHPExcel导出方法实现过程 /*...
  • u014236259
  • u014236259
  • 2017年03月06日 21:53
  • 5433

通用的excel导入导出类

原文链接:http://www.iteye.com/topic/657977 闲来无事,做了一个通用的excel导入导出类。  可以在数据库查询过滤的基础上导出数据(只需传入过滤后的List即可), ...
  • jarniyy
  • jarniyy
  • 2016年03月20日 22:47
  • 856

Java实现Excel的导入和导出

一、下载jxl.jar包导入到工程中 http://download.csdn.net/detail/lrici/9758600 二、新建Book.java package com.cc.r...
  • Lrici
  • Lrici
  • 2017年02月20日 13:43
  • 11313

excel导入导出实战

新建Java项目ImportExcelDemo,环境是eclipse+jdk1.8+mysql5.5,主要是完成一个单表,没有主外键关联的导入导出的功能.将excel中的数据,导入到数据库中;将数据库...
  • liuyanlinglanq
  • liuyanlinglanq
  • 2015年08月21日 13:18
  • 1105

web项目实现Excel数据导入导出

由于项目要求,需要实现一个数据库信息导出为Excel文件,并能将Excel文件中的信息导入数据库的功能,网上找了一下资料,发现大都只涉及到Excel文件的简单操作,所以特地在此分享了自己写的两个简单的...
  • xiaoshuifu
  • xiaoshuifu
  • 2016年06月19日 23:00
  • 9395

excel导入导出的通用方案

随着企业 IT 系统的不断升级,很多企业有提升遗留系统的强烈需求。而整合遗留系统中的信息资源是其中最为关键的一步,进而可对信息进行数据挖掘等创新工作。本文涉及这样的一个应用案例,企业的数据信息是以 E...
  • ETTTTTSS
  • ETTTTTSS
  • 2014年07月14日 14:38
  • 4979

java中实现Excel的导入与导出

1 使用Jxl实现Excel的导入与导出 1 ) 新建实体类Book [Java] view plain copy package com.b...
  • provence_20
  • provence_20
  • 2017年11月27日 22:55
  • 104

利用POI实现Excel的导入导出

在项目开中,我们经常需要用excel来导入导出数据,而POI技术是实现我们需求的一个选择。 他兼容2003-2013的版本(目前只测试到2013,以后如果有测试更新的,再修改) 以下是一个对应03...
  • qq_1017097573
  • qq_1017097573
  • 2017年02月18日 22:51
  • 3377

thinkphp实现excel数据的导入导出

首先需要确认项目内是否存在 ThinkPHP/Library/Org/Util/PHPExcel ThinkPHP/Library/Org/Util/PHPExcel.class.php //模板代码...
  • hdxyzlh_0225
  • hdxyzlh_0225
  • 2016年07月07日 10:35
  • 3193

PHP 使用PHPExcel实现文件导入与导出功能

PHPExcel文件导入导出常用方法 1、PHP文件导入 上传文件 页面样式 HTML代码 "" enctype="multipart/form-data" method="post" ...
  • s390124971
  • s390124971
  • 2016年10月24日 13:52
  • 8015
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:导入/导出Excel
举报原因:
原因补充:

(最多只允许输入30个字)