如何通过ODBC用Delphi或其它编程语言向Excel工作表中添加数据

Question

我没有用成功过。但是我用OLE对象没有问题。你需要的话，我给你个例子。unit UExcelTools;interfaceuses
  classes, comctrls, stdctrls, windows, Dialogs, controls, SysUtils,
  Db,forms,DBClient,ComObj;//把数据集导入ExcelSheet的核心函数
function DataSetToExcelSheet
            (
            DataSet    :TDataSet;
            FieldTagMax :Integer;  // 字段的Tag值如果大于这个值，就不导出到Excel
            Sheet      :OleVariant
            ): Boolean;//实际使用的函数，内部调用了DataSetToExcelSheet，在外面加入UI接口和错误处理
function DataSetToExcel
            (
            DataSet    :TDataSet;  // 要转换的数据集
            FieldTagMax :Integer;  // 字段的Tag值如果大于这个值，就不导出到Excel
            Visible    :Boolean;      // 是否让做转换工作的Excel可见
            ExcelFileName:String='' // Excel文件名，*.xls
            ): Boolean;implementationFunction DataSetToExcelSheet(DataSet:TDataSet;FieldTagMax:Integer;Sheet:OleVariant): Boolean;
var
  Row,Col,FieldIndex :Integer;
  BK:TBookMark;
begin
  Result := False;
  if not Dataset.Active then exit;
  BK:=DataSet.GetBookMark;
  DataSet.DisableControls;  Sheet.Activate;
  try    // 列标题
    Row:=1;
    Col:=1;
    for FieldIndex:=0 to DataSet.FieldCount-1 do
        begin
        if DataSet.Fields[FieldIndex].Tag <= FieldTagMax then
            begin
            Sheet.Cells(Row,Col)  :=DataSet.Fields[FieldIndex].DisplayLabel;
            Inc(Col);
            end;
        end;
    // 表内容
    DataSet.First;
    while Not DataSet.Eof do
        begin
        Row:=Row+1;
        Col:=1;
        for FieldIndex:=0 to DataSet.FieldCount-1 do
            begin
            if DataSet.Fields[FieldIndex].Tag <= FieldTagMax then
              begin
              Sheet.Cells(Row,Col):=DataSet.Fields[FieldIndex].AsString;
              Inc(Col);
              end;
            end;
        DataSet.Next;
        end;    Result := True;
    finally
      DataSet.GotoBookMark(BK);
      DataSet.EnableControls;
    end;
end;
Function DataSetToExcel(
                  DataSet:TDataSet;FieldTagMax:Integer;
                  Visible:Boolean;ExcelFileName:String=''): Boolean;
var
  ExcelObj, Excel, WorkBook, Sheet: OleVariant;
    OldCursor:TCursor;
  SaveDialog:TSaveDialog;
begin
  Result := False;
  if not Dataset.Active then exit;  OldCursor:=Screen.Cursor;
  Screen.Cursor:=crHourGlass;  try
      ExcelObj := CreateOleObject('Excel.Sheet');
      Excel := ExcelObj.Application;
      Excel.Visible := Visible ;
      WorkBook := Excel.Workbooks.Add ;
      Sheet:= WorkBook.Sheets[1];
  except
      MessageBox(GetActiveWindow,'无法调用Mircorsoft Excel! '+chr(13)+chr(10)+
                    '请检查是否安装了Mircorsoft Excel。','提示',MB_OK+MB_ICONINFORMATION);
      Screen.Cursor:=OldCursor;
      Exit;
  end;  Result:=DataSetToExcelSheet(DataSet,FieldTagMax,Sheet) ;
  if Result then
      if Not Visible then
      begin
        if ExcelFileName<>'' then
          WorkBook.SaveAs(FileName:=ExcelFileName)
        else begin
          SaveDialog:=TSaveDialog.Create(Nil);
          SaveDialog.Filter := 'Microsoft Excel 文件&brvbar;*.xls';
          Result:=SaveDialog.Execute;
          UpdateWindow(GetActiveWindow);
          if Result then
            WorkBook.SaveAs(FileName:=SaveDialog.FileName);
          SaveDialog.Free;
        end;
        Excel.Quit;
      end;
  Screen.Cursor:=OldCursor;
end;

Answer 1

另外你检索这个主题：谁知道delphi中调用OLEexcel后可以对其执行哪些操作

Answer 2

下面的程序就是把DBGRID的数据导入EXCEL。QUERY1就是DBGRID1.DATASOURCE.DATASETprocedure Tfrmsunycodeuser.BitBtn1Click(Sender: TObject);
var
 MsExcel: oleVariant;
 ExcelSheet1: Variant;
 i:integer;
 Bm: TBookStr;
 row: integer; //Excel行
begin
  try
   MsExcel:=CreateOleObject('Excel.Application');
  except
   MessageBox(Handle,'初始化Excel错误。',
          'error',MB_OK +MB_ICONERROR);
   exit;
  end;  Screen.Cursor:=crHourGlass;
  MsExcel.Visible:=False;
  MsExcel.WorkBooks.Add;
  ExcelSheet1:= MsExcel.WorkBooks[1].WorkSheets[1];
  Bm := query1.Book;
  query1.first;
  query1.DisableControls;
  try
  for i:=0 to dbgrid1.Columns.Count -1 do
  begin
    if dbGrid1.Columns[I].Visible then
    ExcelSheet1.Cells.Item[1,i+1]:=dbgrid1.Columns[i].Title.Caption;  //标题
  end;
  row:=2;
  while not Query1.Eof do
  begin
     for I := 0 to dbGrid1.Columns.Count - 1 do
          begin
              if dbGrid1.Columns[I].Visible then
                ExcelSheet1.Cells[Row, i+1] := dbGrid1.Columns[I].Field.DisplayText;
      end;
      row:=row+1;
      Query1.Next;
  end;
  
  MsExcel.Visible:=True;
  Screen.Cursor:=crDefault;
  finally
       query1.enableControls;
       query1.Book:=bm;
   end;
end;