[Solved] Remove empty/blank strings at the end of cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
sergeevms
Posts: 5
Joined: Wed Aug 03, 2022 10:12 am

[Solved] Remove empty/blank strings at the end of cell

Post by sergeevms »

Hello.

I stack with the problem.
empty_rows.jpg
empty_rows.jpg (12.31 KiB) Viewed 3380 times
How to remove empty lines (paragraphs?) at the end of cell and save empty line inside the text and save text formatting also?
I can iterate over the TextRange-s (including empty), but don't understand how to remove "empty" TextRange.
Last edited by Hagar Delest on Mon Aug 22, 2022 1:28 pm, edited 1 time in total.
Reason: tagged solved.
LibreOffice 7.2 on Windows 8
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by Zizi64 »

Try to avoid using different formatting properties inside a single cell. The spreadsheet IS NOT a text editor. Use the Cell Styles instead.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
sergeevms
Posts: 5
Joined: Wed Aug 03, 2022 10:12 am

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by sergeevms »

It's not possible. I'm not the author of documents/templates. I'm just try to avoid some problems with converting from xls to pdf by preprocessing document.
LibreOffice 7.2 on Windows 8
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by Zizi64 »

I'm just try to avoid some problems with converting from xls to pdf by preprocessing document.
Then use Excel.

Use the native, international standard ODF file format (.ods for spreadsheets), an the most valuable feature of the AOO and LO: the Styles - if you want work with the AOO/LO efficiently.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
sergeevms
Posts: 5
Joined: Wed Aug 03, 2022 10:12 am

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by sergeevms »

Zizi64 wrote: Wed Aug 03, 2022 3:52 pm Then use Excel.
It's not possible for some reasons.

Your position is absolutely clear for me and commonly I agreed with thesis about "use spreadsheets for calculation only". But am I asking for something impossible? Can I get cell's formatting the same way with AOO and LO? Yes, I can. It's not specific only for ms excel. Or it's some limitations in uno api?

Thanks.
LibreOffice 7.2 on Windows 8
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by Zizi64 »

But am I asking for something impossible?
Absolutely not, but you you're making it more difficult. Determining/modifying the direct (manual) format properties of each characters in each cells will be slow by usage the StarBasic+API macros.

Can you upload an ODF type sample file here (instead of the image)?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by Sébastien C »

Zizi64 wrote: Wed Aug 03, 2022 6:09 pmAbsolutely not,
I'm not sure I understand the sergeevms question but if I understand it correctly, then I disagree. There are still write cursors that allow you to work on the text IN the cell, just like in Writer.

Code: Select all

Sub Main
 Dim myCell As Object, myCursor As Object
 Dim myBreak As String

   myCell = thisComponent.Sheets.getByName("Feuille1").getCellRangeByName("A1")
 myCursor = myCell.createTextCursor

  myBreak = chr(10)               ' Unix       :-)
  myBreak = chr(13) & chr(10)     ' M$-Window$ :-(

 myCursor.gotoEnd(false)
 myCursor.goLeft(1, true)

 While myCursor.string = myBreak
  myCursor.string = ""
  myCursor.gotoEnd(false)
  myCursor.goLeft(1, true)
 Wend
End Sub
Attachments
cursorInCell.ods
(14.28 KiB) Downloaded 71 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
sergeevms
Posts: 5
Joined: Wed Aug 03, 2022 10:12 am

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by sergeevms »

Sébastien C wrote: Wed Aug 03, 2022 6:59 pm here are still write cursors that allow you to work on the text IN the cell, just like in Writer.
Hi there!
Thank you, Sébastien! That's all I need.

Code: Select all

           
final XTextCursor textCursor = xText.createTextCursor();
textCursor.gotoEnd(false);
textCursor.goLeft((short) 1, true);
while (textCursor.getString().isBlank()) {
   textCursor.setString("");
   textCursor.goLeft((short) 1, true);
}
Can you tell me what mean the boolean parameter in the goToend and goLeft methods?
There is no information in the documentation except bExpand
LibreOffice 7.2 on Windows 8
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by Sébastien C »

