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

tags = []
sheets = []
sheet_names = []
whole_sheets = []
sheet_total_index = []

headings = ["Production Date", "Product Disposition", "Acct Ref", "Typ Int", "Owner Decimal", "UOM",
            "Gross Vol", "Owner Volume", "Amount Description", "Unit Value", "Gross Amounts", "Owner Amounts",
            "Owner Net Amount"]

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

num_list = ["1", "2", "3", "4", "5", "6"]

col_list = ['A', 'B', 'G', 'L']

os.chdir('/Users/porte/Desktop')
cwd = os.getcwd()

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

file_name = 'sept.xlsx'

grand_total = 168291.22

wb = load_workbook(filename=file_name)
ws = wb.active


row_counter = 2

# Captures all cells in a range, creating and returning a sheet
def cell_range(start, end):
    sheet = []
    for cell_row in ws.iter_rows(min_row=start, max_col=13, max_row=end):
        for data in cell_row:
            sheet.append(data)

    return sheet


# Captures all cell values in range
def cell_range_values(start, end):
    sheet = []
    for cell_row in ws.iter_rows(min_row=start, max_col=13, max_row=end):
        for data in cell_row:
            sheet.append(data.value)

    return sheet


def cell_range_paster(start, end, list):
    for cell_row in ws.iter_rows(min_row=start, max_col=13, max_row=end):
        for idx, cell in enumerate(cell_row):
            cell.value = list[idx].value


# Creates a list of cell values from a cell range
def cell_copier(cell_range):
    copied_cells = []
    for row in cell_range:
        for cell in row:
            copied_cells.append(cell.value)

    return copied_cells


# Deletes a range of cells
def cell_delete(cell_range):
    for row in cell_range:
        for cell in row:
            cell.value = None


# Pastes the values from a list into a range of cells
def cell_paster(cell_list, cell_range):
    counter = 0
    for row in cell_range:
        for cell in row:
            cell.value = cell_list[counter]
            counter += 1


# copies cell values from on Range, deletes the cells in that range, then pastes the values in the given range
def cell_copy_paste(copy_range, paste_range):
    counter = 0
    cell_list = cell_copier(copy_range)
    cell_delete(copy_range)
    for row in paste_range:
        for cell in row:
            cell.value = cell_list[counter]
            counter += 1


for cell in ws['A']:
    try:
        if "NAME:" in cell.value:
            name = cell.value.split("NAME:")[-1]
            ws['C' + str(cell.row)] = name
    except TypeError:
        continue

# Turn date cells into date time
for cell in ws['A']:
    if type(cell.value) is int:
        cell.value = str(cell.value)
    try:
        if len(cell.value) == 4:
            year = 2000 + int(cell.value[2:4])
            month = int(cell.value[0:2])
            day = 1
            date = datetime.datetime.strftime(datetime.date(year, month, day), '%m/%y')
            cell.value = date
    except TypeError:
        continue

for cell in ws['B']:
    try:
        if cell.value == "ALL PLANT PRODUCTS":
            cell.value = "LIQUID PRODUCTS"
        elif "SKIM" in cell.value:
            cell.value = "SKIM OIL"
        elif "GAS" in cell.value:
            cell.value = "TEXAS GAS"
        elif "OIL" in cell.value:
            cell.value = "TEXAS OIL"
    except TypeError:
        continue

# Finds all property tags
for cell in ws['A']:
    try:
        if "PROPERTY" in cell.value:
            tags.append(cell)
            if ws['E' + str(cell.row)] is not None:
                prop_name = ws['C' + str(cell.row)].value
                name_ext = ws['E' + str(cell.row)].value
                ws['C' + str(cell.row)] = prop_name + " " + name_ext
                ws['E' + str(cell.row)] = None
    except TypeError:
        continue

# Captures all cell values between property tags
for idx, cell in enumerate(tags):
    try:
        r = cell.row
        mr = (tags[idx + 1].row - 1)
        sheets.append(cell_range(r, mr))

    except IndexError:
        r = cell.row
        mr = ws.max_row
        sheets.append(cell_range(r, mr))

# Grab property name values
for tag in tags:
    sheet_names.append(ws['c' + str(tag.row)].value)

# Remove duplicate sheet names
new_sheet_list = []
[new_sheet_list.append(item) for item in sheet_names if item not in new_sheet_list]

# loop through sheets and combine all like properties
for sheet_name in new_sheet_list:
    combined_sheet = []
    for sheet in sheets:

        name = sheet[2].value

        if name == sheet_name:
            combined_sheet += sheet

    whole_sheets.append(combined_sheet)

sheet_names = [name.replace("NAME: ", "").replace(":", "")[0:30] for name in new_sheet_list]

for idx, name in enumerate(sheet_names):
    name_list = name.split(" ")
    if "F" in name_list[-1]:
        del name_list[-1]
        sheet_names[idx] = " ".join(name_list)

# Create one new sheet per Property and fill in the values
for idx, sheet in enumerate(sheet_names):

    wb.create_sheet(sheet_names[idx])

    active_ws = wb[sheet_names[idx]]

    row_count = whole_sheets[idx][-1].row - whole_sheets[idx][0].row + 1
    ws['O' + str(whole_sheets[idx][-1].row)] = "=sum(M" + str(whole_sheets[idx][0].row + 1) + ":M" + str(
        whole_sheets[idx][-1].row) + ")"

    i = 0
    for x in range(2, row_count + 2):
        for y in range(1, 14):
            active_ws.cell(row=x, column=y, value=whole_sheets[idx][i].value)
            i += 1

    # create column headings for each sheet
    for l, title in enumerate(headings):
        active_ws.cell(row=1, column=l + 1, value=title)

    # delete property names from sheets
    colA = active_ws["A"]
    for cell in colA:
        try:
            if "PROPERTY" in cell.value:
                active_ws.delete_rows(cell.row, 1)
        except TypeError:
            continue

