Trying to add values to the next empty cell in Excel using Python 3.11

Question

I'm relatively new to python and have picked it up as a hobby. I've been trying to make a password manager but to save variables I need to access excel files and copy variable data into the next empty row in columns A and B.

I have currently tried this to no avail:

value3 = v3.get()
value2 = v2.get()

folderPath = "C:\Password Manager"
if not os.path.exists(folderPath):
    os.makedirs(folderPath)
filename = "Encrypted_Passwords.xlsx"
filePath = os.path.join(folderPath, filename)
print(filePath)
open(filePath, "w+")
wb = openpyxl.load_workbook(filePath)
ws = wb.active
for i in range (ws.max_row):
    if ws.cell(row=i, column=1).value == None:
        ws.cell(row=i, column=1).value = value2
        ws.cell(row=i, column=2).value = value3

Any help would be greatly appreciated, have been trying to figure this out for days.

Answer 1

Here is the openpyxl doc on worksheet.max_row: worksheet.max_row

If you want to write value to empty row, you need to start from rowIndex = ws.max_row + 1. not looping from 0 to ws.max_row.

e.g below will work:

       ws.cell(row=ws.max_row + 1, column=1).value = value2
       ws.cell(row=ws.max_row + 1, column=2).value = value3

If you really need a for loop to check each row, you can just change for i in range(ws.max_row) to for i in range(1, 1000):

import openpyxl
import os

value3 = v3.get()
value2 = v2.get()

folderPath = "C:\Password Manager"
# if not os.path.exists(folderPath):
#     os.makedirs(folderPath)
filename = "Encrypted_Passwords.xlsx"
filePath = os.path.join(folderPath, filename)
print(filePath)
# open(filePath, "w+")
wb = openpyxl.load_workbook(filePath)
ws = wb.active

for i in range(1, 1000):
    if ws.cell(row=i, column=1).value is None:
        ws.cell(row=i, column=1).value = value2
        ws.cell(row=i, column=2).value = value3
        break

wb.save(filePath)

If you need to input a list of passwords into column A and column B alternatively, you can try below for loop:

import openpyxl
import os

passwords = ["password1", "password2", "passw@rd3", "password4", "passowrd5"]

folderPath = "C:\Password Manager"
filename = "Encrypted_Passwords.xlsx"
filePath = os.path.join(folderPath, filename)
print(filePath)
wb = openpyxl.load_workbook(filePath)
ws = wb.active

rowIdx = ws.max_row + 1
for i in range(0, len(passwords), 2):
    ws.cell(row=rowIdx, column=1).value = passwords[i]
    if i >= len(passwords) - 1:
        break
    ws.cell(row=rowIdx, column=2).value = passwords[i+1]
    rowIdx += 1

wb.save(filePath)

Answer 2

If you use Openpyxl 'append' it will always add the values to the next unused row in your Spreadsheet.

E.g. Run the code as many times you like and the value2 & value3 values will be appended to the end of the sheet each time.

import os
import openpyxl

folderPath = "C:\Password Manager"
filename = "Encrypted_Passwords.xlsx"

value3 = 'password1'
value2 = 'password2'

if not os.path.exists(folderPath):
    os.makedirs(folderPath)
filename = "Encrypted_Passwords.xlsx"
filePath = os.path.join(folderPath, filename)
print(filePath)
wb = openpyxl.load_workbook(filePath)
ws = wb.active

### Append the values as a list
ws.append([value3, value2])

wb.save(filePath)

Openpyxl append requires the values to be in a list (or a dictionary). When using a list each element is inserted into the next column, so for [value3, value2] value3 in inserted in column A the value2 into column B and so on for all the elements in the list. And always on the next free row.

FYI ws.max_row and append use the 'max row' as the last row with any cell with any value that is not (Python) None. So if you have only filled column A to 5 rows but Column Z has filled 10 rows then the next unused row will be row 11 (ws.max_row will equal 10).