Formatting entire rows

[udif]

I'm trying to paint entire rows with a background color of my choice.

I would like to have the effect equivalent to what I get on Excel when I select a row by pressing Left mouse button on the row number itself, not a specific cell.

I've seen this thread:

https://groups.google.com/g/openpyxl-users/c/FkmbdDvC4SA/m/vixEkG_6cqUJ

which hints that setting a specific row via row_dimensions[] would not work.

So far I've tried setting the fill type on cells, and on rows, but neither gives me the desired effect.

How do I achieve this?

thanks,

Udi

[Charlie Clark]

You have to go cell by cell. Excel uses row and column dimensions as hints about what to do for future cells but formats themselves are always applied to individual cells.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Charlie Clark]

On 4 Jul 2022, at 20:19, udif wrote:

> The problem is that when I do it cell by cell, only those cells are
>
> highlighted.
>
> When you do it in Excel by selecting the rows themselves, the entire row is
>
> highlighted, regardless of how many columns you scroll.
>
> I created an empty Excel file with one highlighted row (background color)
>
> and I took a peek into the XML file of the sheet, and from what I see, the
>
> format is encoded into the row tag itself.

Just use the maximum column width on iter_rows(max_col=16384)

[udif]

Will that actually create create 16384 cells in each row?

[Charlie Clark]

On 5 Jul 2022, at 13:29, udif wrote:

> Will that actually create create 16384 cells in each row?

Yes, because that's the only reliable way. If you set the styles for the row, then Excel will format any "new" cells, ie. ones that it creates when enter data into them, accordingly, but you'll always have to format your own cells yourself. You can, of course, combine the approaches: format your cells and leave a note for Excel.

[Udi Finkelstein]

As I wrote, when I created an empty file in Excel 2019, marked one line in Yellow, and saved it, I got the following in xl\worksheets\sheet1.xml (inside the ZIP file):

(I added some spaces to highlight the required section)

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{F9D4723E-0BC4-4BD4-B227-07A42BBD6312}"><dimension ref="A1"/><sheetViews><sheetView tabSelected="1" workbookViewId="0"><selection sqref="A1:XFD1"/></sheetView></sheetViews><sheetFormatPr defaultRowHeight="15" x14ac:dyDescent="0.25"/>

<sheetData><row r="1" s="1" customFormat="1" x14ac:dyDescent="0.25"/></sheetData>

<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/></worksheet>

When I saved a totally empty file, I got an empty <sheetData tag> :

<sheetData/>

Reading both files under openpyxl 3.0.x shows that customFormat is a boolean attribute of the wb['Sheet1'].row_dimensions[1] object.

Reading further in ECMA-376, it seems that in order to have a custom format for a whole line, I need to set customFormat to "1", and have the style index attribute "s" set to the style index number.

So lets say I want to "cheat" and create the desired effect based on underfind openpyxl behavior, and ECMA-376t knowledge:

wb['Sheet1'].rom_dimension[index].customFormat = "1" # "1" is true

wb['Sheet1'].rom_dimension[index].s = "1" # "1" is style index

The only remaining question is how can I create a custom style with openpyxl and get its index.

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/FC7B5B7D-AE7F-47E5-807E-26AA9E2D61CC%40clark-consulting.eu.

[Udi Finkelstein]

OK, I solved my problem, and gained a better understanding of how formatting works:

When you set formatting on an entire line (ws.row_dimensions[] element) you create a style.

A style is a collection of several formatting attributes, such as folor, fill, borders etc.

If the specific combination has not been used yet on the spreadsheet, a new style will be added to the style table, and its index will be marked on the row.

When excel reads the file, the row style is applied by Excel only on unformatted or empty cells.

When you write any format to a cell, it no longer applies the row style.

This means that if you want your row formatting to apply to the whole line, you must reapply the specific formats you have applied to the row again, this time to any cell you touch.

As for Excel, what it does is probably this:

When you mark a whole line, and apply any format over it (let's say a new background color), it is marked using the "s" attribute on the row tag, which contains the index of the style created.

However, Excel also applies this manually to any existing cell on the same line, which may create additional styles.

Excel (and I think openpyxl as well) tracks unique styles, and if your new formatting creates a style that already exists, its index will be reused, and no new style will be added to the style table. 

For example, if one cell on the line already had borders, the new cell's style will have the original border plus the line formatting (background color in my case).

With openpyxl, we have to do it manually.

So to summarize, to get a background color on the entire row, I don't need to set the background on all 16384 cells,

I just need to set it on the row using the ws.row_dimensions[] array, and on any additional cell on that row that is not empty.

Empty, untouched cells on that line will inherit the background color from the row style.

[Charlie Clark]

On 5 Jul 2022, at 23:37, Udi Finkelstein wrote:

> So to summarize, to get a background color on the entire row, I don't need
>
> to set the background on all 16384 cells,
>
> I just need to set it on the row using the ws.row_dimensions[] array, and
>
> on any additional cell on that row that is not empty.

I think the point is somewhat moot and will depend upon the number of cells you want to format manually. Formatting 16384 cells won't take that long, especially if you use a named style and then you know exactly where you are.

> Empty, untouched cells on that line will inherit the background color from
> the row style.

It's not quite that simple. First, you have to define "empty" lots of cells have no value but that doesn't make them empty for Excel. It really is best not to rely on Excel doing the right thing, because that isn't always what you expect.

Charlie

PS FWIW I've spent more time than I care to admit on row and especially column dimensions. The working group has improved the definitions but we're now working through the ISO process on how we can actually publish an updated version that doesn't require the whole specification be managed in a single MS Word file.