procedure TLoad_Frm.Button3Click(Sender: TObject); var MSExcel: Variant; FileName: String; i,j,k :integer; begin try MSExcel:=CreateOLEObject('Excel.Application'); FileName:= ExtractFilePath(Application.ExeName) + '年度统计.XLS'; MSExcel.WorkBooks.Open(FileName); MSExcel.Visible:=False; //进程条 Gauge1.Visible := True; //报表标题 MSExcel.Cells[16,12].Value:=year.Text+'年度水电费'; ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select Sum(WaterF) as WaterF, Sum(ElectF) as ElectF ,Count(ElectF) as SFZ from SDF where year='''+year.Text+''' '; ADO_Goods.Open; MSExcel.Cells[18,12].Value:='总水费:'+ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[20,12].Value:='总电费:'+ADO_Goods.FieldByName('ElectF').AsString; Gauge1.MaxValue:=ADO_Goods.FieldByName('SFZ').AsInteger*2; //2月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''2'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,1].Value:=Trim(ADO_Goods.FieldByName('XM').AsString); MSExcel.Cells[i+46,2].Value:=Trim(ADO_Goods.FieldByName('DoorId').AsString); MSExcel.Cells[i+46,3].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,4].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,16].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,17].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //4月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''4'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,5].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,6].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,18].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,19].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //6月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''6'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,7].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,8].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,20].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,21].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //8月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''8'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,9].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,10].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,22].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,23].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //10月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''10'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,11].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,12].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,24].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,25].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //12月 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''12'' order by b.RYBH'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,13].Value:=ADO_Goods.FieldByName('WaterN').AsString; MSExcel.Cells[i+46,14].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,26].Value:=ADO_Goods.FieldByName('ElectN').AsString; MSExcel.Cells[i+46,27].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; //小计 ADO_Goods.Close; ADO_Goods.SQL.Clear; ADO_Goods.SQL.Text:='select Sum(a.WaterF) as WaterF,sum(a.ElectF) as ElectF from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' group by a.XM'; ADO_Goods.Open; ADO_Goods.First; //从有数据的行逐行读入数据 i:=1; while not ADO_Goods.Eof do begin MSExcel.Cells[i+46,15].Value:=ADO_Goods.FieldByName('WaterF').AsString; MSExcel.Cells[i+46,28].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1); i:=i+1; ADO_Goods.Next; end; Gauge1.Hide; Except Gauge1.Hide; Exit; end; MSExcel.Visible:=True; end;
var
MSExcel: Variant;
FileName: String;
i,j,k :integer;
begin
try
MSExcel:=CreateOLEObject('Excel.Application');
FileName:= ExtractFilePath(Application.ExeName) + '年度统计.XLS';
MSExcel.WorkBooks.Open(FileName);
MSExcel.Visible:=False;
//进程条
Gauge1.Visible := True;
//报表标题
MSExcel.Cells[16,12].Value:=year.Text+'年度水电费';
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select Sum(WaterF) as WaterF, Sum(ElectF) as ElectF ,Count(ElectF) as SFZ from SDF where year='''+year.Text+''' ';
ADO_Goods.Open;
MSExcel.Cells[18,12].Value:='总水费:'+ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[20,12].Value:='总电费:'+ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.MaxValue:=ADO_Goods.FieldByName('SFZ').AsInteger*2;
//2月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''2'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,1].Value:=Trim(ADO_Goods.FieldByName('XM').AsString);
MSExcel.Cells[i+46,2].Value:=Trim(ADO_Goods.FieldByName('DoorId').AsString); MSExcel.Cells[i+46,3].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,4].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,16].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,17].Value:=ADO_Goods.FieldByName('ElectF').AsString; Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//4月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''4'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,5].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,6].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,18].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,19].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//6月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''6'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,7].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,8].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,20].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,21].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//8月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''8'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,9].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,10].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,22].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,23].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//10月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''10'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,11].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,12].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,24].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,25].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//12月
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select a.WaterF as WaterF,a.WaterN as WaterN,a.ElectF as ElectF,a.ElectN as ElectN,b.XM as XM,a.XM,b.DoorId as DoorId from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' and a.month=''12'' order by b.RYBH';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,13].Value:=ADO_Goods.FieldByName('WaterN').AsString;
MSExcel.Cells[i+46,14].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,26].Value:=ADO_Goods.FieldByName('ElectN').AsString;
MSExcel.Cells[i+46,27].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
//小计
ADO_Goods.Close;
ADO_Goods.SQL.Clear;
ADO_Goods.SQL.Text:='select Sum(a.WaterF) as WaterF,sum(a.ElectF) as ElectF from SDF a ,tblrywh b where b.XM=a.XM and a.year='''+year.Text+''' group by a.XM';
ADO_Goods.Open;
ADO_Goods.First;
//从有数据的行逐行读入数据
i:=1;
while not ADO_Goods.Eof do
begin
MSExcel.Cells[i+46,15].Value:=ADO_Goods.FieldByName('WaterF').AsString;
MSExcel.Cells[i+46,28].Value:=ADO_Goods.FieldByName('ElectF').AsString;
Gauge1.AddProgress(1);
i:=i+1;
ADO_Goods.Next;
end;
Gauge1.Hide;
Except
Gauge1.Hide;
Exit;
end; MSExcel.Visible:=True;
end;
http://www.delphibbs.com/delphibbs/dispq.asp?lid=1691966
{插入新的工作博}function TDBGridExport.New_Workbook: Boolean;vari: Integer;beginResult := True;tryFWorkbook_Handle := FExcel_Handle.Workbooks.Add;exceptraise exception.Create('新建Excel工作表出错!');Result := False;Exit;end;if FTitle <> '' thenFWorkbook_Handle.Application.ActiveWindow.Caption := FTitle;if FSheetName <> '' thenbeginfor i := 2 to FWorkbook_Handle.Sheets.Count doif FSheetName = FWorkbook_Handle.Sheets[i].Name thenbeginraise exception.Create('工作表命名重复!');Result := False;exit;end;tryFWorkbook_Handle.Sheets[1].Name := FSheetName;exceptraise exception.Create('工作表命名错误!');Result := False;exit;end;end;end;{插入数据}function TDBGridExport.InsertData_To_Excel: Boolean;vari, j, k: Integer;data_Str: string;Column_name: string;Data_Set: TDataSet;book: pointer;Before_Scroll, Afrer_Scroll: TDataSetNotifyEvent;begintry{显示插入进度}if FShow_Progress = True thenbeginCreate_ProgressForm(nil);FProgress_Form.Show;end;{第一行,插入标题}{仅仅插入可见数据}j := 1;for i := 1 to FDB_Grid.Columns.Count doif FDB_Grid.Columns[i - 1].Visible = True thenbeginFWorkbook_Handle.WorkSheets[1].Cells[1, j].Value := FDB_Grid.Columns[i - 1].Title.Caption;FWorkbook_Handle.WorkSheets[1].Columns[j].ColumnWidth := FDB_Grid.Columns[i - 1].Width div 6;j := j + 1end;{插入DBGrid中的数据}Data_Set := FDB_Grid.DataSource.DataSet;{记忆当前位置并取消任何事件}// new(book);book := Data_Set.GetBook;Data_Set.DisableControls;Before_Scroll := Data_Set.BeforeScroll;Afrer_Scroll := Data_Set.AfterScroll;Data_Set.BeforeScroll := nil;Data_Set.AfterScroll := nil;if FShow_Progress = True thenbeginData_Set.Last;FProgress_Form.Refresh;FProgressBar.Max := Data_Set.RecordCount;end;Data_Set.First;k := 2;while not Data_Set.Eof dobeginif FShow_Progress = True thenFProgressBar.Position := k;j := 1;for i := 1 to FDB_Grid.Columns.Count dobeginif FDB_Grid.Columns[i - 1].Visible = True thenbeginColumn_name := FDB_Grid.Columns[i - 1].FieldName;data_Str := FDB_Grid.Fields[i - 1].DisplayText;FWorkbook_Handle.WorkSheets[1].Cells[k, j].Value := data_Str;j := j + 1;end;end;k := k + 1;Data_Set.Next;end;{恢复原始事件以及标志位置}Data_Set.GotoBook(book);Data_Set.FreeBook(book);// dispose(book);Data_Set.EnableControls;Data_Set.BeforeScroll := Before_Scroll;Data_Set.AfterScroll := Afrer_Scroll;Result := True;finallyFExcel_Handle.Visible := True;FExcel_Handle.Application.ScreenUpdating := True;if FShow_Progress = True thenbeginFProgress_Form.Free;FProgress_Form := nil;end;end;end;{===============================================================================}{启动Excel时给出进度显示}procedure TDBGridExport.Create_Run_Excel_Form(AOwner: TComponent);varPanel: TPanel;Prompt: TLabel; {提示的标签}beginif assigned(FRun_Excel_Form) then exit;FRun_Excel_Form := TForm.Create(AOwner);with FRun_Excel_Form dobegintryFont.Name := '宋体'; {设置字体}Font.Size := 9;BorderStyle := bsNone;Width := 300;Height := 100;BorderWidth := 2;Color := clBlue;Position := poScreenCenter;Panel := TPanel.Create(FRun_Excel_Form);with Panel dobeginParent := FRun_Excel_Form;Align := alClient;BevelInner := bvNone;BevelOuter := bvRaised;Caption := '';end;Prompt := TLabel.Create(Panel);with Prompt dobeginParent := panel;AutoSize := True;Left := 25;Top := 25;Caption := '正在导出数据,请稍候……';end;exceptend;end;end; {===============================================================================}{创建进度显示窗口}procedure TDBGridExport.Create_ProgressForm(AOwner: TComponent);varPanel: TPanel;Prompt: TLabel; {提示的标签}beginif assigned(FProgress_Form) then exit;FProgress_Form := TForm.Create(AOwner);with FProgress_Form dobegintryFont.Name := '宋体'; {设置字体}Font.Size := 9;BorderStyle := bsNone;Width := 300;Height := 100;BorderWidth := 2;Color := clBlue;Position := poScreenCenter;Panel := TPanel.Create(FProgress_Form);with Panel dobeginParent := FProgress_Form;Align := alClient;BevelInner := bvNone;BevelOuter := bvRaised;Caption := '';end;Prompt := TLabel.Create(Panel);with Prompt dobeginParent := panel;AutoSize := True;Left := 25;Top := 25;Caption := '正在导出数据,请稍候……';end;FProgressBar := TProgressBar.Create(panel);with FProgressBar dobeginParent := panel;Left := 20;Top := 50;Height := 18;Width := 260;end;exceptend;end;end; end.