![]()
|
|
#5
2010-04-28 13:45
我最近也做了个项目，里面有Delphi和excel的操作，希望可以帮助你：
function TForm1.ExportDBGrid(DBGrid: TDBGrid; SheetName: string): Boolean;
var
i,j,r,h,k,kk:Integer;
app:OleVariant;
ResultFileName:string;
array1:array of string;
begin
try
Result:=true;
app:=CreateOLEObject('Excel.application');
except
Application.MessageBox('Excel没有正确安装！','警告',MB_OK);
result := False;
exit;
end;
app.Workbooks.add;
App.Columns[1].NumberFormatLocal:='@';
App.Columns[2].NumberFormatLocal:='@';
App.Columns[22].NumberFormatLocal:='@';
App.Columns[23].NumberFormatLocal:='@';
App.Columns[24].NumberFormatLocal:='@';
App.Columns[27].NumberFormatLocal:='@';
app.Visible := false;
Screen.Cursor := crHourGlass;
DBGrid1.DataSource.DataSet.First;
UniQuery1.First;
r:=0;
while not UniQuery1.Eof do
begin
r:=r+1;
UniQuery1.Next;
end;
for I := 0 to DBGrid1.FieldCount - 1 do
app.cells(1,i+1):=DBGrid1.Columns[i].Title.Caption;
DBGrid1.DataSource.DataSet.First;
for j := 1 to r do
begin
for I := 0 to DBGrid1.FieldCount - 1 do
begin
app.cells(j+1,i+1):=DBGrid1.Fields[i].AsString;
Next;
end;
DBGrid1.DataSource.DataSet.Next;
end;
setLength(array1,256);
for i := 0 to 256 do
array1[i]:='';
if cbb_xiangxian.Text='单相' then
begin
DBGrid1.DataSource.DataSet.First;
with UniQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('select * from dxhbiaowc where (dxhbiaowc.dianya=''1Ub'' or dxhbiaowc.dianya=''220'')and ubiaohao='''+DBGrid1.Fields[0].AsString+'''');
Open;
kk:=0;
DBGrid1.DataSource.DataSet.First;
while not UniQuery2.Eof do
begin
app.cells(1,kk+28):=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
array1[kk]:=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
kk:=kk+1;
UniQuery2.Next;
end;
DBGrid1.DataSource.DataSet.First;
for j := 0 to r-1 do
begin
Close;
SQL.Clear;
SQL.Add('select * from dxhbiaowc where (dxhbiaowc.dianya=''1Ub'' or dxhbiaowc.dianya=''220'')and ubiaohao='''+DBGrid1.Fields[0].AsString+'''');
//UniQuery2.Params.ParamByName('ubiaohao').Value:=DBGrid1.Fields[0].AsString;
open;
UniQuery2.First;
k:=0;
while not UniQuery2.Eof do
begin
k:=k+1;
UniQuery2.Next;
end;
UniQuery2.First;
h := 0 ;
while h<k do
begin
for i:= 0 to kk do
begin
if jydzl(UniQuery2.FieldByName('jiaoyandian').AsString)=array1[i] then
begin
app.cells(j+2,i+28):=UniQuery2.FieldByName('wucha').AsString;
UniQuery2.Next;
h:=h+1;
Break;
end
else if i=kk then
begin
KK:=kk+1;
app.cells(1,kk+28):=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
app.cells(j+2,kk+28):=UniQuery2.FieldByName('wucha').AsString;
//app.cells(j+2,i+28):='';
//array1[kk]:='';
array1[kk]:=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
//app.cells(j+2,kk+28):=UniQuery2.FieldByName('wucha').AsString;
UniQuery2.Next;
h:=h+1;
end;
end;
end;
DBGrid1.DataSource.DataSet.Next;
end;
if ResultFileName = '' then
ResultFileName := Edit5.Text;
app.Activeworkbook.saveas(edit5.Text);
app.Activeworkbook.close(false);
app.quit;
app := unassigned;
Application.MessageBox('数据导出成功！','提示',MB_OK);
end;
end
else
if cbb_xiangxian.Text='三相' then
begin
DBGrid1.DataSource.DataSet.First;
with UniQuery2 do
begin
Close;
SQL.Clear;
SQL.Add('select distinct(a.jiaoyandian),a.ubiaohao,a.wucha,a.dianya,a.zhuangtai from sxhbiaowc a where ubiaohao='''+DBGrid1.Fields[0].AsString+'''order by jiaoyandian');
Open;
kk:=0;
DBGrid1.DataSource.DataSet.First;
while not UniQuery2.Eof do
begin
app.cells(1,kk+28):=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
array1[kk]:=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
kk:=kk+1;
UniQuery2.Next;
end;
DBGrid1.DataSource.DataSet.First;
for j := 0 to r-1 do
begin
Close;
SQL.Clear;
SQL.Add('select distinct(a.jiaoyandian),a.ubiaohao,a.wucha,a.dianya,a.zhuangtai from sxhbiaowc a where ubiaohao='''+DBGrid1.Fields[0].AsString+'''order by jiaoyandian');
//UniQuery2.Params.ParamByName('ubiaohao').Value:=DBGrid1.Fields[0].AsString;
open;
UniQuery2.First;
k:=0;
while not UniQuery2.Eof do
begin
k:=k+1;
UniQuery2.Next;
end;
UniQuery2.First;
h := 0 ;
while h<k do
begin
for i:= 0 to kk do
begin
if jydzl(UniQuery2.FieldByName('jiaoyandian').AsString)=array1[i] then
begin
app.cells(j+2,i+28):=UniQuery2.FieldByName('wucha').AsString;
UniQuery2.Next;
h:=h+1;
Break;
end
else if i=kk then
begin
KK:=kk+1;
app.cells(j+2,kk+28):=UniQuery2.FieldByName('wucha').AsString;
app.cells(1,kk+28):=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
//app.cells(j+2,kk+27):=UniQuery2.FieldByName('wucha').AsString;
//app.cells(j+2,i+28):='';
//array1[kk]:='';
array1[kk]:=jydzl(UniQuery2.FieldByName('jiaoyandian').AsString);
//app.cells(j+2,kk+28):=UniQuery2.FieldByName('wucha').AsString;
UniQuery2.Next;
h:=h+1;
end;
end;
end;
DBGrid1.DataSource.DataSet.Next;
end;
end;
if ResultFileName = '' then
ResultFileName := '数据导出';
app.Activeworkbook.saveas(Edit5.Text);
app.Activeworkbook.close(false);
app.quit;
app := unassigned;
Application.MessageBox('数据导出成功！','提示',MB_OK);
end;
end;
|
