Save user input to a Excel File in Python
In this article, we will learn how to store user input in an excel sheet using Python,
What is Excel?
Excel is a spreadsheet in a computer application that is designed to add, display, analyze, organize, and manipulate data arranged in rows and columns. It is the most popular application for accounting, analytics, data presentation, etc.
Methods used for taking user input in an excel sheet using Python
Method 1: Using CSV module
CSV Module: CSV stands for Comma Separated Values and it is the most common import and export format for spreadsheets and databases. Python CSV module implements classes to read and write tabular data in CSV format since CSV is inbuilt and comes with the default installation of Python we do not need to install it.
Step-by-step implementation:
Step 1: Import CSV module
import csv
Step 2: Open the excel file using file handling
file = open('mydata.xlsx', 'w')
Note: We can open files in write mode or append mode according to your requirement to learn more about file handling in Python refer to this article.
Step 3: Create a CSV writer object by calling the writer() function of the CSV module
csv.writer() method returns a writer object responsible for converting the user’s data into delimited strings on the given file-like object.
file = csv.writer(file)
Syntax: csv.writer(csvfile, dialect=’excel’, **fmtparams)
Parameters:
- csvfile: A file object with write() method.
- dialect (optional): Name of the dialect to be used.
- fmtparams (optional): Formatting parameters that will overwrite those specified in the dialect.
Step 4: We are taking a name, age and enrollment number from the user hence making it as user input and storing them in variables
name = input(f'{i+1}. Enter name: ')
age = input(f'{i+1}. Enter age: ')
enroll = input(f'{i+1}. Enter Enrollment number: ')
Step 5: Write the data into an excel file by using the writerow() method
file.writerow([name, age, enroll])
By using the writerow() method it writes the row parameter to the writer’s file object, formatted according to the current Dialect.
Syntax: csvwriter.writerow(row)
Parameters:
- row: A sequence(list) of data that need to be written
Complete Code:
- Python3
Python3
import csvfile = open('mydata.xlsx', 'w')file = csv.writer(file)file.writerow(['Name', 'Age', 'Enrollment Number'])n = int(input('How many records you want to insert: '))for i in range(n): name = input(f'{i+1}. Enter name: ') age = input(f'{i+1}. Enter age: ') enroll = input(f'{i+1}. Enter Enrollment number: ') file.writerow([name, age, enroll]) print()print('All records inserted successfully !') |
Output:
Method 2: Using openpyxl module
openpyxl module: Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python programs to read and modify Excel files.
Step-by-step implementation:
Step 1: Import openpyxl module
import openpyxl
Step 2: Load the workbook
- Open the given file and returns the workbook
wBook = openpyxl.load_workbook('filename.xlsx')Syntax: load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True)
Parameters:
- filename (string or a file-like object open in binary mode c.f., zipfile.ZipFile) – the path to open or a file-like object
- read_only (bool) (OPTIONAL)– optimised for reading, content cannot be edited
- keep_vba (bool) (OPTIONAL)– preseve vba content (this does NOT mean you can use it)
- data_only (bool) (OPTIONAL)– controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet
- keep_links (bool) (OPTIONAL)– whether links to external workbooks should be preserved. The default is True
Return type: openpyxl.workbook.Workbook
Step 3: Specify the sheet in which we are going to enter the data. Since we have only one sheet, we can just use the active keyword to use the currently active sheet to enter the data.
sheet = wBook.activate
In case you have multiple sheets, you have to mention the name of the worksheet like follows:
sheet = wBook["SheetName"]
Step 4: Take the input from the user and store it in a list
name = input(f'{i+1}. Enter name: ')
age = input(f'{i+1}. Enter age: ')
enroll = input(f'{i+1}. Enter Enrollment number: ')
data = [name,age,enroll]
Step 5: To append a group of values at the bottom of the current sheet, can be done by append() method.
sheet.append([name,age,enroll])
Syntax: append(iterable)
Parameters:
- iterable: Object that contains a countable number of value like list, tuple, set, etc
Step 6: Save the changes using save() method in the worksheet data
wBook.save('mydata.xlsx')Syntax: save(filepath)
Parameters:
- filepath: path to which you want to save the workbook.
Complete code:
- Python3
Python3
import openpyxlwBook = openpyxl.load_workbook('mydata.xlsx')sheet = wBook.activen = int(input('How many records you want to insert: '))for i in range(n): name = input(f'{i+1}. Enter name: ') age = input(f'{i+1}. Enter age: ') enroll = input(f'{i+1}. Enter Enrollment number: ') data = [name, age, enroll] sheet.append(data) wBook.save('mydata.xlsx')print('All records inserted successfully !') |
Output:
