2022年通过SQL语句直接实现Excel与数据库的导入导出 .pdf

《2022年通过SQL语句直接实现Excel与数据库的导入导出 .pdf》由会员分享，可在线阅读，更多相关《2022年通过SQL语句直接实现Excel与数据库的导入导出 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、通过 SQL 语句直接实现Excel 与数据库的导入导出导入 /导出 Excel 1.-从 Excel 文件中 ,导入数据到SQL 数据库中 ,很简单 ,直接用下面的语句: /*=*/ -如果接受数据导入的表已经存在insert into 表 select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0 ,Excel 5.0;HDR=YES;DATABASE=c: est.xls,sheet1$) -如果导入数据并生成表select * into 表 from OPENROWSET(MICROSOFT.JET.OLEDB.4.0 ,Excel 5.0;HDR

2、=YES;DATABASE=c: est.xls,sheet1$) /*=*/ 2.-从 SQL 数据库中 ,导出数据到Excel: -如果从 SQL 数据库中 ,导出数据到Excel, 如果 Excel 文件已经存在 ,而且已经按照要接收的数据创建好表头,就可以简单的用: insert into OPENROWSET(MICROSOFT.JET.OLEDB.4.0 ,Excel 5.0;HDR=YES;DATABASE=c: est.xls,sheet1$) select * from 表-如果 Excel 文件不存在 ,也可以用BCP 来导成类Excel 的文件 ,注意大小写 : -导出表

3、的情况EXEC master.xp_cmdshell bcp 数据库名 .dbo. 表名out c: est.xls /c -/S服务器名 /U 用户名 -P 密码 -导出查询的情况EXEC master.xp_cmdshell bcp SELECT au_fname, au_lname FROM pubs.authors ORDER BY au_lname queryout c: est.xls /c -/S服务器名 /U 用户名 -P 密码 /*-说明 : c:test.xls 为导入 /导出的 Excel 文件名 . sheet1$ 为 Excel 文件的工作表名,一般要加上 $才能正常

4、使用 . -*/ 3.-建立存储过程，导出真正的Excel 文件（用此方法导出的Excel 文件在用于导入时不会报错）-下面是导出真正Excel 文件的方法 :（请将一下所有代码复制到存储过程中）if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1

5、页，共 7 页 - - - - - - - - - drop procedure dbo.p_exporttb GO /*/*- 数据导出EXCEL 导出表中的数据到Excel, 包含字段名 ,文件为真正的Excel 文件,如果文件不存在,将自动创建文件,如果表不存在 ,将自动创建表基于通用性考虑,仅支持导出标准数据类型-邹建2003.10( 引用请保留此信息)-*/ /*/*- 调用示例p_exporttb tbname=地区资料 ,path=c:,fname=aa.xls -*/ create proc p_exporttb tbname sysname, -要导出的表名path nvar

