View Issue Details

IDProjectCategoryView StatusLast Update
0034789LazarusLCLpublic2019-01-02 22:47
ReporterJesus Reyes Assigned ToJesus Reyes  
Status assignedResolutionopen 
Product Version2.1 (SVN) 
Target Version2.2 
Summary0034789: Grid pasting of selection into Excel doesn't preserve text line endings and may generate merged cells.
DescriptionIf a cell contains line breaks and this cell is pasted into a Excel sheet the content is split into several cells in the target spreadsheet.
Additional InformationThis problem and its test data is described in the related bug report
TagsNo tags attached.
Fixed in Revision
Attached Files


related to 0030623 resolvedJesus Reyes TStringGrid copy/paste to/from MS Excel and OO Calc bug 



2018-12-31 20:49


grids.pas.v2.4.diff (6,532 bytes)   
uses .., HTMLDefs,


  TCustomStringGrid = class(TCustomDrawGrid)
      procedure SelectionSetHTML(TheHTML, TheText: String);


procedure TCustomStringGrid.CopyCellRectToClipboard(const R: TRect);
  SelStr, SelHTMLStr: String;
  aRow,aCol,k: LongInt;
  function QuoteText(s: string): string;
    DoCellProcess(aCol, aRow, cpCopy, s);
    if (pos(#9, s)>0) or
       (pos(#10, s)>0) or
       (pos(#13, s)>0)
      result := AnsiQuotedStr(s, '"')
      result := s;

  function PrepareToHTML(s: string): string;
    i1: Integer;
    s1: string;
    Result := '';
    for i1 := 1 to Length(s) do
      case s[i1] of
        #13: s1 := '<br>';
        #10: if i1 > 1 then if s[i1 - 1] = #13 then s1 := '' else s1 := '<br>';
        '<': s1 := '&lt;';
        '>': s1 := '&gt;';
        '"': s1 := '&quot;';
        '&': s1 := '&amp;';
        else s1 := s[i1];
      Result := Result + s1;

  SelStr := '';

  SelHTMLStr := '<head><style><!--table br {mso-data-placement:same-cell;} --></style></head>' + #13#10 +
                '<table>' + #13#10;

  //<head>...</head> MS Excel crutch, otherwise Excel split merged cell if it found <br> tag

  for aRow := R.Top to R.Bottom do begin

    SelHTMLStr := SelHTMLStr + '<tr>' + #13#10;

    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

        if (aRow = 0) and (FixedRows > 0) then
          SelStr := SelStr + QuoteText(Columns[k].Title.Caption);
          SelHTMLStr := SelHTMLStr + '<td>' + PrepareToHTML(Columns[k].Title.Caption) + '</td>' + #13#10;
          SelStr := SelStr + QuoteText(Cells[aCol,aRow]);
          SelHTMLStr := SelHTMLStr + '<td>' + PrepareToHTML(Cells[aCol,aRow]) + '</td>' + #13#10;

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

      if aCol<>R.Right then
        SelStr := SelStr + #9;

    SelStr := SelStr + sLineBreak;
    SelHTMLStr := SelHTMLStr + '</tr>' + #13#10;
  SelHTMLStr := SelHTMLStr + #13#10 + '</table>';
  Clipboard.SetAsHtml(SelHTMLStr, SelStr);

procedure TCustomStringGrid.DoPasteFromClipboard;
  // Unpredictable results when a multiple selection is pasted back in.
  // Therefore we inhibit this here.
  if HasMultiSelection then

  if EditingAllowed(Col) then
    if Clipboard.HasFormat(CF_TEXT) and not Clipboard.HasFormat(CF_HTML) then SelectionSetText(Clipboard.AsText);
    if Clipboard.HasFormat(CF_TEXT) and Clipboard.HasFormat(CF_HTML) then SelectionSetHTML(Clipboard.GetAsHtml(True), Clipboard.AsText);

procedure TCustomStringGrid.SelectionSetHTML(TheHTML, TheText: String);
  bStartCol, bStartRow, bCol, bRow: Integer;
  bCellStr: string;
  bSelRect: TRect;

  bCellData, bTagEnd: Boolean;
  bStr, bEndStr: PChar;

  function ReplaceEntities(cSt: string): string;
    o,a,b: pchar;
    dName: widestring;
    dEntity: WideChar;
    while true do begin
      result := cSt;
      if cSt = '' then
      o := @cSt[1];
      a := strscan(o, '&');
      if a = nil then
      b := strscan(a + 1, ';');
      if b = nil then
      dName := UTF8Decode(copy(cSt, a - o + 2, b - a - 1));
      dEntity := ' ';
      if ResolveHTMLEntityReference(dName, dEntity) then begin
        system.delete(cSt, a - o + 1, b - a + 1);
        system.insert(UTF8Encode(dEntity), cSt, a - o + 1);

  if theHTML <> '' then
    bSelRect := Selection;
    bStartCol := Selection.Left;
    bStartRow := Selection.Top;
    bCol := bStartCol;
    bRow := bStartRow;
    bStr := PChar(theHTML);
    bEndStr := bStr + StrLen(bStr) - 4;
    bCellStr := '';
    bCellData := False;

    while bStr < bEndStr do
      if bStr^ = #13 then // delete #13#10#20...#20  Excel place this after <br> tag.
        while bStr < (bEndStr - 1) do
          if (bStr^ <> #10) and (bStr^ <> ' ') then Break;
      if bStr^ = '<' then // tag start sign '<'
        bTagEnd := False;

        if UpCase(bStr^) = 'B' then
          if (UpCase(bStr^) = 'R') and bCellData then bCellStr := bCellStr + #10; // tag <br> in table cell

        if bStr^ = '/' then // close tag sign '/'
          bTagEnd := True;

        if UpCase(bStr^) = 'T' then

          if UpCase(bStr^) = 'R' then // table start row tag <tr>
            bCellData := False;
            if bTagEnd then // table end row tag  </tr>
              bSelRect.Bottom := bRow;
              bCol := bStartCol;

          if UpCase(bStr^) = 'D' then // table start cell tag <td>
            bCellData := not bTagEnd;
            if bTagEnd then // table end cell tag </td>
              if (bCol < ColCount) and (bRow < RowCount) then Cells[bCol, bRow] := ReplaceEntities(bCellStr);
              bSelRect.Right := bCol;
              bCellStr := '';

        while bStr < bEndStr do
          if bStr^ = '>' then // tag end sign '>'
      end else
        if (bStr^ <> #13) and (bStr^ <> #10) and (bStr^ <> #9) and bCellData then bCellStr := bCellStr + bStr^;

    if (bCol = bStartCol) and (bRow = bStartRow) then Cells[bCol, bRow] := TheText; //set text in cell if clipboard has CF_HTML fomat, but havent HTML table
    Selection := bSelRect; // set correct selection
grids.pas.v2.4.diff (6,532 bytes)   


2018-12-31 20:52

reporter   ~0113051

Last edited: 2018-12-31 22:12

View 2 revisions

I think I found crutch for MS Excel. Please test grids.pas.v2.4.diff.
Excel does not split cells if it finds "br {mso-data-placement:same-cell;}" in "style" tag.

jamie philbrook

2018-12-31 22:49

reporter   ~0113053

Why must we bastardize a component for a specific product ?

if an EXCEL grid is what you want, why can't a TExcelStringGrid be made?

What happens when someone wants to use the TstringGrid and build around it to
find out you need to jump all these hurtles to disable stuff that has been
specifically tailored just for Excel.

 I've already explained this once before on another thread but for what ever
reason this just keeps going on like a bad joke..

 I have two apps where I had to do some hacky stuff to disable copy and paste
processes specifically to make external apps happy, specifically Excel..

 Can someone please come to their senses and stop this and maybe create a new
class just for Excel and fix what is in the string grid now that inhibits what was once easy to do in Delphi...

 Sorry for my ranting but this is just getting out of hand, and its always the
same poster.


2018-12-31 23:02

developer   ~0113054



2019-01-01 09:33

reporter   ~0113058

Your experience is just your personal experience and you should not extrapolate it to all. But even your personal experience shows that there is a problem.
You made two applications, someone twenty-two, and someone two hundred and two-two. And in each floats one and the same problem. And do you offer in each case do some hacky stuff?
Maybe much better when there is compatibility out of the box?
I think that the behavior should be the same if not for all applications, then at least for the most popular ones.
This functionality does not affect the essence of TStringGrid. Moreover, TStringGrid in Lazarus already has more functionality than TStringGrid in Delphi. If you want to return to the Stone Age, then you need to trim the already existing functionality.
And I’m not even asking you to add basic conveniences in the form of "Ctr+A" support and clearing the selected range on the "Del" key, not to mention even undo/redo support.


2019-01-01 11:19

developer   ~0113060

We don't have to wonder why the LCL is getting fatter and fatter from version to version. Why don't you write a derived TStringGrid for your needs? All essential methods are virtual and ready to be overridden by you. Or use TsWorksheetGrid from fpspreadsheet which tries to mimic Excel (to a low degree, though). Why must my standard application carry all this stuff which it does not need?


2019-01-01 14:06

reporter   ~0113062

Stuffing impossible to turn back.
TStringGrid is already incredibly inflated. It is only necessary to trim the functionality (compare at least the Opions section in Lazarus and Delphi) and thousands and thousands of applications simply stop compiling.
Draw your attention - changing the code of copying and pasting through the clipboard does not affect the compilation of previously created applications.
The only way to return to the TStringGrid as in Delphi in low blood is to introduce another component, for example TStringGridExt, with the current TStringGrid functionality, then in previous applications it will be enough to change TStringGrid to TStringGridExt.
But who will do this?

jamie philbrook

2019-01-01 16:53

reporter   ~0113065

"But who will do this? "

 You of course! You seem to know exactly what it is "YOU" need, while the rest
of us would love not to have bloated code in their apps doing nothing.

 If there is something in the TStringGrid that prevents you from subclassing it
to one you need, then I am sure the community would love to fix it or you could
even offer a fix, one that does not "STUFF" specific product software needs in it.
 The default components that come installed are what I called building blocks, if one of them does not provide the full operation as needed, you can derive one from that and make it do what you need in your app while the rest of us
can follow suit.

 I fully agree with WP, and WP has a good point about fpspreedsheet, someone has
already thought about it..

 Good day.

Jesus Reyes

2019-01-01 19:50

developer   ~0113072

In fact I delayed the patch too much because my intention was to find a way to modularize this feature, something that could be added on demand. Simply I ran out of time (difficult times here) and 2.0 is coming. Modularizing this component is a pending task, ideas are welcomed.

For those invoking that derivation should be made, what stop them doing it? if there are technical impediments I would like to hear them together with the proposed solutions and I'm sure we will arrive to some agreement.

Now, where to stop improving the grid?, well I publicly asked about this precisely: what should we do with the grid, shall we keep improving it or stop doing it. Not a single reply (or idea) was the answer. I know this reclaim may be unfair as was done long ago and some of you were may have been not with us yet. But I mention it so you know is not a recent thought.

With regard to the Lazarus TStringGrid copy and paste feature, I can tell you with some precision that it has been a feature for roughly 13 years, do you suggest to remove it just because you don't use it? come on, be less emotional and more realistic. Moreover, although it worked well (at least for my needs) it was demonstrated it had bugs and this patch fixed the bugs. That is acceptable and OK to me.

I appreciate everybody's opinions and I'm glad to hear what should be made and what no, I would be more glad if actual code or good ideas are provided towards making the grid better than now. Over the years some people actually didn't ask and actually improved the grid, others contributed changes that in my opinion didn't make it worst but better.

In particular wp, do you think we should add merging cells functionality into the grid?, if I remember correctly you provided a patch for that end.

Now, we enter the realm of curiosities :), in which way is the grid now more bastard than before?. For me this imply some parenthood as such, the grid may be considered my child, if it is a bastard then I, as the father, would like to know at what point it was made it bastard :D. This is of course a fun thought because I think it is difficult to objectively draw lines here.


2019-01-01 20:27

reporter   ~0113073

Last edited: 2019-01-01 20:28

View 2 revisions

2 Jesus Reyes: Case 0030623 is closed and applied the grids.pas.v2.3.1.diff patch, but when examining the contents of the clipboard an annoying error was found when copying a cell from Excel containing several lines, extra spaces are added. In patch grids.pas.v2.4.diff, it is fixed.
In procedure SelectionSetHTML added this code:
if bStr^ = # 13 then // delete # 13#10#20...# 20 Excel place this after < br > tag.
  while bStr < (bEndStr - 1) do
    if (bStr^ <> # 10) and (bStr^ <> ' ') then Break;

jamie philbrook

2019-01-01 20:51

reporter   ~0113074

I guess the price for Delphi isn't looking so bad now...

Any coder worth their weight could take a simple implementation of the
stringGrid and filter the resulting contents of a copy or paste to format it
to their needs.
 I thought the LCL was a building block for constructing programs to the coders
needs. I guess I was wrong, it's becoming another Microsoft specific supported
tool starting with the Grid, where does this stop ?

Jesus Reyes

2019-01-01 21:37

developer   ~0113075

... and now it seems to be multi-platform, so why no?

Any coders worth their weight would take a TCustomControl and build a grid according to their needs, it would be the best, why no? of course no, one might probably found that for most cases it's not very smart.

The LCL doesn't offer finished programs, so it is a building block always, this is incontrovertible, nobody can get that wrong. I would like to know why you think it is in another way.

There is nothing specific to microsoft sistems here apart from being a target system and then you always will have to have something specific do you like it or not. There is something you may be misunderstanding, bugs needs to be reported with specificity, always, if there is a bug when interacting with excel then it will be an error report it against libre office for example.

Where it will stop?, never or being less dramatic, it doesn't stop, or it will stop when people stop contributing. I find it very confusing when people start complaining about other people particular views that lead to particular contributions (read needs) but at the same time seems not to realize that his own particular view (or need) is only one of many.

But what ones feels should be not just disregarded by others as if it has no value, and if you can demonstrate with facts that the copy and paste feature do really harm more than good, I will be the first happy in removing this feature. Just remember that we need to be creative and remove features with options (don't forget what I already said that modularizing this component is a pending task).


2019-01-01 22:53

reporter   ~0113076

2 jamie philbrook: This may seem strange to you, but the initial problem was that when you copy from StringGrid into OO Calc, extra quotes appeared. The problem was specific for OO Calc and not for MS Excel. With copying in MS Excel everything with quotes was fine.
And no changes when using only textual data format in the clipboard did not solve this problem (if it was solved in OO Calc, then it appeared in MS Excel, and vice versa).
After examining the contents of the clipboard, it turned out that if the clipboard contains a table in HTML format, the text part is ignored, and MS Excel and OO Calc do the same.


2019-01-01 23:16

developer   ~0113077

Last edited: 2019-01-02 00:26

View 2 revisions

I think the guideline should be that the grid (like any other general-purpose component) must provide basic functionality and enough "hooks" to allow the user to extend it as needed. The hooks are
- events
- virtual methods.

Events are favored because the user simply can implement them in his own code and the LCL is not overloaded with the code for special tasks. Virtual methods require the user to derive a new grid class from the existing ones which requires some level of experience.

The LCL grids are prepared in a very rudimentary way for merging cells. However, implementation is not straightforward, it requires a derived class to access inherited virtual methods. Therefore I wrote a patch for cell merging on the basis of an additional "MergedCells" collection (0034180). However, as objected by Luiz, I was not aware of the already prepared infrastructure in the internal grid. Unfortunately I could not focus on this topic to rewrite the patch as suggested by Luiz. But maybe I should put it on the to-do list for the New Year...

So, Luiz, to answer your question regarding cell merging: Yes it is needed in my opinion.


What is not needed (again, in my opinion) is to get 100% agreement with clipboard copying and pasting to and from other applications - there will always be cases when this does not match. And it will be an appetizer for more: When we can paste cell with line breaks from Excel correctly why aren't they displayed? Why don't we paint pasted cells left-aligned while they are centered in Excel?

I would not have implemented html support for pasting because it opens a Box of Pandora.

In order to provide a "hook" for clipboard access I would add events for copying and pasting: OnCopyToClipboard and OnPasteFromClipboard. If an event handler is available it should override the built-in CF_TEXT support. In the handler the user has full freedom and full responsibility. The LCL grid remains free from the responsibility to fix all borderline cases.

Jesus Reyes

2019-01-02 02:02

developer   ~0113082

As explained by K155LA3 the problem is not only interoperability with other other apps, the problem remained in the grid itself. Yes, the conditions for triggering the problem may be regarded as corner cases but in order to fix that I would have to put much more code than is currently done, believe me as I actually did it without html but in despite of that the interoperability with other apps (which some regard as no useful but others would argue the contrary but that is a fact of the life that is a grid feature), will still have to be fixed, and even if I didn't like the html approach as you can read your self, there was at the moment, no other way to fixing it (I don't count saying that the grid doesn't support interoperability as an option).

Now there is an interesting challenge, remove all grid features that people are against to and do it in a way that the removed features are implemented on demand. Wp mentions a couple, I would add feature auto-registering of plugins, much like LazReport can support custom views, you just link in a unit in the uses clause and some auto-registering mechanics will register full unicode clipboard support with interoperability, by default the grid would only support copy&paste with itself. I planned to do just that but I would not realistically end it before 2.0. Maybe later.

About the merging cells, let me answer (instead of our good friend Luiz) with another question, why a feature like merging cells deserve to be in the standard grid and copy&paste doesn't? is not adding that to a standard grid way too much for a grid building block ...? That is why I said that is not easy to draw a line here.

Maybe it is (easy to decide) with facts but we have to know the facts.

jamie philbrook

2019-01-02 02:12

reporter   ~0113083

Last edited: 2019-01-02 02:17

View 2 revisions

K155LA3 :

 I am more aware of what is going on than you think and no matter what you do
to change the grid there will always be issues in that regard.

 Your idea is perfect and the way it should be done. That not only fixes this
on going issue of hacking the LCL for specific products It also allows for others to use that same set of events for interception.

 And maybe we can review the current implementation and do a little retraction
to make the Copy and Paste a 1:1 of what is in the cells because I had to hack
it a little to get around some apparent changes to the text that was taking place.

 As for Cell merging? Is it that hard to use the current implementation for that?
 This reminds me of the DeleteRow, DeleteCOL and then you have the DeleteROWCOL(Boolean), So we have a single function that does one of the other with a flag,
was that really needed? That is just a sample..

Thanks WP.


2019-01-02 12:45

developer   ~0113092

> instead of our good friend Luiz

Sorry, I feel ashamed for confusing your name...


> why a feature like merging cells deserve to be in the standard grid and copy&paste doesn't
I never said that. The point is that without copying and pasting a full worksheet stream to/from Excel there will always be differences between the LCL grid and Excel - and we hopefully agree that this is beyond the scope of the LCL grids. The same with other external applications. There is a line to be drawn not only between features, but also in to which extent a feature is to be implemented. As for copy&paste within the same grid I had brought up a decidated clipboard format some time ago (0028755) which never had been considered.

Jesus Reyes

2019-01-02 22:47

developer   ~0113117

Don't worry wp I understood it was a confusion and didn't bother me :)

About what belongs to the grid and what not, I used the cell merging feature as an example, because some people insist in that adding to the grid make it less of a building block than before. The question is still valid for any X feature, and was my concern long ago.

I have no doubts that you know that the grid do already use virtual methods and events that users may use (and actually use) for creating derived grids with special requirements.

The point here is to find a useful common denominator, some balanced choice of features.

I firmly think that the copy&paste feature doesn't set the grid in a via to convert it into a spreadsheet nor the recent html patch pretend that, also the standard string grid should remain a "string" grid so no internal way for sorting integers or other data types (the grid already use events for that).

wp said ... "What is not needed (again, in my opinion) is to get 100% agreement with clipboard copying and pasting to and from other applications - there will always be cases when this does not match. And it will be an appetizer for more: When we can paste cell with line breaks from Excel correctly why aren't they displayed? Why don't we paint pasted cells left-aligned while they are centered in Excel?

I would not have implemented html support for pasting because it opens a Box of Pandora."

Read it aloud: the standard grid do not support (and will not support) html, the grid has not and will not have any Excel or any other spreadsheet specific features.

The html clipboard format is another thing, it is I think the ONLY way to transport tabular like unicode data between applications, all rich text that is carried in the data is and should be discarded (in a standard grid) because we don't pretend to be 100% compliant with all spreadsheet features. Line endings is perhaps at the border on what it should be supported (and that at the discretion of the programmer, cell merging is now in the twilight zone), this is precisely why I resolved the related report, that report has accomplished the goal of fixing the reported problems.

Yes we can have a grid-only way of handling the clipboard but why not have both things at the same time with almost the same (or maybe less) amount of code?.

Multiple selections in the grid is, in my opinion, a lightly implemented feature, I tried to use it in the project mentioned in the related report but was of no use. Multiselection can be useful for some applications even with its limitations so they remained in the grid, but they have to be handled specially by the programmer, the same should be true for copy&paste of multiselections.

Issue History

Date Modified Username Field Change
2018-12-31 17:15 Jesus Reyes New Issue
2018-12-31 17:15 Jesus Reyes Status new => assigned
2018-12-31 17:15 Jesus Reyes Assigned To => Jesus Reyes
2018-12-31 17:16 Jesus Reyes Relationship added related to 0030623
2018-12-31 20:49 K155LA3 File Added: grids.pas.v2.4.diff
2018-12-31 20:52 K155LA3 Note Added: 0113051
2018-12-31 22:12 K155LA3 Note Edited: 0113051 View Revisions
2018-12-31 22:49 jamie philbrook Note Added: 0113053
2018-12-31 23:02 wp Note Added: 0113054
2019-01-01 09:33 K155LA3 Note Added: 0113058
2019-01-01 11:19 wp Note Added: 0113060
2019-01-01 14:06 K155LA3 Note Added: 0113062
2019-01-01 16:53 jamie philbrook Note Added: 0113065
2019-01-01 19:50 Jesus Reyes Note Added: 0113072
2019-01-01 20:27 K155LA3 Note Added: 0113073
2019-01-01 20:28 K155LA3 Note Edited: 0113073 View Revisions
2019-01-01 20:51 jamie philbrook Note Added: 0113074
2019-01-01 21:37 Jesus Reyes Note Added: 0113075
2019-01-01 22:53 K155LA3 Note Added: 0113076
2019-01-01 23:16 wp Note Added: 0113077
2019-01-02 00:26 wp Note Edited: 0113077 View Revisions
2019-01-02 02:02 Jesus Reyes Note Added: 0113082
2019-01-02 02:12 jamie philbrook Note Added: 0113083
2019-01-02 02:17 jamie philbrook Note Edited: 0113083 View Revisions
2019-01-02 12:45 wp Note Added: 0113092
2019-01-02 22:47 Jesus Reyes Note Added: 0113117