How to output excel with openpyxl — python

Openpyxl is a Python library that allows users to read and write Excel xlsx/xlsm/xltx/xltm files. I will explain how to get the output one by one now

1-Import the openpyxl module: To start using openpyxl, you need to import the module in your Python script. You can do this by adding the following line of code at the beginning of your script:

import openpyxl

2-Create a new workbook: After importing the openpyxl module, you can create a new workbook using the Workbook() function. This function creates a new workbook with a single sheet named “Sheet”. You can assign this workbook to a variable for later use:

wb = openpyxl.Workbook()

3-Set the active sheet: By default, the Workbook() function creates a single sheet named “Sheet”. You can set this sheet as the active sheet by accessing it using the active attribute of the workbook:

sheet = wb.active

Alternatively, you can create a new sheet and set it as the active sheet using the create_sheet() function:

sheet = wb.create_sheet("MySheet")

4-Create and style the headings: You can create and style the headings for each column in the worksheet using the cell() function. This function takes two arguments: the row and column number of the cell. You can use the Font, PatternFill, and Alignment classes from openpyxl.styles to style the headings:

sheet.cell(row=1, column=1).value = "Product ID"
sheet.cell(row=1, column=1).font = openpyxl.styles.Font(bold=True, name="Arial", size=12)
sheet.cell(row=1, column=1).fill = openpyxl.styles.PatternFill(patternType="solid", fgColor="00e5ee")
sheet.cell(row=1, column=1).alignment = openpyxl.styles.Alignment(horizontal="center")

You can repeat this process for each heading in your worksheet.

5-Set the column dimensions: You can set the column dimensions using the column_dimensions attribute of the worksheet. This attribute is a dictionary where the keys are column letters (e.g., “A”, “B”, “C”) and the values are ColumnDimension objects. You can set the width of a column using the width attribute of the ColumnDimension object:

sheet.column_dimensions["A"].width = 20
sheet.column_dimensions["B"].width = 15
sheet.column_dimensions["C"].width = 30

6-Retrieve data from your data source and fill it into the worksheet: You can retrieve data from your data source (e.g., a database, a CSV file) and fill it into the worksheet using the cell() function. You can use a loop to iterate over each row in your data source and each column in your worksheet:

for i, row in enumerate(data):
    for j, value in enumerate(row):
        sheet.cell(row=i+2, column=j+1).value = value

In this example, data is a list of lists where each inner list represents a row in the worksheet.

7-Save the workbook: Once you have finished creating your worksheet, you can save the workbook using the save() function. This function takes one argument: the filename of the Excel file:

wb.save("output.xlsx")

The Excel file will be saved in the current working directory.

Now I will explain the code on my own project to you. You can find the project details on my github profile.

# -*- coding: utf-8 -*-
"""
Created on Tue Dec 27 10:00:19 2022
@author: Azmi Deliaslan
"""
from database import Database
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, NamedStyle
from openpyxl.formatting.rule import Rule
from openpyxl.styles.differential import DifferentialStyle

def convert():
    # Create workbook
    wb = openpyxl.Workbook()
    # Get workbook active sheet
    sheet = wb.active
    # Creating Headings
    sheet.cell(row=1, column=1).value = "Product ID"
    sheet.cell(row=1, column=2).value = "Product Category"
    sheet.cell(row=1, column=3).value = "Product Brand"
    sheet.cell(row=1, column=4).value = "Product Name"
    sheet.cell(row=1, column=5).value = "Product Stock"
    sheet.cell(row=1, column=6).value = "Cost Price"
    sheet.cell(row=1, column=7).value = "Selling Price"

    # Styling Headings
    for i in range(1, 8):
        sheet.cell(row=1, column=i).font = Font(bold=True, name="arial", size=15)
        sheet.cell(row=1, column=i).fill = PatternFill(patternType="solid", fgColor="00e5ee")
        sheet.cell(row=1, column=i).alignment = Alignment(horizontal="center")

    # Setting column dimensions
    for col in "ABCDEFGH":
        sheet.column_dimensions[col].width = 25

    # Retrieving date from database
    db = Database("products.db")
    rows = db.fetch_all_rows()

    # Filling date from Database into the worksheet
    for sheet_row, row in enumerate(rows):
        for sheet_col, item in enumerate(row):
            sheet.cell(row=sheet_row + 2, column=sheet_col + 1).value = item
            sheet.cell(row=sheet_row + 2, column=sheet_col + 1).alignment = Alignment(horizontal="center")

    wb.save("product_list.xlsx")


def calc_profit():
    # loading excel file to read and write to
    wb = openpyxl.load_workbook(filename="product_list.xlsx")

    sheet = wb.active

    # Creating  and styling Profit Column Heading
    sheet.cell(row=1, column=8).value = "Profit"
    sheet.cell(row=1, column=8).font = Font(bold=True, name="arial", size=15)
    sheet.cell(row=1, column=8).fill = PatternFill(patternType="solid", fgColor="00e5ee")
    sheet.cell(row=1, column=8).alignment = Alignment(horizontal="center")

    # Calculating Profit
    row_index = 2
    for cell in sheet["H"][1:]:
        cell.value = f"=G{row_index}-F{row_index}"
        row_index = row_index + 1
        cell.alignment = Alignment(horizontal="center")

    # Adding Conditional Formatting on entire row
    yellow_background = PatternFill(bgColor="e9ee9e")
    diff_style = DifferentialStyle(fill=yellow_background)
    rule = Rule(type="expression", dxf=diff_style)
    rule.formula = ["$H1<0"]
    sheet.conditional_formatting.add(f"A1:H{sheet.max_column}", rule)

    # Same formula as but for a cell in a column
    # sheet.conditional_formatting.add(f'H2:H{sheet.max_column}', CellIsRule(operator='lessThan', formula=['0'], fill=yellow_background))
    wb.save("product_list.xlsx")

This code is a Python script that converts data from a SQLite database to an Excel file using the openpyxl library. It then calculates the profit for each product based on the cost price and selling price, and applies conditional formatting to highlight rows where the profit is negative.

The script first imports the necessary modules: Database from database, openpyxl, and several classes from openpyxl.styles and openpyxl.formatting.rule.

The convert() function creates a new workbook and sets the active sheet. It then creates and styles the headings for each column in the worksheet. The column dimensions are also set to 25 for each column. Next, data is retrieved from a SQLite database using the Database class and filled into the worksheet. The worksheet is then saved as an Excel file.

The calc_profit() function loads the Excel file created in the convert() function and sets the active sheet. It then creates and styles the Profit column heading. Profit is calculated for each row using the formula Selling Price - Cost Price. Conditional formatting is then applied to highlight rows where the profit is negative.