6、char(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

7、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

8、 Properties=Excel 8.0;HDR=YES +;DATABASE=+sql+ -连接数据库exec err=sp_oacreate adodb.connection,obj out if err0 goto lberr 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - exec err=sp_oamethod obj,open,null,constr if err0 goto lberr /*/*- 如果覆盖已经存

9、在的表,就加上下面的语句-创建之前先删除表/如果存在的话select sql=drop table +tbname+ exec err=sp_oamethod obj,execute,out out,sql -*/ -创建表的SQL select sql=,fdlist= select fdlist=fdlist+,+a.name+ ,sql=sql+,+a.name+ +case when b.name like %char then case when a.length255 then memo else text(+cast(a.length as varchar)+) end when

10、 b.name like %int or b.name=bit then int when b.name like %datetime then datetime when b.name like %money then money when b.name like %text then memo else b.name end FROM syscolumns a left join systypes b on a.xtype=b.xusertype where b.name not in(image,uniqueidentifier,sql_variant,varbinary,binary,

11、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 err0 goto lberr exec err=sp_oadestroy obj -导入数据set sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel 8.0;HDR=YES;IMEX=1 ;DATABASE

12、=+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 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - -

13、名师精心整理 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1) drop procedure dbo.p_exporttb GO /*/*- 数据导出EXCEL 导出查询中的数据到Excel, 包含字段名 ,文件为真正的Excel 文件如果文件不存在,将自动创建文件如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准

14、数据类型-邹建2003.10( 引用请保留此信息)-*/ /*/*- 调用示例p_exporttb sqlstr=select * from 地区资料 ,path=c:,fname=aa.xls,sheetname=地区资料 -*/ create proc p_exporttb sqlstr varchar(8000), -查询语句 ,如果查询语句中使用了order by , 请加上 top 100 percent path nvarchar(1000), -文件存放目录fname nvarchar(250), -文件名sheetname varchar(250)= -要创建的工作表名,默认为

15、文件名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 #

16、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;Ext

17、ended Properties=Excel 8.0;HDR=YES +;DATABASE=+sql+ 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - -连接数据库exec err=sp_oacreate adodb.connection,obj out if err0 goto lberr exec err=sp_oamethod obj,open,null,constr if err0 goto lberr -创建表的SQL

18、 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 like %char then case when a.length255 then memo else text(+cast(a.length as varchar)+

19、) end when b.name like %int or b.name=bit then int when b.name like %datetime then datetime when b.name like %money then money when b.name like %text then memo else b.name end FROM tempdb.syscolumns a left join tempdb.systypes b on a.xtype=b.xusertype where b.name not in(image,uniqueidentifier,sql_v

20、ariant,varbinary,binary,timestamp) and a.id=(select id from tempdb.sysobjects where name=tbname) if rowcount=0 return select sql=create table +sheetname +(+substring(sql,2,8000)+) ,fdlist=substring(fdlist,2,8000) exec err=sp_oamethod obj,execute,out out,sql if err0 goto lberr exec err=sp_oadestroy o

21、bj -导入数据set sql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel 8.0;HDR=YES ;DATABASE=+path+fname+,+sheetname+$) exec(insert into +sql+(+fdlist+) select +fdlist+ from +tbname+) set sql=drop table +tbname+ exec(sql) return 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共

22、 7 页 - - - - - - - - - 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 4.-在 .net 中导出到Excel 的简单实例 . (1).添加引用Microsoft Excel 11.0 Object Library (在 COM 中) (2).在 web.config的上 里加 /此句为了使用户能在本地打开文档，identify

23、 属性规定了身份验证的模式，一般情况下为false ，这样安全性较高(3).在 Text.aspx 上加 Button1 控件(4).在 cs 页面添加using Microsoft.Office.Interop.Excel; (5).添加单击事件,在这里处理将数据库中的数据导入excel, 代码如下 : /定义方法GetData(), 返回一个数据表private System.Data.DataTable GetData() . SqlConnection conn= new SqlConnection(Server=XJIE;Initial Catalog=Northwind;Uid=s

24、a;Pwd=xjie;); SqlDataAdapter adapter= new SqlDataAdapter(select CompanyName 用户名 ,ContactTitle 联系主题,Address 住宅地址 ,ContactName 联系人 ,Phone 电话 ,City 城市from Customers order by CustomerID desc,conn); DataSet ds= new DataSet(); try . adapter.Fill(ds,Customer); catch(Exception ex) . MessageBox.Show(ex.ToStr

25、ing(); return ds.Tables0; private void Button1_Click(object sender, System.EventArgs e) . Application excel= new Application(); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页 - - - - - - - - - int rowIndex=1; int colIndex=0; excel.Application.Workbooks.Add(

26、true); DataTable table=GetData(); /将所得到的表的列名,赋值给单元格foreach(DataColumn col in table.Columns) . colIndex+; excel.Cells1,colIndex=col.ColumnName; /同样方法处理数据foreach(DataRow row in table.Rows) . rowIndex+; colIndex=0; foreach(DataColumn col in table.Columns) . colIndex+; excel.CellsrowIndex,colIndex=rowcol.ColumnName.ToString(); /不可见 ,即后台处理excel.Visible=true; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -