How to Work with Excel Spreadsheets in Python

Excel is a go-to tool for managing data, but what if you could supercharge your workflow by combining it with Python? In this tutorial, we’ll explore how to work with Excel spreadsheets in Python. We’ll cover everything from creating and modifying workbooks to automating tasks like data entry and formatting. Whether you’re a data enthusiast or just love finding efficient solutions, this article will help you get the most out of your Excel spreadsheets with Python.

We’ll be using libraries like tkinter to build user-friendly interfaces and openpyxl to handle Excel files. With these tools, you’ll be ready to dive in and transform your spreadsheet work in no time. Let’s get started!

Necessary Libraries
Imports
Initializing Variables and Creating a New Workbook
Loading an Existing Workbook
Updating a Specific Cell and Adding a New Row
Saving the Workbook
Adding a Table
Setting Up the Main Window
Example
Full Code

Necessary Libraries

To get this code up and running smoothly, you’ll need to install a few libraries in your terminal or command prompt:

$ pip install tk 
$ pip install openpyxl

Imports

Before we dive into today’s journey, let’s get familiar with the libraries we’ll be using. These are our trusty tools for the job:

First up is tkinter, which helps us create a user-friendly graphical interface. With tkinter, we can not only design and save files using filedialog, but also display information and errors through handy pop-up messages with messagebox.

import tkinter as tk
from tkinter import filedialog, messagebox

Since we’ll be working with Excel files (.xlsx), the openpyxl library is a must-have. This library lets us handle everything from creating new spreadsheets to modifying existing ones.

import openpyxl

We’ll also make use of Font and PatternFill to add a touch of style to our Excel documents—think bold text or colorful cell backgrounds.

from openpyxl.styles import Font, PatternFill

To convert column numbers into letters (like turning column 1 into ‘A’), we’ll use the get_column_letter function.

from openpyxl.utils import get_column_letter

Lastly, Table and TableStyleInfo will help us not just create tables but also style them to look great in Excel.

from openpyxl.worksheet.table import Table, TableStyleInfo

With these tools in hand, we’re all set to make our Excel tasks a breeze!

Initializing Variables and Creating a New Workbook

Firstly, to keep our code well-organized, we need a method for managing our Excel workbook and the active sheet. This involves using global variables to store these elements, which will be updated once we create or load an Excel file.

workbook = None
sheet = None

Now that we’ve established our variables, we can proceed to creating the Excel workbook if it doesn’t already exist. The create_new_workbook() function takes care of this for us. First, it sets up the global variables workbook and sheet so we can use them throughout our program.

def create_new_workbook():
   global workbook, sheet

It then creates a new Excel workbook with openpyxl.Workbook() and stores it in the workbook variable.

   workbook = openpyxl.Workbook()

Next, the function retrieves the first sheet from this workbook and assigns it to the sheet variable. It also sets the title of this sheet to “Sheet1“.

   sheet = workbook.active
   sheet.title = "Sheet1"

Finally, it shows a pop-up message to let us know that the new workbook and sheet were created successfully.

   messagebox.showinfo("Create Workbook", "New workbook created successfully.")

So, with this function, we get our workbook and sheet ready to use and receive a confirmation that everything went smoothly!

Loading an Existing Workbook

However, if you have an existing Excel workbook that you want to modify, you can easily load it using the load_workbook() function. This function starts by opening a file dialog to let the user select an Excel file.

def load_workbook():
   file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
   if not file_path:
       return

If the user selects a file, the function then loads this file into the workbook variable using openpyxl.load_workbook().

   global workbook, sheet
   workbook = openpyxl.load_workbook(file_path)

The function also retrieves the first sheet from the loaded workbook and assigns it to the sheet variable.

   sheet = workbook.active

After successfully loading the workbook and selecting the sheet, a pop-up message informs the user of the successful operation.

   messagebox.showinfo("Load Workbook", f"Workbook '{file_path}' loaded successfully.")

If no Excel file is selected, the function simply exits without making any changes.

   if not file_path:
       return

