请教请教，如何在delphi中将数据导入EXCEL？

Question

太好用了！请查中国铁道出版社出版的DELPHI书籍

Answer 1

procedure WriteQueryToExcel(AQueryName: TQuery;//数据集控件的名字
xlsFileName:String);//excle表名
var
  EclApp,WorkBook : Variant;
  I : Integer ;
  column : Integer ;
  Row : Integer ;
Begin
  If AQueryName.Eof then
  begin
    exit ;
  end;
  Try
    Begin
      EclApp := CreateOleObject('Excel.Application');
      WorkBook:=CreateOleObject('Excel.Sheet');
    End
  Except
    ShowMessage('未装 Microsoft Excel!');
    Exit;
  end;  try
    workBook:=EclApp.workBooks.Add ;
    EclApp.Workbooks.Item[1].Activate;
    eclApp.Cells.font.colorindex:=5 ;
    For I := 1 To AQueryName.FieldCount Do
    begin
      EclApp.Activesheet.Cells(1,I):=AQueryName.Fields[I-1].FieldName ;
    end;
    If Not AQueryName.Active Then
    begin
      AQueryName.Active := True ;
    end;
    row:=2;
    AQueryName.First ;
    While Not(AQueryName.Eof) do
    begin
      column:=1;
      for i:=1 to AQueryName.FieldCount do
      begin
        eclApp.Cells.Item[row+1,column]:=AQueryName.fields[i-1].AsString;
        column:=column+1;
      end;
      AQueryName.Next;
      row:=row+1;
    End ;
      WorkBook.saveas(xlsFileName);
      WorkBook.close;
      WorkBook:=eclApp.workBooks.Open(xlsFileName);
      WorkBook.save;
      eclApp.Quit;
      eclApp:=Unassigned;
  except
      WorkBook.close;
      eclApp.Quit;  {释放VARIANT变量}
      eclApp:=Unassigned;
  end;
end;

Answer 2

樓上的太麻煩，只需采用導入文本就可以了，在需要換格的地方加一個TAB就可以，換行就加換行符。很簡單，又方便，真的好用極了。

Answer 3

use activeX      //头文件中引用activex单元
    lcid:integer; //声明窗体级私有变量
