DELPHI如何将数据导出到指定格式的EXCEL模版

2011-10-31 14:27445人阅读评论(0)收藏举报

参考代码1

Delphi(Pascal) code

 path:=ExtractFilePath(Application.ExeName);  if self.OpenDialog1.Execute then    filename:=self.OpenDialog1.FileName;  try    Self.ExcelApplication1:=TExcelApplication.Create(self);    Self.ExcelApplication1.Connect;  except    messagebox(application.Handle,'无法生成Excel报表，请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);    exit;  end;  Self.ExcelApplication1.Visible[0]:=true;  self.ExcelApplication1.DisplayAlerts[0]:=False;  self.ExcelApplication1.Workbooks.Open(filename,EmptyParam,                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,                                         EmptyParam,EmptyParam,EmptyParam,0);  self.ExcelWorkbook1.ConnectTo(Self.ExcelApplication1.Workbooks[1]);  self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);  self.ExcelWorksheet1.ConnectTo(Self.ExcelWorkbook1.Worksheets[1] as _worksheet);  i:=self.StringGrid2.RowCount;  for j:=1 to i-1 do  begin    xh:=Self.StringGrid2.Cells[0,j];    pscj:=self.StringGrid2.Cells[2,j];    kscj:=Self.StringGrid2.Cells[4,j];    zpcj:=Self.StringGrid2.Cells[5,j];    self.ExcelWorksheet1.cells.Item[l+j,m]:=pscj;    self.ExcelWorksheet1.cells.Item[l+j,n]:=kscj;    self.ExcelWorksheet1.cells.Item[l+j,k]:=zpcj;  end;  Self.ExcelWorksheet1.SaveAs(filename);  Self.ExcelApplication1.Disconnect;  Self.ExcelWorkbook1.Disconnect;  Self.ExcelWorksheet1.Disconnect;

Delphi(Pascal) code
path:=ExtractFilePath(Application.ExeName);
if self.OpenDialog1.Execute then
filename:=self.OpenDialog1.FileName;

try
Self.ExcelApplication1:=TExcelApplication.Create(self);
Self.ExcelApplication1.Connect;
except
messagebox(application.Handle,'无法生成Excel报表，请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);
e…

try
  excel := CreateOleObject('Excel.Application');
  WorkBook := excel.Workbooks.Add('模板的路径.xls');
  Sheet := WorkBook.Worksheets[1];
  except
  excel := NULL;
  DJShow('请先安装Excel97/2000。');
  EXIT;
  end;
  excel.Visible:=true;
  I := 2;
//假设是从数据库取数据
  with ADOQuery1 do
  try
  first;
  while not eof do
  begin
  Sheet.cells(i, 1) := FieldbyName('Field1').asstring;
  Sheet.cells(i, 2) := FieldbyName('Field2').asstring;
  Sheet.cells(i, 3) := FieldbyName('Field3').asstring;
  Inc(I);
  Next;
  end;
  finally
  free;
  end;
  try
  excel.Visible:=true;
  WorkBook.Saved := True;
  finally
  excel.Quit;
  excel:=Unassigned;
  end;

在單元中加入comobj;procedure TForm1.Button1Click(Sender: TObject);var a,b : string;    ExcelApp,WorkBook:Variant;         ExcelRowCount:integer;    i:integer;begin   ExcelApp:= CreateOleObject('Excel.Application');  //創建Excel程序   opendialog1.Execute;  //打開對話框   WorkBook := ExcelApp.WorkBooks.Open(opendialog1.FileName);   ExcelApp.Visible := false;   ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count; //獲取Excel的行數    for i:=1 to ExcelRowCount+1 do     begin      a:=excelapp.Cells.Value;  //第一列的值      b:=excelapp.Cells.Value; //第二列的值      if (excelapp.Cells.Value='')and(excelapp.Cells.Value='') then //第一列與第二列值都為空 則中止      break     else      begin        with adoquery1 do        begin        close;        sql.Clear;        sql.Add('insert into reny0830(a,b) values(:A,:B)');   //往表中插入Excel的內容        parameters.ParamByName('A').Value:=a;        parameters.ParamByName('B').Value:=b;        Execsql;        end;      end;    end;    WorkBook.Close;//关闭工作簿    ExcelApp.Quit; //退出Excel    ExcelApp:=Unassigned;//釋放變量    WorkBook:= Unassigned;       with adoquery1 do   //把結果顯示出來    begin    close;    sql.Clear;    sql.Add('select * from reny0830');    open;    end;end;

procedure TmainFrm.Button4Click(Sender: TObject);var   i, row, column:integer;begin if not mainAdo.Active then  begin    messagedlg('數據集沒有打開，不能轉Excel!',mtwarning,[mbOK],0);  end else begin try  ExcelApplication1.Connect;  except    MessageDlg('Excel may not be installed',      mtError, [mbOk], 0);    Abort;  End;    mainAdo.First;      ExcelApplication1.Visible[0] := true;    ExcelApplication1.Caption := 'Excel';    ExcelApplication1.Workbooks.Add(Null, 0);      ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);    row:=1;   for i:=0 to mainAdo.FieldCount-1  do      begin    ExcelWorksheet1.Cells.Item[1,i+1]:=mainAdo.Fields[i].DisplayName;   end;   row:=row+1;    while not mainAdo.Eof do    begin     column:=1;       for i:=1 to mainAdo.FieldCount do           begin         ExcelWorksheet1.Cells.Item[row,column]:=mainAdo.Fields[i-1].AsString;         column:=column+1;       end;       mainAdo.Next;       row:=row+1;    end;     ExcelApplication1.Disconnect;       ExcelWorkbook1.Disconnect;     ExcelWorksheet1.Disconnect;   end; end;