I quote you the excellent book of Bernard Marcelly which helps us, French speakers, to access to the API, perhaps more easily than the equally excellent text by Andrew Pitonyak. But you have to know how my translation is undoubtedly very bad...
Bernard Marcelly wrote:
Move the write cursor
The cursor object has several methods for moving it. Some return a result:
  • True if the action could be performed;
  • False otherwise.
In practice, we rarely use the result of these functions and we use them as methods of type Sub.
The moving functions all have a boolean argument, which we will denote by SEL, which has the following effect:
  • SEL = False: the cursor moves (like the vertical cursor visible bar when you edit text in a cell).
  • SEL = True: the cursor moves by extending the selection (this is the same effect as a progressive selection of the cursor visible by dragging the mouse).
And here is a small example:

Code: Select all

Dim myDocument As Object
Dim theSheets As Object, mySheet As Object
Dim myCell As Object, myCursor As Object
myDocument = ThisComponent
theSheets = myDocument.Sheets
mySheet = theSheets.getByName("Writing")
myCell = mySheet.getCellRangeByName("C2")
myCursor = myCell.createTextCursor
' here the cursor is at the end of the text
myCursor.gotoStart(False)
' here the cursor is at the beginning of the text
Table 9-16 lists the cursor movement functions.

Code: Select all

Table 9-16 Moving the Write Cursor
 
 ╔════════════════╤════════════════════════════════════════════════════════════════════╗
 ║ METHOD         │ Effect on the Cursor                                               ║
 ╠════════════════╪════════════════════════════════════════════════════════════════════╣
 ║ goRight(n,SEL) │ Move n characters right. Returns True if the action was performed. ║
 ╟────────────────┼────────────────────────────────────────────────────────────────────╢
 ║ goLeft(n,SEL)  │ Move n characters left. Returns True if the action was performed.  ║
 ╟────────────────┼────────────────────────────────────────────────────────────────────╢
 ║ gotoStart(SEL) │ Move to beginning of cell text.                                    ║
 ╟────────────────┼────────────────────────────────────────────────────────────────────╢
 ║ gotoEnd(SEL)   │ Move to end of cell text.                                          ║
 ╚════════════════╧════════════════════════════════════════════════════════════════════╝
We reported that the write cursor can select an area. After performing an action on this area, there are two methods of the cursor to bring it back to an insertion point, located at the beginning or at the end of the area.

Code: Select all

myCursor.collapseToStart ' Start of the zone
myCursor.collapseToEnd   ' End of the zone
The cell cursor object also provides a Boolean function isCollapsed, which returns False if the cursor is expanded for a selection and True if it is pointwise.

Code: Select all

if myCursor.isCollapsed then
' here the cursor is pointwise
end if
I take advantage of this post to apologize to Zizi64. I read his writing too quickly and thought he was writing “Absolutely not”, meaning, “it is impossible”. However, the correct reading of his sentence shows that he knew perfectly well that it was possible. Otherwise, the title of this thread contained “with UNO api?”, which suggests to me that Friend sergeev knows the temporary price to pay for this kind of iteration... The code proposed by him still suggests that he makes the difference between API, Basic and Java...

Who knows why, I don't feel like I have to teach him so much more... :arrow: :knock: :lol:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
sergeevms
Posts: 5
Joined: Wed Aug 03, 2022 10:12 am

Re: How to remove empty/blank strings at the end of cell with UNO api?

Post by sergeevms »

Sébastien C wrote: Fri Aug 05, 2022 7:00 pm But you have to know how my translation is undoubtedly very bad...
I'm not a native speaker too and you help me so much.

Very appreciate you and Zizi64 for the help.
Sébastien C wrote: Fri Aug 05, 2022 7:00 pm The code proposed by him still suggests that he makes the difference between API, Basic and Java...
Sorry, maybe I have to be more clear :oops:
LibreOffice 7.2 on Windows 8
Post Reply