View Revisions: Issue #30623

Summary 0030623: TStringGrid copy/paste to/from MS Excel and OO Calc bug
Revision 2018-09-05 12:18 by Juha Manninen
Additional Information MS Excel and OO Calc when copy cells to clipboard set this clipboard flag:

CF_Text = True
CF_Bitmap = False
CF_Picture = True
CF_MetaFilePict = False
CF_Object = False
CF_Component = False
CF_HTML = True

If CF_HTML = True then, MS Excel and OO Calc ignore text data and parse HTML data from clipboard.

Fix (only copy cells from StringGrid to Excel/Calc):

procedure TCustomStringGrid.CopyCellRectToClipboard(const R: TRect);
var
- SelStr: String;
+ SelStr, SelHTMLStr: String;
+ rflags: TReplaceFlags;
  aRow,aCol,k: LongInt;
  function QuoteText(s: string): string;
  begin
    DoCellProcess(aCol, aRow, cpCopy, s);
    if (pos(0000009, s)>0) or
       (pos(0000010, s)>0) or
       (pos(0000013, s)>0) or
       (pos('"', s)>0)
    then
      result := AnsiQuotedStr(s, '"')
    else
      result := s;
  end;
begin
  SelStr := '';
+ SelHTMLStr := '<table>';
  for aRow:=R.Top to R.Bottom do begin
+ SelHTMLStr := SelHTMLStr + '<tr>';

    for aCol:=R.Left to R.Right do begin

      if Columns.Enabled and (aCol>=FirstGridColumn) then begin

        k := ColumnIndexFromGridColumn(aCol);
        if not Columns[k].Visible then
          continue;

        if (aRow=0) and (FixedRows>0) then
- SelStr := SelStr + QuoteText(Columns[k].Title.Caption)
+ begin
+ SelStr := SelStr + QuoteText(Columns[k].Title.Caption);
+ SelHTMLStr := SelHTMLStr + '<td>' + Columns[k].Title.Caption + '</td>';
+ end
        else
+ begin
          SelStr := SelStr + QuoteText(Cells[aCol,aRow]);
+ SelHTMLStr := SelHTMLStr + '<td>' + Cells[aCol,aRow] + '</td>';
+ end;

      end else
+ begin
          SelStr := SelStr + QuoteText(Cells[aCol,aRow]);
+ SelHTMLStr := SelHTMLStr + '<td>' + Cells[aCol,aRow] + '</td>';
+ end;

      if aCol<>R.Right then
        SelStr := SelStr + 0000009;
    end;

    SelStr := SelStr + sLineBreak;
+ SelHTMLStr := SelHTMLStr + '</tr>';
  end;
+ rflags := [rfReplaceAll, rfIgnoreCase];
+ SelHTMLStr := StringReplace(SelHTMLStr, '"', '"', rflags) + '</table>';
- Clipboard.AsText := SelStr;
+ Clipboard.SetAsHtml(SelHTMLStr, SelStr);
end;

P.S.: To fix paste cells from Excel/Calc to StringGrid I think that need to parse HTML clipboard content in procedure TCustomStringGrid.SelectionSetText(TheText: String) or remove function QuoteText from procedure TCustomStringGrid.CopyCellRectToClipboard and correct operations with quote symbol in procedure LoadFromCSVStream.
Revision 2016-09-20 23:20 by K155LA3
Additional Information MS Excel and OO Calc when copy cells to clipboard set this clipboard flag:

CF_Text = True
CF_Bitmap = False
CF_Picture = True
CF_MetaFilePict = False
CF_Object = False
CF_Component = False
CF_HTML = True

If CF_HTML = True then, MS Excel and OO Calc ignore text data and parse HTML data from clipboard.

Fix (only copy cells from StringGrid to Excel/Calc):

procedure TCustomStringGrid.CopyCellRectToClipboard(const R: TRect);
var
- SelStr: String;
+ SelStr, SelHTMLStr: String;
+ rflags: TReplaceFlags;
  aRow,aCol,k: LongInt;
  function QuoteText(s: string): string;
  begin
    DoCellProcess(aCol, aRow, cpCopy, s);
    if (pos(0000009, s)>0) or
       (pos(0000010, s)>0) or
       (pos(0000013, s)>0) or
       (pos('"', s)>0)
    then
      result := AnsiQuotedStr(s, '"')
    else
      result := s;
  end;
begin
  SelStr := '';
+ SelHTMLStr := '<table>';
  for aRow:=R.Top to R.Bottom do begin
+ SelHTMLStr := SelHTMLStr + '<tr>';

    for aCol:=R.Left to R.Right do begin

      if Columns.Enabled and (aCol>=FirstGridColumn) then begin

        k := ColumnIndexFromGridColumn(aCol);
        if not Columns[k].Visible then
          continue;

        if (aRow=0) and (FixedRows>0) then
- SelStr := SelStr + QuoteText(Columns[k].Title.Caption)
+ begin
+ SelStr := SelStr + QuoteText(Columns[k].Title.Caption);
+ SelHTMLStr := SelHTMLStr + '<td>' + Columns[k].Title.Caption + '</td>';
+ end
        else
+ begin
          SelStr := SelStr + QuoteText(Cells[aCol,aRow]);
+ SelHTMLStr := SelHTMLStr + '<td>' + Cells[aCol,aRow] + '</td>';
+ end;

      end else
+ begin
          SelStr := SelStr + QuoteText(Cells[aCol,aRow]);
+ SelHTMLStr := SelHTMLStr + '<td>' + Cells[aCol,aRow] + '</td>';
+ end;

      if aCol<>R.Right then
        SelStr := SelStr + 0000009;
    end;

    SelStr := SelStr + sLineBreak;
+ SelHTMLStr := SelHTMLStr + '</tr>';
  end;
+ rflags := [rfReplaceAll, rfIgnoreCase];
+ SelHTMLStr := StringReplace(SelHTMLStr, '"', '"', rflags) + '</table>';
- Clipboard.AsText := SelStr;
+ Clipboard.SetAsHtml(SelHTMLStr, SelStr);
end;

P.S.: To fix paste cells from Excel/Calc to StringGrid I think that need to parse HTML clipboard content in procedure TCustomStringGrid.SelectionSetText(TheText: String) or remove function QuoteText from procedure TCustomStringGrid.CopyCellRectToClipboard and correct operations with quote symbol in procedure LoadFromCSVStream.