This way, you can easily load and work with existing Excel files, and get a confirmation message once the file is successfully loaded and ready for use.

Updating a Specific Cell and Adding a New Row

Now that we’ve got our Excel workbook ready, it’s time to make some updates. First up, we’ll use the update_cell() function to change the content of a specific cell. This function checks if the workbook is loaded. If it is, it grabs the cell reference and new value from the entry boxes we’ve set up. It then updates the cell with the new value, making the text bold and red, and gives the cell a yellow background.

Once the update is complete, we’ll get a pop-up to let us know everything went smoothly. If there’s no workbook loaded, though, we’ll see an error message instead.

def update_cell():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   cell = cell_entry.get()
   value = value_entry.get()
   if cell and value:
       sheet[cell] = value
       sheet[cell].font = Font(bold=True, color="FF0000")
       sheet[cell].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
       messagebox.showinfo("Update Cell", f"Cell {cell} updated with value '{value}'.")

Updating cells isn’t the only thing we can do; we can also add new rows to our workbook. The add_row() function takes care of this. It starts by checking if a workbook is loaded, and if not, it shows an error message. Then, it grabs the comma-separated values from the entry box with row_entry.get().split().

The function inserts a new row at position 2 using sheet.insert_rows(2). After that, it loops through the values and fills them into the new row. Finally, it celebrates the success with a pop-up message to let you know everything went smoothly.

def add_row():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   row_values = row_entry.get().split(',')
   sheet.insert_rows(2)
   for col_num, data in enumerate(row_values, 1):
       sheet.cell(row=2, column=col_num).value = data
   messagebox.showinfo("Add Row", "Row added successfully.")

Saving the Workbook

Once we’ve made all our changes, saving them to a file is the natural next step. We’ll use the save_workbook() function for this. First, it checks if we have a workbook loaded; if not, it gives us a heads-up with an error message. Then, it tidies up by going through every row and cell, swapping out any empty strings for None.

Next, a file dialog pops up, letting us pick where to save our workbook and what to name it. Once we’ve made our choice, the function saves the workbook to that location. A cheerful pop-up message will confirm that everything was saved successfully. If anything goes wrong during this process, we’ll get an error message to let us know.

def save_workbook():
   if workbook is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   # Before saving, ensure that all cell values are properly set and that there are no issues with the table range
   try:
       for row in sheet.iter_rows():
           for cell in row:
               if isinstance(cell.value, str) and cell.value.strip() == "":
                   cell.value = None  # Replace empty strings with None


       save_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")])
       if save_path:
           workbook.save(save_path)
           messagebox.showinfo("Save Workbook", f"Workbook saved as '{save_path}'.")
   except Exception as e:
       messagebox.showerror("Save Error", f"An error occurred while saving the workbook: {e}")

Adding a Table

Tables are a great way to organize data in Excel, which is why we’ve created the add_table() function. This function starts by checking if a workbook is loaded or created, and if not, it shows an error message. It then loops through the first row to grab column headers, ensuring each header is a string.

Once the headers are gathered, the function calculates the table’s range by determining which columns and rows will be included. It then creates the table using Table(displayName="DataTable", ref=table_range). After the table is created, it applies some styling and adds it to the Excel sheet with sheet.add_table(table). A pop-up message will let you know that the table has been added successfully. If something goes wrong, an error message will appear to inform you.

def add_table():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   try:
       # Ensure headers are strings
       for col in range(1, sheet.max_column + 1):
           header = sheet.cell(row=1, column=col).value
           if not isinstance(header, str):
               sheet.cell(row=1, column=col).value = str(header)


       table_range = f'A1:{get_column_letter(sheet.max_column)}{sheet.max_row}'
       table = Table(displayName="DataTable", ref=table_range)
       style = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True, showColumnStripes=True)
       table.tableStyleInfo = style
       sheet.add_table(table)
       messagebox.showinfo("Add Table", "Table added successfully.")
   except Exception as e:
       messagebox.showerror("Table Error", f"An error occurred while adding the table: {e}")

