Python Forum
Complete NEWB and openpyxl project
Thread Rating:
  • 6 Vote(s) - 3.5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Complete NEWB and openpyxl project
#41
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')]}) >>>
Reply
#42
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')) 
Reply
#43
(Jan-18-2019, 03:31 AM)Netopia Wrote: 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"
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#44
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! :)
Reply
#45
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.')
    
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  NameError issue with daughter's newb code MrGonk 2 1,406 Sep-16-2021, 01:29 PM
Last Post: BashBedlam
  Simple newb string question Involute 2 2,168 Sep-08-2019, 12:50 AM
Last Post: Involute
  please help this newb install pygame iofhua 7 5,844 May-15-2019, 01:09 PM
Last Post: buran
  Newb question: Debugging + Linting Python in Visual Studio Code Drone4four 1 2,387 Apr-15-2019, 06:19 AM
Last Post: perfringo
  Newb question about %02d %04d bennylava 30 19,140 Mar-05-2019, 11:23 PM
Last Post: snippsat
  Pthyon 3 question (newb) bennylava 11 5,741 Feb-28-2019, 06:04 PM
Last Post: buran
  newb selfie PatM 5 3,543 Feb-19-2019, 12:20 AM
Last Post: snippsat
  Newb Question - Threading in Crons vvarrior 2 2,718 Jul-20-2018, 08:12 PM
Last Post: vvarrior
  Matt's newb question 1 MattSS102 1 2,669 Aug-28-2017, 03:27 AM
Last Post: BerlingSwe
  Newb: Simple Explicit Formula Duplicitous 1 3,107 May-05-2017, 07:03 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020