{
Exporting ADO tables into various formats
In this article I want to present a component I built in order to
supply exporting features to the ADOTable component. ADO supplies
an extended SQL syntax that allows exporting of data into various
formats. I took into consideration the following formats:
1)Excel
2)Html
3)Paradox
4)Dbase
5)Text
You can see all supported output formats in the registry:
"HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\ISAM formats"
This is the complete source of my component }unit ExportADOTable;
interfaceuses
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;
//Export procedures//"FiledNames" is a comma separated list of the names of the fields you want to export//"FileName" is the name of the output file (including the complete path)//if the dataset is filtered (Filtered = true and Filter <> ""), then I append//the filter string to the sql command in the "where" directive//if the dataset is sorted (Sort <> "") then I append the sort string to the sql command in the//"order by" directiveprocedure 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;
procedureRegister;
implementationprocedureRegister;
begin
RegisterComponents("Carlo Pasolini", [TExportADOTable]);
end;
constructor TExportADOTable.Create(AOwner: TComponent);
begininherited;
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
}ifnot 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;
beginifnot 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
}ifnot 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
}ifnot 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;
beginifnot 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.
{
Note that you can use an already existing database as destination but not an already existing
table in the database itself: if you specify an already exixting table you will receive
an error message. You might insert a verification code inside every exporting procedure of my
component, before the execution of the sql exporting command, in order to send a request of
deleting the already present table or aborting the exporting process.
carlo Pasolini, Riccione(italy), e-mail: ccpasolini@libero.it
}
Если Вас заинтересовала или понравилась информация по разработке на Delph - "Экспорт ADO таблиц в разные форматы", Вы можете поставить закладку в социальной сети или в своём блоге на данную страницу: Так же Вы можете задать вопрос по работе этого модуля или примера через форму обратной связи, в сообщение обязательно указывайте название или ссылку на статью!