# Copy and sort data into sheets per property
for idx, sheet in enumerate(sheet_names):
    try:

        active_ws = wb[sheet_names[idx]]

        date_list = []
        items = []

        date_col = active_ws['A']

        for cell in date_col:
            if type(cell.value) is datetime.date:
                date_list.append(cell)

        for idx, cell in enumerate(date_list):
            sheet = []
            try:
                for cell_row in active_ws.iter_rows(min_row=cell.row, max_col=13, max_row=date_list[idx + 1].row - 1):
                    for data in cell_row:
                        sheet.append(data.value)

            except IndexError:
                for cell_row in active_ws.iter_rows(min_row=cell.row, max_col=13, max_row=active_ws.max_row):
                    for data in cell_row:
                        sheet.append(data.value)

            items.append(sheet)

        items = sorted(items, key=itemgetter(0, 1))

        flat_list = [item for sublist in items for item in sublist]

        i = 0
        for x in range(2, active_ws.max_row + 1):
            for y in range(1, 14):
                active_ws.cell(row=x, column=y, value=flat_list[i])
                i += 1
    except IndexError:
        continue

for idx, name in enumerate(sheet_names):
    active_ws = wb[sheet_names[idx]]
    active_ws['N' + str(active_ws.max_row)] = '=sum(M1:' + 'M' + str(active_ws.max_row + 2) + ')'
    sheet_total_index.append(active_ws['N' + str(active_ws.max_row)].coordinate)

wb.create_sheet('Totals', 0)
active_ws = wb['Totals']


# grab total values from sheets
for idx, name in enumerate(sheet_names):
    active_ws.cell(row=idx + 1, column=1, value=name)
    active_ws.cell(row=idx + 1, column=2, value="='" + sheet_names[idx] + "'!" + sheet_total_index[idx])
# checks total values
active_ws.cell(row=active_ws.max_row + 1, column=2, value="=sum(B1:B" + str(active_ws.max_row) + ")")
active_ws.cell(row=active_ws.max_row, column=3, value=Decimal(grand_total))
active_ws.cell(row=active_ws.max_row, column=4, value="=IF(C" + str(active_ws.max_row) + "=B" + str(
    active_ws.max_row) + ", \"True\", \"False\")")
active_ws['E' + str(active_ws.max_row)] = "=C" + str(active_ws.max_row) + "-" + "B" + str(active_ws.max_row)

# checks translated sheet totals against original sheet
for idx, sheet in enumerate(whole_sheets):
    active_ws['C' + str(idx + 1)] = "=sheet1!O" + str(whole_sheets[idx][-1].row)
    active_ws['E' + str(idx + 1)] = "=C" + str(idx + 1) + "-" + "B" + str(idx + 1)

final_sort = []

for name in sheet_names:
    property_row_tags = []
    product_rows = []
    active_ws = wb[name]

    # Get all row coordinates with products in them
    for cell in active_ws['B']:
        if cell.value is not None and cell.row != 1:
            property_row_tags.append(cell)

    # [print(tag) for tag in property_row_tags]


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

        product_rows.append(holder_list)

    # Get distance between product rows
    for idx, cell in enumerate(property_row_tags):
        try:
            distance = ((property_row_tags[idx + 1].row - 2) - property_row_tags[idx].row)
            product_rows[idx].append(distance)
        except IndexError:
            distance = (active_ws.max_row - 1) - property_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):
            value_holder = active_ws['K' + str(property_row_tags[idx].row + x)].value
            value_label = active_ws['I' + str(property_row_tags[idx].row + x)].value
            owner_value = active_ws['L' + str(property_row_tags[idx].row + x)].value
            product_rows[idx].append((value_label, value_holder, "owner " + value_label, owner_value))

        product_rows[idx].append(active_ws.title)


    # 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]:
            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(total_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=2, value=item[0][0])  # Date
                active_ws.cell(row=row_counter + x, column=3, value=item[0][0])  # Date
                active_ws.cell(row=row_counter + x, column=4, value="SWEPI: " + 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=2, 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=4, value="SWEPI: " + 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=2, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + x, column=3, value=item[0][0])  # Date
            active_ws.cell(row=row_counter + x, column=4, value="SWEPI: " + 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 row in active_ws['E']:
        for num in num_list:
            if row.value is not None and num in row.value:
                row.value = row.value.replace(num, "")

    for row in active_ws['B']:
        if cell.value == "SKIM OIL":
            cell.value = "TEXAS OIL: SKIM OIL"
# save_file = input("What name would you like to save the file under?: ")
# print("Saving file...........................................")
# wb.save(save_file + '.xlsx')
# print('Save complete!')
#
# print("File saved to " + os.getcwd())

wb.save('2018-09-25-V-SWEPI-168,291.22-MPI.xlsx')

# active_ws = wb['Sheet1']
#
# for cell in active_ws['I']:
#     if cell.value == "SUBTOTAL" and active_ws['L' + str(cell.row)].value is not None:
#         print(cell.coordinate)