社区 › MSSQL数据库开发 › MSSQL网页编程之导进/导出Excel

再现理想 发表于 2015-1-16 22:28:41

MSSQL网页编程之导进/导出Excel

你看出了作者的深度？深处半米！当初是冲那么多的大牛给他写序才买的，后来才发现无啥内容，作者也只是才用几年的新手，百花了几十两银子，再次感叹当今社会的虚伪与浮躁excel|导出excel
从Excel文件中,导进数据到SQL数据库中,很复杂,间接用上面的语句:

/*===================================================================*/
--假如承受数据导进的表已存在
insertinto表select*from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:        est.xls,sheet1$)

--假如导进数据并天生表
select*into表from
OPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:        est.xls,sheet1$)


/*===================================================================*/
--假如从SQL数据库中,导出数据到Excel,假如Excel文件已存在,并且已依照要吸收的数据创立好表头,就能够复杂的用:
insertintoOPENROWSET(MICROSOFT.JET.OLEDB.4.0
,Excel5.0;HDR=YES;DATABASE=c:        est.xls,sheet1$)
select*from表


--假如Excel文件不存在,也能够用BCP来导成类Excel的文件,注重巨细写:
--导出表的情形
EXECmaster..xp_cmdshellbcp数据库名.dbo.表名out"c:        est.xls"/c-/S"服务器名"/U"用户名"-P"暗码"

--导出查询的情形
EXECmaster..xp_cmdshellbcp"SELECTau_fname,au_lnameFROMpubs..authorsORDERBYau_lname"queryout"c:        est.xls"/c-/S"服务器名"/U"用户名"-P"暗码"

申明.

c:        est.xls为导进/导出的Excel文件名.
sheet1$为Excel文件的事情表名,一样平常要加上$才干一般利用.



上面是导出真正Excel文件的办法:


/*--数据导出EXCEL

导出表中的数据到Excel,包括字段名,文件为真实的Excel文件
,假如文件不存在,将主动创立文件
,假如表不存在,将主动创立表
基于通用性思索,仅撑持导出尺度数据范例
---*/

/*--挪用示例

p_exporttb@tbname=区域材料,@path=c:,@fname=aa.xls
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure.
GO

createprocp_exporttb
@tbnamesysname,--要导出的表名,注重只能是表名/视图名
@pathnvarchar(1000),--文件寄存目次
@fnamenvarchar(250)=--文件名,默许为表名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)

--参数检测
ifisnull(@fname,)=set@fname=@tbname+.xls

--反省文件是不是已存在
ifright(@path,1)set@path=@path+
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql

--数据库创立语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr=DRIVER={MicrosoftExcelDriver(*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB="+@sql+";DBQ=+@sql
else
set@constr=Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel5.0;HDR=YES
+;DATABASE=+@sql+"

--毗连数据库
exec@err=sp_oacreateadodb.connection,@objout
if@err0gotolberr

exec@err=sp_oamethod@obj,open,null,@constr
if@err0gotolberr

--创立表的SQL
select@sql=,@fdlist=
select@fdlist=@fdlist+,+a.name
,@sql=@sql+,[+a.name+]
+casewhenb.namein(char,nchar,varchar,nvarchar)then
text(+cast(casewhena.length>255then255elsea.lengthendasvarchar)+)
whenb.namein(tynyint,int,bigint,tinyint)thenint
whenb.namein(smalldatetime,datetime)thendatetime
whenb.namein(money,smallmoney)thenmoney
elseb.nameend
FROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertype
whereb.namenotin(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
andobject_id(@tbname)=id
select@sql=createtable[+@tbname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)

exec@err=sp_oamethod@obj,execute,@outout,@sql
if@err0gotolberr

exec@err=sp_oadestroy@obj

--导进数据
set@sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES
;DATABASE=+@path+@fname+,[+@tbname+$])

exec(insertinto+@sql+(+@fdlist+)select+@fdlist+from+@tbname)

return

lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as毛病号
,@srcas毛病源,@descas毛病形貌
select@sql,@constr,@fdlist
go



*--数据导出EXCEL

导出查询中的数据到Excel,包括字段名,文件为真实的Excel文件
,假如文件不存在,将主动创立文件
,假如表不存在,将主动创立表
基于通用性思索,仅撑持导出尺度数据范例
--*/

