Write and Format in Excel by Pandas/openpyxl

Write and Format in Excel Sheet by Pandas and openpyxl libraries

In this tutorial, we will show examples of creating a new Excel file by using Python Pandas library.

Along with file creation, we will also format rows/columns in an Excel file by openpyxl library.

• The formatting includes coloring the header row
• Setting the font of rows and columns
• The interior color of rows and columns
• Border color etc.

Step by Step – Creating and formatting

Let us first create an Excel file by Pandas to_excel method based on a data frame that uses a list in Python.

For that, we have a list of Products that we will use for the newly created Excel file.

The program creates an Excel file with data from the list as follows:

import pandas as pd

#Creating a list containing Products Information

product_list = [  ["p-001", "Wheat", "$3.50", "In Stock"],

              ["p-003", "Rice", "$5.5", "In Stock"],

              ["p-004", "Sugar", "$2.0", "In Stock"],

              ["p-005", "Maze", "$1.5", "Out of Stock"],

              ["p-006", "Tea Set", "$5.55", "In Stock"],

              ["p-007", "Dinner Set", "$25.99", "In Stock"],  

              ["p-008", "Rice", "$1.2", "Out of Stock"]

            ]

df_prods = pd.DataFrame (product_list, columns = ['Product ID', 'Product Name', 'Price', 'Status'])

#Creating Excel File with Product Information

df_prods.to_excel("products.xlsx", index=False, sheet_name = "Products Data")

Output:

Setting the Interior color of the heading row

In this example, we will create the product.xlsx file by using the Pandas to_excel method.

This is followed by applying interior color (background) to the top row by openpyxl library:

import pandas as pd

from openpyxl import load_workbook

from openpyxl.styles import PatternFill

#Creating a list containing Products Information

product_list = [  ["p-001", "Wheat", "$3.50", "In Stock"],

              ["p-003", "Rice", "$5.5", "In Stock"],

              ["p-004", "Sugar", "$2.0", "In Stock"],

              ["p-005", "Maze", "$1.5", "Out of Stock"],

              ["p-006", "Tea Set", "$5.55", "In Stock"],

              ["p-007", "Dinner Set", "$25.99", "In Stock"],  

              ["p-008", "Rice", "$1.2", "Out of Stock"]

            ]

df_prods = pd.DataFrame (product_list, columns = ['Product ID', 'Product Name', 'Price', 'Status'])

#Creating Excel File with Product Information

df_prods.to_excel("products.xlsx", index=False, sheet_name = "Products Data")

#######

#Appling formatting to header row by openpyxl

#######


#Loading the Workbook

wb_style_prod = load_workbook('products.xlsx')



#Accessing Product Informaiton Sheet

sheet = wb_style_prod.active

#Setting background heading row only

for rows in sheet.iter_rows(min_row=1, max_row=1, min_col=None):

   for cell in rows:

     cell.fill = PatternFill(start_color="80FF00", end_color="80FF00",fill_type = "solid")

wb_style_prod.save("products.xlsx")

Result:

Applying border colors to odd rows example

Again, we will create the Excel file by Pandas – based on list data and then apply the borders to odd rows.

The code:

import pandas as pd

from openpyxl import load_workbook

from openpyxl.styles import Border, Side

#Creating a list containing Products Information

product_list = [  ["p-001", "Wheat", "$3.50", "In Stock"],

              ["p-003", "Rice", "$5.5", "In Stock"],

              ["p-004", "Sugar", "$2.0", "In Stock"],

              ["p-005", "Maze", "$1.5", "Out of Stock"],

              ["p-006", "Tea Set", "$5.55", "In Stock"],

              ["p-007", "Dinner Set", "$25.99", "In Stock"],  

              ["p-008", "Rice", "$1.2", "Out of Stock"]

            ]

df_prods = pd.DataFrame (product_list, columns = ['Product ID', 'Product Name', 'Price', 'Status'])

#Creating Excel File with Product Information

df_prods.to_excel("products.xlsx", index=False, sheet_name = "Products Data")

#######

#Appling formatting to header row by openpyxl

#######

#Loading the Workbook

wb_style_prod = load_workbook('products.xlsx')


#Accessing Product Informaiton Sheet

sheet = wb_style_prod.active


#Specifying border style and color

thick = Side(border_style="thick", color="008000")

double = Side(border_style="double", color="800000")

#Change background color of even rows

for rows in sheet.iter_rows(min_row=2, max_row=8, min_col=None):

   for cell in rows:

       if cell.row % 2:

           cell.border = Border(top=thick, left=double, right=double, bottom=thick)

wb_style_prod.save("products.xlsx")

Result: