Arsip: Save hasil query di DBGrid ke dalam file Excel

more 10 years ago
nunkee
Master Delphi,
Mo tanya nih.. saya buat aplikasi query DB Mysql pake ADO, hasilnya ditampilin di DBGrid. Saya pengin simpan hasil tsb ke file dengan format Excel. Kira2 cara paling efektif gmn ya? Apakah pake ADOQuery nya atau DBGridnya?
Oh ya klo pke command Mysql 'INTO FILE' itu nyimpennya ke server ya? bisa gak disimpan di client.
Mohon pencerahannya para master..
rgds,-

more 10 years ago
madi
unit DBGridExportToExcel;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ExtCtrls, StdCtrls, ComCtrls, DB, IniFiles, Buttons, dbgrids, ADOX_TLB, ADODB;
type TScrollEvents = class
BeforeScroll_Event: TDataSetNotifyEvent;
AfterScroll_Event: TDataSetNotifyEvent;
AutoCalcFields_Property: Boolean;
end;
procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
implementation
//Support procedures: I made that in order to increase speed in
//the process of scanning large amounts
//of records in a dataset
//we make a call to the "DisableControls" procedure and then disable the "BeforeScroll" and
//"AfterScroll" events and the "AutoCalcFields" property.
procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
begin
with DataSet do
begin
DisableControls;
ScrollEvents := TScrollEvents.Create();
with ScrollEvents do
begin
BeforeScroll_Event := BeforeScroll;
AfterScroll_Event := AfterScroll;
AutoCalcFields_Property := AutoCalcFields;
BeforeScroll := nil;
AfterScroll := nil;
AutoCalcFields := False;
end;
end;
end;
//we make a call to the "EnableControls" procedure and then restore
// the "BeforeScroll" and "AfterScroll" events and the "AutoCalcFields" property.
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
begin
with DataSet do
begin
EnableControls;
with ScrollEvents do
begin
BeforeScroll := BeforeScroll_Event;
AfterScroll := AfterScroll_Event;
AutoCalcFields := AutoCalcFields_Property;
end;
end;
end;
//This is the procedure which make the work:
procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
var
cat: _Catalog;
tbl: _Table;
col: _Column;
i: integer;
ADOConnection: TADOConnection;
ADOQuery: TADOQuery;
ScrollEvents: TScrollEvents;
SavePlace: TBookmark;
begin
//
//WorkBook creation (database)
cat := CoCatalog.Create;
cat._Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0');
//WorkSheet creation (table)
tbl := CoTable.Create;
tbl.Set_Name(SheetName);
//Columns creation (fields)
DBGrid.DataSource.DataSet.First;
with DBGrid.Columns do
begin
for i := 0 to Count - 1 do
if Items[i].Visible then
begin
col := nil;
col := CoColumn.Create;
with col do
begin
Set_Name(Items[i].Title.Caption);
Set_Type_(adVarWChar);
end;
//add column to table
tbl.Columns.Append(col, adVarWChar, 20);
end;
end;
//add table to database
cat.Tables.Append(tbl);
col := nil;
tbl := nil;
cat := nil;
//exporting
ADOConnection := TADOConnection.Create(nil);
ADOConnection.LoginPrompt := False;
ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
ADOQuery := TADOQuery.Create(nil);
ADOQuery.Connection := ADOConnection;
ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
ADOQuery.Open;
DisableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
SavePlace := DBGrid.DataSource.DataSet.GetBookmark;
try
with DBGrid.DataSource.DataSet do
begin
First;
while not Eof do
begin
ADOQuery.Append;
with DBGrid.Columns do
begin
ADOQuery.Edit;
for i := 0 to Count - 1 do
if Items[i].Visible then
begin
ADOQuery.FieldByName(Items[i].Title.Caption).AsString := FieldByName(Items[i].FieldName).AsString;
end;
ADOQuery.Post;
end;
Next;
end;
end;
finally
DBGrid.DataSource.DataSet.GotoBookmark(SavePlace);
DBGrid.DataSource.DataSet.FreeBookmark(SavePlace);
EnableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
ADOQuery.Close;
ADOConnection.Close;
ADOQuery.Free;
ADOConnection.Free;
end;
end;
end.

more 10 years ago
nunkee
OK thanks masukannya para master.. aku milih pke komponen TscExcelExport.. :wink:
more ...
- Pages:
- 1
reply |
Report Obsolete
Last Articles
- Project Group dalam Lazarus
- FastPlaz Database Explorer
- Release: FastPlaz Super Mom v0.12.22
- PascalClass #3: Web Development with Free Pascal
- Makna Pascal di Pascal Indonesia
- Kulgram : Instalasi Lazarus di Perangkat Berbasis ARM
- PascalClass #1: Analisa Database dan Machine Learning
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
- Mengenal OXYGENE – Pascal For .NET
- PascalTalk #5: UX: Research, Design and Engineer
Last Topic
- PascalTalk #6: (Podcast) Kuliah IT di luar negeri, susah gak sih?
by LuriDarmawan in Tutorial & Community Project more 3 months ago - PascalTalk #5: UX: Research, Design and Engineer
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #4: Obrolan Ringan Seputar IT
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #2: Membuat Sendiri SMART HOME
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #3: RADically Fast and Easy Mobile Apps Development with Delphi
by LuriDarmawan in Tutorial & Community Project more 4 months ago - PascalTalk #1: Pemanfaatan Artificial Intelligence di Masa Covid-19
by LuriDarmawan in Tutorial & Community Project more 4 months ago - Tempat Latihan Posting
by LuriDarmawan in OOT more 1 years ago - Archive
- Looping lagi...
by idhiel in Hal umum tentang Pascal Indonesia more 8 years ago - [ask] koneksi ke ODBC user Dsn saat runtime dengan ado
by halimanh in FireBird more 8 years ago - Validasi menggunakan data tanggal
by mas_kofa in Hal umum tentang Pascal Indonesia more 8 years ago
Random Topic
- tanya penggunaan datetimepicker
by misdie in MsSQL more 12 years ago - tanya Fast Report [Lagi...]
by irmains in Reporting more 11 years ago - Indahnya Quick Report :: Sample Program
by Tbawor in Free dan Gratis more 13 years ago - tcard data dari query
by brodien in Tip n Trik Pemrograman more 14 years ago - gmn y cara memanggil prosedur timer
by vj_edogawa in Tip n Trik Pemrograman more 12 years ago - md5 di delphi 6 /7
by pippo in Tip n Trik Pemrograman more 13 years ago - ASK : Image recognition
by double580 in Multimedia & Graphic Enhancement more 14 years ago - capture data dari aplikasi lain
by nurez in Tip n Trik Pemrograman more 13 years ago - Epson LQ1150 custom paper size
by _lmz in Network, Files, I/O & System more 11 years ago - [Tanya] InterBase Express (IBX)
by babypigs in Tutorial & Community Project more 12 years ago