Write Data to Excel using Python

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!!