Prerequisite : Reading an excel file using openpyxl
Openpyxl is a Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files. The openpyxl module allows Python program to read and modify Excel files.
For example, user might have to go through thousands of rows and pick out few handful information to make small changes based on some criteria. Using Openpyxl module, these tasks can be done very efficiently and easily.
Let’s see how to create and write to an excel-sheet using Python.
Code #1 : Program to print a active sheet title name
# import openpyxl moduleimport openpyxl # Call a Workbook() function of openpyxl # to create a new blank Workbook objectwb = openpyxl.Workbook() # Get workbook active sheet # from the active attribute. sheet = wb.active # Once have the Worksheet object,# one can get its name from the# title attribute.sheet_title = sheet.title print("active sheet title: " + sheet_title) |
Output :
active sheet title: Sheet
Code #2 : Program to change the Title name
# import openpyxl moduleimport openpyxl # Call a Workbook() function of openpyxl # to create a new blank Workbook objectwb = openpyxl.Workbook() # Get workbook active sheet # from the active attributesheet = wb.active # One can change the name of the titlesheet.title = "sheet1" print("sheet name is renamed as: " + sheet.title) |
Output :
sheet name is renamed as: sheet1
Code #3 :Program to write to an Excel sheet
# import openpyxl moduleimport openpyxl # Call a Workbook() function of openpyxl # to create a new blank Workbook objectwb = openpyxl.Workbook() # Get workbook active sheet # from the active attributesheet = wb.active # Cell objects also have row, column# and coordinate attributes that provide# location information for the cell. # Note: The first row or column integer# is 1, not 0. Cell object is created by# using sheet object's cell() method.c1 = sheet.cell(row = 1, column = 1) # writing values to cellsc1.value = "ANKIT" c2 = sheet.cell(row= 1 , column = 2)c2.value = "RAI" # Once have a Worksheet object, one can# access a cell object by its name also.# A2 means column = 1 & row = 2.c3 = sheet['A2']c3.value = "RAHUL" # B2 means column = 2 & row = 2.c4 = sheet['B2']c4.value = "RAI" # Anytime you modify the Workbook object# or its sheets and cells, the spreadsheet# file will not be saved until you call# the save() workbook method.wb.save("C:\\Users\\user\\Desktop\\demo.xlsx") |
Output :
code #4 :Program to add Sheets in the Workbook
# import openpyxl moduleimport openpyxl # Call a Workbook() function of openpyxl # to create a new blank Workbook objectwb = openpyxl.Workbook() sheet = wb.active # Sheets can be added to workbook with the# workbook object's create_sheet() method. wb.create_sheet(index = 1 , title = "demo sheet2") wb.save("C:\\Users\\user\\Desktop\\demo.xlsx") |
Output :
