Сообщество Чайников

Вопрос

Как осуществить экспорт данных из 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&;quot;
//если база отсортирована (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;

Кирилл Краснов