Solved

TDBGrid contents to Clipboard

Posted on 1998-04-01
24
764 Views
Last Modified: 2010-04-06
How do you code a CopyToClipboard function for the TDBGrid Component? I populate a tdbgrid from a query object, and would like to give the users the ability to copy the grid contents to the clipboard for pasting in Excel spreadsheet.
0
Comment
Question by:evansj
  • 12
  • 8
  • 3
  • +1
24 Comments
 
LVL 1

Expert Comment

by:Greedy
ID: 1361193
I was playing with it and this is what I came up with as an exceptable way to do it...but I'm not calling it the answer because I know there is a better way I just can't think on it right now.

this requires you to add a editbox componet to the form...set the visible proporty to false...or just create it dynamicaly.

then to get the text to the clip board do a

  Edit1.Text := DBGrid1.SelectedField.AsString;
  Edit1.SelectAll;
  Edit1.Perform(WM_COPY, 0, 0);

you could also handle memo fields by maping the text into a memo if that was the type of field selected

  if DBGrid1.SelectedField is TMemoField then
  begin
    Memo1.Text := DBGrid1.SelectedField.AsString;
    Memo1.SelectAll;
    Memo1.Perform(WM_COPY, 0, 0);
  end
  else
  begin
    Edit1.Text := DBGrid1.SelectedField.AsString;
    Edit1.SelectAll;
    Edit1.Perform(WM_COPY, 0, 0);
  end;

if you had more than one field that you wanted to copy in you could do it by staging the data into one of these components one by one maby seperating them with commas so that it will import into excel nicly.

well hope this kinda helps...in reality this has the potential to be better than what I was thinking on in the first place...

0
 

Author Comment

by:evansj
ID: 1361194
How would you select particular rows?
0
 
LVL 4

Expert Comment

by:itamar
ID: 1361195
Hi evansj,

I remember of a component that may help you. That is its description:

"T(Adv)Excel is a free DDE component to talk to
Microsoft Excel. DDE is used for maximum data
transfer speed.


