rosetta.py

import os
from openpyxl import load_workbook
import datetime
from operator import itemgetter
from decimal import *
from collections import Counter
from collections import OrderedDict

os.chdir('/Users/mbpsmac/Desktop')

file_name = 'rossetta.xlsx'

wb = load_workbook(file_name)

ws = wb.active
property_names = []
tags = []
product_rows = []
product_row_tags = []
final_sort = []

max_row = 786

headings = ["REF", "CUSTOMER:JOB", "SHIP DATE", "DUE DATE", "ITEM", "GROSS VOL", "GROSS EXP", "GROSS OWNER"]

col_list = ['A', 'B', 'H', 'O']

# Get well names, correct them, and add them to list, get product row tags
for cell in ws['A']:
    if type(cell.value) is int:
        name = ws["C" + str(cell.row)].value
        D_cell = ws["D" + str(cell.row)].value
        E_cell = ws["E" + str(cell.row)].value
        F_cell = ws["F" + str(cell.row)].value

        ws['C' + str(cell.row)] = f'{name} {D_cell} {E_cell} {F_cell}'.replace('None', '')
        # Get property Names
        property_names.append(ws['C' + str(cell.row)].value)
        # Get property tags
        tags.append(cell)

        ws["D" + str(cell.row)] = None
        ws["E" + str(cell.row)] = None
        ws["F" + str(cell.row)] = None

# Add property names to the end of each row
for idx, tag in enumerate(tags):
    try:
        row_count = tags[idx + 1].row - tag.row - 1
        for x in range(1, row_count + 1):
            ws['S' + str(tag.row + x)] = property_names[idx]
    except IndexError:
        row_count = max_row - tag.row - 1
        for x in range(1, row_count + 1):
            ws['S' + str(tag.row + x)] = property_names[idx]

# Grab all row locations for rows with products in them
for cell in ws['A']:
    if type(cell.value) is datetime.datetime:
        product_row_tags.append(cell)

# Get all necessary values from row
for idx, cell in enumerate(product_row_tags):
    holder_list = []
    for col in col_list:
        holder_list.append(ws[col + str(cell.row)].value)

    product_rows.append(holder_list)

    # Get distance between product rows
for idx, cell in enumerate(product_row_tags):
    try:
        distance = ((product_row_tags[idx + 1].row - 1) - product_row_tags[idx].row)
        product_rows[idx].append(distance)
    except IndexError:
        distance = (max_row - 1) - product_row_tags[idx].row
        product_rows[idx].append(distance)

# Pick up all expense rows
for idx, lst in enumerate(product_rows):
    distance = lst[-1]
    for x in range(1, distance + 1):
        try:
            row_num = str(product_row_tags[idx].row + x)
            value_holder = ws['J' + row_num].value
            value_label = ws['K' + row_num].value
            owner_value = ws['P' + row_num].value
            product_rows[idx].append((value_label, value_holder, "owner " + value_label, owner_value))
        except TypeError:
            product_rows[idx].append(('Delete', 0, "owner " + '', 0))
    product_rows[idx].append(ws['S' + str(product_row_tags[idx].row)].value)

# Combine like products
holder = list([product_rows[0]])
hold_counter = 0
for idx in range(1, len(product_rows) + 1):
    try:
        value_list = product_rows[idx]
    except IndexError:
        final_sort.append(holder)
        continue

    if value_list[0] == holder[hold_counter][0] and value_list[1] == holder[hold_counter][1] and value_list[-1] == \
            holder[hold_counter][-1]:
        holder.append(value_list)
        hold_counter += 1
    else:
        final_sort.append(holder)
        holder = [value_list]
        hold_counter = 0


wb.create_sheet("Drop In", 1)
active_ws = wb['Drop In']

# Create titles on sheet
for idx, title in enumerate(headings):
    active_ws.cell(row=1, column=idx + 1, value=title)

row_counter = 2

total = 0
for item in final_sort:

    final_gross_vol = 0
    final_exp = 0
    final_owner_gross = 0
    final_owner_deduct = 0

    if len(item) == 1:
        products = item[0][4]
        if products >= 1:

            active_ws.cell(row=row_counter, column=5, value=item[0][1])  # Product Code
            active_ws.cell(row=row_counter, column=6, value=item[0][2])  # Gross VOL
            active_ws.cell(row=row_counter, column=8, value=item[0][3])  # Owner Value

            for x in range(products + 1):
                active_ws.cell(row=row_counter + x, column=3, value=item[0][0])  # Date
                active_ws.cell(row=row_counter + x, column=4, value=item[0][0])  # Date
                active_ws.cell(row=row_counter + x, column=2, value="ROSETTA: " + item[0][-1])  # Property Name

            for y in range(products):
                active_ws.cell(row=row_counter + y + 1, column=5, value=item[0][5 + y][0])  # Expense Name
                active_ws.cell(row=row_counter + y + 1, column=7, value=item[0][5 + y][1])  # Gross EXP
                active_ws.cell(row=row_counter + y + 1, column=8, value=item[0][5 + y][-1])  # Owner Value

            row_counter += products + 1

        else:
            active_ws.cell(row=row_counter, column=5, value=item[0][1])  # Product Code
            active_ws.cell(row=row_counter, column=6, value=item[0][2])  # Gross VOL
            active_ws.cell(row=row_counter, column=8, value=item[0][3])  # Owner Value
            active_ws.cell(row=row_counter, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter, column=2, value="ROSETTA: " + item[0][-1])  # Property Name

            row_counter += 1

    else:
        exp_holder = []
        product_name = ""

        for lst in item:
            num_products = lst[4]
            product_name = lst[1]

            final_gross_vol += lst[2]
            final_owner_gross += lst[3]

            if num_products != 0:
                for x in range(5, 5 + num_products):
                    exp_holder.append(lst[x])

        # count all like values together in dict

        exp_counter = Counter()
        for exp, val, owner, owner_val in exp_holder:
            exp_counter.update(({exp: val, owner: owner_val}))

        exp_counter = OrderedDict(sorted(exp_counter.items(), key=lambda t: len(t[0])))

        key_list = exp_counter.keys()

        if len(key_list) % 2 == 0:
            len_lst = int(len(key_list) / 2)
        else:
            len_lst = int(len(key_list) / 2) + 1

        for x in range(len_lst + 1):
            active_ws.cell(row=row_counter + x, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + x, column=4, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + x, column=2, value="ROSETTA: " + item[0][-1])  # Property Name

        temp_keys = []
        for key in key_list:
            if "owner" not in key:
                temp_keys.append(key)
        x = 1
        for key in temp_keys:
            active_ws.cell(row=row_counter + x, column=5, value=key)  # Expense Name
            active_ws.cell(row=row_counter + x, column=7, value=exp_counter[key])  # Gross EXP
            active_ws.cell(row=row_counter + x, column=8, value=exp_counter['owner ' + key])  # Owner Value

            x += 1

        active_ws.cell(row=row_counter, column=5, value=product_name)  # Product Name
        active_ws.cell(row=row_counter, column=6, value=final_gross_vol)  # Gross VOL
        active_ws.cell(row=row_counter, column=8, value=final_owner_gross)  # Owner Value

        row_counter += (len_lst + 1)

for cell in active_ws['E']:
    if cell.value == "Delete":
        active_ws.delete_rows(cell.row, 1)

wb.save('try.xlsx')