In the previous blog "Handling Excel file using Python (Part 1)", I have covered how to create the Excel file using Python, add a sheet, add data and view Excel sheet details, etc. Now we will cover how to view Excel data using Python and how to add styles on Excel sheet cell value. So let us continue from where we left in the last blog.

Reading from an Excel sheet:
Now let us read data from the Excel sheet and we can read data in the same way we were writing:
1) By using the cell address
data1 = sheet['B2']
2) By providing the row and column number
data2 = sheet.cell(row=1,column=2)
So now let us read the data from a sheet by using the below code snippet:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Take the reference of the active sheet
sheet = workbook.active

# Get the values
data1 = sheet['A1'] 
data2 = sheet.cell(row=1, column=2)

# print the Excel sheet values
print("data 1 is {} and data 2 is {}".format(data1.value, data2.value))

After executing the above Python code we can get the following response:

data 1 is id and data 2 is name

This way we can read the sheet data using Python. We can iterate using a loop to fetch the complete table from the Excel sheet and can print it using Python.

Copy data of one sheet to another sheet:
We can copy the data of one sheet into a new copy sheet. We can write the following code to generate a copy of any sheet:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# get Sheet which you want to copy
source=workbook['Anurag test sheet']
# create the copy sheet
target=workbook.copy_worksheet(source)

# save workbook
workbook.save(filename)

Using the above code we can create a copy of the "Anurag test sheet" sheet.

Remove a sheet from the Excel file:
Now we are going to remove a sheet from the Excel file and for that, we need to call the remove() method by passing the sheet reference, see the below code snippet:

# import openpyxl module as this is required to work with Excel
import openpyxl as exl  

# provide the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# remove a sheet from the Excel file.
workbook.remove(workbook['Anurag test sheet Copy'])

# save workbook
workbook.save(filename)

using the above code we can remove the "Anurag test sheet Copy" sheet from the Excel file.

Add style in Excel cell data:
We can change the font and fill it in a cell. If we want to configure the font for a cell then we need to define the font first then assign that font to the desired cell, please refer to the below code snippet:

font = Font(color = colors.GREEN, bold = True, italic = True)
cell1.font = font

Using the above code we can change the formatting for the particular cell value. In the same way we can change the filling color also where we need to set the fill variable and then same can be assigned to any cell, please refer to the below code snippet:

fill = PatternFill(fill_type = 'lightUp', bgColor = 'D8F1D3')
cell2.fill = fill

Please refer to the below code where we are applying the font and fill in the same excel file which we have used earlier.

# importing openpyxl and its styles module as this is required to work with Excel
import openpyxl as exl  
from openpyxl.styles import *

# providing the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Fetching the current sheet
sheet = workbook['Anurag test sheet']

# Fetching the cell values
cell1 = sheet['B2']
cell2 = sheet['C2']

# Configuring the font for the first cell
font = Font(color = colors.GREEN, bold = True, italic = True)
cell1.font = font

# Configuring the fill for the first cell
fill = PatternFill(fill_type = 'lightUp', bgColor = 'D8F1D3')
cell2.fill = fill

# saving the workbook
workbook.save(filename)

After executing the above code we can get the following output (refer to the screenshot).

Apply style for tabular data in Excel:
In the previous section, we were styling the individual cells but using Table and TableStyleInfo module we can style complete tabular data for a given range. We first need to create a table object as shown in below code snippet:

table = Table(displayName = "Table", ref = "A1:G11")

After that we can create the styel object as shown in below code snippet:

style = TableStyleInfo(name = "TableStyleMedium9", showRowStripes = True, showColumnStripes = True)

Then we can assign the style to the table:

table.tableStyleInfo = style

At last, we can add the table to the sheet:

sheet.add_table(table)

Please refer to the below code snippet using which we can style the tabular Excel data:

# importing openpyxl and its styles module as this is required to work with Excel
import openpyxl as exl  
from openpyxl.styles import *
from openpyxl.worksheet.table import Table, TableStyleInfo

# providing the Excel file name 
filename="testexcel.xlsx"

# Loading the Excel workbook .xlsx file which we have created earlier
workbook = exl.load_workbook(filename)

# Fetching the current sheet
sheet = workbook['Anurag test sheet']

#Creating a table inside the sheet
table = Table(displayName = "Table", ref = "A1:C3")

#Defining a style for the table (default style name, row/column stripes)
#Choose your table style from the default styles of openpyxl
#Just type in openpyxl.worksheet.table.TABLESTYLES in the Python interpreter
style = TableStyleInfo(name = "TableStyleMedium9", showRowStripes = True, showColumnStripes = True)

#Applying the style to the table
table.tableStyleInfo = style

#Adding the newly created table to the sheet
sheet.add_table(table)

# saving the workbook
workbook.save(filename)

Using the above code we can format the tabular data in an Excel file. It is not possible to cover all features of openpyxl module in two blogs but I have tried to cover the important features using which anyone can handle the Excel file manipulation using Python. In case of any query please feel free to leave a comment.

In case of any doubt please leave your comments. You can also follow me on Twitter: https://twitter.com/anu4udilse
Medium: https://anubioinfo.medium.com/

If you found this article interesting, then you can explore Mastering Kibana 6.0, Kibana 7 Quick Start Guide, Learning Kibana 7, and Elasticsearch 7 Quick Start Guide” books to get more insight about Elastic Stack, how to perform data analysis, and how you can create dashboards for key performance indicators using Kibana.