Write and Format in Excel by Pandas/openpyxl
In this tutorial, we will show you examples of creating a new Excel file by Python Pandas library.
Write and Format in Excel Sheet by Pandas and openpyxl libraries
In this tutorial, we will show you examples of creating a new Excel file by 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
- 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 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:
For more on Background color by using openpyxl
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:
Learn more about Borders in openpyxl
