Last active
September 11, 2018 21:59
-
-
Save MuddyBootsCode/5daf2ab655596013eeddcb788aa0f603 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import os | |
import openpyxl | |
from openpyxl import load_workbook | |
import csv | |
from openpyxl import Workbook | |
import datetime | |
import operator | |
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 = 'august.xlsx' | |
grand_total = 392381.12 | |
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) | |
# 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 + str(x), value_holder, "owner" + str(x), 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: | |
if len(item[0]) > 6: | |
products = item[0][4] | |
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): | |
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(1, products): | |
active_ws.cell(row=row_counter + y, column=5, value=item[0][5 + y][0]) # Expense Name | |
active_ws.cell(row=row_counter + y, column=7, value=item[0][5 + y][1]) # Gross EXP | |
active_ws.cell(row=row_counter + y, column=8, value=item[0][5 + y][-1]) # Owner Value | |
row_counter += products | |
elif len(item[0]) == 6: | |
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 = [] | |
for lst in item: | |
num_products = lst[4] | |
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() | |
for x in range(num_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 x in range(1, num_products + 1): | |
for key in key_list: | |
prod_key = "" | |
if str(x) in key and "owner" not in key: | |
prod_key = key | |
active_ws.cell(row=row_counter + x, column=5, value=prod_key) # Expense Name | |
active_ws.cell(row=row_counter + x, column=7, value=exp_counter[prod_key]) # Gross EXP | |
active_ws.cell(row=row_counter + x, column=8, value=exp_counter['owner' + str(x)]) # Owner Value | |
active_ws.cell(row=row_counter, column=5, value=lst[1]) # 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 += (num_products + 1) | |
for row in active_ws['E']: | |
for num in num_list: | |
if 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-08-24-V-SWEPI-392,381.12.xlsx') | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment