Bug 153527 - LibreOffice 7.5 Calc: unable to apply formatting to all cells in spreadsheet
Summary: LibreOffice 7.5 Calc: unable to apply formatting to all cells in spreadsheet
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
7.5.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
: 153351 155180 (view as bug list)
Depends on:
Blocks: orcus_bugs
  Show dependency treegraph
 
Reported: 2023-02-10 15:16 UTC by defector
Modified: 2023-05-22 06:11 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description defector 2023-02-10 15:16:52 UTC
Description:
You open a new table and enter some data (numbers and text). Then you want to format all the cells. Select all cells and press Ctrl + 1 and format the cells: e.g.: Left alignment of text with 10 pt indentation. Everything works as intended up to this point: the data entered up to this point (numbers and text) are aligned accordingly. However, if you enter additional data in other cells, these entries are not positioned with the text aligned to the left with a 10 pt indent. This data is positioned using the default settings.

On the other hand, if you format an empty table with these settings, the data entered afterwards will be positioned as specified.


Steps to Reproduce:
1. open a new table and enter some data
2. select all cells and press Ctrl + 1 and format the cells: e.g.: Left alignment of text with 10 pt indentation
3. enter additional data in other cells, these entries are not positioned with the text aligned to the left with a 10 pt indent

Actual Results:
the additional entries after formatting all cells are not positioned with the text aligned to the left with a 10 pt indent

Expected Results:
the additional entries after formatting all cells should be positioned with the text aligned to the left with a 10 pt indent


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 7.5.0.3 (X86_64) / LibreOffice Community
Build ID: c21113d003cd3efa8c53188764377a8272d9d6de
CPU threads: 4; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: de-DE
Calc: threaded
Comment 1 m.a.riosv 2023-02-10 21:50:43 UTC
I think the behavior was implemented not too much ago, to avoid so large direct format by mistake, so it is applied up to the last cell with data on the sheet.
If you really want it, go to the last cell (right+bottom) on the sheet or where you want the styles, introduce something and delete it, now you can select all, an apply a style or the direct format.
Use styles as much as possible.

Or maybe the option:
Menu/Tools/Options/LIbreOffice Calc/General - Input settings - Expand formatting.
Is what you are looking for.

"
Expand formatting

Specifies whether to automatically apply the formatting attributes of the selected cell to the empty adjacent cells. If, for example, the contents of the selected cell have the bold attribute, this bold attribute will also apply to adjacent cells. Cells that already have a special format will not be modified by this function. You can see the range in question by pressing the Ctrl + * (multiplication sign on the number pad) shortcut. This format also applies to all new values inserted within this range. The normal default settings apply to cells outside this range.
"
Comment 2 V Stuart Foote 2023-02-10 22:32:53 UTC
Actually believe this is correct behavior. With your Select All, only the cells with values are actually selected and directly formatted by the change. 

The empty cells outside the filled selection remain unformatted and with defaults.

Alternatively, if you want to affect the entire sheet all at once you would modify the 'Cell' style--likely just editing the 'Default' style.

So IMHO => NAB

@Eike?
Comment 3 defector 2023-02-11 10:49:15 UTC
Hello,

thank you for your message. Please excuse me, but the behavior described can't possibly be intentional? You deliberately mark ALL CELLS and adjust their format, and in your opinion it makes sense NOT to format ALL CELLS accordingly? I can't believe you're serious. It contradicts every logical procedure. I intentionally mark each cell - not just each cell with content...

Best regards

d.
Comment 4 defector 2023-02-11 11:49:26 UTC
Hello,

unfortunately, adjusting the settings (Menu/Tools/Options/LIbreOffice Calc/General - Input settings - Expand formatting) does not lead to the desired formatting of all cells either.

Best regards.

i.e.
Comment 5 Eike Rathke 2023-02-13 19:04:53 UTC
This looks like a fallout from the 16k columns work and cleanup and attributes after Ctrl+A are only applied to actually allocated columns (maybe even because people hit Ctrl+A followed by some action unintentionally too often). In this case even completely unused columns would have to be allocated to be able to attribute all 16k columns. Whether that is actually desirable is another question.. but is expected in _this_ case here.
Fwiw, it still worked in 7.4 and hence is a regression.
Comment 6 ady 2023-02-14 11:10:04 UTC
FWIW and only as a side note, see bug 131916 comment 14 (and/or the whole ticket) regarding the use of CTRL+* (in numeric pad) and CTRL+A in Calc (and in Excel).
Comment 7 Stéphane Guillou (stragu) 2023-02-17 22:52:45 UTC
*** Bug 153351 has been marked as a duplicate of this bug. ***
Comment 8 Stéphane Guillou (stragu) 2023-02-17 23:07:45 UTC
bibisected with linux-64-7.5 repo to first bad commit 9bbe835ae76014760a6abfe10b7e50874a5ea2fa which points to core commit:

