How do I get user input into an excel spreadsheet via input() either in a csv or xlsx spreadsheet?

Question

So far, I have been able to access csv and xlsx files in python, but I am unsure how to put in user inputs input() to add data to the spreadsheet.

I would also want this input() to only be enterable once per day but for different columns in my spreadsheet. (this is a separate issue)

Here is my code so far, first for csv, second for xlsx, I don't need both just either will do:

# writing to a CSV file

import csv
def main():
    filename = "EdProjDBeg.csv"
    header = ("Ans1", "Ans2", "Ans3")
    data = [(0, 0, 0)]
    writer(header, data, filename, "write")
    updater(filename)


def writer(header, data, filename, option):
    with open(filename, "w", newline = "") as csvfile:
        if option == "write":
            clidata = csv.writer(csvfile)
            clidata.writerow(header)
            for x in data:
                clidata.writerow(x)
        elif option == "update":
            writer = csv.DictWriter(csvfile, fieldnames = header)
            writer.writeheader()
            writer.writerows(data)
        else:
            print("Option is not known")



# Updating the CSV files with new data

def updater(filename):
    with open(filename, newline= "") as file:
        readData = [row for row in csv.DictReader(file)]
        readData[0]['Ans2'] = 0

    readHeader = readData[0].keys()
    writer(readHeader, readData, filename, "update")


# Reading and updating xlsx files

import openpyxl

theFile = openpyxl.load_workbook(r'C:\Users\joe_h\OneDrive\Documents\Data Analysis STUDYING\Excel\EdProjDBeg.xlsx')
print(theFile.sheetnames)
currentsheet = theFile['Customer1']
print(currentsheet['B3'].value)


wb = openpyxl.load_workbook(r'C:\Users\joe_h\OneDrive\Documents\Data Analysis STUDYING\Excel\EdProjDBeg.xlsx')
ws = wb.active
i = 0
cell_val = ''
# Finds which row is blank first
while cell_val != '':
    cell_val = ws['A' + i].value
    i += 1
# Modify Sheet, Starting With Row i
wb.save(r'C:\Users\joe_h\OneDrive\Documents\Data Analysis STUDYING\Excel\EdProjDBeg.xlsx')

x = input('Prompt: ')

Dharman · Accepted Answer · 2021-01-10 23:39:05Z

1

This works for inputting data into an xlsx file.

Just use:

ws['A1'] = "data"

to input into cell A1

See code below for example using your original code:


wb = openpyxl.load_workbook('sample.xlsx')
print(wb.sheetnames)
currentsheet = wb['Sheet']


ws = currentsheet
#ws = wb.active <-- defaults to first sheet

i = 0
cell_val = ''
# Finds which row is blank first
while cell_val != None:
    i += 1
    cell_val = ws['A' + str(i)].value
    print(cell_val)

x = input('Prompt: ')

#sets A column of first blank row to be user input
ws['A' + str(i)] = x

#saves spreadsheet
wb.save("sample.xlsx")

Also just made a few edits to your original while loop in the above code:

When a cell is blank, 'None' is returned
A1 is the first cell on the left, not A0 (moved i += 1 above finding value of cell)
Converted variable 'i' to a string when accessing the cell

See https://openpyxl.readthedocs.io/en/stable/ for the full documentation

edited Jan 10, 2021 at 23:39

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jan 10, 2021 at 23:33

John Skeen

2231 gold badge7 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I get user input into an excel spreadsheet via input() either in a csv or xlsx spreadsheet?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related