openpyxl

vibssingh

Appending Data to Excel with Python using openpyxl

HOME

We are given an Excel file and our task is to append the data into this excel file using Python. In this article, we’ll explore openpyxl library to append data to an Excel file using Python.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Sample Excel

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Load the workbook

workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

Step 3 – Select the active worksheet

sheet = workbook.active

Step 4 – Create new data to be appended to the Excel in the list of lists.

new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

Step 5 – Find the next available row in the sheet.

last_row = sheet.max_row + 1

Step 6 – Append the data to the excel by using the nested loops.

for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

Step 7 – Save the changes made to the workbook

workbook.save("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

The complete program is

from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Font


#Load the workbook
workbook = load_workbook("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

sheet = workbook.active

#Sample data
new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

#Append the new data to the sheet
last_row = sheet.max_row + 1
for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

#Save the workbook
workbook.save("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

print("Data is appended in the Excel file successfully")

The output of the above program is

Updated Excel

That’s it! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

Create and Format Excel Files with Python Openpyxl – Tutorial

HOME

Reading data from Excel files is a common practice in software development and testing. Excel provides a tabular format which is easy to understand and navigate. Data can be written to Excel for manipulation, analysis and visualization using Java, Python, R and soon. There are several ways to read the data from Excel in Python, but we will discuss openpyxl.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Implementation Steps

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Create a new workbook

workbook = Workbook()
sheet = workbook.active

Step 3 – Select or rename the Sheet

sheet.title ="Sample Sheet"

Step 4 – Prepare the data

data =[
    ["Name", "Designation", "EmployeeId"],
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

Step 5 – Write the data

Loop through each sub-list and use sheet.append(row) to add the row to the sheet

for row in data:
    sheet.append(row)

Step 6 – Save the workbook

workbook.save("C:\\Users\\Vibha\\Automation\\WriteExcel.xlsx")

The complete program is

from openpyxl.workbook import Workbook

#Create a new workbook
workbook = Workbook()
sheet = workbook.active

#Rename the sheet
sheet.title ="Sample Sheet"

#Sample data
data =[
    ["Name", "Designation", "EmployeeId"],
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

#Write data to the sheet
for row in data:
    sheet.append(row)

#Save the workbook
workbook.save("C:\\Users\\Documents\\Vibha\\Automation\\WriteExcel.xlsx")
print("Data is written the Excel file successfully")

The output of the above program is

The Excel is created and saved at the specified location.

Header with bold font

Below is the complete program:

from openpyxl.styles import Font
from openpyxl.workbook import Workbook

#Create a new workbook
workbook = Workbook()
sheet = workbook.active

#Rename the sheet
sheet.title ="Sample Sheet"

#Sample data
headers =  ["Name", "Designation", "EmployeeId"]
rows =[
    ["Tom", "BA", 11001],
    ["Trina", "PO", 11002],
    ["Will", "Dev", 11003]
]

#Write headers with bold font
for col_num, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_num, value=header)
    cell.font = Font(bold=True)


#Write rows
for row_num, row_data in enumerate(rows, start=2):
    for col_num, cell_value in enumerate(row_data, start=1):
       sheet.cell(row=row_num, column=col_num, value=cell_value)

#Save the workbook
workbook.save("C:\\Users\\Documents\\Vibha\\Automation\\FormattedExcel.xlsx")
print("Data is written the Excel file successfully")

Define the list “headers” containing the name of the headers. Iterate over the headers using enumerate(headers, start=1), which provides an index starting from 1. For each header, create a cell in the first row and corresponding column and set its value.

Use Font(bold=True) to make the font of the header bold and assign it to “cell.font”.

The output of the above program is

Formatted Excel

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!

vibssingh

Reading Excel Data in Python using openpyxl

Last Updated On

HOME

Reading data from Excel files is a common practice in software development and testing. Excel provides a tabular format which is easy to understand and navigate. Data can be read from Excel for manipulation, analysis and visualization using Java, Python, R and soon. There are several ways to read the data from Excel in Python, but we will discuss openpyxl.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Sample Excel

Implementation Steps

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Load the workbook

workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

Step 3 – Select the active worksheet

sheet = workbook.active

Step 4 – Read the Excel (.xlsx) file

for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        print(cell.value)

The complete program is

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Iterate over rows and columns
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        print(cell.value)

The output of the above program is

sheet.iter_rows() is used to iterate over the specified rows and columns

cell.value extracts the data from the each cell.

As we can see that the output is not displayed in structured format.

To display the output in the structure format, use the below program:

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Display data in a structured format
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    row_data =[]
    for cell in row:
        row_data.append((cell.value))
    print(row_data)

The output of the above program is

Display the Excel data in Tabular Format

Install the tabulate library

pip install tabulate

Below is the complete program:

from openpyxl import load_workbook
from tabulate import tabulate

#Load the workbook
workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

#Select the active worksheet
sheet = workbook.active

#Display data in a tabular format
data =[]
for row in sheet.iter_rows(values_only = True):
    data.append(list(row))

print(tabulate(data, headers='firstrow', tablefmt='grid'))

sheet.iter_rows(values_only = True) reads the rowss in the sheet and values_only=True ensures that we get the cell values only. Collect the data onto a list of lists.

tabulate(data, headers=’firstrow’, tablefmt=’grid’) is used to print the data nicely in grid format. headers=firstrow uses the first row as headers and tablefmt=grid sets the format to a grid style.

The output of the above program is

We are done! Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!!