openpyxl python3 -- formatting whole rows ellicits strange behavior

Question

I'm working with pivot-tabled XLSX files and writing a script to parse them out in to a new file per tab. Since openpyxl doesn't support pivot tables by default I need to do some work to reinsert the pivot 'style' that is lost during copy. To do this I'm iterating through each row and col, looking for the value `Total` in column 0. Once found, the row should change to be all `bold=True`. Instead, I receive erratic behavior that sometimes bolds ALL cells after the first `None`. My `print('bolding totals')` message shows that it is evaluating each row/cell correctly. Am I bone-headed and screwing up my indents on the loops, perhaps? ``` from openpyxl import Workbook from openpyxl import load_workbook from copy import deepcopy wb = load_workbook(filename=r'input.xlsx') # Print 1 sheetlist = wb.get_sheet_names() print(sheetlist) for i in range(len(sheetlist)-1): dest_filename = r''+sheetlist[i]+'.xlsx' new_wb = Workbook() ws = wb.get_sheet_by_name(sheetlist[i]) new_wb.add_sheet(ws, 0) for…

Accepted Answer

Prior to openpyxl 2.0 cell styles were shared between cells: this was a holdover from the implementation using pointers in the source XML: two (or more) cells would both use style "1". Changing this style for one cell would mean changing it for all cells, which sounds like the behaviour being observed here.

Since then, while there have been various changes in implementation, there are no longer any side-effects when changing the style for one cell. An important change is that formatting objects, such as `Font` are directly available and do not have to be wrapped in a style.

There have been some other changes: worksheets cannot be copied between workbooks because they are dependent upon data stored in the parent workbook.

Without the original file it's difficult to be sure but the following code should work with openpyxl >= 2.2

```
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook(filename='input.xlsx', read_only=True)

for sheet in wb.sheetnames:

dest_filename = '{0}.xlsx'.format(sheet)
    new_wb = Workbook()
    del new_wb["Sheet"]

ws1 = wb[sheet]
    ws2 = new_wb.create_sheet(sheet)

for row in ws1:
        ws2.append([c.value for c in row])
        first = row[0]
        if first.data_type == "s" and "Total" in first.value:
            for idx in range(len(row)):
                cell = ws2.cell(row=ws2.max_row, column=idx+1)
                bolded = cell.font.copy(bold=True)
                cell.font = bolded

new_wb.save(dest_filename)
    print("saving {0}".format(dest_filename))

print('finished')
```

openpyxl python3 -- formatting whole rows ellicits strange behavior

Solution

Problem