Python Program to Write into an Excel File Using openpyxl Library

Python Program to Write to an Excel File Using openpyxl Module

by

In this article we are going to see how we can write to an excel sheets by using Python language. To do this we will be using openpyxl library which is used to read and write to an excel file.

Python Program to Write to an Excel File Using openpyxl Module

In Python we have openpyxl library which is used to create, modify, read and write different types of excel files like xlsx/xlsm/xltx/xltm etc. When user operates with thousands of records in a excel file and want to pick out few useful information or want to change few records then he/she can do it very easily by using openpyxl library.

To use openpyxl library we first have to install it using pip.

Command : pip install openpyxl

After installation we can use the library to create and modify excel files.

Let’s see different programs to understand it more clearly.

Program-1: Python Program to Print the Name of an Active Sheet Using openpyxl Module

Approach:

  • First of all we have to import the openpyxl module
  • Then we use the workbook( ) function to create a workbook object
  • From the object we created, we extract the active sheet from the active attribute
  • Then we use the title attribute of the active sheet to fetch its title and print it.

Program:

# Import the openpyxl library
import openpyxl as opxl

# We will need to create a blank workbook first
# For that we can use the workbook() funtion available in th openpyxl library
workb = opxl.Workbook()

# Get the active workbook sheet from the active attribute
activeSheet = workb.active

# Extracting the sheet title from the activSheet object
sheetTitle = activeSheet.title

# Printing the sheet title
print("The active sheet name is : " + sheetTitle)

Output:

The active sheet name is : Sheet

Program-2: Python Program to Update the Name of an Active Sheet Using openpyxl Module

Approach:

  • First of all we have to import the openpyxl module.
  • Then we use the workbook( ) function to create a workbook object.
  • From the object we created, we extract the active sheet from the active attribute.
  • Then we use the title attribute of the active sheet to fetch its title and print it.
  • Now we store the new name in the title attribute of the active sheet.
  • Finally we fetch its title and print it.

Program:

# Import the openpyxl library
import openpyxl as opxl

# We will need to create a blank workbook first
# For that we can use the workbook() funtion available in th openpyxl library
workb = opxl.Workbook()

# Get the active workbook sheet from the active attribute
activeSheet = workb.active

# Fethcing the sheet title from the activSheet object
sheetTitle = activeSheet.title

# Printing the sheet title
print("The active sheet name is : " + sheetTitle)

# Updating the active sheet name
activeSheet.title = "New_Sheet_Name"

# Fethcing the sheet title from the activSheet object
sheetTitle = activeSheet.title

# Printing the new sheet title
print("The active sheet name after updation is : " + sheetTitle)

Output:

The active sheet name is : Sheet

The active sheet name after updation is : New_Sheet_Name

Program-3: Python Program to Write into an Excel Sheet Using openpyxl Module

Approach:

  • First of all we have to import the openpyxl module.
  • Then we use the workbook( ) function to create a workbook object.
  • From the object we created, we extract the active sheet from the active attribute.
  • Then we create cell objects from the active sheet object that store the row column coordinates. These cells can be called using row and column values or just the cell name like A1 *for row = 1 and column = 1).
  • Store some values in those cells.
  • Save the file using save( ) to make the changes permanent.
  • Now open the excel sheet to find the changes.

Program:

# Import the openpyxl library
import openpyxl as opxl

# We will need to create a blank workbook first
# For that we can use the workbook() funtion available in th openpyxl library
workb = opxl.Workbook()

# Get the active workbook sheet from the active attribute
activeSheet = workb.active

# Creating a cell object that contains attributes about rows, columns
# and coordinates to locate the cell
cell1 = activeSheet.cell(row=1, column=1)
cell2 = activeSheet.cell(row=2, column=1)

# Addind values to the cells
cell1.value = "Hi"
cell2.value = "Hello"

# Rather than writing the row and column number,
# we can also access the cells by their individual names
# C2 means third column 1st row
cell3 = activeSheet['C1']
cell3.value = "Gracias"

# C1 means third column 2nd row
cell3 = activeSheet['C2']
cell3.value = "Bye"

# FInally we have to save the file to save our changes
workb.save("E:\\Article\\Python\\file1.xlsx")

Output:

Python Program to Write into an Excel File Using openpyxl Library

Program-4: Python Program to Add more Sheets to the Active Workbook Using openpyxl Module

Approach:

  • First of all we have to import the openpyxl module.
  • Then we use the workbook( ) function to create a workbook object.
  • From the object we created, we extract the active sheet from the active attribute.
  • Then create a new sheet by using create_sheet() method.
  • Save the file by specifying the path.

Program:

# Import the openpyxl library
import openpyxl as opxl

# We will need to create a blank workbook first
# For that we can use the workbook() funtion available in th openpyxl library
workb = opxl.Workbook()

# Get the active workbook sheet from the active attribute
activeSheet = workb.active

# To add more sheets into the workbook we have to use the create_sheet() method
workb.create_sheet(index=1, title="2nd sheet")

# FInally we have to save the file to save our changes
workb.save("E:\\Article\\Python\\file1.xlsx")

Output:

Python Program to Write into an Excel File Using openpyxl Library