CMX.py

import os
import openpyxl
from openpyxl import load_workbook
import operator
from operator import itemgetter
from datetime import datetime
import datetime
from collections import Counter

# file_name = input("Please enter your file name: ")
# print("Processing file...........................")
#
# wb = load_workbook(file_name)

os.chdir('/Users/porte/Desktop')
print(os.getcwd())

file_name = 'Sheet1.xlsx'



wb = load_workbook(file_name)

value_columns = ['A', 'B', 'D', 'K', 'L', 'O']

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

product_dict = {"DRIP": "Texas Oil:Condensate Drip", "GAS": "Texas Gas", "PPROD": "Liquid Products",
                "RSGAS": "Texas gas"
    , "FUEL": "Texas Gas:Lease Use Gas", "OIL": "Texas Oil", "FLARE": "Flared Gas", "SEV": "Texas Severance Exp",
                "GA": "Gathering Exp", "PC": "Processing Exp", "TR": "Transportation Exp", "TRT": "Treatment Exp",
                "SKIM": "Texas Oil:Skim Oil",
                "COND": "Texas Oil", "IN": "Interest"}
ws = wb.active
property_names = []
tags = []
product_row_tags = []
product_rows = []
final_sort = []

max_row = ws.max_row

# get all property names and cell tags for coordinates
for cell in ws['D']:
    if cell.value is not None and type(cell.value) == str:
        d_cell = cell.value
        e_cell = ws['E' + str(cell.row)].value
        if e_cell is None:
            e_cell = ""
        f_cell = ws['F' + str(cell.row)].value
        if f_cell is None:
            f_cell = ""
        d_cell = d_cell + " " + e_cell + " " + f_cell
        ws['E' + str(cell.row)] = None
        ws['F' + str(cell.row)] = None
        property_names.append(d_cell)
        tags.append(cell)

# 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['O' + str(tag.row + x)] = property_names[idx]
    except IndexError:
        row_count = ws.max_row - tag.row - 1
        for x in range(1, row_count + 1):
            ws['O' + str(tag.row + x)] = property_names[idx]

# Get all row coordinates with products in them
for cell in ws['B']:
    if cell.value is not None and type(cell.value) == str:
        product_row_tags.append(cell)

# Grab needed information from product rows
for idx, cell in enumerate(product_row_tags):
    holder_list = []
    for col in value_columns:
        if ws[col + str(cell.row)].value is None:
            holder_list.append(0)
        else:
            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 - 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):
        value_holder = ws['F' + str(product_row_tags[idx].row + x)].value
        value_label = ws['E' + str(product_row_tags[idx].row + x)].value
        product_rows[idx].append((value_label, value_holder))


# Clean up product rows
for product_lst in product_rows:
    if product_lst[6] == 3 and product_lst[-1][0] is None:
        product_lst.pop(-1)

# Sort value rows with the same date and same product code together
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][0] != "IN":
        holder.append(value_list)
        hold_counter += 1
    else:
        final_sort.append(holder)
        holder = [value_list]
        hold_counter = 0


# Create new sheet in workbook
wb.create_sheet("totals")
active_ws = wb['totals']

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

