def copyTotalValues(monthSheetData, yearSheetData, yearSheetEq):
# Get the data from the DATA workbook
totalSpent = monthSheetData[info.getRowNum(monthSheetData, 2, 1,
"Total")][2].value
net = monthSheetData[info.getRowNum(monthSheetData, 2, 1,
"Spending Money")][2].value
# Find the next open row in the yearly table
nextOpenRow = info.getRowNum(yearSheetData, 4, 3)
# Writing data to EQUATION file
yearSheetEq.cell(row=nextOpenRow, column=4).value = totalSpent
yearSheetEq.cell(row=nextOpenRow, column=5).value = net
def copySummaryTable(monthSheetData, yearSheetEq, categoryList):
# Initialize some needed variables
row = 2
namesList = []
amountsList = []
nameMonthCell = monthSheetData[row][1]
amountMonthCell = monthSheetData[row][2]
# Loop down the category table on the monthly sheet
for l in range(len(categoryList) + 1):
# Clean values are not needed because excel needs to see the values as numbers not strings
namesList.append(nameMonthCell.value)
amountsList.append(amountMonthCell.value)
row += 1
nameMonthCell = monthSheetData[row][1]
amountMonthCell = monthSheetData[row][2]
# Get the next open row in the column of months
# Always start the search at row 23 and column C
nextOpenRow = info.getRowNum(yearSheetEq, 23, 2)
monthTitleCell = nextOpenRow
categoryColumn = 2
amountColumn = 3
# Write in the Month name with formatting
monthCell = yearSheetEq[monthTitleCell][categoryColumn]
monthCell.value = info.month
monthCell.fill = info.greenFill
monthCell.font = info.yearlyMonth
row = monthTitleCell + 1
# Write in the category names and the amounts
for name, amount in zip(namesList, amountsList):
currentCategoryCell = yearSheetEq[row][categoryColumn]
currentAmountCell = yearSheetEq[row][amountColumn]
# Specific formats for the column titles
if name == "Category" or amount == "Amount":
currentCategoryCell.font = info.boldFont
currentAmountCell.font = info.boldFont
# Write and format the category name
currentCategoryCell.value = name
currentCategoryCell.fill = info.lightGreenFill
currentCategoryCell.border = info.allBorders
# Write and format the category's amount
currentAmountCell.value = amount
currentAmountCell.fill = info.lightGreenFill
currentAmountCell.border = info.allBorders
currentAmountCell.number_format = info.accountingFormat
row += 1
def shiftMonthlyCategories(catBelow, categoryName):
# Get the last row number from the excel file
lastRow = info.getRowNum(info.monthSheetEq, 3, 1)
# Figure out the row number in the excel file that needs to be used for insertions
# The nth row from the 'Categories' cell
catBelowIndex = lastRow - catBelow - 1
# Read the category names and possible equations from catBelow to lastRow and store in a list
row = catBelowIndex
cutNames = []
cutAmts = []
while (row < lastRow + 6):
cutNames.append(info.monthSheetEq[row][1].value)
cutAmts.append(info.monthSheetEq[row][2].value)
row += 1
# Insert the new category
info.monthSheetEq[catBelowIndex][1].value = categoryName
info.monthSheetEq[catBelowIndex][2].value = None
# Write the categories and the amounts to the cell one row down
row = catBelowIndex + 1
ind = 0
while (row < lastRow + 7):
info.monthSheetEq[row][1].value = cutNames[ind]
info.monthSheetEq[row][2].value = cutAmts[ind]
ind += 1
row += 1
def updateEntryTable(monthSheetData, monthSheetEq, dataSetSheet):
# Get the last row number of the data
lastRow = info.getRowNum(monthSheetData, 24, 1) - 1
dataList = []
# Get each data row into a list and then add that list to dataList
for i in range(25, lastRow + 1):
dataRow = []
for j in range(1, 6):
dataRow.append(monthSheetData.cell(row=i, column=j).value)
dataList.append(dataRow)
nextOpen = info.getRowNum(dataSetSheet, 3, 4)
# Copy the dataList into the Data Set sheet and insert the MONTH() formula, row by row
for d in range(nextOpen, nextOpen + len(dataList)):
temp = dataList[d - nextOpen]
dataSetSheet.cell(row=d, column=4).value = "=MONTH(E" + str(d) + ")"
for c in range(5, 10):
dataSetSheet.cell(row=d, column=c).value = temp[c - 5]
# Date format
if (c == 5):
dataSetSheet.cell(row=d,
column=c).number_format = info.dateFormat
# Money format
if (c == 8):
dataSetSheet.cell(
row=d, column=c).number_format = info.accountingFormat
# Clear the entries table from the "Monthly" sheet
for i in range(25, lastRow + 1):
for j in range(1, 7):
monthSheetEq.cell(row=i, column=j).value = None
# Save the number of entries for checking purposes
info.numEntries = (lastRow + 1) - 25
def create_GUI(sheet, categoryList=None):
budgetCell = sheet[18][0]
spendingCell = sheet[info.getRowNum(sheet, 2, 1, "Spending Money")][2]
perDayCell = sheet[19][3]
# List of strings that holds the text
textList = ["Budget Set At:", "Spending Money:", "Remaining Per Day:"]
amountList = [budgetCell, spendingCell, perDayCell]
colors = ["yellow", 'lightgreen', 'seagreen']
# Labels for the first three (fixed) rows
for x in range(1, len(textList) + 1):
# Actual label
tk.Label(info.window, text=textList[x - 1],
font="Calibri 12 bold").grid(row=x,
column=0,
columnspan=2,
sticky=tk.W,
padx=5,
pady=5)
text = clean_values(amountList[x - 1])
if (text[2] == "-"):
color = "orangered"
else:
color = colors[x - 1]
# Add the amount
tk.Label(info.window,
text=text,
font="Calibri 12",
relief='solid',
bg=color,
width=20).grid(row=x,
column=2,
columnspan=2,
sticky=tk.E,
padx=5,
pady=5)
# Make the rest of the table for the non-fixed categories
numRows = create_category_table(sheet, categoryList)
return numRows
def shiftYearlyTotalsAverages(catBelow, categoryName):
# Get the last row number from the excel file
lastRow = info.getRowNum(info.yearSheetEq, 6, 11)
# Figure out the row number in the excel file that needs to be used for insertions
# The nth row down from the 'Categories' cell
catBelowIndex = lastRow - catBelow - 1
# Read the category names and possible equations from catBelow to lastRow and store in a list
row = catBelowIndex
cutTotalNames = []
cutTotalAmts = []
cutAvgNames = []
cutAvgAmts = []
while (row < lastRow + 3):
cutTotalNames.append(info.yearSheetEq[row][11].value)
cutTotalAmts.append(info.yearSheetEq[row][12].value)
cutAvgNames.append(info.yearSheetEq[row][14].value)
cutAvgAmts.append(info.yearSheetEq[row][15].value)
row += 1
# Insert the new category
# Total
info.yearSheetEq[catBelowIndex][11].value = categoryName
info.yearSheetEq[catBelowIndex][12].value = None
# Average
info.yearSheetEq[catBelowIndex][14].value = categoryName
info.yearSheetEq[catBelowIndex][15].value = None
# Write the categories and the amounts to the cell one row down
row = catBelowIndex + 1
ind = 0
while (row < lastRow + 3):
info.yearSheetEq[row][11].value = cutTotalNames[ind]
info.yearSheetEq[row][14].value = cutAvgNames[ind]
info.yearSheetEq[row][12].value = cutTotalAmts[ind]
info.yearSheetEq[row][15].value = cutAvgAmts[ind]
ind += 1
row += 1
def updateGUI():
# Re-load the workbook
newWbData = xl.load_workbook(info.excelFilePath, data_only=True)
# Update the Display Month
info.window.title("Budget GUI - " + info.month + " " + info.excelFileName)
# Loops through the category names starting with the rent cell
categoryList = []
# Load in the different categories
row = 3
col = 2
lastRow = info.getRowNum(newWbData["Monthly"], row, 1)
for x in range(lastRow - row):
categoryList.append(newWbData["Monthly"][row][1].value)
row += 1
# Re-create the GUI
create_GUI(newWbData['Monthly'], categoryList)
def submit():
# Reload the Monthly Data sheet to allow for multiple enrtries
newSheet = xl.load_workbook(info.excelFilePath, data_only=True)["Monthly"]
# Get the next open row in the entry table
openRow = info.getRowNum(newSheet, 24, 1)
# Loop through the entries list and handle each column accordingly
for col in range(len(entries)):
input = entries[col].get()
# Date format
if (col == 0):
if (input == "Today"):
info.monthSheetEq[openRow][col].value = datetime.datetime.now(
).date()
else:
month, day, year = input.split("/")
input = datetime.datetime((int)(year), (int)(month),
(int)(day))
info.monthSheetEq[openRow][col].value = input.date()
info.monthSheetEq[openRow][col].number_format = info.dateFormat
# Accounting format
elif (col == 3):
try:
info.monthSheetEq[openRow][col].value = (float)(input)
info.monthSheetEq[openRow][
col].number_format = info.accountingFormat
except:
createGUI.displayMessage(
input +
" is not a numeric value! Change the amount entered in Excel"
)
# Handle the abbreviated categories
elif (col == 4):
if (input == "Entertainment"):
input = "ENT"
elif (input == "Eating Out"):
input = "EO"
info.monthSheetEq[openRow][col].value = input
# Remaining
else:
info.monthSheetEq[openRow][col].value = input
# Save the file
try:
info.wbEq.save(info.excelFilePath)
# Inform the user
createGUI.displayMessage(
itemEntry.get() + " was added to the Entry Table on Monthly. \n\n"
"Due to limitations of Openpyxl you need to go \n in and save the excel file"
" in order for \n the main window to properly display the new amounts."
)
# Close the window
addWindow.withdraw()
except:
createGUI.displayMessage(
"The Excel workbook is already open, the entry was not saved")
# Close the file
info.wbEq.close()
# Update the main window
createGUI.updateGUI()
def shiftRowOfMonthsDown(leftMonth):
print(leftMonth)
# Given rightMonth
ind = info.months.index(leftMonth)
# Get the title cells
rightMonthCell = findMonthTitleCell(leftMonth)
midMonthCell = findMonthTitleCell(info.months[ind + 1])
leftMonthCell = findMonthTitleCell(info.months[ind + 2])
# Assign the correct shift amount
# As the bottom rows need to shift more than the upper rows
if (leftMonth == "October"):
shiftAmount = 3
elif (leftMonth == "July"):
shiftAmount = 2
elif (leftMonth == "April"):
shiftAmount = 1
else: # (leftMonth == "Janurary"):
return
# Update the info start cells
# info.cells[ind][0] += shiftAmount
# info.cells[ind+1][0] += shiftAmount
# info.cells[ind+2][0] += shiftAmount
# Get the last row num which should be the same for all three months
lastRow = info.getRowNum(info.yearSheetEq, leftMonthCell[0], 1)
rightMonthData = []
midMonthData = []
leftMonthData = []
currentRow = leftMonthCell[0]
# Save the data into lists and clear the cells
while (currentRow < lastRow):
# Right
rightMonthData.append([
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1]).value,
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1] + 1).value
])
# Mid
midMonthData.append([
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1]).value,
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1] + 1).value
])
# Left
leftMonthData.append([
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1]).value,
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1] + 1).value
])
currentRow += 1
# Shift down the appropriate amount
currentRow = leftMonthCell[0] + shiftAmount
lastRow += shiftAmount
ind = 0
# Re-insert all the data from the three lists
while (currentRow < lastRow):
# Values
# Right
info.yearSheetEq.cell(
row=currentRow,
column=rightMonthCell[1]).value = rightMonthData[ind][0]
info.yearSheetEq.cell(row=currentRow, column=rightMonthCell[1] +
1).value = rightMonthData[ind][1]
# Mid
info.yearSheetEq.cell(
row=currentRow,
column=midMonthCell[1]).value = midMonthData[ind][0]
info.yearSheetEq.cell(row=currentRow, column=midMonthCell[1] +
1).value = midMonthData[ind][1]
# Left
info.yearSheetEq.cell(
row=currentRow,
column=leftMonthCell[1]).value = leftMonthData[ind][0]
info.yearSheetEq.cell(row=currentRow, column=leftMonthCell[1] +
1).value = leftMonthData[ind][1]
ind += 1
currentRow += 1
# Clear the cells that were just copied and moved down
currentRow = leftMonthCell[0]
lastRow = leftMonthCell[0] + shiftAmount
while (currentRow < lastRow):
# Values
# Right
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1]).value = None
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1] + 1).value = None
# Mid
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1]).value = None
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1] + 1).value = None
# Left
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1]).value = None
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1] + 1).value = None
# Fill
# Right
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1]).fill = info.noFill
info.yearSheetEq.cell(row=currentRow,
column=rightMonthCell[1] + 1).fill = info.noFill
# Mid
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1]).fill = info.noFill
info.yearSheetEq.cell(row=currentRow,
column=midMonthCell[1] + 1).fill = info.noFill
# Left
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1]).fill = info.noFill
info.yearSheetEq.cell(row=currentRow,
column=leftMonthCell[1] + 1).fill = info.noFill
currentRow += 1
def shiftAllMonths(catBelow, categoryName):
# Reverse the months list from info
reverseMonths = info.months[::-1]
x = 1
for r in reverseMonths:
# Get the actual title cell for each month
# Returns the cell for the first amount cell, ex (Rent:_____)
revRow, revCol = findMonthTitleCell(r)
revCol -= 1
# Get the last row number from the excel file
lastRow = info.getRowNum(info.yearSheetEq, revRow, 1)
# Need a different catBelowIndex because last row is different for some months
if (x != 1 or x != 4 or x != 7 or x != 10):
# Figure out the row number in the excel file that needs to be used for insertions
# The nth row from the 'Categories' cell
catBelowIndex = lastRow - catBelow - 1
else:
catBelowIndex = lastRow - catBelow
# Read the category names and possible equations from catBelow to lastRow and store in a list
currentRow = catBelowIndex
cutNames = []
cutAmts = []
while (currentRow <= lastRow):
# Prevents 0-indexing the January row
if (currentRow == 0):
catBelowIndex = 1
lastRow += 1
cutNames.append(info.yearSheetEq[currentRow][revCol].value)
cutAmts.append(info.yearSheetEq[currentRow][revCol + 1].value)
currentRow += 1
# Insert the new category
info.yearSheetEq[catBelowIndex][revCol].value = categoryName
info.yearSheetEq[catBelowIndex][revCol + 1].value = None
# Write the categories and the amounts to the cell one row down
# row and lastRow need to be shifted down one
row = catBelowIndex + 1
lastRow += 1
ind = 0
# Insert the saved data
while (row <= lastRow):
info.yearSheetEq[row][revCol].value = cutNames[ind]
info.yearSheetEq[row][revCol + 1].value = cutAmts[ind]
ind += 1
row += 1
# Shift the entire row down only when we reach the left month in the row
if (x % 3 == 0):
shiftRowOfMonthsDown(r)
x += 1