Creating Charts in Excel with Python

import openpyxl, os
from openpyxl.styles import Font
from openpyxl.styles.colors import Color

os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
print(os.getcwd())
 
'''Adapted from https://automatetheboringstuff.com/2e/chapter13/
Data files used can be found at this link

Charts
======
OpenPyXL supports creating bar, line, scatter, and pie charts using the data in
a sheet’s cells.

1. Create a Reference object from a rectangular selection of cells.
2. Create a Series object by passing in the Reference object.
3. Create a Chart object.
4. Append the Series object to the Chart object.
5. Add the Chart object to the Worksheet object, optionally specifying which cell
   should be the top-left corner of the chart.

You create Reference objects by calling the openpyxl.chart.Reference() function
and passing three arguments:

1. The Worksheet object containing your chart data.
2. A tuple of two integers, denoting the top-left row/column cell of the selection:
   Note: 1 is the first row, not 0.
3. A tuple of two integers, denoting the bottom-right row/column cell of the

   From top-left to bottom-right: (3, 2), (6, 4)

   ___|_A_|_B_|_C_|_D_|_E_
   _1_|_10|___|___|___|___
   _2_|_20|___|___|___|___
   _3_|_30|_x_|_x_|_x_|___
   _4_|_40|_x_|_x_|_x_|___
   _5_|_50|_x_|_x_|_x_|___
   _6_|_60|_x_|_x_|_x_|___
   _7_|_70|___|___|___|___
   _8_|_80|___|___|___|___
   _9_|_90|___|___|___|___
   10_|100|___|___|___|___
   11_|___|___|___|___|___
  
'''
# Open blank wb, create 3 tabs, delete default tab "Sheet", set sheet 'S3' as active
# Create myFont and list worksheets in workbook
wb = openpyxl.Workbook()
wb.create_sheet(index=0, title='S1')
wb.create_sheet(index=1, title='S2')
wb.create_sheet(index=2, title='S3')
del wb['Sheet']
wb.active = wb['S3']
sheet = wb.active
myFont = Font(name='Arial', color='FF0000', size=14, bold=True)
print(wb.sheetnames)

# Create some data in cells A1-A10
for i in range(1, 11):
    sheet['A' + str(i)] = (i * 10)

# 1. Create a reference object for the data in A1-A10
refObj = openpyxl.chart.Reference(sheet, min_col=1, min_row=1, max_col=1, max_row=10)

# 2. Create a series object from the reference object
seriesObj = openpyxl.chart.Series(refObj, title='First series')

# 3. Create a chart object (bar chart)
  #Types of charts include:
  #------------------------------
chartObj = openpyxl.chart.BarChart()
  #chartObj = openpyxl.chart.LineChart()
  #chartObj = openpyxl.chart.ScatterChart()
  #chartObj = openpyxl.chart.PieChart()
chartObj.title = 'My Chart'

# 4. Append the series object to the chart object
chartObj.append(seriesObj)

# 5. The charts top left corner is B3
sheet.add_chart(chartObj, 'B3')
wb.save('sampleChart.xlsx')
# To reopen this file:
#wb = openpyxl.load_workbook('sampleChart.xlsx')
wb.close()