SQL操作EXCEL.doc

《SQL操作EXCEL.doc》由会员分享，可在线阅读，更多相关《SQL操作EXCEL.doc（7页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流SQL操作EXCEL.精品文档.通过SQL语句直接实现Excel与数据库的导入导出2011-03-23 23:16转载自 sunsung111最终编辑 sunsung111导入/导出Excel 1.-从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:-如果接受数据导入的表已经存在insert into 表 select * from OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=YES;DATABASE=c: est.xls,sheet1$)-如果导入数据并生成表sel

2、ect * into 表 from OPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel 5.0;HDR=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 表-如果Exce

3、l文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:-导出表的情况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$ 为Exc

4、el文件的工作表名,一般要加上$才能正常使用.3.-建立存储过程，导出真正的Excel文件（用此方法导出的Excel文件在用于导入时不会报错）-下面是导出真正Excel文件的方法:（请将一下所有代码复制到存储过程中）if exists (select * from dbo.sysobjects where id = object_id(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_exporttbGO/*/*-数据导出EXCEL导出表中的数据到Excel,包含字段名,文件为真正的Ex

5、cel文件,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型-邹建 2003.10(引用请保留此信息)-*/*/*-调用示例p_exporttb tbname=地区资料,path=c:,fname=aa.xlscreate proc p_exporttbtbname sysname, -要导出的表名path nvarchar(1000), -文件存放目录fname nvarchar(250)= -文件名,默认为表名asdeclare err int,src nvarchar(255),desc nvarchar(255),out intdecla

6、re 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+fnameinsert into #tb exec master.xp_fileexist sql-数据库创建语句set sql=path+fnameif exists(select 1

7、from #tb where a=1)set constr=DRIVER=Microsoft Excel Driver (*.xls);DSN=;READONLY=FALSE +;CREATE_DB=+sql+;DBQ=+sqlelseset constr=Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;HDR=YES +;DATABASE=+sql+-连接数据库exec err=sp_oacreate adodb.connection,obj outif err0 goto lberrexec err=sp_oam

8、ethod obj,open,null,constrif err0 goto lberr/*/*-如果覆盖已经存在的表,就加上下面的语句-创建之前先删除表/如果存在的话select sql=drop table +tbname+exec err=sp_oamethod obj,execute,out out,sql-创建表的SQLselect sql=,fdlist=select fdlist=fdlist+,+a.name+,sql=sql+,+a.name+ +case when b.name like %char then case when a.length255 then memo

9、else text(+cast(a.length as varchar)+) 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 endFROM syscolumns a left join systypes b on a.xtype=b.xusertypewhere b.name not in(image,u

10、niqueidentifier,sql_variant,varbinary,binary,timestamp)and object_id(tbname)=idselect sql=create table +tbname+(+substring(sql,2,8000)+),fdlist=substring(fdlist,2,8000)exec err=sp_oamethod obj,execute,out out,sqlif err0 goto lberrexec err=sp_oadestroy obj-导入数据set sql=openrowset(MICROSOFT.JET.OLEDB.4

11、.0,Excel 8.0;HDR=YES;IMEX=1 ;DATABASE=+path+fname+,+tbname+$)exec(insert into +sql+(+fdlist+) select +fdlist+ from +tbname)returnlberr:exec sp_oageterrorinfo 0,src out,desc outlbexit:select cast(err as varbinary(4) as 错误号,src as 错误源,desc as 错误描述select sql,constr,fdlistgoif exists (select * from dbo.

12、sysobjects where id = object_id(Ndbo.p_exporttb) and OBJECTPROPERTY(id, NIsProcedure) = 1)drop procedure dbo.p_exporttbGO/*/*-数据导出EXCEL导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件如果文件不存在,将自动创建文件如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型-邹建 2003.10(引用请保留此信息)-*/*/*-调用示例p_exporttb sqlstr=select * from 地区资料,path=c:,fname=a

13、a.xls,sheetname=地区资料create proc p_exporttbsqlstr varchar(8000), -查询语句,如果查询语句中使用了order by ,请加上top 100 percentpath nvarchar(1000), -文件存放目录fname nvarchar(250), -文件名sheetname varchar(250)= -要创建的工作表名,默认为文件名as declare err int,src nvarchar(255),desc nvarchar(255),out intdeclare obj int,constr nvarchar(1000

14、),sql varchar(8000),fdlist varchar(8000)-参数检测if isnull(fname,)= set fname=temp.xlsif 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+fnameinsert into #tb exec master.xp_fileexist sql-数据库创建语句set sql=path+fna

15、meif exists(select 1 from #tb where a=1)set constr=DRIVER=Microsoft Excel Driver (*.xls);DSN=;READONLY=FALSE +;CREATE_DB=+sql+;DBQ=+sqlelseset constr=Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;HDR=YES +;DATABASE=+sql+-连接数据库exec err=sp_oacreate adodb.connection,obj outif err0 goto

16、 lberrexec err=sp_oamethod obj,open,null,constrif err0 goto lberr-创建表的SQLdeclare tbname sysnameset tbname=#tmp_+convert(varchar(38),newid()set sql=select * into +tbname+ from(+sqlstr+) aexec(sql)select sql=,fdlist=select fdlist=fdlist+,+a.name+,sql=sql+,+a.name+ +case when b.name like %char then cas

17、e when a.length255 then memo else text(+cast(a.length as varchar)+) 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 endFROM tempdb.syscolumns a left join tempdb.systypes b on a.x

18、type=b.xusertypewhere b.name not in(image,uniqueidentifier,sql_variant,varbinary,binary,timestamp)and a.id=(select id from tempdb.sysobjects where name=tbname)if rowcount=0 returnselect sql=create table +sheetname+(+substring(sql,2,8000)+),fdlist=substring(fdlist,2,8000)exec err=sp_oamethod obj,exec

19、ute,out out,sqlif err0 goto lberrexec err=sp_oadestroy obj-导入数据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)returnlberr:exec sp_oageterrorinfo 0,src

20、out,desc outlbexit:select cast(err as varbinary(4) as 错误号,src as 错误源,desc as 错误描述select sql,constr,fdlistgo4.-在.net中导出到Excel的简单实例.(1).添加引用Microsoft Excel 11.0 Object Library (在COM中)(2).在web.config的上里加/此句为了使用户能在本地打开文档，identify属性规定了身份验证的模式，一般情况下为false，这样安全性较高(3).在Text.aspx上加Button1控件(4).在cs页面添加 using

21、Microsoft.Office.Interop.Excel;(5).添加单击事件,在这里处理将数据库中的数据导入excel,代码如下:/定义方法GetData(),返回一个数据表 private System.Data.DataTable GetData() SqlConnection conn= new SqlConnection(Server=XJIE;Initial Catalog=Northwind;Uid=sa;Pwd=xjie;); SqlDataAdapter adapter= new SqlDataAdapter(select CompanyName 用户名,ContactT

22、itle 联系主题,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.ToString(); return ds.Tables0; private void Button1_Click(object sender, System.EventArgs e) Applica

23、tion excel= new Application(); int rowIndex=1; int colIndex=0; excel.Application.Workbooks.Add(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;