如何格式化剪贴板中的行和列以粘贴来自Excel？

I'm working with a bunch of grids and those grids don't support copying rows and columns with a table layout like Excel does.

我正在使用一堆网格,这些网格不支持像Excel一样使用表格布局复制行和列。

We need to be able to copy some rows and columns from the grid, and paste them into an Outlook email with decent formatting in properly aligned columns. If you copy from Excel, it does that nicely.

我们需要能够从网格中复制一些行和列,并将它们粘贴到Outlook电子邮件中,并在正确对齐的列中使用适当的格式。如果从Excel复制,它可以很好地完成。

If I copy from the grid, I get tab-delimited data, and that doesn't work. Also the fonts are not monospaced like Courier, so padding the data to the same number of characters also doesn't work.

如果我从网格中复制,我会得到制表符分隔的数据,但这不起作用。此外,字体不像Courier那样是等宽字体,因此将数据填充到相同数量的字符也不起作用。

I'd really like to know how Excel manages to put this extra formatting into the clipboard. I'm using Delphi by the way, but any advice is appreciated.

我真的很想知道Excel如何将这些额外的格式设置到剪贴板中。顺便提一下,我正在使用Delphi,但是我们对此表示赞赏。

EDIT: we don't want to go via Excel first...we want to go straight from the grid to the clipboard and then to the email.

编辑:我们不想先通过Excel ...我们想直接从网格到剪贴板再到电子邮件。

Thanks! Bart

2 个解决方案

#1

When you copy to the clipboard from Excel, many different formats are placed on the clipboard. You need to find one of those formats that you can replicate, and that will give the desired result.

从Excel复制到剪贴板时,剪贴板上会放置许多不同的格式。您需要找到可以复制的格式之一,这样才能获得所需的结果。

The way I have achieved this in the past is to put HTML on the clipboard. For which you can use this function:

我过去实现这一目标的方法是将HTML放在剪贴板上。您可以使用此功能:

procedure ClipboardError;
begin
  raise EMyExceptionClass.Create('Could not complete clipboard operation.');
end;

procedure CheckClipboardHandle(Handle: Windows.HGLOBAL);
begin
  if Handle=0 then begin
    ClipboardError;
  end;
end;

procedure CheckClipboardPtr(Ptr: Pointer);
begin
  if not Assigned(Ptr) then begin
    ClipboardError;
  end;
end;

procedure PutInClipboard(ClipboardFormat: UINT; Buffer: Pointer; Count: Integer);
var
  Handle: Windows.HGLOBAL;
  Ptr: Pointer;
begin
  if Count>0 then begin
    Clipboard.Open;
    Try
      Handle := Windows.GlobalAlloc(GMEM_MOVEABLE, Count);
      Try
        CheckClipboardHandle(Handle);
        Ptr := Windows.GlobalLock(Handle);
        CheckClipboardPtr(Ptr);
        Move(Buffer^, Ptr^, Count);
        Windows.GlobalUnlock(Handle);
        Clipboard.SetAsHandle(ClipboardFormat, Handle);
      Except
        GlobalFree(Handle);
        raise;
      End;
    Finally
      Clipboard.Close;
    End;
  end;
end;

var
  HTMLClipboardFormat: UINT;

procedure PutHTMLInClipboard(Strings: TStrings);

var
  Data: TStringList;

  procedure WriteDescription(const StartOffset, EndOffset: Integer);
  begin
    while Data.Count<5 do begin
      Data.Add('');
    end;
    Data[0] := 'Version:0.9';
    Data[1] := Format('StartHTML:%.8d', [StartOffset]);
    Data[2] := Format('EndHTML:%.8d', [EndOffset]);
    Data[3] := Format('StartFragment:%.8d', [StartOffset]);
    Data[4] := Format('EndFragment:%.8d', [EndOffset]);
  end;

var
  StartOffset, EndOffset: Integer;
  Text: UTF8String;
begin
  Data := TStringList.Create;
  Try
    WriteDescription(0, 0);//write out description stub - will be replaced later
    StartOffset := Length(UTF8String(Data.Text));
    Data.AddStrings(Strings);
    EndOffset := Length(UTF8String(Data.Text))-1;
    WriteDescription(StartOffset, EndOffset);//now we know the offsets we can write proper description
    Text := Data.Text;
    PutInClipBoard(HTMLClipboardFormat, PAnsiChar(Text), Length(Text));
  Finally
    FreeAndNil(Data);
  End;
end;
....
initialization
  HTMLClipboardFormat := Windows.RegisterClipboardFormat('HTML Format');

The only thing that remains is to generate the HTML which you pass to that function. That's down to you. I suggest that you use Excel to put HTML on the clipboard and then inspect the HTML that it generates. Use that as a guide for what you need to do.

唯一剩下的就是生成传递给该函数的HTML。这取决于你。我建议您使用Excel将HTML放在剪贴板上,然后检查它生成的HTML。使用它作为您需要做的指导。

#2

You can use this function to export your grid contents to Excel, and other applications which support tabular data:

您可以使用此功能将网格内容导出到Excel以及支持表格数据的其他应用程序:

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

    // create the Excel object
    if toExcel then
    begin
      ExcelApp := CreateOleObject('Excel.Application');
      ExcelApp.WorkBooks.Add(1); //xlWBatWorkSheet);
      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 := TStringList.Create;
    try
      sline := '';

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

      // get the data into the memo
      for row := 0 to DBGrid.DataSource.DataSet.RecordCount-1 do
      begin
        sline := '';
        for col := 0 to DBGrid.FieldCount-1 do
          if Assigned(DBGrid.Fields[col]) then
            if DBGrid.Fields[col].Visible then
              sline := sline + DBGrid.Fields[col].AsString + #9;
        mem.Add(sline);
        DBGrid.DataSource.DataSet.Next;
      end;

      // we copy the data to the clipboard
      Clipboard.AsText := mem.Text;
    finally
      mem.Free;
    end;
    // 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;
    end;
  finally
    DBGrid.DataSource.DataSet.GotoBookmark(bm);
    DBGrid.DataSource.DataSet.EnableControls;
    Screen.Cursor := crDefault;
  end;
end;

#1