|
procedure CopyDbDataToExcel(Args: array of const); http://www.lovegirl.org.cn
var http://www.lovegirl.org.cn
iCount, jCount: Integer; http://www.ladyspark.cn
XLApp: Variant; http://www.100jz.com
Sheet,range: Variant; http://www.100jz.com
I: Integer; http://blog.zj.com
begin http://www.digitalstreet.net.cn
Screen.Cursor := crHourGlass; http://www.digitalstreet.net.cn
if not VarIsEmpty(XLApp) then http://www.ladyspark.cn
begin http://www.lovegirl.org.cn
XLApp.DisplayAlerts := False; http://www.100jz.com
XLApp.Quit; http://www.digitalstreet.net.cn
VarClear(XLApp); http://www.100jz.com
end;
try http://www.ladyspark.cn
XLApp:=CreateOleObject(Excel.Application); http://blog.zj.com
except http://www.100jz.com
Screen.Cursor := crDefault; http://www.digitalstreet.net.cn
Exit; http://www.100jz.com
end;
XLApp.WorkBooks.Add; http://www.100jz.com
XLApp.SheetsInNewWorkbook := High(Args) + 1;
for I := Low(Args) to High(Args) do http://www.ladyspark.cn
begin http://blog.zj.com
XLApp.WorkBooks[1].WorkSheets[I+1].Name := TDBGrid(Args[I].VObject).Name; http://www.lovegirl.org.cn
Sheet := XLApp.Workbooks[1].WorkSheets[TDBGrid(Args[I].VObject).Name];
if not TDBGrid(Args[I].VObject).DataSource.DataSet.Active then http://blog.zj.com
begin http://www.lovegirl.org.cn
Screen.Cursor := crDefault; http://www.digitalstreet.net.cn
Exit; http://www.digitalstreet.net.cn
end; http://www.100jz.com
TDBGrid(Args[I].VObject).DataSource.DataSet.first; http://www.digitalstreet.net.cn
for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do http://www.lovegirl.org.cn
range:=sheet.range[sheet.cells[1,1],sheet.cells[1,iCount + 1]]; http://www.100jz.com
range.select; http://www.lovegirl.org.cn
range.merge; http://www.digitalstreet.net.cn
sheet.cells[1,1]:=[+fqueryhuman.dbedit2.text+]+个人报销记录(普通报销、特殊报销)查询; http://blog.zj.com
jCount :=2; http://www.100jz.com
for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do http://www.digitalstreet.net.cn
Sheet.Cells[2, iCount + 1]:=TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption; http://blog.zj.com
while not TDBGrid(Args[I].VObject).DataSource.DataSet.Eof do http://www.digitalstreet.net.cn
begin http://blog.zj.com
for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do http://www.lovegirl.org.cn
Sheet.Cells[jCount + 1, iCount + 1] := http://www.digitalstreet.net.cn
TDBGrid(Args[I].VObject).Columns.Items[iCount].Field.AsString;
Inc(jCount); http://www.digitalstreet.net.cn
TDBGrid(Args[I].VObject).DataSource.DataSet.Next; http://blog.zj.com
end; http://www.lovegirl.org.cn
XlApp.Visible := True; http://www.ladyspark.cn
end; http://www.lovegirl.org.cn
Screen.Cursor := crDefault; http://www.ladyspark.cn
end;
方法三
从Excel文件中,导入数据到SQL数据库中,很简单,直接用下面的语句: /*===================================================================*/ --如果接受数据导入的表已经存在 insert into 表 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) --如果导入数据并生成表
--导出查询的情况 /*--说明: --下面是导出真正Excel文件的方法: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) /*--数据导出EXCEL --邹建 2003.10(引用请保留此信息)--*/ /*--调用示例 p_exporttb @tbname='地区资料',@path='c:\',@fname='aa.xls' --参数检测 --检查文件是否已经存在 --数据库创建语句
exec @err=sp_oamethod @obj,'open',null,@constr /*--如果覆盖已经存在的表,就加上下面的语句 --创建表的SQL exec @err=sp_oadestroy @obj --导入数据 exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname) return lberr: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) /*--数据导出EXCEL --邹建 2003.10(引用请保留此信息)--*/ /*--调用示例 p_exporttb @sqlstr='select * from 地区资料' --参数检测 --检查文件是否已经存在 --数据库创建语句 --连接数据库 exec @err=sp_oamethod @obj,'open',null,@constr --创建表的SQL select @sql='',@fdlist='' if @@rowcount=0 return select @sql='create table ['+@sheetname exec @err=sp_oamethod @obj,'execute',@out out,@sql exec @err=sp_oadestroy @obj --导入数据 exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']') set @sql='drop table ['+@tbname+']' lberr: |