Open office: Difference between revisions

Revision as of 15:29, 3 November 2022

This page is dedicated to the free office suites, Open Office and its recent fork Libre Office.

Repositories

Libre Office PPA for Ubuntu:

sudo add-apt-repository ppa:libreoffice/ppa

Tips

Calc — Remove the delete confirmation box

By default, backspace is mapped to immediate deletion, and delete to open a dialog box with different delete options.

This can be changed in menu Tools → Customize..., panel Keyboard. (Make sure Calc radio button is selected), pick Category Edit:

1st Function Delete Contents is the one showing dialog box (default Delete)
2nd Function Delete Contents is the one that deletes without confirmation (default Backspace)

Calc — Move rows or columns

See [1]:

Click row/column head
Drag row/column by clicking in the selected area (NOT on header)
Hold ALT to insert where you want it to be

Note that ALT+drag may conflict in Gnome with shortcut to move Windows (see Preferences → Windows → Windows movement).

Calc — Disable spell-checking for cells in Calc

Select cells for which spell-check must be disabled
Select Format Cells... → Font tab → Select [None]] as language.

Calc — Find row in table matching multiple columns

Links

Say we have the spreadsheet:

     *      *      *      *      *
     A      B      C      D      E
1    foo    10     FOO
2    foo    20     FOO    crit1  20
3    bar    10     BAR    crit2  FOO
4    bar    20     BAR    value  ***
5    bar    20     BAR

We want to get the row (or here the value of corresponding column A) in the table whose column B and C match some given values. The formula is:

=INDEX(A:A,MATCH(1,(B:B=E2)*(C:C=E3),0))

We use MATCH with a criteria value 1, and a boolean formula that is forced to be converted to an integer value because of *.
Note: on Google sheet, if we only have one criteria, we need to multiply by 1 to force integer conversion: =INDEX(A:A,MATCH(1,(B:B=E2)*1,0))

Alternative formulas (but may return several results):

=FILTER(A:A,(B:B=E2),(C:C=E3))

One can also use UNIQUE() to only keep non duplicate entries (but still potentially many of them).

Also, note that if we want to have only the index of the match, we have to enable the evaluation for array formulas (See SO):

=ARRAYFORMULA(MATCH(1,(A:A="bar")*(B:B=20),0))

Note that array formula evaluation is only necessary Google Sheets.

Import pictures in batch

use add-ons Photo Album
To unlink photos, i.e. such that photos are embedded in the file, click on a picture, then go to Edit → Links, select all links, then click break links [2].

Install language pack

In Ubuntu, installing language must be done via the Language Support in System Settings menu.

Doing so install the necessary packages.

Writer - Image resize macro

Open macro editor:

rem Inspired from https://ask.libreoffice.org/t/resize-position-image-macro-calc-macro-help/19420
Sub ResizeGraphic
Dim oCurrSel As Object
Dim oItem As Object
Dim aSize As New com.sun.star.awt.Size
Dim aPosition As New com.sun.star.awt.Point
oCurrSel = ThisComponent.getCurrentSelection()
If oCurrSel.ImplementationName = "SwXTextGraphicObject" Then
    rem 	oCurrSel.Width=oCurrSel.ActualSize.Width * 0.7
    oCurrSel.Width=2000
	rem oCurrSel.Height=oCurrSel.ActualSize.Height * 0.7
	oCurrSel.Height=oCurrSel.ActualSize.Height * oCurrSel.Width / oCurrSel.ActualSize.Width
Else
	MsgBox "Image not selected " + oCurrSel.ImplementationName
End If
end sub

Rant

Calc

Spreadsheet with multiple sheets. Want to delete some of the first sheets: click on the first sheet, then with ctrl select others sheets. Right click on the last sheet: now OO moves the sheet selector, to reveal next sheet tab, select this tab, and open context menu. Selecting delete would then delete this sheet instead of the previously selected ones! Thank you, Mr Oo!
Various hangs here and there, and sky rocketing memory consumption. Oups, sorry Mr Oo, I just wanted to scroll the sheet a bit on the right.
Select a cell, change font color. Select another cell, reapply same font col... ah sh*t, I can't simply click on the button to reapply the same setting. So click back on previous cell, what color did I select? Ah sh*t, the selected color is not even shown!!!
Merge cell. Select cells, right click... ah no, no option. Go to menu... which menu? ok, Format, merge cells... yet other options, I just want to merge, you know. So click on "merge cells", then bang! Hurray, a pop-up window!!! It so convenient! All these pop-up everywhere it's not nice: you want to delete? pop-up! you want to merge? pop-up! Want to go to the toilet? Poop-up!!! Of course the pop up does not remember your past answer, but who cares? God d*mn it, luckily I just can repeat (Ctrl-Shift-...Alt-Meta-Esc-Y, it's Ctrl-Y everywhere, but who want to repeat, don't you dare being too much productive)... NO! Merge is not an operation that you can repeat! But what is the design underneath that makes it so special that Merge cannot be repeated ?!?!?
But you have to learn the mystic ways. Oo is maybe opensource, but it's not free as in freedom. You have to follow the only ways that the application wants you to follow. Like, you want to change the border of a cell. Ok, Ctrl-1, border tab. I want a... thick border, so let's click that: BANG! All past borders on the cell are changed. Ah ja, of course, Oo thought that you would like to change these borders, so it preselected them in advance. Want to unselect them? Well, just read the manual, it's so intuitive!
FIX: seems that the pop up is only shown when there was content in the merged cells, or (and that's the bug I guess), when there are borders.
Oo is faaaaAAAAAST! Like, select several rows, copy them, aaaaannnnnnnd nnnnnnnooooooowwwww yyyyyyoooooooouuuuuu ccccaaannnnn sssssssiiiiiiiimmmmmmmpppppllllllyyyyy mmmmmmmmooooooovvvvveeeee tttthhhhhhheeeeee cccccccuuuuuuuurrrrrrssssssooooooorrrrr..... Argh! killall soffice.bin!

Troubleshoot

Regular freezes

Probably due to corrupted file in the config folder:

mv ~/.config/libreoffice ~/.config/libreoffice.bak

@@ Line 50: / Line 50: @@
 One can also use <code>UNIQUE()</code> to only keep non duplicate entries (but still potentially many of them).
+Also, note that if we want to have '''only the index''' of the match, we have to enable the evaluation for array formulas ([https://stackoverflow.com/questions/74305195/ See SO]):
+ =ARRAYFORMULA(MATCH(1,(A:A="bar")*(B:B=20),0))
+Note that array formula evaluation is only necessary Google Sheets.
 === Import pictures in batch ===