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

Question

如题，请高人给出参考代码，谢谢！

Answer 1

参考代码  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;

Answer 2

十分感谢：lovelymelon
我这就试试看哦~！@#￥%……&*

Answer 3

To:lovelymelon 
我参照你的代码，在编译时出现如下错误提示信息：
Build
  [Error] Unit1.pas(52): Not enough actual parameters
  [Fatal Error] Project1.dpr(5): Could not compile used unit 'Unit1.pas'

Answer 4

问题出在这句：
  self.ExcelApplication1.Workbooks.Open(filename,EmptyParam,
                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,
                                         EmptyParam,EmptyParam,EmptyParam,0);

Answer 5

我的代码是经过测试的，我用的是delphi6。
根据你给出的错误提示，应该是open函数内参数个数的问题，你看看你那里open函数需要多少个参数。

Answer 6

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;

Answer 7

 
 
在單元中加入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;注意的地方:必須保證Excel中的內容的類型與數據庫中對應的字段類型一致或可以隱含轉換. 否則出現異常.  

Answer 8

不好意思,樓主我弄反了,我上面的代碼是從Execl導入到數據庫中.
下面這個事從數據庫轉Excel.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;