To create a report in Excel using Python, you can use the openpyxl library, which allows you to create, read, and modify Excel files. Here’s an example of how you can create a simple Excel report:

from openpyxl import Workbook

# Create a new Excel workbook
wb = Workbook()

# Select the active worksheet
ws = wb.active

# Set the title of the report
ws.title = "Sales Report"

# Add headers
ws['A1'] = "Product"
ws['B1'] = "Quantity Sold"
ws['C1'] = "Revenue"

# Add some data
data = [
    ("Product A", 100, 5000),
    ("Product B", 150, 7500),
    ("Product C", 200, 10000),
]

for row in range(2, len(data) + 2):
    for col in range(1, len(data[0]) + 1):
        ws.cell(row=row, column=col, value=data[row - 2][col - 1])

# Save the workbook
wb.save("sales_report.xlsx")

Let’s break down the code and understand each part and see the output part wise:

First part just giving heading to your report in your excel

from openpyxl import Workbook

# Create a new Excel workbook
wb = Workbook()

# Select the active worksheet
ws = wb.active

# Set the title of the report
ws.title = "Sales Report"

# Add headers
ws['A1'] = "Product"
ws['B1'] = "Quantity Sold"
ws['C1'] = "Revenue"

# Save the workbook
wb.save("sales_report.xlsx")

1. from openpyxl import Workbook: This line imports the Workbook class from the openpyxl library. The Workbook class is used to create a new Excel workbook.
2. wb = Workbook(): This line creates a new instance of the Workbook class, which represents a new Excel workbook. The wb variable now holds this workbook object.
3. ws = wb.active: This line gets the active worksheet from the wb workbook. By default, a new workbook has one worksheet, which is set as the active worksheet. The ws variable now holds a reference to this worksheet.
4. ws.title = "Sales Report": This line sets the title of the active worksheet to “Sales Report”. This title appears on the tab at the bottom of the Excel window.
5. ws['A1'] = "Product": This line sets the value of cell A1 in the active worksheet to “Product”. This cell will be used as a header for the “Product” column in the report.
6. ws['B1'] = "Quantity Sold": This line sets the value of cell B1 in the active worksheet to “Quantity Sold”. This cell will be used as a header for the “Quantity Sold” column in the report.
7. ws['C1'] = "Revenue": This line sets the value of cell C1 in the active worksheet to “Revenue”. This cell will be used as a header for the “Revenue” column in the report.
8. wb.save("sales_report.xlsx"): This line saves the wb workbook to a file named “sales_report.xlsx” in the current directory. The workbook is saved in Excel format.

Output:

Second part of the code: Let’s add data under eaching heading in our excel

# Add some data
data = [
    ("Product A", 100, 5000),
    ("Product B", 150, 7500),
    ("Product C", 200, 10000),
]

for row in range(2, len(data) + 2):
    for col in range(1, len(data[0]) + 1):
        ws.cell(row=row, column=col, value=data[row - 2][col - 1])

# Save the workbook
wb.save("sales_report.xlsx")

1. data = [("Product A", 100, 5000), ("Product B", 150, 7500), ("Product C", 200, 10000)]: This line defines a list called data that contains tuples. Each tuple represents a row of data for the sales report. The first element of each tuple is the product name, the second element is the quantity sold, and the third element is the revenue.
2. for row in range(2, len(data) + 2):: This line starts a for loop that iterates over a range of numbers from 2 to the length of data plus 2. This range corresponds to the rows in the Excel worksheet where the data will be inserted. The loop variable row represents the current row number.
3. for col in range(1, len(data[0]) + 1):: This line starts a nested for loop that iterates over a range of numbers from 1 to the length of the first tuple in data plus 1. This range corresponds to the columns in the Excel worksheet where the data will be inserted. The loop variable col represents the current column number.
4. ws.cell(row=row, column=col, value=data[row - 2][col - 1]): This line accesses the cell at the current row and col coordinates in the active worksheet ws and sets its value to the value at the (row - 2, col - 1) index in the data list. The row - 2 and col - 1 indices are used because the loop starts at row 2 and column 1, but Python uses 0-based indexing.
5. wb.save("sales_report.xlsx"): This line saves the wb workbook to a file named “sales_report.xlsx” in the current directory. The workbook now contains the sales report data that was added in the previous steps.

Note: Before you see the output make sure your excel is saved in same place where your python project is saved and then run the code in the command line like this(for me the file name is op.py) and then open the excel sheet from the location desired

Output:

Now let’s add some graph to our report to make it visually appealing:

you can use the openpyxl library to create a chart object and insert it into the worksheet.

# Add a bar chart
chart = BarChart()
chart.title = "Sales Report"
chart.y_axis.title = "Quantity Sold"
chart.x_axis.title = "Product"

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=len(data) + 1)
categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)

ws.add_chart(chart, "E1")

# Save the workbook
wb.save("sales_report.xlsx")

we have just added a bar chart to the above mentioned code for creating heading title and adding data to report.

How To Automate Word Document Using Python (codemagnet.in): How To Create Report in Excel Using Python – `Openpyxl`

Let’s us break the code above for adding graph and understand each part:

1. chart = BarChart(): This line creates a new BarChart object, which represents a bar chart that can be added to an Excel worksheet.
2. chart.title = "Sales Report": This line sets the title of the chart to “Sales Report”. This title will be displayed above the chart in the Excel worksheet.
3. chart.y_axis.title = "Quantity Sold": This line sets the title of the y-axis (vertical axis) to “Quantity Sold”. This label will be displayed on the y-axis of the chart.
4. chart.x_axis.title = "Product": This line sets the title of the x-axis (horizontal axis) to “Product”. This label will be displayed on the x-axis of the chart.
5. data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=len(data) + 1): This line creates a Reference object called data, which represents the data range for the chart. It specifies the range of cells in the worksheet ws that contains the data to be used in the chart.
6. categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1): This line creates a Reference object called categories, which represents the category labels for the chart. It specifies the range of cells in the worksheet ws that contains the category labels (in this case, the product names).
7. chart.add_data(data, titles_from_data=True): This line adds the data to the chart. The titles_from_data=True argument specifies that the first row of the data range (data) contains the series names for the chart.
8. chart.set_categories(categories): This line sets the category labels for the chart. It specifies that the category labels are contained in the categories range.
9. ws.add_chart(chart, "E1"): This line adds the chart to the worksheet ws at cell “E1”. The chart will be displayed starting at cell E1 in the Excel worksheet.
10. wb.save("sales_report.xlsx"): This line saves the wb workbook to a file named “sales_report.xlsx” in the current directory. The workbook now contains the sales report data and the chart that were added in the previous steps.

Output after adding Graph:

Now Let say you also want to add a line chart in your excel sheet but in a next sub tab with different name then what should you do?

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

# Create a new Excel workbook
wb = Workbook()

# Select the active worksheet
ws = wb.active

# Set the title of the report
ws.title = "Sales Report"

# Add headers
ws['A1'] = "Product"
ws['B1'] = "Quantity Sold"
ws['C1'] = "Revenue"

# Add some data
data = [
    ("Product A", 100, 5000),
    ("Product B", 150, 7500),
    ("Product C", 200, 10000),
]

for row in range(2, len(data) + 2):
    for col in range(1, len(data[0]) + 1):
        ws.cell(row=row, column=col, value=data[row - 2][col - 1])

# Add a bar chart
bar_chart = BarChart()
bar_chart.title = "Sales Report"
bar_chart.y_axis.title = "Quantity Sold"
bar_chart.x_axis.title = "Product"

bar_data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=len(data) + 1)
bar_categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)

bar_chart.add_data(bar_data, titles_from_data=True)
bar_chart.set_categories(bar_categories)

ws.add_chart(bar_chart, "E1")

# Create a new worksheet for the line chart
ws_line = wb.create_sheet(title="Revenue Report")

# Add a line chart to the new worksheet
line_chart = LineChart()
line_chart.title = "Revenue Report"
line_chart.y_axis.title = "Revenue"
line_chart.x_axis.title = "Product"

line_data = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=len(data) + 1)
line_categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)

line_chart.add_data(line_data, titles_from_data=True)
line_chart.set_categories(line_categories)

ws_line.add_chart(line_chart, "A1")  # Position the line chart at cell A1 in the new worksheet

# Save the workbook
wb.save("sales_report.xlsx")

Let us understand the line chart part of code:

# Create a new worksheet for the line chart
ws_line = wb.create_sheet(title="Revenue Report")

# Add a line chart to the new worksheet
line_chart = LineChart()
line_chart.title = "Revenue Report"
line_chart.y_axis.title = "Revenue"
line_chart.x_axis.title = "Product"

line_data = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=len(data) + 1)
line_categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1)

line_chart.add_data(line_data, titles_from_data=True)
line_chart.set_categories(line_categories)

ws_line.add_chart(line_chart, "A1")  # Position the line chart at cell A1 in the new worksheet

Here’s an explanation of each line of code:

1. ws_line = wb.create_sheet(title="Revenue Report"): This line creates a new worksheet in the Excel workbook wb with the title “Revenue Report” and assigns it to the variable ws_line. The create_sheet() method is used to create a new worksheet, and the title parameter specifies the title of the new worksheet.
2. line_chart = LineChart(): This line creates a new LineChart object and assigns it to the variable line_chart. The LineChart class is used to create a line chart that can be added to an Excel worksheet.
3. line_chart.title = "Revenue Report": This line sets the title of the line chart to “Revenue Report”. This title will be displayed above the chart in the Excel worksheet.
4. line_chart.y_axis.title = "Revenue": This line sets the title of the y-axis (vertical axis) of the line chart to “Revenue”. This label will be displayed on the y-axis of the chart.
5. line_chart.x_axis.title = "Product": This line sets the title of the x-axis (horizontal axis) of the line chart to “Product”. This label will be displayed on the x-axis of the chart.
6. line_data = Reference(ws, min_col=3, min_row=1, max_col=3, max_row=len(data) + 1): This line creates a Reference object called line_data, which represents the data range for the line chart. It specifies the range of cells in the worksheet ws that contains the revenue data for each product.
7. line_categories = Reference(ws, min_col=1, min_row=2, max_row=len(data) + 1): This line creates a Reference object called line_categories, which represents the category labels for the line chart. It specifies the range of cells in the worksheet ws that contains the product names.
8. line_chart.add_data(line_data, titles_from_data=True): This line adds the data to the line chart. The titles_from_data=True argument specifies that the first row of the data range (line_data) contains the series names for the chart.
9. line_chart.set_categories(line_categories): This line sets the category labels for the line chart. It specifies that the category labels are contained in the line_categories range.
10. ws_line.add_chart(line_chart, "A1"): This line adds the line chart to the worksheet ws_line at cell A1. The chart will be displayed starting at cell A1 in the Excel worksheet.

