Python Program for Writing to an Excel File using openpyxl Module

Python Program for Writing to an Excel File using openpyxl Module

by

Openpyxl is a Python library that reads and writes Excel files (with the extensions xlsx/xlsm/xltx/xltm). Python openpyxl module allows the program to read and modify Excel files.

For example, a user will have to go through hundreds of rows of data and select a few handful of information to make minor changes based on some criterion. These activities can be completed quickly and easily with the Openpyxl module.

Program for Writing to an Excel File using openpyxl Module in Python

Example1: Python Code for printing the title of an active sheet.

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in another variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Get the title of the above sheet using the title attribute and store it in another variable.
  • Print the title of the above active sheet.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
  
# Call the Workbook() function to create a workbook object and store it in another variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active
  
# Get the title of the above sheet using the 
# title attribute and store it in another variable.
titlesheet = actve_sheet.title
# Print the title of the above active sheet.
print("The Title of the given active sheet = ",titlesheet)

Output:

The Title of the given active sheet = Sheet

Example2: Python Code for changing the Title name

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Give some random title for the above active sheet using the title attribute.
  • Print the title of the above active sheet.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
  
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active
  
# Give some random title for the above active sheet using the title attribute
actve_sheet.title = "My_sheetTitle"

# Print the title of the above active sheet.
print("The User given Title of the above active sheet = ",actve_sheet.title)

Output:

The User given Title of the above active sheet = My_sheetTitle

Example3: Python Code for writing to an Excel sheet

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Select the 1st cell of the excel sheet by passing the argument row_no & col_no as 1 using the cell() function and store it in a variable.
  • Set the value of the above cell to some random text using the value attribute.
  • Select the 2nd cell of the excel sheet by passing the argument row_no & col_no as 1 and 2 respectively using the cell() function and store it in a variable.
  • Set the value of the above cell to some random text using the value attribute.
  • We can also access the cell of the excel sheet by square brackets([]) by passing the cell number to it. Here A2 represents column = 1 & row = 2.
  • Similarly do the same for 4th cell. Here B2 means column = 2 & row = 2.
  • Save the above Excel workbook using the save() function by passing the Excel file path to it.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
  
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active
  
# Select the 1st cell of the excel sheet by passing the argument row_no & col_no as 1 
# using the cell() function and store it in a variable.
cell1 = actve_sheet.cell(row = 1, column = 1)
  
# Set the value of the above cell to some random text using the value attribute
cell1.value = "hello"
# Select the 2nd cell of the excel sheet by passing the argument row_no & col_no as 1 and 2 respectively
# using the cell() function and store it in a variable.
cell2 = actve_sheet.cell(row= 1 , column = 2)
# Set the value of the above cell to some random text using the value attribute
cell2.value = "this"
  
# We can also access the cell of the excel sheet by square brackets([]) by passing cell number to it.
# Here A2 represents column = 1 & row = 2.
cell3 = actve_sheet['A2']
cell3.value = "is"
# similarly do the same for 4th cell 
# Here B2 means column = 2 & row = 2.
cell4 = actve_sheet['B2']
cell4.value = "btechgeeks"
# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo.xlsx")

Output:

Example4: Python Code for adding sheets in the workbook

Approach:

  • Import openpyxl module using the import keyword.
  • Call the Workbook() function to create a workbook object and store it in a variable.
  • Choose the active sheet using the active attribute and store it in another variable.
  • Add the sheets into the workbook using the create_sheet() function of the workbook object by passing index=1 and some random title as arguments to it.
  • Save the above Excel workbook using the save() function by passing the Excel file path to it.
  • The Exit of the Program.

Below is the implementation:

# Import openpyxl module using the import keyword
import openpyxl
  
# Call the Workbook() function to create a workbook object and store it in a variable.
work_book = openpyxl.Workbook()
  
# Choose the active sheet using the active attribute and store it in another variable.
actve_sheet = work_book.active

# Add the sheets into the workbook using the create_sheet() function of the workbook object.
# By passing index=1 and some random title as arguments to it.
work_book.create_sheet(index = 1 , title = "My_newsheet")
# Save the above Excel workbook using the save() function by passing the Excel file path to it.
work_book.save("demo.xlsx")

Output:

Leave a Comment