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!!
QA Automation Expert 