ADO连接Excel,Access

注:Excel2003和之后的版本后缀名有所不同

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;'
     + 'Driver={Microsoft Excel Driver (*.xls)};DBQ=E:\Book1.xls';
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Connection := ADOConnection1;
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'SELECT * FROM [Sheet1$]';
  ADOQuery1.Open;
end;

DataSet导出到Excel

procedure WriteToExcel(aDataSet: TDataSet;const sName, Title: string);
var
  ExcelApplication1: TExcelApplication;
  ExcelWorksheet1: TExcelWorksheet;
  ExcelWorkbook1: TExcelWorkbook;
  i, j, idx: integer;
  filename: string;
begin
  filename := Concat(extractfilepath(application.exename), sName, '.xls');
  try
    ExcelApplication1 := TExcelApplication.Create(Application);
    ExcelWorksheet1 := TExcelWorksheet.Create(Application);
    ExcelWorkbook1 := TExcelWorkbook.Create(Application);
    ExcelApplication1.Connect;
  except
    Application.Messagebox('Excel not install!', 'Error!', MB_ICONERROR + mb_Ok);
    Exit;
  end;
  try
    ExcelApplication1.Connect;
    ExcelApplication1.Visible[0] := True;
    ExcelApplication1.Workbooks.Add(EmptyParam, 0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _worksheet);
    aDataSet.First;
    for j := 0 to aDataSet.Fields.Count - 1 do
    begin
      ExcelWorksheet1.Cells.item[3, j + 1] := aDataSet.Fields[j].DisplayLabel;
      ExcelWorksheet1.Cells.item[3, j + 1].font.size := 10;
    end;
    for i := 4 to aDataSet.RecordCount + 3 do
    begin
      for j := 0 to aDataSet.Fields.Count - 1 do
      begin
        ExcelWorksheet1.Cells.Item[i,j+1].Value := aDataSet.Fields[j].Asstring;
        ExcelWorksheet1.Cells.Item[i,j+1].font.size := 10;
      end;
      aDataSet.Next;
    end;
    ExcelWorksheet1.Columns.AutoFit;
    ExcelWorksheet1.Cells.item[1, 2] := Title;
    ExcelWorksheet1.Cells.Item[1, 2].font.size := 14;
    //ExcelWorksheet1.SaveAs(filename);
    Application.Messagebox(PAnsiChar('Excel Successful' + filename), 'Excel', mb_Ok);
  finally
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Free;
    ExcelWorksheet1.Free;
    ExcelWorkbook1.Free;
  end;
end;

 

连接Access:

 

procedure TForm1.Button2Click(Sender: TObject);
const
  SConnectionStringAccess='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False';
var
  FADOConnection:TADOConnection;
begin
  FADOConnection:=TADOConnection.Create(nil);
  try
    FADOConnection.LoginPrompt:=False;
    FADOConnection.ConnectionString:=Format(SConnectionStringAccess,[ExtractFilePath(ParamStr(0))+'test.mdb']);;
    FADOConnection.Open;
  finally
    if FADOConnection.Connected then FADOConnection.Close;
    if Assigned(FADOConnection) then FreeAndNil(FADOConnection);
  end;
end;