Complete NEWB and openpyxl project

[Netopia]

buran,

I hope it's ok that I keep posting my progress. I guess at this point I feel like a little kid with his mentor (even though I'm 56 year old!).

Taking it apart piece by piece worked wonders! Below I have working code that separates the groups and subgroups into workbooks/worksheets... though you really did that part for me... and I understand it a LOT better now too. I also have the font changed in all sheets, and the width of the columns changed to acceptable widths based on the reported width times a factor, dependent on the value of the reported width. Everything looks very nice!

Next I'm going to see if I can figure out how to change the name of the default "Sheet" that comes with every created workbook to "Counts", and then create a table that shows the counts for each Group/Subgroup combination.

For your review is the current working code.

Blagodarya vi, Buran, blagodarya vi, blagodarya vi, blagodarya vi !!

Joe

import os, glob, shutil, openpyxl
from pathlib import Path
from collections import defaultdict
from openpyxl.reader.excel import load_workbook
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
from datetime import datetime

startTime = datetime.now()

os.chdir("c://test")
 
mainfile = 'Example.xlsx'
wb = load_workbook(mainfile)
ws = wb.active

header, *data = ws.values

groups = defaultdict(lambda: defaultdict(list))

for row in data:
    subgroup, group = row[-1][:-1] , row[-1][-1]
    groups[group][subgroup].append(row)
    

for group, subgroups in sorted(groups.items()):
    wb = Workbook()
    wb.remove(wb.active)
    dest_filename = (f'{mainfile[:-5]} Group {group}.xlsx')

    for subgroup, rows in sorted(subgroups.items()):
        ws = wb.create_sheet(title = subgroup)
        ws.append(header)            
        for row in rows:
            ws.append(row)

        dims = {}
        for row in ws.rows:
            for cell in row:
                cell.font = Font(name = 'Ariel Narrow', sz = 10)
                if cell.value:
                    dims[cell.column] = max((dims.get(cell.column, 0), len(str(cell.value))))
                for col, value in dims.items():
                    if int(value) < 10:
                        ws.column_dimensions[col].width = int(value * 1.8)
                    else:
                        ws.column_dimensions[col].width = int(value * 1.2)

    wb.save(filename = (f'{mainfile[:-5]} Group {group}.xlsx'))