{
Экспортирование данных из ADO tables в различные форматы

В этом письме описан компонент, который реализует возможность экспорта данных в следующие форматы данных:

1. Excel
2. Html
3. Paradox
4. Dbase
5. Text

Все поддерживаемые форматы Вы можете просмотреть в реестре:
"HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\ISAM formats"

Полный листинг компонента}


unit ExportADOTable;

interface

uses   Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs, Db, ADODB;

type
  TExportADOTable = class(TADOTable)
  private
    { Private declarations }
    //TADOCommand component used to execute the SQL exporting commands
    FADOCommand: TADOCommand;
  protected
    { Protected declarations }
  public
    { Public declarations }
    constructor Create(AOwner: TComponent); override;

  //Экспортируемые процедуры
  //"FiledNames" строка с названиями полей, разделенные запятыми, для экспорта
  //"FileName" полный путь на файл, в который будут импортироваться данные
  //если база отфильтрована(Filtered = true and Filter <> ''), добавлена
  //строка фильтра в sql command в директиву "where"
  //если база отсортирована (Sort <> '') добавлена строка в sql command в
  //директиве "order by"

    procedure ExportToExcel(FieldNames: string; FileName: string;
      SheetName: string; IsamFormat: string);
    procedure ExportToHtml(FieldNames: string; FileName: string);
    procedure ExportToParadox(FieldNames: string; FileName: string; IsamFormat: string);
    procedure ExportToDbase(FieldNames: string; FileName: string; IsamFormat: string);
    procedure ExportToTxt(FieldNames: string; FileName: string);
  published
    { Published declarations }
  end;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('ADO', [TExportADOTable]);
end;

constructor TExportADOTable.Create(AOwner: TComponent);
begin
  inherited;

  FADOCommand := TADOCommand.Create(Self);
end;


procedure TExportADOTable.ExportToExcel(FieldNames: string; FileName: string;
  SheetName: string; IsamFormat: string);
begin
  {IsamFormat values
   Excel 3.0
   Excel 4.0
   Excel 5.0
   Excel 8.0
  }

  if not Active then
    Exit;
  FADOCommand.Connection := Connection;   FADOCommand.CommandText := 'Select ' + FieldNames + ' INTO ' + '[' +
    SheetName + ']' + ' IN ' + '"' + FileName + '"' + '[' + IsamFormat +
    ';]' + ' From ' + TableName;
  if Filtered and (Filter <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' where ' + Filter;
  if (Sort <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' order by ' + Sort;
  FADOCommand.Execute;
end;

procedure TExportADOTable.ExportToHtml(FieldNames: string; FileName: string);
var
  IsamFormat: string;
begin
  if not Active then
    Exit;

  IsamFormat := 'HTML Export';

  FADOCommand.Connection := Connection;
  FADOCommand.CommandText := 'Select ' + FieldNames + ' INTO ' + '[' +
    ExtractFileName(FileName) + ']' +
    ' IN ' + '"' + ExtractFilePath(FileName) + '"' + '[' + IsamFormat +
    ';]' + ' From ' + TableName;
  if Filtered and (Filter <> '') then     FADOCommand.CommandText := FADOCommand.CommandText + ' where ' + Filter;
  if (Sort <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' order by ' + Sort;
  FADOCommand.Execute;
end;


procedure TExportADOTable.ExportToParadox(FieldNames: string;
  FileName: string; IsamFormat: string);
begin
  {IsamFormat values
  Paradox 3.X
  Paradox 4.X
  Paradox 5.X
  Paradox 7.X
  }
  if not Active then
    Exit;

  FADOCommand.Connection := Connection;
  FADOCommand.CommandText := 'Select ' + FieldNames + ' INTO ' + '[' +
    ExtractFileName(FileName) + ']' +
    ' IN ' + '"' + ExtractFilePath(FileName) + '"' + '[' + IsamFormat +
    ';]' + ' From ' + TableName;
  if Filtered and (Filter <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' where ' + Filter;
  if (Sort <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' order by ' + Sort;
  FADOCommand.Execute;
end;

procedure TExportADOTable.ExportToDbase(FieldNames: string; FileName: string;
  IsamFormat: string);
begin
  {IsamFormat values
  dBase III
  dBase IV
  dBase 5.0
  }
  if not Active then
    Exit;

  FADOCommand.Connection := Connection;
  FADOCommand.CommandText := 'Select ' + FieldNames + ' INTO ' + '[' +
    ExtractFileName(FileName) + ']' +
    ' IN ' + '"' + ExtractFilePath(FileName) + '"' + '[' + IsamFormat +
    ';]' + ' From ' + TableName;
  if Filtered and (Filter <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' where ' + Filter;
  if (Sort <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' order by ' + Sort;
  FADOCommand.Execute;
end;

procedure TExportADOTable.ExportToTxt(FieldNames: string; FileName: string);
var
  IsamFormat: string;
begin
  if not Active then
    Exit;

  IsamFormat := 'Text';

  FADOCommand.Connection := Connection;
  FADOCommand.CommandText := 'Select ' + FieldNames + ' INTO ' + '[' +
    ExtractFileName(FileName) + ']' +
    ' IN ' + '"' + ExtractFilePath(FileName) + '"' + '[' + IsamFormat +
    ';]' + ' From ' + TableName;
  if Filtered and (Filter <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' where ' + Filter;
  if (Sort <> '') then
    FADOCommand.CommandText := FADOCommand.CommandText + ' order by ' + Sort;
  FADOCommand.Execute;
end;

end.

//-----------------------------------------------------------

//Пример:

var ADOTable: TADOTable;
   ExpADOTable:TExportADOTable;
begin
....
  ExpADOTable:=TExportADOTable.Create(Self{если есть форма});
  ExpADOTable.Assign(ADOTable);
  ExpADOTable.ExportToExcel('name,birthday','c:\test.xls','Test','Excel 8.0');
....
end;