Complete NEWB and openpyxl project

[Netopia]

So, as I realized that I really didn't understand what collections, group, groups, subgroup, subgroups... etc were doing. I took your advice earlier about printing things. IT HELPED A LOT! :)

Output:
>>> print('\n\n\n\n group--     \n      ',group, '\n\n','groups--    \n      ',groups, '\n\n','row--       \n      ',row, '\n\n','rows--      \n      ',rows, '\n\n','subgroup--  \n      ',subgroup, '\n\n','subgroups-- \n      ',subgroups)




 group--     
       r 

 groups--    
       defaultdict(<function <lambda> at 0x000002586FC0CB70>, {'r': defaultdict(<class 'list'>, {'Reorde': [('Cust#', 'First Name', 'Last Name', 'Full Name', 'Reorder')]})}) 

 row--       
       ('Cust#', 'First Name', 'Last Name', 'Full Name', 'Reorder') 

 rows--      
       [('Cust#', 'First Name', 'Last Name', 'Full Name', 'Reorder')] 

 subgroup--  
       Reorde 

 subgroups-- 
       defaultdict(<class 'list'>, {'Reorde': [('Cust#', 'First Name', 'Last Name', 'Full Name', 'Reorder')]})
>>> 

[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')) 

[buran]

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"

But you already did it on your own - om line 44 you create new sheet and set its title.
or if you want to change the name of existing tab just change ws.title property

ws.title = "New Title"

[Netopia]

I ended up just deleting the 'Sheet' and creating a new one. Perhaps I'll go back and just change the title. Knowing lots of ways to do something is always good! :)

[Netopia]

WOOHOO! It might not be elegant, but I got everything working! Next I'm going to delve into the win32api and win32com.client libraries and see if I can make the Excel workbooks open for review, and have them loaded into an email as attachments.

import os, glob, shutil, openpyxl
from pathlib import Path
from collections import defaultdict
from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment, Border, PatternFill, Side
import win32api     #For manipulating Windows Programs
import win32com.client as win32
from datetime import datetime

startTime = datetime.now()

os.chdir("c://test")
 
mainfile = 'Example.xlsx'
wb = openpyxl.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)
    
print(f'Done importing libraries and data in ' + str((datetime.now() - startTime).total_seconds()) + ' seconds.')    

for group, subgroups in sorted(groups.items()):
    
    #Set Formating values
    thick_left = Border(left = Side(border_style = 'thick', color = '000000'),
                             top = Side(border_style = 'thick', color = '000000'),
                             bottom = Side(border_style = 'thick', color = '000000'))


    thick_right = Border(right = Side(border_style = 'thick', color = '000000'),
                             top = Side(border_style = 'thick', color = '000000'),
                             bottom = Side(border_style = 'thick', color = '000000'))

    medium_border = Border(left = Side(border_style = 'medium', color = '000000'),
                             right = Side(border_style = 'medium', color = '000000'),
                             top = Side(border_style = 'medium', color = '000000'),
                             bottom = Side(border_style = 'medium', color = '000000'))

    thin_border = Border(left = Side(border_style = 'thin', color = '000000'),
                             right = Side(border_style = 'thin', color = '000000'),
                             top = Side(border_style = 'thin', color = '000000'),
                             bottom = Side(border_style = 'thin', color = '000000'))

    group_font = Font(name = 'Ariel', size = 14, color = '676DC6', bold = 'yes')

    data_font = Font(name = 'Ariel', size = 11)

    data_align = Alignment(horizontal = 'center', vertical = 'center')

    # Flow for each workbook
    wb = Workbook()
    wb.remove(wb.active)
    wb.create_sheet('Effort Counts')
    ws = wb.active
    dest_filename = (f'{mainfile[:-5]} Group {group}.xlsx')
   
    # Set generic fomatting values for Effort Counts Sheet
    
        #Main Text
    ws.column_dimensions["B"].width = 17
    ws.column_dimensions["C"].width = 17
    ws.row_dimensions[3].height = 30
    ws['B3'].font = Font(name = 'Ariel', size = 16, color = 'F4427D', bold = 'yes')
    ws['B3'].alignment = data_align
    ws['B3'].fill = PatternFill(start_color = 'EAFF82', end_color = 'EAFF82', fill_type = 'solid')
    ws['B3'] = (f'GROUP {group} COUNTS')
    ws.merge_cells('B3:C3')
    ws['B3'].border = thick_left
    ws['C3'].border = thick_right
    
        #Group Text
    ws.row_dimensions[5].height = 25
    ws['B5'].alignment = data_align
    ws['B5'].font = group_font
    ws['B5'].border = medium_border
    ws['B5'] = 'Group'
    ws['C5'].alignment = data_align
    ws['C5'].font = group_font
    ws['C5'].border = medium_border
    ws['C5'] = 'Records'
    

    count_cell = 6
    # Create worksheets in workbook and add header
    for subgroup, rows in sorted(subgroups.items()):
        loopTime = datetime.now()
        ws = wb.create_sheet(title = subgroup)
        ws.append(header)
        # Append data (row by row) to individual worksheet
        for row in rows:            
            ws.append(row)
        count = ws.max_row -1

        # Append groups and counts to "Effort Counts" sheet
        wscount = wb['Effort Counts']
        wscount.row_dimensions[count_cell].height = 20
        wscount['B' + str(count_cell)].alignment = data_align
        wscount['B' + str(count_cell)].border = thin_border
        wscount['B' + str(count_cell)].font = data_font
        wscount['B' + str(count_cell)] = subgroup
        wscount['C' + str(count_cell)].alignment = data_align
        wscount['C' + str(count_cell)].border = thin_border
        wscount['C' + str(count_cell)].font = data_font
        wscount['C' + str(count_cell)] = count
        count_cell = count_cell + 1
        
      
        # Set various column widths to sheet based data longest data in column
        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)
                    elif int(value) < 25:
                        ws.column_dimensions[col].width = int(value * 1.4)
                    else:
                        ws.column_dimensions[col].width = int(value)

            
    
    
    wb.save(filename = (f'{mainfile[:-5]} Group {group}.xlsx')) 
    print(f'Done with Group {group} in ' + str((datetime.now() - loopTime).total_seconds()) + ' seconds.')


print(f'Total time to completion ' + str((datetime.now() - startTime).total_seconds()) + ' seconds.')