Bug 89920 - inserting line break to cell from macro
Summary: inserting line break to cell from macro
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Hardware: Other All
: medium normal
Assignee: Andreas Heinisch
Depends on:
Blocks: Macro
  Show dependency treegraph
Reported: 2015-03-09 22:09 UTC by Andras Timar
Modified: 2023-03-11 10:56 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:

test case (10.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-03-10 14:45 UTC, raal

Note You need to log in before you can comment on or make changes to this bug.
Description Andras Timar 2015-03-09 22:09:35 UTC
Consider the following macro:
    sub Main

    my_cell = ThisComponent.Sheets(0).getCellByPosition(0,0)
    my_cell.String = "aa bb" + chr(10) + "cc dd"

    end sub
It will write to A1 cell on the first sheet:
aa bb
cc dd

It will be saved to ODS as:
          <table:table-cell office:value-type="string" calcext:value-type="string">
            <text:p>aa bb</text:p>
            <text:p>cc dd</text:p>
It is good. However, let's have "aa bb     cc dd" in cell A1. Now we want to replace consecutive spaces to line breaks. We cannot do this from UI, so let's use a macro.
	sub Main
	rem ----------------------------------------------------------------------
	rem define variables
	dim document   as object
	dim dispatcher as object
	rem ----------------------------------------------------------------------
	rem get access to the document
	document   = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

	rem ----------------------------------------------------------------------
	dim args2(17) as new com.sun.star.beans.PropertyValue
	args2(0).Name = "SearchItem.StyleFamily"
	args2(0).Value = 2
	args2(1).Name = "SearchItem.CellType"
	args2(1).Value = 0
	args2(2).Name = "SearchItem.RowDirection"
	args2(2).Value = true
	args2(3).Name = "SearchItem.AllTables"
	args2(3).Value = false
	args2(4).Name = "SearchItem.Backward"
	args2(4).Value = false
	args2(5).Name = "SearchItem.Pattern"
	args2(5).Value = false
	args2(6).Name = "SearchItem.Content"
	args2(6).Value = false
	args2(7).Name = "SearchItem.AsianOptions"
	args2(7).Value = false
	args2(8).Name = "SearchItem.AlgorithmType"
	args2(8).Value = 1
	args2(9).Name = "SearchItem.SearchFlags"
	args2(9).Value = 65536
	args2(10).Name = "SearchItem.SearchString"
	args2(10).Value = " {2,}"
	args2(11).Name = "SearchItem.ReplaceString"
	args2(11).Value = chr(10)
	args2(12).Name = "SearchItem.Locale"
	args2(12).Value = 255
	args2(13).Name = "SearchItem.ChangedChars"
	args2(13).Value = 2
	args2(14).Name = "SearchItem.DeletedChars"
	args2(14).Value = 2
	args2(15).Name = "SearchItem.InsertedChars"
	args2(15).Value = 2
	args2(16).Name = "SearchItem.TransliterateFlags"
	args2(16).Value = 1280
	args2(17).Name = "SearchItem.Command"
	args2(17).Value = 3

	dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args2())

	end sub
Please note, that the replacement text is the same chr(10) as in the first macro. But in A1 we will see: "aa bbcc dd". When we save it to ODS, we have this xml in the file:
          <table:table-cell office:value-type="string" calcext:value-type="string">
            <text:p>aa bb
              cc dd</text:p>
So, we don't have two <text:p> elements, but the <text:line-break/> between the two lines. Unfortunately when LibreOffice reads this back, the line break disappears forever. Interestingly saving to XLS and reload from that XLS preserves the line break.

Expected behaviour: if chr(10) means line break in the cell, then it should work in case of search and replace, too.
Comment 1 raal 2015-03-10 14:43:22 UTC
Hello Andras,
is it duplicate of bug 75702?
Comment 2 raal 2015-03-10 14:45:05 UTC
Created attachment 114018 [details]
test case
Comment 3 Andras Timar 2015-03-10 14:46:41 UTC
(In reply to raal from comment #1)
> Hello Andras,
> is it duplicate of bug 75702?

It is not duplicate, but related.
Comment 4 raal 2015-03-10 15:23:36 UTC
(In reply to Andras Timar from comment #3)
> (In reply to raal from comment #1)
> > Hello Andras,
> > is it duplicate of bug 75702?
> It is not duplicate, but related.

The root of the problem is in reading of <text:line-break/>, isn't it?
When I run your second macro, I see line break, but after save and reopen is line break missing.
Comment 5 Andras Timar 2015-03-10 15:31:59 UTC
(In reply to raal from comment #4)

I don't see the line break when I run the second macro. Moreover, I expect that first and second macro insert the same type of line break. It is incorrect that chr(10) is a paragraph break one time, and soft line break (which is not imported) the other time.
Comment 6 raal 2015-03-10 16:21:53 UTC
chr(10) should be the same probably, setting to new.
Comment 7 tommy27 2016-04-16 07:24:32 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2017-05-22 13:25:35 UTC Comment hidden (obsolete)
Comment 9 Buovjaga 2017-12-19 14:39:49 UTC
*** Bug 114398 has been marked as a duplicate of this bug. ***
Comment 10 Hernan 2017-12-19 14:48:09 UTC
As per bug #114398 (closed as dup of this one), this issue is present, and can manifest not only in macros but also (and IMO more dangeoursly) in copy-paste to cell when text in the clipboard is available in Rich Text format. See that bug for steps to reproduce it in Calc
Comment 11 himajin100000 2019-04-21 23:25:30 UTC


either of


Comment 12 QA Administrators 2021-04-21 03:52:15 UTC Comment hidden (obsolete)
Comment 13 Hernan 2021-04-21 11:32:56 UTC
Bug detailed in my comment #10 (2017-12-19 14:48:09 UTC) still present.

Steps for reproduce in linked issue https://bugs.documentfoundation.org/show_bug.cgi?id=114398

Version: (x64) / LibreOffice Community
Build ID: 8a45595d069ef5570103caea1b71cc9d82b2aae4
CPU threads: 6; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: es-AR (es_AR); UI: en-US
Calc: CL
Comment 14 Andreas Heinisch 2021-12-30 11:12:17 UTC
If I execute the macro:

    Sub Main

    my_cell = ThisComponent.Sheets(0).getCellByPosition(0,0)
    my_cell.String = "aa bb" + chr(10) + "cc dd"

    End Sub

Then I end up in [1] where simpy the AdjustRowHeight is missing. Imho, bug 114398 is not a duplicate of this one, since it ends in [2] where no string or edit cell is set.

[1] https://opengrok.libreoffice.org/xref/core/sc/source/ui/docshell/docfunc.cxx?r=a23a7eea&mo=40925&fi=1270#1270
[2] https://opengrok.libreoffice.org/xref/core/sc/source/ui/view/viewfunc.cxx?r=ec1c4c49&mo=19813&fi=566#566
Comment 15 Eike Rathke 2022-12-07 17:00:36 UTC
For not creating an EditCell when reading an embedded <text:line-break/> this is a duplicate of bug 75702 that was fixed. For not creating (I'm *assuming* this is the case) an EditCell with multiple paragraphs for such replacement result with an embedded linefeed this is similar to bug 120190 but not a duplicate as another code path is involved. Keeping for that.