加入TExcelApplication,Texcelworkbook,Texcelworksheet控件function ChangeToExcel(....
var i,j: Integer;
    MyArray:variant;
    M,N:INTEGER;
    FLD:STRING;
begin
  lcid := GetUserDefaultLCID;
  eapp.Disconnect;
  eapp.Connect;
  eapp.Visible[0]:=True;
  eWB.ConnectTo(eapp.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
  eWS.ConnectTo(ewb.Worksheets[1] as _Worksheet);
  q_result.first;
  q_result.DisableControls;
  IF q_result.FieldCount>26 THEN
  BEGIN
    M:= q_result.FieldCount DIV 26;
    N:= q_result.FieldCount MOD 26;
    IF N>0 THEN FLD:= chr(M+64)+chr(N+64)
    ELSE FLD:= chr(M-1+64)+'Z';
    END
  ELSE FLD:=chr(q_result.FieldCount+64);
  MyArray:=VarArrayCreate([0,(q_result.FieldCount-1)],varVariant);
  for i:=0 to q_result.Fieldcount-1 do
  begin
    MyArray[i]:= DBgrideh2.Columns[i].Title.Caption ;
  end;
  eWS.Range['A1', (FLD+'1')].Value := MyArray;
  for i:=2 to q_result.RecordCount+1 do
  begin
    for j:=0 to q_result.FieldCount-1 do
    begin
      MyArray[j]:= q_result.Fields[j].Value ;
    end;
    eWS.Range['A'+intTostr(i), (FLD+intTostr(i))].Value := MyArray;
    q_result.Next;
  end;
  q_result.EnableControls;
end;
//这段代码是以显式的方法把数据转入Excel,需要隐式转入，只需设Texcelapplication.visible:=false;
至于保存成文件，调用Texcelworksheet.saveas()方法即可 

Answer 4

这是我的代码procedure TForm1.Button1Click(Sender: TObject);
var
  i,j,h,k:Integer;
  handle : HWND;
begin
  {Handle := FindWindow('XLMAIN', nil);    //判断Excel是否执行
  if handle <> 0 then
    showmessage('Excel is Running!')
  else
    showmessage('not Excel is Running!');
  }
  ExcelExistFlag := True;
  try
    EApp := CreateOleObject('Excel.Application.9') as _Application;  //Excel2000
  except
    ExcelExistFlag := False;
  end;
  if not ExcelExistFlag then
  try
    EApp := CreateOleObject('Excel.Application.8') as _Application;  //Excel97
    ExcelExistFlag := True;
  except
    ExcelExistFlag := False;
    ShowMessage('Excel调用失败！');
  end;  //EApp := CreateOleobject('Excel.Application');  if ExcelExistFlag then
  begin
    ADOQuery1.DisableControls;
    EApp.Visible:=true;
    //EApp.Caption := '还不行??';
    //EApp.Workbooks.Add;
    EApp.Workbooks.Add(xlWBatWorkSheet);
    EApp.Workbooks[1].WorkSheets[1].Name := Label1.Caption;
    ESheet := EApp.Workbooks[1].WorkSheets[Label1.Caption];
    ExcelCellRange := EApp.WorkBooks[1].WorkSheets[Label1.Caption].Columns;
    ADOQuery1.First;    for j:=0 to ADOQuery1.FieldCount-1  do
    begin
      //ESheet.Cells[1,j+1] :='';
      //ESheet.Cells[2,j+1] :='';
      //ESheet.Cells[3,j+1] :='';
      h:=Trunc(ADOQuery1.FieldCount/2);   //取整
      ESheet.Cells[1,h] :='北京tikkypeng科技开发有限公司';
      ESheet.Cells[2,h] :='用户信息表';
      ESheet.Cells[3,h] :='';
      ESheet.Cells[4,j+1] := DBGrid1.Columns[j].Title.Caption;
      //EApp.WorkBooks[1].WorkSheets[1].Cells[4,j+1].ColumnWidth := DBGrid1.Columns[j].Width;
      EApp.WorkBooks[1].WorkSheets[1].Cells[4,j+1].Borders.LineStyle := xlContinuous; //边框类型
      EApp.WorkBooks[1].WorkSheets[1].Cells[4,j+1].Borders.Weight := xlMedium;        //边框粗细
      //ExcelCellRange.Columns[j+1].ColumnWidth :=DBGrid1.Columns[j].Field.Size;// DisplayWidth  ;
      ExcelCellRange.Columns[j+1].ColumnWidth := DBGrid1.Columns[j].Width/7;
      //ExcelCellRange.Columns.Item[j+1].Name:=DBGrid1.Columns[j].Title.Caption ;
      //ExcelCellRange.Characters.Font.Name := '宋体'; //字体
      //ExcelCellRange.Characters.Font.FontStyle := '加粗';
      //ExcelCellRange.Characters.Font.Size := 9;
      //ExcelCellRange.Characters.Font.Color := clBlue; //颜色
      //ExcelCellRange.Characters.Font.ColorIndex := xlAutomatic; //颜色
      //ExcelCellRange := EApp.WorkBooks[1].WorkSheets[1].Range['A1:D4'];
      //ExcelCellRange.Borders.LineStyle := xlDouble;
    end;
    for i:=1 to ADOQuery1.RecordCount  do
    begin
      for j:=1 to ADOQuery1.FieldCount  do
      begin
        ESheet.Cells[i+4,j] :=ADOQuery1.Fields[j-1].AsString;
        EApp.WorkBooks[1].WorkSheets[1].Cells[i+4,j].Borders.LineStyle := xlContinuous; //边框类型
        EApp.WorkBooks[1].WorkSheets[1].Cells[i+4,j].Borders.Weight := xlThin;          //边框粗细
        //ESheet.Cells[i+2,j].Characters.Font.Name := '宋体'; //字体
        //ESheet.Cells[i+2,j].Characters.Font.FontStyle := '';
        //ESheet.Cells[i+2,j].Characters.Font.Size := 9;      //大小
        //ESheet.Cells[i+2,j].Characters.Font.Color := clRed; //颜色
        //ESheet.Cells[i+1,j].Characters.Font.ColorIndex := xlAutomatic; //颜色
      end;
      ADOQuery1.next;
    end;
    k:=ADOQuery1.RecordCount+8;
    ESheet.Cells[k,1] :='★Vigorous★';
    ESheet.Cells[k,h] :=DateToStr(Date);   //'2001-03-14';
    ESheet.Cells[k,ADOQuery1.FieldCount] :='☆★☆★☆★☆'; //'tikkypeng打印';
    //HandleRange;
    ADOQuery1.EnableControls;
    //EApp.Visible:=True;
  end
  else
    ShowMessage('调用Excel2000或Excel97失败，请确认是否安装！'+#13#13+'如果未安装，请先安装office');
    //ExcelCellRange.Characters.Font.Name := '宋体'; //字体
    //ExcelCellRange.Characters.Font.FontStyle := '';
    //ExcelCellRange.Characters.Font.Size := 9;
    {EApp.WorkBooks[1].WorkSheets[1].Range['A1'].Borders[xlBottom].LineStyle := xlContinuous;
    EApp.WorkBooks[1].WorkSheets[1].Range['A1'].Borders[xlBottom].Weight := xlHairline;
    EApp.WorkBooks[1].WorkSheets[1].Range['A2'].Borders[xlBottom].LineStyle := xlDash;  //虚线
    EApp.WorkBooks[1].WorkSheets[1].Range['A2'].Borders[xlBottom].Weight := xlThin;  //虚线
    EApp.WorkBooks[1].WorkSheets[1].Range['A3'].Borders.LineStyle := xlContinuous;
    EApp.WorkBooks[1].WorkSheets[1].Range['A3'].Borders.Weight := xlMedium;
    EApp.WorkBooks[1].WorkSheets[1].Range['A4'].Borders[xlBottom].LineStyle := xlDashDot;
    EApp.WorkBooks[1].WorkSheets[1].Range['A4'].Borders[xlBottom].Weight := xlThick;
    }
end;