Connecting Excel with Python

Recently, Microsoft is considering adding Python as one of the official Excel scripting languages, according to a topic on Excel feedback hub.

Openpyxl

Let’s learn what’s there in this python package of excel !!

Openpyxl

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files. It was born from a lack of an existing library to read/write natively from Python the Office Open XML format.

Now, Let us Setup this module and install it to know what it can do in python :

pip install openpyxl

And we will also create a GUI of Form from Tkinter to collab the excel and see the output in excel.

After installing this module, now it’s time for our coding session :

from openpyxl import *
from tkinter import *

This is an import statement to make invoke to excel

After this create an empty excel file in your project folder. For.eg:- C: -> PythonProject -> Empty excel file and .py file .

wb = load_workbook('<Your Excel File Path>')

Here, We have globally declared the wb variable under that load_workbook (used for loading excel book) and given the empty excel file path where our all data will be taken and placed under the excel sheet block.

sheet = wb.active

I have created a variable named as a sheet and under that, I have called wb. active this will start/activate the excel file which we have given in the wb variable.

def excel(): 
 
 sheet.column_dimensions['A'].width = 30
 sheet.column_dimensions['B'].width = 10
 sheet.column_dimensions['C'].width = 10
 sheet.column_dimensions['D'].width = 20
 sheet.column_dimensions['E'].width = 20
 sheet.column_dimensions['F'].width = 40
 sheet.column_dimensions['G'].width = 50
sheet.cell(row=1, column=1).value = "Name"
 sheet.cell(row=1, column=2).value = "Course"
 sheet.cell(row=1, column=3).value = "Semester"
 sheet.cell(row=1, column=4).value = "Form Number"
 sheet.cell(row=1, column=5).value = "Contact Number"
 sheet.cell(row=1, column=6).value = "Email id"
 sheet.cell(row=1, column=7).value = "Address"

Here I have created a function and named it to excel, under this, I have written this line

sheet.column_dimensions[‘A’].width = 30

this will resize the width of the column in the excel spreadsheet and after that
i have written in same for other also with different sizes . Now, we have to give the name of each column at their particular location like this

sheet.cell(row=1, column=1).value = “Name”

and as follow for other also .

def focus1(event): 
 course_field.focus_set()
def focus2(event): 
 sem_field.focus_set() 
 
def focus3(event):
 form_no_field.focus_set()
def focus4(event):
 contact_no_field.focus_set()
def focus5(event): 
 email_id_field.focus_set()
def focus6(event): 
 address_field.focus_set()

This function name focus1 will set focus on the field named as course and return the event value in it. And, We have to do this for Semester, Form no, contact, email id, and Address also.

def clear(): 
 
 name_field.delete(0, END) 
 course_field.delete(0, END) 
 sem_field.delete(0, END) 
 form_no_field.delete(0, END) 
 contact_no_field.delete(0, END) 
 email_id_field.delete(0, END) 
 address_field.delete(0, END)

This function is created to clear all contents of the text entry box. This uses the .delete method by passing parameters 0 and END.

def insert():
if (name_field.get() == "" and
  course_field.get() == "" and
  sem_field.get() == "" and
  form_no_field.get() == "" and
  contact_no_field.get() == "" and
  email_id_field.get() == "" and
  address_field.get() == ""): 
   
  print("empty input")
else:
current_row = sheet.max_row 
  current_column = sheet.max_column
# excel spreadsheet at particular location 
  sheet.cell(row=current_row + 1, column=1).value = name_field.get() 
  sheet.cell(row=current_row + 1, column=2).value = course_field.get() 
  sheet.cell(row=current_row + 1, column=3).value = sem_field.get() 
  sheet.cell(row=current_row + 1, column=4).value = form_no_field.get() 
  sheet.cell(row=current_row + 1, column=5).value = contact_no_field.get() 
  sheet.cell(row=current_row + 1, column=6).value = email_id_field.get() 
  sheet.cell(row=current_row + 1, column=7).value = address_field.get()
wb.save('C:\\Users\\dell\\OneDrive\\Desktop\\Python project\\DataWithExcel\\excel.xlsx') 
 
  name_field.focus_set() 
 
  clear()

