Mengkopi Baris dbgrid ke clipboard n paste ke excel di Delphi

Tips ini bagaimana cara mengkopi dari dbgrid ke klipboard excel.

yang dibutuhin

uses
ComCtrls, COMObj, StdCtrls;

jangan lupa install excelnya

Berikut ini prosedur kopi baris yang dipilih / di sorot ke Clipboard dan siap di kopi ke excel

procedure TForm1.CopySelRow(dbgrd1:TDBGrid;Ortu:TForm);
var
i,col: Integer;
sline:string;
mmo1:TMemo;
begin
if dbgrd1.SelectedRows.Count > 0 then
begin
mmo1 := TMemo.Create(Ortu);
mmo1.Visible := false;
mmo1.Parent := Ortu;
mmo1.Lines.Clear;
Screen.Cursor:=crHourGlass;
for i:=0 to dbgrd1.SelectedRows.Count-1 do
begin
dbgrd1.DataSource.DataSet.Bookmark := dbgrd1.SelectedRows[i]; // moves!!
sline:='';
for col := 0 to dbgrd1.FieldCount-1 do
sline := sline + dbgrd1.Fields[col].AsString + #9;
mmo1.Lines.Add(sline);
end;
mmo1.SelectAll;
mmo1.CopyToClipboard;
mmo1.Free;
Screen.Cursor:=crDefault;
MessageDlg(IntToStr(dbgrd1.SelectedRows.Count)+' Selected Rows Copied', mtInformation,[mbOk],0);
end;
end;

Berikut ini prosedur mengkopi seluruh baris, dan bisa langsung membuka excel nya sekaligus

procedure TForm1.ExportDBGrid(dbgrd1:TDBGrid;toExcel: Boolean;ortu:Tform);
var
bm: TBookmark;
col, row: Integer;
sline: String;
mem: TMemo;
ExcelApp: Variant;
begin
Screen.Cursor := crHourglass;
dbgrd1.DataSource.DataSet.DisableControls;
bm := dbgrd1.DataSource.DataSet.GetBookmark;
dbgrd1.DataSource.DataSet.First;

// create the Excel object
if toExcel then
begin
ExcelApp := CreateOleObject(‘Excel.Application’);
ExcelApp.WorkBooks.Add(1);
ExcelApp.WorkBooks[1].WorkSheets[1].Name := ‘Grid Data’;
end;

// First we send the data to a memo
// works faster than doing it directly to Excel
mem := TMemo.Create(ortu);
mem.Visible := false;
mem.Parent := ortu;
mem.Clear;
sline := ”;

// add the info for the column names
for col := 0 to dbgrd1.FieldCount-1 do
sline := sline + dbgrd1.Fields[col].DisplayLabel + #9;
mem.Lines.Add(sline);

// get the data into the memo
for row := 0 to dbgrd1.DataSource.DataSet.RecordCount-1 do
begin
sline := ”;

for col := 0 to dbgrd1.FieldCount-1 do
sline := sline + trim(dbgrd1.Fields[col].AsString) + #9;
mem.Lines.Add(sline);
dbgrd1.DataSource.DataSet.Next;
end;

// we copy the data to the clipboard
mem.SelectAll;
mem.CopyToClipboard;

// if needed, send it to Excel
// if not, we already have it in the clipboard
if toExcel then
begin
ExcelApp.Workbooks[1].WorkSheets[‘Grid Data’].Paste;
ExcelApp.Visible := true;
ExcelApp.Workbooks[1].SaveAs(‘C:\tempExcel.xls’);
// ExcelApp.Quit;
// ShellExecute(0, ‘Open’, PChar(‘C:\tempExcel.xls’), PChar(‘123 999’), PChar(‘c:\temp’), SW_Hide);
ExcelApp := Unassigned;
end;
// FreeAndNil(ExcelApp);
if not VarIsEmpty(ExcelApp) then
begin
ExcelApp.DisplayAlerts := False;
ExcelApp.Quit;
ExcelApp := Unassigned;
end;

dbgrd1.DataSource.DataSet.GotoBookmark(bm);
dbgrd1.DataSource.DataSet.FreeBookmark(bm);
dbgrd1.DataSource.DataSet.EnableControls;
Screen.Cursor := crDefault;
MessageDlg(‘All Rows Copied’, mtInformation,[mbOk],0);
end;

Tampilan programnya :

cara nya :

  1. Pilih baris, tekan shit+ctr+klik kanan untuk multi dbgrid
  2. tekan tombol copy
  3. buka excel
  4. paste ke excel

kalo pengen langsung buka excel nya silahkan ceklis klipboard excel, dan tekan copy semua baris dan excel akan keluar dengan data dengan sendirinya.

download Source Codenya :http://cid-4583f23bbe8b0f42.office.live.com/self.aspx/.Public/delphi/Copy%20Grid%20to%20Excel.zip

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s