SQL操作EXCEL备课讲稿.doc

《SQL操作EXCEL备课讲稿.doc》由会员分享，可在线阅读，更多相关《SQL操作EXCEL备课讲稿.doc（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。SQL操作EXCEL-通过SQL语句直接实现Excel与数据库的导入导出2011-03-2323:16转载自sunsung111最终编辑sunsung111导入/导出Excel1.-从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句:/*=*/-如果接受数据导入的表已经存在insertinto表select*fromOPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES;DATABASE=c:est.xls,sheet1$)-如果导入数

2、据并生成表select*into表fromOPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES;DATABASE=c:est.xls,sheet1$)/*=*/2.-从SQL数据库中,导出数据到Excel:-如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:insertintoOPENROWSET(MICROSOFT.JET.OLEDB.4.0,Excel5.0;HDR=YES;DATABASE=c:est.xls,sheet1$)select*from表-如果Excel

3、文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:-导出表的情况EXECmaster.xp_cmdshellbcp数据库名.dbo.表名outc:est.xls/c-/S服务器名/U用户名-P密码-导出查询的情况EXECmaster.xp_cmdshellbcpSELECTau_fname,au_lnameFROMpubs.authorsORDERBYau_lnamequeryoutc:est.xls/c-/S服务器名/U用户名-P密码/*-说明:c:test.xls为导入/导出的Excel文件名.sheet1$为Excel文件的工作表名,一般要加上$才能正常使用.-*/3.-建

4、立存储过程，导出真正的Excel文件（用此方法导出的Excel文件在用于导入时不会报错）-下面是导出真正Excel文件的方法:（请将一下所有代码复制到存储过程中）ifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.p_exporttb)andOBJECTPROPERTY(id,NIsProcedure)=1)dropproceduredbo.p_exporttbGO/*/*-数据导出EXCEL导出表中的数据到Excel,包含字段名,文件为真正的Excel文件,如果文件不存在,将自动创建文件,如果表不存在,将自动创建表基于通用性考虑,

5、仅支持导出标准数据类型-邹建2003.10(引用请保留此信息)-*/*/*-调用示例p_exporttbtbname=地区资料,path=c:,fname=aa.xls-*/createprocp_exporttbtbnamesysname,-要导出的表名pathnvarchar(1000),-文件存放目录fnamenvarchar(250)=-文件名,默认为表名asdeclareerrint,srcnvarchar(255),descnvarchar(255),outintdeclareobjint,constrnvarchar(1000),sqlvarchar(8000),fdlistva

6、rchar(8000)-参数检测ifisnull(fname,)=setfname=tbname+.xls-检查文件是否已经存在ifright(path,1)setpath=path+createtable#tb(abit,bbit,cbit)setsql=path+fnameinsertinto#tbexecmaster.xp_fileexistsql-数据库创建语句setsql=path+fnameifexists(select1from#tbwherea=1)setconstr=DRIVER=MicrosoftExcelDriver(*.xls);DSN=;READONLY=FALSE+

7、;CREATE_DB=+sql+;DBQ=+sqlelsesetconstr=Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;HDR=YES+;DATABASE=+sql+-连接数据库execerr=sp_oacreateadodb.connection,objoutiferr0gotolberrexecerr=sp_oamethodobj,open,null,constriferr0gotolberr/*/*-如果覆盖已经存在的表,就加上下面的语句-创建之前先删除表/如果存在的话selectsql=droptable+

8、tbname+execerr=sp_oamethodobj,execute,outout,sql-*/-创建表的SQLselectsql=,fdlist=selectfdlist=fdlist+,+a.name+,sql=sql+,+a.name+casewhenb.namelike%charthencasewhena.length255thenmemoelsetext(+cast(a.lengthasvarchar)+)endwhenb.namelike%intorb.name=bitthenintwhenb.namelike%datetimethendatetimewhenb.nameli

9、ke%moneythenmoneywhenb.namelike%textthenmemoelseb.nameendFROMsyscolumnsaleftjoinsystypesbona.xtype=b.xusertypewhereb.namenotin(image,uniqueidentifier,sql_variant,varbinary,binary,timestamp)andobject_id(tbname)=idselectsql=createtable+tbname+(+substring(sql,2,8000)+),fdlist=substring(fdlist,2,8000)ex

10、ecerr=sp_oamethodobj,execute,outout,sqliferr0gotolberrexecerr=sp_oadestroyobj-导入数据setsql=openrowset(MICROSOFT.JET.OLEDB.4.0,Excel8.0;HDR=YES;IMEX=1;DATABASE=+path+fname+,+tbname+$)exec(insertinto+sql+(+fdlist+)select+fdlist+from+tbname)returnlberr:execsp_oageterrorinfo0,srcout,descoutlbexit:selectca

