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"