lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

automationIntroApplicationChapter1Title

automationIntroApplicationChapter2Title

automationIntroApplicationChapter3Title

automationIntroApplicationChapter4Title

from pptx import Presentation
from pptx.util import Inches
from pptx.chart.data import CategoryChartData
from pptx.enum.chart import XL_CHART_TYPE
import openpyxl
from datetime import datetime, timedelta
import random

# Open the Excel file
wb = openpyxl.load_workbook('input_file.xlsx')
sheet = wb.active


# Extract data from Excel
data = []
for row in sheet.iter_rows(values_only=True):
    data.append(list(row))


# Add "Days Left" column and adjust the end date randomly
data[0].append("D-DAY")  # Add "D-DAY" header to the first row
today = datetime.today()


for row in data[1:]:
    # Convert start and end dates to datetime objects
    start_date = datetime.strptime(row[2], "%Y-%m-%d")  # Start date
    end_date = datetime.strptime(row[3], "%Y-%m-%d")    # End date

    # Randomly extend the end date for teaching purposes from 30 to 100 days from today
    random_days = random.randint(30, 100)
    new_end_date = today + timedelta(days=random_days)

    # Update the end date to the new random end date
    row[3] = new_end_date.strftime("%Y-%m-%d")
    
    # Calculate days remaining
    remaining_days = (new_end_date - today).days
    row.append(remaining_days)  # Add days remaining to the column


# Create a PowerPoint presentation
prs = Presentation()


# Select blank slide layout
slide_layout = prs.slide_layouts[5]  # Blank slide
slide = prs.slides.add_slide(slide_layout)


# Define table location and size
rows, cols = len(data), len(data[0])
left = Inches(0.5)
top = Inches(0.5)
width = Inches(9.0)  # Adjusted for slide size
height = Inches(2.5)


# Add table to the slide
table = slide.shapes.add_table(rows, cols, left, top, width, height).table


# Populate table with Excel data
for row_idx, row_data in enumerate(data):
    for col_idx, cell_value in enumerate(row_data):
        table.cell(row_idx, col_idx).text = str(cell_value)


# Adjust column widths
column_widths = [Inches(2.0), Inches(1.0), Inches(1.5), Inches(1.5), Inches(2.0), Inches(1.0)]  # Set width for each column
for col_idx, width in enumerate(column_widths):
    for row_idx in range(len(data)):
        table.columns[col_idx].width = width  # Set column width


# Prepare data for chart (project name and progress %)
chart_data = CategoryChartData()
chart_data.categories = [row[0] for row in data[1:]]  # Exclude the first row (header)
chart_data.add_series('Progress (%)', (row[1] for row in data[1:]))


# Add chart to the slide (clustered column chart)
x, y, cx, cy = Inches(0.5), Inches(3.0), Inches(5.0), Inches(3.0)
chart = slide.shapes.add_chart(
    XL_CHART_TYPE.COLUMN_CLUSTERED, x, y, cx, cy, chart_data
).chart


# Add title to the chart
chart.has_title = True
chart.chart_title.text_frame.text = 'Project Progress'


# Save the PowerPoint file
pptx_file_path = 'output_file.pptx'
prs.save(pptx_file_path)

# Automatically Calculating Project Deadline D-DAY

Below is a code example that shows how to automatically calculate the `Deadline D-DAY` for a project on a PowerPoint slide.

```python title="Calculate Project Deadline D-DAY"
# Add "D-DAY" header to the first row
data[0].append("D-DAY")

# Store today's date in the today variable
today = datetime.today()

# Calculate remaining days for each row in the data
for row in data[1:]:
    # Extract start date
    start_date = datetime.strptime(row[2], "%Y-%m-%d")

    # Extract end date
    end_date = datetime.strptime(row[3], "%Y-%m-%d")

    # Calculate remaining days
    remaining_days = (end_date - today).days

    # Add remaining days to the row
    row.append(remaining_days)
```

In Python, you can fetch today's date using the `today()` method from the `datetime` module.

Subsequently, use the `strptime` method to extract the start and end dates for each project, and calculate the `remaining days` to add to the data.

By automating the calculation of the project's deadline D-DAY this way, you can ensure that the remaining days are automatically reflected when converting Excel data to PowerPoint slides.

In the next Chapter, we will explore how to practically apply **web scraping** with real-life examples.