Setting Up the Main Window

This is where we bring everything together into a single graphical interface:

First, we set up the interface using tk, giving it a title:

# Initialize Tkinter
root = tk.Tk()
root.title("Excel Modifier - The Pycodes")

Next, we create a frame to hold all our widgets:

# Create new workbook button
create_button = tk.Button(root, text="Create New Workbook", command=create_new_workbook)
create_button.pack(pady=5)

We add a “Create Workbook” button that triggers the create_new_workbook() function, a “Load Workbook” button for the load_workbook() function, and a “Save Workbook” button for the save_workbook() function:

# Load workbook button
load_button = tk.Button(root, text="Load Workbook", command=load_workbook)
load_button.pack(pady=5)

# Save workbook button
save_button = tk.Button(root, text="Save Workbook", command=save_workbook)
save_button.pack(pady=5)

We also add entry boxes for user inputs and label them accordingly:

# Update cell frame
update_frame = tk.Frame(root)
update_frame.pack(pady=5)

tk.Label(update_frame, text="Cell (e.g., A1):").pack(side=tk.LEFT)
cell_entry = tk.Entry(update_frame)
cell_entry.pack(side=tk.LEFT, padx=5)

tk.Label(update_frame, text="Value:").pack(side=tk.LEFT)
value_entry = tk.Entry(update_frame)
value_entry.pack(side=tk.LEFT, padx=5)

Following that, we introduce an “Update Cell” button linked to the update_cell() function, an “Add Row” button for the add_row() function, and an “Add Table” button that connects to the add_table() function:

update_button = tk.Button(update_frame, text="Update Cell", command=update_cell)
update_button.pack(side=tk.LEFT, padx=5)

# Add row frame
row_frame = tk.Frame(root)
row_frame.pack(pady=5)

tk.Label(row_frame, text="Row Values (comma-separated):").pack(side=tk.LEFT)
row_entry = tk.Entry(row_frame, width=50)
row_entry.pack(side=tk.LEFT, padx=5)

row_button = tk.Button(row_frame, text="Add Row", command=add_row)
row_button.pack(side=tk.LEFT, padx=5)

# Add table button
table_button = tk.Button(root, text="Add Table", command=add_table)
table_button.pack(pady=5)

To wrap things up, we kick off with mainloop(). This keeps the window open and responsive, allowing you to interact with all the buttons and features we’ve set up. It’s like setting the stage for your Excel-modifying adventure to begin!

# Run the Tkinter event loop
root.mainloop()

Example

Full Code

View on Github

import tkinter as tk
from tkinter import filedialog, messagebox
import openpyxl
from openpyxl.styles import Font, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo


workbook = None
sheet = None



def create_new_workbook():
   global workbook, sheet
   workbook = openpyxl.Workbook()
   sheet = workbook.active
   sheet.title = "Sheet1"
   messagebox.showinfo("Create Workbook", "New workbook created successfully.")




def load_workbook():
   file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
   if not file_path:
       return


   global workbook, sheet
   workbook = openpyxl.load_workbook(file_path)
   sheet = workbook.active
   messagebox.showinfo("Load Workbook", f"Workbook '{file_path}' loaded successfully.")



def update_cell():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   cell = cell_entry.get()
   value = value_entry.get()
   if cell and value:
       sheet[cell] = value
       sheet[cell].font = Font(bold=True, color="FF0000")
       sheet[cell].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
       messagebox.showinfo("Update Cell", f"Cell {cell} updated with value '{value}'.")



def add_row():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   row_values = row_entry.get().split(',')
   sheet.insert_rows(2)
   for col_num, data in enumerate(row_values, 1):
       sheet.cell(row=2, column=col_num).value = data
   messagebox.showinfo("Add Row", "Row added successfully.")



