halcon.py

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

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

file_name = 'Sheet1.xlsx'

wb = load_workbook(file_name)

max_row = 367

ws = wb.active
property_names = []
tags = []
sheets = []

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

col_list = ['B', 'A', 'E']


# 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=12, 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=12, 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


# Get well names, correct them, and add them to list, get product row tags
# Check Well Names Carefully
for cell in ws['B']:
    if type(cell.value) is str:
        property_names.append(cell.value.strip().split(",")[0])
        tags.append(cell)

# Get all cell values between property tags
# 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))

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

    wb.create_sheet(property_names[idx])

    active_ws = wb[property_names[idx]]

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

    i = 0
    for x in range(2, row_count + 2):
        for y in range(1, 13):
            active_ws.cell(row=x, column=y, value=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)

sheet_total_index = []

# get totals for each sheet
for idx, name in enumerate(property_names):
    active_ws = wb[property_names[idx]]
    active_ws['M' + str(active_ws.max_row)] = '=sum(L1:' + 'L' + str(active_ws.max_row + 2) + ')'
    sheet_total_index.append(active_ws['M' + str(active_ws.max_row)].coordinate)

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

grand_total = 117604.51

# grab total values from sheets
for idx, name in enumerate(property_names):
    active_ws.cell(row=idx + 1, column=1, value=name)
    active_ws.cell(row=idx + 1, column=2, value="='" + property_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=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(sheets):
    active_ws['C' + str(idx + 1)] = "=sheet1!M" + str(sheets[idx][-1].row)
    active_ws['E' + str(idx + 1)] = "=C" + str(idx + 1) + "-" + "B" + str(idx + 1)



final_sort = []

for name in property_names:
    product_row_tags = []
    product_rows = []
    active_ws = wb[name]

    # Grab all row locations for rows with products in them
    for cell in active_ws['B']:
        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(active_ws[col + str(cell.row)].value)

        holder_list.append(active_ws['F' + str(cell.row + 1)].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 = active_ws.max_row - 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]
        if distance == 3:
            distance -= 1
        for x in range(0, distance, 2):
            value_holder = active_ws['H' + str(product_row_tags[idx].row + x)].value
            value_label = active_ws['G' + str(product_row_tags[idx].row + x)].value
            owner_value = active_ws['H' + str(product_row_tags[idx].row + + x + 1)].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']

print(final_sort[0])

# 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 % 2 == 0:
            products = int(products / 2)
        elif products == 3:
            products = 2

        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="HALCON: " + 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="HALCON: " + item[0][-1])  # Property Name

            row_counter += 1

    else:
        exp_holder = []
        for lst in item:
            num_products = lst[4]

            if num_products % 2 == 0:
                num_products = int(num_products / 2)
            elif num_products == 3:
                num_products = 2

            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

            if type(exp_holder[-1]) is not tuple:
                del exp_holder[-1]

        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="HALCON: " + 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)

wb.save('halcon.xlsx')