This function named as the insert is to take data from GUI window and write it in excel file, In this function, we have created a condition where if statement contains that if every field are empty then it will print the given statement otherwise it will go to else statement where we have to declare the max row and column value where data is written in an excel sheet to the variable (current_row,current_column).

In else part we have given line as:

sheet.cell(row=current_row + 1, column=1).value = name_field.get()

This line will work to get the date by the .get method and it will return as a string which we will write in an excel spreadsheet at a particular location
Then we have to save the file so we have written :

wb.save('C:\\Users\\dell\\OneDrive\\Desktop\\Python project\\DataWithExcel\\excel.xlsx')

This line will automatically save the excel file.

After this the line we have written is :

name_field.focus_set()

This will make the first focus on the name field in the default way.

Then the last line is invoking the clear() function to clear the content and make GUI clear.

Now, it’s time to make GUI !!!

if __name__ == "__main__":
root = Tk() 
 root.configure(background='light green') 
 root.title("registration form") 
 root.geometry("500x300") 
 excel()
heading = Label(root, text="Form", bg="light green") 
 name = Label(root, text="Name", bg="light green") 
 course = Label(root, text="Course", bg="light green") 
 sem = Label(root, text="Semester", bg="light green") 
 form_no = Label(root, text="Form No.", bg="light green") 
 contact_no = Label(root, text="Contact No.", bg="light green") 
 email_id = Label(root, text="Email id", bg="light green") 
 address = Label(root, text="Address", bg="light green")
heading.grid(row=0, column=1) 
 name.grid(row=1, column=0) 
 course.grid(row=2, column=0) 
 sem.grid(row=3, column=0) 
 form_no.grid(row=4, column=0) 
 contact_no.grid(row=5, column=0) 
 email_id.grid(row=6, column=0) 
 address.grid(row=7, column=0)
name_field = Entry(root) 
 course_field = Entry(root) 
 sem_field = Entry(root) 
 form_no_field = Entry(root) 
 contact_no_field = Entry(root) 
 email_id_field = Entry(root) 
 address_field = Entry(root)
name_field.bind("<Return>", focus1) 
 course_field.bind("<Return>", focus2) 
 sem_field.bind("<Return>", focus3) 
 form_no_field.bind("<Return>", focus4) 
 contact_no_field.bind("<Return>", focus5) 
 email_id_field.bind("<Return>", focus6)
name_field.grid(row=1, column=1, ipadx="100") 
 course_field.grid(row=2, column=1, ipadx="100") 
 sem_field.grid(row=3, column=1, ipadx="100") 
 form_no_field.grid(row=4, column=1, ipadx="100") 
 contact_no_field.grid(row=5, column=1, ipadx="100") 
 email_id_field.grid(row=6, column=1, ipadx="100") 
 address_field.grid(row=7, column=1, ipadx="100")
excel()
submit = Button(root, text="Submit", fg="Black", bg="Red", command=insert) 
 submit.grid(row=8, column=1)
root.mainloop()

I have created the main function that’s also called Driver code in python where I have to initialize the root of tkinter with the configuration of background color, title, and their windows geometry(height and width).
After this, I invoke the excel function and initialize after I initializing the root then created labels of name, sem, course ,form_no ,contact_no ,email_id and address with header label(For GUI heading).

Now to adjust all labels at the structure so I have used the .grid method for giving rows and columns to labels then we have to create an entry box where we have to write the information.

After this all now we have to bind all the function with events whenever the button is pressed:

name_field.bind("<Return>", focus1)

like this, it will become invokable with function and we have to do for other also.

Then again we have to create a .grid method for giving positions to the entry box data.

Now after all this we just have to create a submit button and give it the command that it should invoke the insert function in it and they give the .grid method to give its position. And Lastly root. mainloop(), to destroy our GUI Form.

Gist of Code

Let’s see the output in our excel file :

Output in excel file

For any queries contact at INSTAGRAM.

Hope you have liked this blog! Now you can make your own excel file with any data in it 😎.