def save_workbook():
   if workbook is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   # Before saving, ensure that all cell values are properly set and that there are no issues with the table range
   try:
       for row in sheet.iter_rows():
           for cell in row:
               if isinstance(cell.value, str) and cell.value.strip() == "":
                   cell.value = None  # Replace empty strings with None


       save_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")])
       if save_path:
           workbook.save(save_path)
           messagebox.showinfo("Save Workbook", f"Workbook saved as '{save_path}'.")
   except Exception as e:
       messagebox.showerror("Save Error", f"An error occurred while saving the workbook: {e}")



def add_table():
   if sheet is None:
       messagebox.showwarning("Error", "No workbook is loaded or created.")
       return


   try:
       # Ensure headers are strings
       for col in range(1, sheet.max_column + 1):
           header = sheet.cell(row=1, column=col).value
           if not isinstance(header, str):
               sheet.cell(row=1, column=col).value = str(header)


       table_range = f'A1:{get_column_letter(sheet.max_column)}{sheet.max_row}'
       table = Table(displayName="DataTable", ref=table_range)
       style = TableStyleInfo(name="TableStyleMedium9", showRowStripes=True, showColumnStripes=True)
       table.tableStyleInfo = style
       sheet.add_table(table)
       messagebox.showinfo("Add Table", "Table added successfully.")
   except Exception as e:
       messagebox.showerror("Table Error", f"An error occurred while adding the table: {e}")



# Initialize Tkinter
root = tk.Tk()
root.title("Excel Modifier - The Pycodes")


# Create new workbook button
create_button = tk.Button(root, text="Create New Workbook", command=create_new_workbook)
create_button.pack(pady=5)


# Load workbook button
load_button = tk.Button(root, text="Load Workbook", command=load_workbook)
load_button.pack(pady=5)


# Update cell frame
update_frame = tk.Frame(root)
update_frame.pack(pady=5)


tk.Label(update_frame, text="Cell (e.g., A1):").pack(side=tk.LEFT)
cell_entry = tk.Entry(update_frame)
cell_entry.pack(side=tk.LEFT, padx=5)


tk.Label(update_frame, text="Value:").pack(side=tk.LEFT)
value_entry = tk.Entry(update_frame)
value_entry.pack(side=tk.LEFT, padx=5)


update_button = tk.Button(update_frame, text="Update Cell", command=update_cell)
update_button.pack(side=tk.LEFT, padx=5)


# Add row frame
row_frame = tk.Frame(root)
row_frame.pack(pady=5)


tk.Label(row_frame, text="Row Values (comma-separated):").pack(side=tk.LEFT)
row_entry = tk.Entry(row_frame, width=50)
row_entry.pack(side=tk.LEFT, padx=5)


row_button = tk.Button(row_frame, text="Add Row", command=add_row)
row_button.pack(side=tk.LEFT, padx=5)


# Add table button
table_button = tk.Button(root, text="Add Table", command=add_table)
table_button.pack(pady=5)


# Save workbook button
save_button = tk.Button(root, text="Save Workbook", command=save_workbook)
save_button.pack(pady=5)


# Run the Tkinter event loop
root.mainloop()

Happy Coding!

How to Work with Excel Spreadsheets in Python

Table of Contents

Necessary Libraries

Imports

Initializing Variables and Creating a New Workbook

Loading an Existing Workbook

Updating a Specific Cell and Adding a New Row

Saving the Workbook

Adding a Table

Setting Up the Main Window

Example

Full Code

Leave a Comment Cancel Reply

– 30% OFF COUPON: THEPYCODES

Want to crack the code of cybersecurity? This ebook is your ultimate guide to ethical hacking with Python. Whether you're just starting out or refining your skills, you'll get hands-on tools, real challenges, and step-by-step tutorials. Ready to hack like a pro?

Table of Contents

Necessary Libraries

Imports

Initializing Variables and Creating a New Workbook

Loading an Existing Workbook

Updating a Specific Cell and Adding a New Row

Saving the Workbook

Adding a Table

Setting Up the Main Window

Example

Full Code

Read Also

Leave a Comment Cancel Reply