commit ac859a4c6a7fce4efee9cdd45821a0c9e40e9e9a
author	Noel Grandin <noel.grandin@collabora.co.uk>	Mon Oct 17 16:36:23 2022 +0200
committer	Noel Grandin <noel.grandin@collabora.co.uk>	Tue Oct 18 09:35:01 2022 +0200
tree 6e7d452d038e92eb122ab3f3d94304bf43fde71e
parent aa70820a79152830a32070eb722311b8531945a4
tdf#147842 shrink selection to data area when applying to entire sheet
This takes the time to apply the formating from "who knows how long" to about 500ms on my machine.
Change-Id: I202d023c58ea191bf080ef3a85068e8acab52dec
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/141463

So the change was definitely on purpose, and in response to a performance issue reported in bug 147842.

Bringing UX into the discussion too, regarding the desirability of applying some formatting to all cells preemptively.
Comment 9 ady 2023-02-18 04:51:12 UTC
(In reply to ady from comment #6)
> FWIW and only as a side note, see bug 131916 comment 14 (and/or the whole
> ticket) regarding the use of CTRL+* (in numeric pad) and CTRL+A in Calc (and
> in Excel).

@UX team,

When users apply an attribute to a selected range of cells, they are usually expecting for the attribute to be applied to all the range of the selected cells. If that's _all_ cells, then that should be it.

If devs are afraid of users misusing any selection method, the way it should be considered should be to affect the resulting selection, not the attribute not being applied to part of the selected cells.

For example, as mentioned in bug 131916 comment 14, if users use [CTRL]+[A], the initial selection should cover the immediate active range of cells. The second immediate consecutive [CTRL]+[A] should select the entire active range of cells. Only the third [CTRL]+[A] would select the entire worksheet. This of course should depend on whether a surrounding active range exists, and/or an entire active range of cells exists; when either of them is not relevant, the next "step" of the selection takes over.

So, for instance, if the worksheet is entirely blank, the first [CTRL]+[A] immediately selects the entire worksheet.

Whichever the case, when a selection of cells is performed, the attributes or actions that follow the selection should apply to the entire selected range.

IOW, please don't overprotect users from their own actions. Preventing users from performing expected actions and receiving expected results should not be overpowered by misused or "by-mistake" selections/actions. Help users, but please don't "block" users from performing the intended action. Allow "steps" for [CTRL]+[A] (for example) is a much better approach than silently affecting a different range of cells than what was selected.
Comment 10 V Stuart Foote 2023-02-18 09:40:25 UTC
Pretty clearly the move to 16K columns necessitated this shift.

Past practice of applying per cell attribute to a <Ctrl>+A selection of the entire sheet instantly became non-performant with sheets of a billion cells.

For acceptable performance the *reasonable* solution of ShrinkToDataArea() requires minor adjustment to work flows, acceptable UX.

As noted, styling of sheet cells is the only way to efficiently apply formatting to entire sheets--not selecting all cells on a sheet and applying DF.

This is not a bug, nor a regression, as implementing 16K columns necessitates retraining to keep sheet manipulations efficient.

As to some edit engine cyclic application of the <Ctrl>+A selection--seems overly complex, and you'd never be sure what you'd selected (you can't see the last cell selected).

No, seems best to just accept that the <Ctrl>+A selection will encompass only the sheets active cells as implemented for bug 147842 keeping the UX for Calc performant.
Comment 11 ady 2023-02-18 21:37:35 UTC
(In reply to V Stuart Foote from comment #10)
> As to some edit engine cyclic application of the <Ctrl>+A selection--seems
> overly complex, and you'd never be sure what you'd selected (you can't see
> the last cell selected).

FWIW, having "steps" for <Ctrl>+A is what Excel already does (see bug 131916 comment 14 regarding [CTRL]+[A]). Such behavior does not contradict whichever other decision about which action is applied to which ranges. In fact, by imitating Excel in this regard the "mistake" of users applying some attribute to the entire worksheet is reduced, while letting users _know_ that they are applying it just to the selected area. IOW, it brings mostly pros, especially in comparison to the current behavior.

Regarding not seeing the last cell, I don't understand why that is relevant in any case. At any rate, users get "surprised" by the result. In some cases, it is a welcome result, whereas in others, it might be an eventual "why is this cell not formatted as I expected?".

I understand the performance issue. I happen to disagree that _forcing_ unexpected results on users is the best that can be done. When users complain about unexpected results, they receive some explanation, or at least a hint, and NAB. I think that allowing "steps" in <Ctrl>+A behavior would reduce the amount of "surprises". If then a user complains about performance when selecting the entire worksheet (which would replace the other complains), the response would also be about a UX decision, with a relevant comment about workarounds ("modify the default cell style").

I am not a developer. My comments come from a user's POV only. It seems strange to _silently force_ users (against the behavior they are used to and expect, from prior experience), when there might be alternatives, especially when the product is named _libre_ :p).

IDK whether having an option to "allow" applying attributes to an entire worksheet is an adequate solution. I do know that having "steps" for <Ctrl>+A should be welcome (because, in comparison to the current status, it brings more freedom and efficiency). Whichever the case, until users are _somehow_ "allowed" to see their actions applied to the selected range of cells (with whichever pros and cons, as with anything), these types of bug reports will keep showing up, simply because the behavior is unexpected by users, independently of whichever logical and justified reasoning developers could present.

Let me put it this way. Current answer: "You cannot do that, even when you expected that result". Alternative answer: "You pressed <Ctrl>+A three times in order to select the entire worksheet and the reaction from Calc is not absolutely immediate? Well, you have the choice to select only the relevant areas of your worksheet, and work faster; or you could change the default cell style; but if you anyway decide to select the entire worksheet with 10^6 columns (or whatever), please be patient".
Comment 12 Heiko Tietze 2023-02-20 12:52:07 UTC
Let's focus on 'apply formatting to all selected cells', and I agree that empty but selected cells should become formatted as well. If this has been done intentionally we better allow to interrupt lengthy operations than blocking / hindering it (see bug 150239).
Comment 13 Eike Rathke 2023-03-13 17:28:25 UTC
The proper solution for the Ctrl+A All special case could probably be that the default attribution item set that is also used when new columns are actually added to contain data needs to be populated as well, not only for allocated columns. Bear in mind though that changing attribution of just one single cell somewhere then will have to allocate all columns up to that cell's column as there can be no gaps.
Comment 14 Heiko Tietze 2023-03-14 07:50:17 UTC
(In reply to Eike Rathke from comment #13)
> ... default attribution item set that is also used when new columns are
> actually added to contain data needs to be populated as well...

I struggle to follow. The default attributes (Default Cell Style) are not changed, the request is to apply direct formatting to empty cells.

Tested now myself and I can apply the background color or the text alignment to empty cells. Please check again.

If there is an issue we should treat it as bug.
Comment 15 Eike Rathke 2023-03-14 15:28:43 UTC
I wasn't talking of the Default cell style. IIRC there's some kind of "default column" for all columns that are not yet allocated. For that default column the attribution would need to change for Ctrl+A.
Comment 16 m.a.riosv 2023-05-08 16:53:21 UTC
*** Bug 155180 has been marked as a duplicate of this bug. ***
Comment 17 defector 2023-05-14 15:01:34 UTC
In the meantime I have updated LibreOffice for Windows to version 7.5.3.2. Unfortunately, this did not change the behavior described.
Comment 18 m.a.riosv 2023-05-18 22:33:33 UTC
*** Bug 155395 has been marked as a duplicate of this bug. ***
Comment 19 TBeholder 2023-05-19 00:01:28 UTC
(In reply to defector from comment #0)
> Then you want
> to format all the cells. Select all cells and press Ctrl + 1 and format the
> cells: 
Obviously it’s a bug, but… I know some people try to paint on spreadsheets manually like this, but you know there are editable styles, right?

(In reply to ady from comment #11)
> other complains), the response would also be about a UX decision, with a
> relevant comment about workarounds ("modify the default cell style").
It’s not a workaround, it’s the primary tool for this purpose. I mean, is it not obvious that ability to apply the same formatting to many elements is the main reason for having styles to begin with?
Comment 20 robert 2023-05-19 10:51:56 UTC
The problem described in https://bugs.documentfoundation.org/show_bug.cgi?id=155395 is completely different, in the case there the formatting of a single cell is completely screwed up, the first 6 characters remain what they were, the last ones are changed to the new font. 

And as for "those changes are required because we now have sheets with a billion cells?" and the general "we know better" attitude from the developers? Who asked for 16K rows, the 0.01% of users?
Comment 21 Heiko Tietze 2023-05-22 06:11:55 UTC
(In reply to robert from comment #20)
> Who asked for 16K rows, the 0.01% of users?
Large sheets is a valid requirement, see https://design.blog.documentfoundation.org/2021/10/18/results-from-the-survey-about-libreoffice-calc/. 

(In reply to robert from comment #20)
> The problem described in bug 155395 is completely different...
Yes, it's about direct formatting for parts of a cell while we discuss here the scope of ctrl+A (whether it affect cells with content or any cell).