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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

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

1	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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141

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