declare @excel nvarchar(1000),@sheetname nvarchar(100) ,@tbnmae sysname select @excel='c:\test.xls' --接受数据导入的Excel文件名 ,@sheetname='sheet' --工作表名的前缀,分拆后的表,按 此名+n 放入excel文件中,n是序号 ,@tbnmae='sysobjects'declare @s nvarchar(4000),@i int,@sheet nvarchar(100) select @i=max(colid-1)/256 from syscolumns where id=object_id(@tbnmae) while @i>=0 begin set @s='' select @s=@s+',['+name+']' from syscolumns where id=object_id(@tbnmae) and colid between @i*256+1 and (@i+1)*256 order by colid select @s=stuff(@s,1,1,'') ,@sheet=@sheetname+cast(@i+1 as varchar) ,@i=@i-1 exec('insert into OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'' ,''Excel 5.0;HDR=YES;DATABASE='+@excel+''',[' +@sheet+'$]) select '+@s+' from ['+@tbnmae+'] ') set @i=@i-1 end
刚写的 procedure TFormDataExp.DataToExcel(aPath: string); var s, xlsConnstr: string; begin xlsConnstr := 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' + aPath + ';Persist Security Info=False'; s := 'Select * Into [sheet1] FROM '+ FMySQLStrList.Strings[ComboBoxTable.ItemIndex] + ' IN [ODBC]' + ' [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=HT01;]'; try ADOConnecSQLServer.ConnectionString := xlsConnstr; ADOConnecSQLServer.Connected := true; ADOConnecSQLServer.Execute(s); Application.MessageBox('导出Excel成功!','信息!',mb_ok + mb_iconinformation); ADOConnecSQLServer.Connected := false; except Application.MessageBox('导出Excel失败','信息!',mb_ok+mb_iconstop); ADOConnecSQLServer.Connected := false; end; end;
,@tbnmae sysname
select @excel='c:\test.xls' --接受数据导入的Excel文件名
,@sheetname='sheet' --工作表名的前缀,分拆后的表,按 此名+n 放入excel文件中,n是序号
,@tbnmae='sysobjects'declare @s nvarchar(4000),@i int,@sheet nvarchar(100)
select @i=max(colid-1)/256
from syscolumns
where id=object_id(@tbnmae)
while @i>=0
begin
set @s=''
select @s=@s+',['+name+']' from syscolumns
where id=object_id(@tbnmae)
and colid between @i*256+1 and (@i+1)*256
order by colid
select @s=stuff(@s,1,1,'')
,@sheet=@sheetname+cast(@i+1 as varchar)
,@i=@i-1
exec('insert into OPENROWSET(''MICROSOFT.JET.OLEDB.4.0''
,''Excel 5.0;HDR=YES;DATABASE='+@excel+''',['
+@sheet+'$])
select '+@s+' from ['+@tbnmae+']
') set @i=@i-1
end
procedure TFormDataExp.DataToExcel(aPath: string);
var
s, xlsConnstr: string;
begin
xlsConnstr := 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' + aPath + ';Persist Security Info=False';
s := 'Select * Into [sheet1] FROM '+ FMySQLStrList.Strings[ComboBoxTable.ItemIndex] + ' IN [ODBC]'
+ ' [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=HT01;]';
try
ADOConnecSQLServer.ConnectionString := xlsConnstr;
ADOConnecSQLServer.Connected := true;
ADOConnecSQLServer.Execute(s);
Application.MessageBox('导出Excel成功!','信息!',mb_ok + mb_iconinformation);
ADOConnecSQLServer.Connected := false;
except
Application.MessageBox('导出Excel失败','信息!',mb_ok+mb_iconstop);
ADOConnecSQLServer.Connected := false;
end;
end;