/*--挪用示例

p_exporttb@sqlstr=select*from区域材料
,@path=c:,@fname=aa.xls,@sheetname=区域材料
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N.)andOBJECTPROPERTY(id,NIsProcedure)=1)
dropprocedure.
GO

createprocp_exporttb
@sqlstrsysname,--查询语句,假如查询语句中利用了orderby,请加上top100percent,注重,假如导出表/视图,用下面的存储历程
@pathnvarchar(1000),--文件寄存目次
@fnamenvarchar(250),--文件名
@sheetnamevarchar(250)=--要创立的事情表名,默许为文件名
as
declare@errint,@srcnvarchar(255),@descnvarchar(255),@outint
declare@objint,@constrnvarchar(1000),@sqlvarchar(8000),@fdlistvarchar(8000)

--参数检测
ifisnull(@fname,)=set@fname=temp.xls
ifisnull(@sheetname,)=set@sheetname=replace(@fname,.,#)

--反省文件是不是已存在
ifright(@path,1)set@path=@path+
createtable#tb(abit,bbit,cbit)
set@sql=@path+@fname
insertinto#tbexecmaster..xp_fileexist@sql

--数据库创立语句
set@sql=@path+@fname
ifexists(select1from#tbwherea=1)
set@constr=DRIVER={MicrosoftExcelDriver(*.xls)};DSN=;READONLY=FALSE
+;CREATE_DB="+@sql+";DBQ=+@sql
else
set@constr=Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties="Excel5.0;HDR=YES
+;DATABASE=+@sql+"

--毗连数据库
exec@err=sp_oacreateadodb.connection,@objout
if@err0gotolberr

exec@err=sp_oamethod@obj,open,null,@constr
if@err0gotolberr

--创立表的SQL
declare@tbnamesysname
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+]
+casewhenb.namein(char,nchar,varchar,nvarchar)then
text(+cast(casewhena.length>255then255elsea.lengthendasvarchar)+)
whenb.namein(tynyint,int,bigint,tinyint)thenint
whenb.namein(smalldatetime,datetime)thendatetime
whenb.namein(money,smallmoney)thenmoney
elseb.nameend
FROMtempdb..syscolumnsaleftjointempdb..systypesbona.xtype=b.xusertype
whereb.namenotin(image,text,uniqueidentifier,sql_variant,ntext,varbinary,binary,timestamp)
anda.id=(selectidfromtempdb..sysobjectswherename=@tbname)
select@sql=createtable[+@sheetname
+](+substring(@sql,2,8000)+)
,@fdlist=substring(@fdlist,2,8000)

exec@err=sp_oamethod@obj,execute,@outout,@sql
if@err0gotolberr

exec@err=sp_oadestroy@obj

--导进数据
set@sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES
;DATABASE=+@path+@fname+,[+@sheetname+$])

exec(insertinto+@sql+(+@fdlist+)select+@fdlist+from[+@tbname+])

set@sql=droptable[+@tbname+]
exec(@sql)
return

lberr:
execsp_oageterrorinfo0,@srcout,@descout
lbexit:
selectcast(@errasvarbinary(4))as毛病号
,@srcas毛病源,@descas毛病形貌
select@sql,@constr,@fdlist
go

Merge将一定数量的MyISAM表联合而成一个整体，在超大规模数据存储时很有用

飘飘悠悠 发表于 2015-1-19 14:07:03

是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

乐观 发表于 2015-1-26 10:13:18

如安全管理、备份恢复、性能监控和调优等，SQL只要熟悉基本操作就可以，只要程序设计部分只要稍加了解即可（如存储过程、触发器等）。

深爱那片海 发表于 2015-2-4 14:35:05

如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

老尸 发表于 2015-2-10 02:06:52

需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

精灵巫婆 发表于 2015-2-28 15:15:20

你可以简单地认为适合的就是好，不适合就是不好。

小魔女 发表于 2015-3-10 00:51:47

以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

第二个灵魂 发表于 2015-3-17 04:07:48

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

愤怒的大鸟 发表于 2015-3-23 18:40:25

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

查看完整版本: MSSQL网页编程之导进/导出Excel