startTimer = time.time()
# ------------- Load ---------------
wbInvo = openpyxl.load_workbook(invoicedFName)
sInvo = wbInvo.active
# ----------- BEGIN Find Column Indexes -------------------
# Should have a dictionary that stores the column names and index position
print("Loading Invoiced column names")
# Create an empty dictionary
invoicedDict = {}
# fill the dictionary with:
# Key = column names
# Value = column index
get_column_names_and_index(sInvo, invoicedDict)
# ------------- Deferred Revenues ---------------
# IF InvoiceDateSent == 2018 Due Gets "Def-1"
if (logMe == 1):
invoicedDeferredLog = open("invoicedDeferredLog.txt", "w+")
for r in range(2, sInvo.max_row + 1):
if (sInvo.cell(row=r, column=invoicedDict["InvoiceDateSent"]).value != None
and sInvo.cell(
row=r,
column=invoicedDict["InvoiceDateSent"]).value.date().year
== 2018):
sInvo.cell(row=r, column=invoicedDict["Due"]).value = "Def-1"
# ------------ SAVE Output Excel File -------------
foreRowCount = 0
# We include the column names from Forecast.xlsx, not for other 2 sheets
for r in range(1, sFore.max_row + 1):
for c in range(1, sFore.max_column + 1):
sCombined.cell(row=r, column=c).value = sFore.cell(row=r,
column=c).value
foreRowCount += 1
wbCombined.save(combinedFName)
wbCombined.close()
wbCombined = openpyxl.load_workbook(combinedFName)
sCombined = wbCombined.active
combinedDict = {}
get_column_names_and_index(sCombined, combinedDict)
format_date_rows(sCombined, combinedDict, "mm-dd-yy", "Due Date",
"InvoiceDateSent", "OriginalDueDate")
yellowFill = PatternFill(patternType="solid", fgColor="FFFF00")
for r in range(2, sCombined.max_row + 1):
# IF InvoiceDateSent != None, Forecast GOT None But formatting doesn't copy over.
# We NOW can fill cell background with yellow
# HAVE to do this step AFTER copying sFore, and BEFORE copying anything else...
if (sCombined.cell(row=r, column=combinedDict["InvoiceDateSent"]).value !=
None): # May need to test for == 2018 or == 2019 instead
sCombined.cell(row=r,
column=combinedDict["Forecast"]).fill = yellowFill
# We want to start
from excelFNames import creditFName, logMe
startTimer = time.time()
# ------------- Load ---------------
wbCred = openpyxl.load_workbook(creditFName)
sCred = wbCred.active
# ----------- BEGIN Find Column Indexes -------------------
print("Loading Credits column names")
#Create an empty dictionary
creditsDict = {}
# fill the dictionary with:
# Key = column names
# Value = column index
get_column_names_and_index(sCred, creditsDict)
# Append "CR" to SubProjectID
for r in range(2, sCred.max_row + 1):
sCred.cell(row=r, column=creditsDict["SubProjectID"]).value = str(
sCred.cell(row=r, column=creditsDict["SubProjectID"]).value) + "CR"
print("Added 'CR' after SubProjectIDs")
# ------------ SAVE Output Excel File -------------
wbCred.save(creditFName)
print("Manipulated Credits query saved as " + creditFName)
wbCred.close()
# -------------- Timer -----------------------
import os
import openpyxl
from myxlutils import get_column_names_and_index
from excelFNames import forecastFName, invoicedFName, creditFName
# -------------------------- FORECAST ----------------------------------------
# Forecast is missing "Type" and "Due"
wbFore = openpyxl.load_workbook(forecastFName)
sFore = wbFore.active
forecastHeading = {}
get_column_names_and_index(sFore, forecastHeading)
# Insert "Type" after SubProjectTypeName
sFore.insert_cols(forecastHeading["SubProjectTypeName"] + 1)
sFore.cell(row=1,
column=forecastHeading["SubProjectTypeName"] + 1).value = "Type"
# Update indexes after move
get_column_names_and_index(sFore, forecastHeading)
# Insert "Due" after "Due Date"
sFore.insert_cols(forecastHeading["Due Date"] + 1)
sFore.cell(row=1, column=forecastHeading["Due Date"] + 1).value = "Due"
# -------------------------- INVOICED ----------------------------------------
# Invoiced is missing "Type" and "Due"
wbInvo = openpyxl.load_workbook(invoicedFName)
sInvo = wbInvo.active
invoicedHeading = {}
get_column_names_and_index(sInvo, invoicedHeading)
# Insert "Type" after SubProjectTypeName
sInvo.insert_cols(invoicedHeading["SubProjectTypeName"] + 1)
sInvo.cell(row=1,
column=invoicedHeading["SubProjectTypeName"] + 1).value = "Type"
# Open Forecast
# Loop through all files in directory
for filename in os.listdir('.'):
# only try to open those with .xlsx
if filename.endswith(".xlsx"):
# test filename then open book and sheet to according variables
if filename.startswith("qry_Forecast"):
wbFore = openpyxl.load_workbook(filename)
sFore = wbFore.active
# print("qry_Forecast loaded")
# Load column names into a dict
forecastDict = {}
get_column_names_and_index(sFore, forecastDict)
# Apply forecast data column formatting
format_date_rows(sFore, forecastDict, "mm-dd-yy", "Due Date", "InvoiceDateSent", "OriginalDueDate")
# Save and close
wbFore.save(forecastFName)
wbFore.close()
# Open Credits
for filename in os.listdir('.'):
# only try to open those with .xlsx
if filename.endswith(".xlsx"):
# test filename then open book and sheet to according variables
if filename.startswith("qry_Credits"):
# Formatting vars
blueFill = PatternFill(patternType="solid", fgColor="91B3D7")
currentFill = PatternFill(patternType="solid", fgColor="E6B8B7")
redBorder = Side(border_style="medium", color="FF0000")
boldFont = Font(bold=True)
leftAlign = Alignment(horizontal="left", vertical="top")
# *****************************************************************
# ************************ Details Tab ****************************
# *****************************************************************
# Load details sheet
sDetail = wbReport["Details"]
detailDict = {}
get_column_names_and_index(sDetail, detailDict)
# Freeze and left-align top row
sDetail.freeze_panes = "A2"
for x in range(1, (sDetail.max_column + 1)):
sDetail.cell(row=1, column=x).alignment = leftAlign
# Format dates to mm-dd-yyyy
format_date_rows(sDetail, detailDict, "mm-dd-yy", "Due Date",
"InvoiceDateSent", "OriginalDueDate")
# Format currency cols to ()
format_dollar_values(sDetail, detailDict, '"$"#,##0_);[Red]("$"#,##0)',
"Forecast", "Quoted", "OriginalForecast", "Budget",
"SubTotal")
startTimer = time.time()
# ------------- Load ---------------
wbFore = openpyxl.load_workbook(forecastFName)
sFore = wbFore.active
# ----------- BEGIN Find Column Indexes -------------------
# Should have a dict list that stores the column names
print("Loading column names")
#Create an empty dictionary
forecastDict = {}
# fill the dictionary with:
# Key = column names
# Value = column index
get_column_names_and_index(sFore, forecastDict)
# Log all of the dictionary key:value pairs
if (logMe == 1):
forcastDictLog = open("forecastDictLog.txt", "w+")
for field, row in forecastDict.items():
logString = field + " : " + str(row) + "\n"
forcastDictLog.write(logString)
# print(logString)
forcastDictLog.close()
print("Created forecastDictLog.txt\n")
# ----------- END Find Column Indexes ------------------
# -------------- BEGIN Formatting -----------
# Freeze the top row
sFore.freeze_panes = "A2"