This component pack contains code for EXTREMELY FAST transfer
speed (like 1000+ elements in 150 milliseconds (!) or better.

The high-speed code used in this component is by far the
fastest way (known to me) to push arbitrary data into
Excel and to get data from Excel, some orders of magnitude
faster than OLE automation and a lot easier to use."

You can find it at:

http://sunsite.icm.edu.pl/delphi/ftp/d10free/excel.zip

If you like it, tell me so I can put this comment as an answer.

IHTH,
Itamar


0
 
LVL 1

Expert Comment

by:Greedy
ID: 1361196
you would have to iterate them one at a time...like this

  Edit1.Text := '';
  For I := 0 to (DBGrid1.FieldCount - 1) do
  begin
    if DBGrid1.Fields[I] is TStringField then
      Edit1.Text := Edit1.Text + ',' + DBGrid1.Fields[I].Value;
    if DBGrid1.Fields[I] is TIntegerField then
      Edit1.Text := Edit1.Text + ',' + IntToStr(DBGrid1.Fields[I].Value);
    if DBGrid1.Fields[I] is TDateField then
      Edit1.Text := Edit1.Text + ',' + FormatDateTime('mm/dd/yyyy', DBGrid1.Fields[I].Value);
  end;


0
 

Author Comment

by:evansj
ID: 1361197
Adjusted points to 75
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361198
you want to copy it to Excel?
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361199
In the DBGrid:Options  set on the MultiSelect and write the following:

procedure TForm1.Button1Click(Sender: TObject);
{Copying data from a grid to the clipboard}
var
  MemoryBlock: pChar;
  I: Word;
  J: Byte;
  BlockSize:LongInt;
  MemoryHandle: THandle;
begin
  Screen.Cursor:=crHourGlass;
  I:=0;

{Calculating the length of the memory block which equals to total length of all
records in the grid + places for tabs and linefeeds}
with DBGrid1 do
begin
    for J:=0 to Columns.Count-1 do
      Inc(I, Columns[J].Field.Size+1);//1 for the tab
    Inc(I);//1 for the carriage return
    BlockSize:=SelectedRows.Count*I+1;//1 for the null
    MemoryHandle:=GlobalAlloc(GHND, BlockSize);
    MemoryBlock:=GlobalLock(MemoryHandle);

{Entering characters from grid to memory block. Tab is Entered between two fields}
  for I:=0 to SelectedRows.Count-1 do
  begin
     DataSource.DataSet.Bookmark:=SelectedRows.Items[I];
     for J:=0 to Columns.Count-1 do
     if Fields[J].Value<>null then
     begin
         StrCat(MemoryBlock,pChar(Fields[J].DisplayText));
          if J<Columns.Count-1 then
            StrCat(MemoryBlock,#9)
          else
            StrCat(MemoryBlock,#13);
      end;
   end;
end;
{Copying memory block to the clipboard and freeing resources}
GlobalUnlock(MemoryHandle);
OpenClipboard(Self.Handle);
EmptyClipboard;
SetClipboardData(CF_TEXT,MemoryHandle);
CloseClipboard;
Screen.Cursor:=crDefault;
end;

0
 

Author Comment

by:evansj
ID: 1361200
The DBGrid component does not have a multiselect property.
0
 
LVL 1

Expert Comment

by:Greedy
ID: 1361201
aaaaa...yeah it does...it's under options. called dgMultiSelect.

to programaticaly include this option do a

DBGrid1.Options := DBGrid1.Options + [dgMultiSelect];


0
 

Author Comment

by:evansj
ID: 1361202
To ronit: the code produces access violations. It will copy 2 rows w/garbage to the clipbrd. You may be on to something though, if we can get past the access violations.
thanks to Greedy also.
0
 

Author Comment

by:evansj
ID: 1361203
Also if we can't get a good way to copy a dbgrid to clipboard, how would you populate a grid with data from a SQL database (Oracle)? I think the standard grid has clipboard copy built in, or is it the string grid?
0
 

Author Comment

by:evansj
ID: 1361204
Adjusted points to 100
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Expert Comment

by:ronit051397
ID: 1361205
My code works perfectly for a long long time. Post your email and I'll send it to you.
What version of delphi are you using?
0
 

Author Comment

by:evansj
ID: 1361206
To ronit:
I'm using the client/server version 3.0. My e-mail address is joe_e_evans@notes.seagate.com
I run on a 200MHZ Pentium (non-MMX) with 128MB ram, Seagate Tape drive, 24x CD-ROM,
four Seagate 4-GB hard drives, Matrox Millineum Video Card w/4MB WRAM. May or may not be pertinent.

Thanks :-)
0
 

Author Comment

by:evansj
ID: 1361207
to ronit:

I checked the code at home on my other work machine and it works. Resubmit your answer for your deserved points! I would appreciate possible pointers for why my work machine (at work) gets access violations when running the code.
0
 
LVL 5

Accepted Solution

by:
ronit051397 earned 100 total points
ID: 1361208
I didn't send you by mail because you say it works.
At what row you get Access Violation?
0
 

Author Comment

by:evansj
ID: 1361209
To ronit:
It bombs out right away. But when you do a paste to Excel you get two rows of data and garbage. What could be causing the access violation.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361210
I have changed the code for your purpose and forgot to handle Null fields, that's why you got the garbage (I assume), so here is the fixed code.

I tested it in Excel 5 & 7 and didn't get any Access Violation. Where do you get this error, in Delphi or Excel?
If in Delphi, on which line in the code?

procedure TForm1.Button1Click(Sender: TObject);
var
  MemoryBlock: pChar;
  I: Word;
  J: Byte;
  BlockSize:LongInt;
  MemoryHandle: THandle;
begin
  Screen.Cursor:=crHourGlass;
  I:=0;
  with DBGrid1 do
  begin
    DataSource.DataSet.DisableControls;
    for J:=0 to Columns.Count-1 do
      Inc(I, Columns[J].Field.Size+1);
    BlockSize:=SelectedRows.Count*I+1;//1 for the null
    MemoryHandle:=GlobalAlloc(GHND, BlockSize);
    MemoryBlock:=GlobalLock(MemoryHandle);

    for I:=0 to SelectedRows.Count-1 do
    begin
      DataSource.DataSet.Bookmark:=SelectedRows.Items[I];
      for J:=0 to Columns.Count-1 do
      begin
        if Fields[J].Value<>null then
          StrCat(MemoryBlock,pChar(Fields[J].DisplayText))
        else
          StrCat(MemoryBlock,' ');
        if J<Columns.Count-1 then
          StrCat(MemoryBlock,#9)
        else
          StrCat(MemoryBlock,#13);
      end;
    end;
    StrCat(MemoryBlock,#0);
    DataSource.DataSet.EnableControls;
  end;
  GlobalUnlock(MemoryHandle);
  OpenClipboard(Self.Handle);
  EmptyClipboard;
  SetClipboardData(CF_TEXT,MemoryHandle);
  CloseClipboard;
  Screen.Cursor:=crDefault;
end;

0
 

Author Comment

by:evansj
ID: 1361211
to ronit:
I get the access violations in Delphi when I select the rows and then click my copy clipboard button. Your new code snippet doesn't give me access violations on some things it used to, but when I query the database, I get several rows back, but only 1 and about 5/7 rows get pasted into excel. The version of excel is the one with Office 97.  And now I get access violations on things I didn't use to.
0
 

Author Comment

by:evansj
ID: 1361212
to ronit:
I ran the code again and stepped through. It does everyhting fine until it executes the "end;" statement. When it does work it returns two rows with garbage. Part of the garbage points to  the path of my virus scanner.  I ran your test app, and on intrabase no errors, but on Oracle, I got the access violations. Not all the time.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361213
Access Violation: The only thing I can think of is that the buffer (MemoryBlock) allocated is too small for the data selected, therefore try to increase it.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361214
Yes, you probabely get the Access Violation because you have many fields of the INTEGER type, therefore, when allocating the buffer, the length of the actual data (number size) is bigger then the field size, so the buffer is too small to store all the data.

Change the line:    Inc(I, Columns[J].Field.Size+1);

to something like :

if Columns[J].Field.DataType=ftInteger then
   Inc(I,10+1)
else
  Inc(I, Columns[J].Field.Size+1);

Do it for other fields that are not string: FLOAT, WORD etc.
0
 

Author Comment

by:evansj
ID: 1361215
to ronit:
thanks for responding. Here is something interesting about this situation. I was looking through my Mastering Delphi 3 book and found a code snippet for copying to clipboard. He used the following:
HMem:=GlobalAlloc(GHND,25 * sizeof(Integer)

So I changed the code to this:
MemoryHandle:=GlobalAlloc(GHND,25 * BlockSize).  It works! Don't know why though. Is it related to your response about integers? The fields are calculated fields that count the average of the sum of days in most of the fields, plus a count of items field. However, they are floats. I have 9 numeric fields and three string fields. I appreciate you sticking with me on this.
0
 
LVL 5

Expert Comment

by:ronit051397
ID: 1361216
Yes, it probably works because you increased the buffer size, either by this way or another. Happy for you. Bye.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now