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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
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
