/ * - Data Export Excel Export Data into Excel, contains the field name, file is a real Excel file, if the file does not exist, will automatically create files, if the table does not exist, will automatically create a form based on versatility, only supports the export of standard data types: Zou - * /
/ * - Call example
P_exporttb @ Sqlstr = 'SELECT * FROM Area, @ Path =' C: / ', @ fname =' aa.xls', @ SheetName = 'Area Information' - * /
Create Proc P_exportTB @ TbName Sysname, - To export the table name @Path nVarchar (1000), - File storage directory @fname nvarchar (250) = '' - file name, default is a table name asDeclare @err int, @ Src nvarchar (255), @ desc nvarchar (255), @ out intdeclare @obj int, @ constr nvarchar (1000), @ SQL VARCHAR (8000), @ fdlist varchar (8000)
- Parameter detection if isnull (@fname, ') =' 'set @ fname = @ TBNAME '. XLS '
- Check if the file already exists 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
- Database creation statement set @ SQL = @ path @ fnameif exists (select 1 from #tb where a = 1) set @ Constr = 'driver = {Microsoft Excel Driver (* .xls)}; DSN =' ''; ReadOnly = false ' '; Create_DB = "' @ SQL '"; DBQ = ' @ SQLSE set @ Constr =' proviDB.4.0; Extended Properties = "Excel 8.0; HDR = YES ' '; Database =' @ SQL '"'
- Connect database EXEC @ Err = sp_oacreate 'adodb.connection', @ Obj outif @err <> 0 goto lberr
Exec @ Err = SP_OAMETHOD @ Obj, 'Open', NULL, @10 @err <> 0 goto lberr
/ * - If you override the already existing table, add the following statement - to delete the table before creating / if you exist, select @ SQL = 'drop table [' @ TBNAME ']' Exec @ Err = sp_oamethod @obj , 'execute', @ out out, @ SQL - * / - Create table sqlselect @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 endFROM syscolumns a left join systypes b on a.xtype = b.xusertypewhere B.Name Not in ('Image', 'Text', 'UNIQUEIDENTIFIER', 'NTEXT', '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 @err <> 0 goto lberr
Exec @ Err = sp_oadestroy @obj
- Import data set @ SQL = 'OpenRowSet (' 'Microsoft.jet.OleDb.4.0', '' Excel 8.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 OUTLBEXIT: SELECT CAST (@err as varbinary (4)) AS error number, @ src AS error source, @DESC AS error Description Select @ SQL, @ Constr, @ fdlist
GO ================================================= NINGOO Note: EXCEL files each worksheet Can't exceed 65,536 records solutions:
If the amount of data is greater than 65536, you can split the table to a few small temporary Table before calling the stored procedure, and then exported to different worksheets respectively.
