Database Aplikasi dengan Media Export To Excel

D2 TEKNIK KOMPUTER (BSI PROGRAMMER)

   Artikel kali ini akan membahas tentang bagaimana sebuah aplikasi database bisa di export ke berbagai media type data seperti contoh Microsoft Access , Word , Power Point ,Excel , SQL , PDF dan lain - lain. lewat pemrograman delphi , kita dapat langsung mengexport hasil kerja dari program database berupa output backup/report yang sesuai dengan keinginan kita.

   Komponent yang saya gunakan pada tahap pembuatan aplikasi ini adalah EMS Advanced VCL for Delphi. Untuk lebih mengenal apa itu EMS Advanced , Silahkan Klik Link ini SQL MANAGER

Let's Go To Coding.....

   Salah satu contoh pemrograman SQL dengan media Export ke Excel

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

unit Valentines_EditDatabase;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Grids, DBGrids, ZConnection, DB, ZAbstractRODataset,
  ZAbstractDataset, ZDataset, StdCtrls, jpeg, W7Classes, W7Images, Menus,
  AeroLabel;

type
  TEdit_Data = class(TForm)
    DataSource1: TDataSource;
    ZQuery1: TZQuery;
    ZConnection1: TZConnection;
    DBGrid1: TDBGrid;
    Edit1: TEdit;
    Button1: TButton;
    Edit2: TEdit;
    ComboBox1: TComboBox;
    Edit3: TEdit;
    Edit4: TEdit;
    ComboBox2: TComboBox;
    Edit5: TEdit;
    Edit6: TEdit;
    Memo1: TMemo;
    Label1: TLabel;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    Button5: TButton;
    W7Image1: TW7Image;
    PopupMenu1: TPopupMenu;
    Refresh1: TMenuItem;
    AboutMe1: TMenuItem;
    AeroLabel1: TAeroLabel;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    Label10: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure Button5Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Refresh1Click(Sender: TObject);
    procedure AboutMe1Click(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
    procedure Edit6Change(Sender: TObject);
    procedure Edit6KeyPress(Sender: TObject; var Key: Char);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Edit_Data: TEdit_Data;

implementation

{$R *.dfm}

procedure TEdit_Data.AboutMe1Click(Sender: TObject);
begin
MessageBox(Handle,'Programmer By Franklin.L','Info',MB_IconInformation);
end;

procedure TEdit_Data.Button1Click(Sender: TObject);
begin
Edit1.Text:='VLTN'+'y4CM'+inttostr(Random(1000))+'No_'+inttostr(ZQuery1.RecordCount+1);
end;

procedure TEdit_Data.Button2Click(Sender: TObject);
begin
ZQuery1.Filtered:=True;
if Length(Edit1.Text) <= 0 then
   Begin
       MessageBox(Handle,'Maaf data Ada Yang Kosong','Warning',MB_IconInformation);
          If ZQuery1.IsEmpty Then
           Begin
             ZQuery1.SQL.Clear;
             ZQuery1.SQL.Add('select * from valentine');
             ZQuery1.ExecSQL;
             Edit1.SetFocus;
           End;
   Exit;
   End;

With ZQuery1 Do
 Begin
   SQL.Clear;
   SQL.Text:='INSERT INTO valentine(Nomor_UrutKodeValentine,Nama_Peserta_y4CM,Activity,Sekolah_Kampus,Hobby,Alamat,Facebook_Twitter,Nomor_Telepon,Pesan_Kesan)VALUES('
   +Quotedstr(Edit1.Text)+',' +Quotedstr(Edit2.Text)+',' +Quotedstr(Combobox1.Text)+',' +Quotedstr(Edit3.Text)+',' +Quotedstr(Combobox2.Text)+','
   +Quotedstr(Edit4.Text)+',' +Quotedstr(Edit5.Text)+',' +Quotedstr(Edit6.Text)+','
   +Quotedstr(Memo1.Text)+')';
   ExecSQL;
 End;

Edit_Data.FormCreate(Sender);
Button5.Click;
end;

procedure TEdit_Data.Button3Click(Sender: TObject);
begin
  if ZQuery1.IsEmpty then
    begin
      MessageBox(Handle,'Data Belum Terpilih','Warning',MB_IconError);
      Exit;
    end
  else if MessageDlg('Yakin Data mau dihapus..??? ',mtConfirmation,[mbYes,mbNo],0)=mrYes then
    begin
      With ZQuery1 Do
        begin
          SQL.Clear;
          SQL.Text:='DELETE FROM valentine where Nomor_UrutKodeValentine=' +Quotedstr(Edit1.Text);
          ExecSQL;
         end;
       Edit_data.FormCreate(Sender);
       Button5.Click;
    end;

end;

procedure TEdit_Data.Button4Click(Sender: TObject);
begin
ZQuery1.Filtered:=True;
if Length(Edit1.Text) <= 0 then
   Begin
       MessageBox(Handle,'Maaf data Ada Yang Kosong','Warning',MB_IconInformation);
          If ZQuery1.IsEmpty Then
           Begin
             ZQuery1.SQL.Clear;
             ZQuery1.SQL.Add('select * from valentine');
             ZQuery1.ExecSQL;
             Edit1.SetFocus;
           End;
   Exit;
   End;


  With ZQuery1 Do
  begin
    SQL.Clear;
    SQL.Text:='UPDATE valentine SET Nama_Peserta_y4CM='+Quotedstr(Edit2.Text)+','+
    'Activity='+Quotedstr(Combobox1.Text)+','+'Sekolah_Kampus='+Quotedstr(Edit3.Text)+','+'Hobby='+Quotedstr(Combobox2.Text)+','+
    'Alamat='+Quotedstr(Edit4.Text)+','+'Facebook_Twitter='+Quotedstr(Edit5.Text)+','+'Nomor_Telepon='+Quotedstr(Edit6.Text)+','+
    'Pesan_Kesan='+Quotedstr(Memo1.Text)+
    'WHERE Nomor_UrutKodeValentine='+Quotedstr(Edit1.Text);
    ExecSQL;
  end;

Button5.Click;

With ZQuery1 Do
  begin
    Active:=False;
    SQL.Clear;
    SQL.Text:='SELECT * FROM valentine';
    ExecSQL;
    Active:=True;
  end;
end;

procedure TEdit_Data.Button5Click(Sender: TObject);
begin
Edit1.Text:='';
Edit2.Text:='';
Edit3.Text:='';
Edit4.Text:='';
Edit5.Text:='';
Edit6.Text:='';
Combobox1.Text:='';
Combobox2.Text:='';
Memo1.Text:='';

With ZQuery1 Do
  begin
    Active:=False;
    SQL.Clear;
    SQL.Text:='SELECT * FROM valentine';
    ExecSQL;
    Active:=True;
  end;
end;

procedure TEdit_Data.DBGrid1DblClick(Sender: TObject);
begin
Edit1.Text:=DBGrid1.Fields[0].AsString;
Edit2.Text:=DBGrid1.Fields[1].AsString;
Combobox1.Text:=DBGrid1.Fields[2].AsString;
Edit3.Text:=DBGrid1.Fields[3].AsString;
Combobox2.Text:=DBGrid1.Fields[4].AsString;
Edit4.Text:=DBGrid1.Fields[5].AsString;
Edit5.Text:=DBGrid1.Fields[6].AsString;
Edit6.Text:=DBGrid1.Fields[7].AsString;;
Memo1.Text:=DBGrid1.Fields[8].AsString;
end;

procedure TEdit_Data.Edit6Change(Sender: TObject);
Var Key:Char;
begin
if Length(Edit6.Text)>=13 then
    begin
      MessageBox(Handle,'Nomor Telepon Anda Lebih Dari 12 Digit','Warning',MB_IconInformation);
      Edit6.Text:='';
      Edit6.SetFocus;
    end;

end;

procedure TEdit_Data.Edit6KeyPress(Sender: TObject; var Key: Char);
begin
if not (key in ['0'..'9',#8]) then key:=#0;
end;

procedure TEdit_Data.FormClose(Sender: TObject; var Action: TCloseAction);
begin
Button5.Click;
end;

procedure TEdit_Data.FormCreate(Sender: TObject);
begin
With ZQuery1 Do
  Begin
     Active:=False;
     SQL.Clear;
     SQL.Text:='SELECT * FROM valentine';
     open;
     Active:=True;
  end;
end;

procedure TEdit_Data.Refresh1Click(Sender: TObject);
begin
ZQuery1.Active:=False;
ZQuery1.Active:=True;
end;

End.

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

   Untuk bagian export ke excel

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

procedure Ty4cm1.AdvToolButton1Click(Sender: TObject);
begin
 QExport4Dialog1.ExportSource:=esDBGrid;
 QExport4Dialog1.Execute;
end;



Screen Shut Program

Silahkan Download Komponent EMS Advanced 

Silahkan Download Contoh Project Valentines Gathering Software Export To Excel (Database MySQL)

Sekian dan Terima Kasih , Salam Delphier.....