Hello. I have a line in my document. It starts from A11 and ends at M11. Each cell in this row has a different style (text size, text slant, cell border thickness). How can I copy the styles of this line a hundred lines down?
It’s important that it works quickly. Previously, I was already told how to do something similar with the contents of cells: How to quickly fill a sheet with data? - #7 by JohnSUN
Is it possible to do something similar with styles?
Simply fill the entire desired range with the template row and remove the content (values+date/time+texts) below the first row.
oSheet = ThisComponent.getSheets().getByIndex(0)
oSheet.getCellRangeByName("A11:M111").fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
oSheet.getCellRangeByName("A12:M111").clearContents(7)
Ah, do you already have some values in the formatted strings? Then first save them into a variable, for example, using getFormulaArray(), and return them after multiplying the formats.
1 Like
A general remark.
… …
If there is a recommendable way to do something quickly in a spreadsheet document can heavily depend on the design.
If the design isn’t well considered regarding the expectable tasks, there may come up problems of the kind presented here again and again, and you may need to ask for help or be tempted to resort to “macros” where they aren’t actually the means of choice.
A task you need to do “quickly” is surely an expectable one, and should therefore be well supported by the design.
More specific.
You use the term “style” here, but actually talk of attribute settings for cells. This may be due to a misunderstanding. There are cell styles in Calc, and they are useful. Do you actually use them? If so, why do you think to also need “quickly” expanding or recreating ranges with specific cell attributes? The recommended proceeding is to (wherever possible at all) define cell styles in advance, and to apply them to whole columns (e.g.) then. Why don’t you work this way?
1 Like
Elaborating a bit on @Lupp’s comments:
In addition to the obvious, applying a named style directly to a set of cells (row, colunm, range), there is also an indirect way: Conditional formatting.
One available approach for conditional formatting is “by formula”. A formula in this context can employ the STYLE() function, which adds another “level of indirection” to the styling. Note that the STYLE() function can also be used within a cell formula, which is the preferred and well documented way. By using the conditional format detour you can also use the function for data entry (non formula) cells.
Both levels of indirection require that you first have a named cell style (i.e. a predefined set of formatting properties) you want to apply.
It is also possible to apply some conditions directly in the format code, within the style definition. I consider this a “quick and dirty” approach, and do not recommend mixing it with the “proper” conditional formatting.