# for row in product_rows:
#     print(row, len(row))

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:
        print(item, len(item[0]))
        active_ws.cell(row=row_counter, column=2, value="CMX:" + item[0][5])  # Property Name
        active_ws.cell(row=row_counter + 1, column=2, value="CMX:" + item[0][5])  # Property Name
        active_ws.cell(row=row_counter, column=3, value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter, column=4, value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter + 1, column=3, value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter + 1, column=4, value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter, column=5, value=product_dict[item[0][1]])  # Product Code
        if len(item[0]) == 7:
            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
            row_counter += 1
        elif item[0][-1][0] is None:
            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
            row_counter += 1
            continue
        elif item[0][-1][0] is not None:
            active_ws.cell(row=row_counter + 1, column=5, value=product_dict[item[0][-1][0]])  # Exp Name
            active_ws.cell(row=row_counter, column=6, value=item[0][2])  # Gross VOL
            active_ws.cell(row=row_counter + 1, column=7, value=item[0][-1][1])  # Gross EXP
            active_ws.cell(row=row_counter, column=8, value=item[0][3])  # Owner Value
            active_ws.cell(row=row_counter + 1, column=8, value=item[0][4])  # Owner Deduct
            row_counter += 2

    else:

        exp_holder = []
        for lst in item:
            # print(len(lst))

            final_gross_vol += lst[2]
            final_owner_gross += lst[3]
            if lst[4] is not None:
                final_owner_deduct += lst[4]
            else:
                final_owner_deduct += 0

            if len(lst) == 8:
                if lst[-1][0] is not None:
                    exp_holder.append(lst[-1])

            elif len(lst) == 9:
                if lst[-1][0] is not None:
                    exp_holder.append(lst[-1])
                if lst[-2][0] is not None:
                    exp_holder.append(lst[-2])

            elif len(lst) == 10:
                if lst[-1][0] is not None:
                    exp_holder.append(lst[-1])
                if lst[-2][0] is not None:
                    exp_holder.append(lst[-2])
                if lst[-3][0] is not None:
                    exp_holder.append(lst[-3])

        # count all like values together in dict
        exp_counter = Counter()
        for exp, val in exp_holder:
            exp_counter.update({exp: val})

        active_ws.cell(row=row_counter, column=2, value="CMX:" + item[0][5])  # Property Name
        active_ws.cell(row=row_counter + 1, column=2, value="CMX:" + item[0][5])  # Property Name
        active_ws.cell(row=row_counter, column=3,
                       value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter, column=4,
                       value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter + 1, column=3,
                       value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter + 1, column=4,
                       value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
        active_ws.cell(row=row_counter, column=5, value=product_dict[item[0][1]])  # Product Code

        if len(exp_counter) == 1:
            for exp, val in exp_counter.items():
                exp_name = exp
                total_exp = val

                active_ws.cell(row=row_counter + 1, column=5, value=product_dict[exp_name])  # Product Code
                active_ws.cell(row=row_counter, column=6, value=final_gross_vol)
                active_ws.cell(row=row_counter + 1, column=7, value=total_exp)  # Gross EXP
                active_ws.cell(row=row_counter, column=8, value=final_owner_gross)
                active_ws.cell(row=row_counter + 1, column=8, value=final_owner_deduct)
                row_counter += 2

        elif len(exp_counter) == 2:
            active_ws.cell(row=row_counter, column=6, value=final_gross_vol)
            active_ws.cell(row=row_counter, column=8, value=final_owner_gross)
            for exp, val in exp_counter.items():
                row_counter += 1
                exp_name = exp
                total_exp = val

                active_ws.cell(row=row_counter, column=2, value="CMX:" + item[0][5])  # Property Name
                active_ws.cell(row=row_counter, column=3,
                               value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
                active_ws.cell(row=row_counter, column=4,
                               value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
                active_ws.cell(row=row_counter, column=5, value=product_dict[exp_name])
                active_ws.cell(row=row_counter, column=7, value=total_exp)

            active_ws.cell(row=row_counter, column=8, value=final_owner_deduct)
            row_counter += 1

        elif len(exp_counter) == 3:
            active_ws.cell(row=row_counter, column=6, value=final_gross_vol)
            active_ws.cell(row=row_counter, column=8, value=final_owner_gross)
            for exp, val in exp_counter.items():
                row_counter += 1
                exp_name = exp
                total_exp = val

                active_ws.cell(row=row_counter, column=2, value="CMX:" + item[0][5])  # Property Name
                active_ws.cell(row=row_counter, column=3,
                               value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
                active_ws.cell(row=row_counter, column=4,
                               value=datetime.datetime.strftime(item[0][0], '%m/%d/%Y'))  # Date
                active_ws.cell(row=row_counter, column=5, value=product_dict[exp_name])
                active_ws.cell(row=row_counter, column=7, value=total_exp)

            active_ws.cell(row=row_counter, column=8, value=final_owner_deduct)
            row_counter += 1
        continue


# save_file = input("What name would you like to save the file under?: ")
#
# wb.save(save_file + '.xlsx')
# print("Saving file...........................................")
#
# print("File saved to " + os.getcwd())

wb.save('2018-09-17-V-CMX-483,962.33-MPI.xlsx')