These lines of code work together to create a new worksheet, create a line chart with specified titles for axes and data, and add the line chart to the new worksheet.

Output:

Some advanced tasks you can automate in Excel with Python include:

1. Data Analysis: Use pandas to read data from Excel, perform data analysis, and write the results back to Excel.
2. Conditional Formatting: Use openpyxl to apply conditional formatting to cells based on certain criteria.
3. Chart Generation: Use openpyxl to create charts (e.g., bar charts, line charts) based on data in Excel.
4. Data Validation: Use openpyxl to add data validation rules to cells, ensuring that users enter valid data.
5. Dynamic Tables: Use openpyxl to create dynamic tables that automatically update when the underlying data changes.

I ll give you one example for the above and rest advance tasks will be coming up next in detail so stay connected.

Let’s take data validation example:

Use the same code as we used above:

For example if you want to add data validation to the “Quantity Sold” column in the sales report then-

You can use the DataValidation class from the openpyxl.worksheet.datavalidation module

# Set up data validation for the "Quantity Sold" column
dv = DataValidation(type="whole", operator="greaterThan", formula1="0", showDropDown=True)
dv.error ='Your entry is not a whole number greater than zero'
dv.errorTitle = 'Invalid Input'
dv.prompt = 'Please enter a whole number greater than zero'
dv.promptTitle = 'Quantity Sold'
dv.sqref = 'B2:B{}'.format(len(data) + 1)
ws.add_data_validation(dv)

Here’s an explanation of each line of code above:

1. dv = DataValidation(type="whole", operator="greaterThan", formula1="0", showDropDown=True): This line creates a DataValidation object dv with the following settings:
   • type="whole": Specifies that the input should be a whole number.
   • operator="greaterThan": Specifies the comparison operator to use for validation (in this case, greater than).
   • formula1="0": Specifies the formula or value to compare the input against (in this case, 0).
   • showDropDown=True: Specifies whether to show a dropdown list for the input.
2. dv.error ='Your entry is not a whole number greater than zero': This line sets the error message that will be displayed if the input does not meet the validation criteria.
3. dv.errorTitle = 'Invalid Input': This line sets the title of the error message dialog box that will be displayed if the input is invalid.
4. dv.prompt = 'Please enter a whole number greater than zero': This line sets the prompt message that will be displayed when the cell is selected.
5. dv.promptTitle = 'Quantity Sold': This line sets the title of the prompt message dialog box that will be displayed when the cell is selected.
6. dv.sqref = 'B2:B{}'.format(len(data) + 1): This line specifies the range of cells to which the data validation should be applied. The sqref attribute is set to a string that represents the range from cell B2 to the last cell in the “Quantity Sold” column (based on the length of the data list).
7. ws.add_data_validation(dv): This line adds the data validation (dv) to the worksheet (ws). The data validation will be applied to the specified range of cells, ensuring that the input meets the specified criteria.

Full Code:

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.dimensions import SheetDimension

# Create a new Excel workbook
wb = Workbook()

# Select the active worksheet
ws = wb.active

# Set the title of the report
ws.title = "Sales Report"

# Add headers
ws['A1'] = "Product"
ws['B1'] = "Quantity Sold"
ws['C1'] = "Revenue"

# Add some data
data = [
    ("Product A", 100, 5000),
    ("Product B", 150, 7500),
    ("Product C", 200, 10000),
]

for row in range(2, len(data) + 2):
    for col in range(1, len(data[0]) + 1):
        ws.cell(row=row, column=col, value=data[row - 2][col - 1])

# Set up data validation for the "Quantity Sold" column
dv = DataValidation(type="whole", operator="greaterThan", formula1="0", showDropDown=True)
dv.error ='Your entry is not a whole number greater than zero'
dv.errorTitle = 'Invalid Input'
dv.prompt = 'Please enter a whole number greater than zero'
dv.promptTitle = 'Quantity Sold'
dv.sqref = 'B2:B{}'.format(len(data) + 1)
ws.add_data_validation(dv)

# Save the workbook
wb.save("sales_report.xlsx")

In conclusion, the Openpyxl library in Python provides powerful tools for creating and customizing Excel reports programmatically. By using features like worksheets, cells, charts, and data validation, you can automate the process of generating reports, making it faster and more efficient. I hope this tutorial has been helpful in understanding how to use Openpyxl for Excel automation. Stay connected for more tutorials on advanced tasks that Python can perform to further enhance your Excel automation skills.