11、st(errasvarbinary(4)as错误号,srcas错误源,descas错误描述selectsql,constr,fdlistgoifexists(select*fromdbo.sysobjectswhereid=object_id(Ndbo.p_exporttb)andOBJECTPROPERTY(id,NIsProcedure)=1)dropproceduredbo.p_exporttbGO/*/*-数据导出EXCEL导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件如果文件不存在,将自动创建文件如果表不存在,将自动创建表基于通用性考虑,仅支持导出标准数据类型-

12、邹建2003.10(引用请保留此信息)-*/*/*-调用示例p_exporttbsqlstr=select*from地区资料,path=c:,fname=aa.xls,sheetname=地区资料-*/createprocp_exporttbsqlstrvarchar(8000),-查询语句,如果查询语句中使用了orderby,请加上top100percentpathnvarchar(1000),-文件存放目录fnamenvarchar(250),-文件名sheetnamevarchar(250)=-要创建的工作表名,默认为文件名asdeclareerrint,srcnvarchar(255)

13、,descnvarchar(255),outintdeclareobjint,constrnvarchar(1000),sqlvarchar(8000),fdlistvarchar(8000)-参数检测ifisnull(fname,)=setfname=temp.xlsifisnull(sheetname,)=setsheetname=replace(fname,.,#)-检查文件是否已经存在ifright(path,1)setpath=path+createtable#tb(abit,bbit,cbit)setsql=path+fnameinsertinto#tbexecmaster.xp_

14、fileexistsql-数据库创建语句setsql=path+fnameifexists(select1from#tbwherea=1)setconstr=DRIVER=MicrosoftExcelDriver(*.xls);DSN=;READONLY=FALSE+;CREATE_DB=+sql+;DBQ=+sqlelsesetconstr=Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;HDR=YES+;DATABASE=+sql+-连接数据库execerr=sp_oacreateadodb.connection,o

15、bjoutiferr0gotolberrexecerr=sp_oamethodobj,open,null,constriferr0gotolberr-创建表的SQLdeclaretbnamesysnamesettbname=#tmp_+convert(varchar(38),newid()setsql=select*into+tbname+from(+sqlstr+)aexec(sql)selectsql=,fdlist=selectfdlist=fdlist+,+a.name+,sql=sql+,+a.name+casewhenb.namelike%charthencasewhena.len

16、gth255thenmemoelsetext(+cast(a.lengthasvarchar)+)endwhenb.namelike%intorb.name=bitthenintwhenb.namelike%datetimethendatetimewhenb.namelike%moneythenmoneywhenb.namelike%textthenmemoelseb.nameendFROMtempdb.syscolumnsaleftjointempdb.systypesbona.xtype=b.xusertypewhereb.namenotin(image,uniqueidentifier,

17、sql_variant,varbinary,binary,timestamp)anda.id=(selectidfromtempdb.sysobjectswherename=tbname)ifrowcount=0returnselectsql=createtable+sheetname+(+substring(sql,2,8000)+),fdlist=substring(fdlist,2,8000)execerr=sp_oamethodobj,execute,outout,sqliferr0gotolberrexecerr=sp_oadestroyobj-导入数据setsql=openrows

18、et(MICROSOFT.JET.OLEDB.4.0,Excel8.0;HDR=YES;DATABASE=+path+fname+,+sheetname+$)exec(insertinto+sql+(+fdlist+)select+fdlist+from+tbname+)setsql=droptable+tbname+exec(sql)returnlberr:execsp_oageterrorinfo0,srcout,descoutlbexit:selectcast(errasvarbinary(4)as错误号,srcas错误源,descas错误描述selectsql,constr,fdlis

19、tgo4.-在.net中导出到Excel的简单实例.(1).添加引用MicrosoftExcel11.0ObjectLibrary(在COM中)(2).在web.config的上里加/此句为了使用户能在本地打开文档，identify属性规定了身份验证的模式，一般情况下为false，这样安全性较高(3).在Text.aspx上加Button1控件(4).在cs页面添加usingMicrosoft.Office.Interop.Excel;(5).添加单击事件,在这里处理将数据库中的数据导入excel,代码如下:/定义方法GetData(),返回一个数据表privateSystem.Data.Da

20、taTableGetData().SqlConnectionconn=newSqlConnection(Server=XJIE;InitialCatalog=Northwind;Uid=sa;Pwd=xjie;);SqlDataAdapteradapter=newSqlDataAdapter(selectCompanyName用户名,ContactTitle联系主题,Address住宅地址,ContactName联系人,Phone电话,City城市fromCustomersorderbyCustomerIDdesc,conn);DataSetds=newDataSet();try.adapte

21、r.Fill(ds,Customer);catch(Exceptionex).MessageBox.Show(ex.ToString();returnds.Tables0;privatevoidButton1_Click(objectsender,System.EventArgse).Applicationexcel=newApplication();introwIndex=1;intcolIndex=0;excel.Application.Workbooks.Add(true);DataTabletable=GetData();/将所得到的表的列名,赋值给单元格foreach(DataColumncolintable.Columns).colIndex+;excel.Cells1,colIndex=col.ColumnName;/同样方法处理数据foreach(DataRowrowintable.Rows).rowIndex+;colIndex=0;foreach(DataColumncolintable.Columns).colIndex+;excel.CellsrowIndex,colIndex=rowcol.ColumnName.ToString();/不可见,即后台处理excel.Visible=true;-