CODEWITHSUNDEEP
python-pptx
Smartis
Smartis

Reputation: 55

Python-pptx - Line Chart - Datetime values for y axis (series)

I would like to create a line chart with text values on the x-axis (categories) and datetime values on the y-axis (series). My goal is to achieve a something like:

Plan vs Actual

I tried the following code. The execution ends without errors, but PowerPoint tells me that there is a problem with the content and asks to repair the file...

from pptx import Presentation
from pptx.util import Inches
from pptx.chart.data import ChartData
from pptx.enum.chart import XL_CHART_TYPE
import datetime

prs = Presentation()
prs.slide_width = Inches(16)
prs.slide_height = Inches(9)

title_slide_layout = prs.slide_layouts[1]
slide = prs.slides.add_slide(title_slide_layout)
title = slide.shapes.title

title.text = "Chart Example!"

val = [datetime.date(2019, 1, 4), datetime.date(2019, 1, 10),
    datetime.date(2019, 1, 11), datetime.date(2019, 1, 14), datetime.date(2019, 1, 15)]
chart_data = ChartData()
chart_data.categories = ['Start', 'Equipment 1', 'Equipment 2', 'Equipment 3', 'End']
chart_data.add_series('Plan', val)

x, y, cx, cy = Inches(0.5), Inches(1.5), Inches(10), Inches(5)
chart = slide.shapes.add_chart(
    XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
).chart

chart.has_legend = True
chart.legend.include_in_layout = False

prs.save('test.pptx')

What could be a solution? I thought a workaround may be to transform the datatime values to numbers, plot them as a number and somehow change the y-axis labels to display the values as date

Upvotes: 1

Views: 673

Answers (1)

Smartis
Smartis

Reputation: 55

I found a good solution. Steps to solve my problem:

  1. Convert the datetime values to 'Excel' number format
  2. Pass to the function add_series the optional paramenter 'number_format', i.e. number_format='mm/dd/yyyy'

Here the code:

def excel_date(date1):
    temp = datetime.datetime(1899, 12, 30)    # Note, not 31st Dec but 30th!
    delta = date1 - temp
    return float(delta.days) + (float(delta.seconds) / 86400)


def getdata():  # function retrieves data form db, text file, etc...
    pass 


def start(stepname_lst, plan_date_lst):
    prs = Presentation()
    prs.slide_width = Inches(16)
    prs.slide_height = Inches(9)

    title_slide_layout = prs.slide_layouts[1]
    slide = prs.slides.add_slide(title_slide_layout)
    title = slide.shapes.title

    title.text = "Chart Example!"
    
    chart_data = CategoryChartData()
    chart_data.categories = stepname_lst
    chart_data.add_series('Plan', plan_date_lst, number_format='mm/dd/yyyy')
    # chart_data.add_series('Actual', actual_lst, number_format='mm/dd/yyyy')

    x, y, cx, cy = Inches(0.5), Inches(1.5), Inches(10), Inches(5)
    chart = slide.shapes.add_chart(
        XL_CHART_TYPE.LINE, x, y, cx, cy, chart_data
    ).chart

    prs.save('test.pptx')


if __name__ == '__main__':
    data_lst = getdata()
    stepname_lst= []
    plan_date_lst = []

    for r in data_lst:
        stepname_lst.append(r[0])
        plan_date_lst.append(excel_date(r[1]))
    
    start(stepname_lst, plan_date_lst)

Upvotes: 1

Related Questions