1

There is option in excel sheet which allows user to export charts to chartsheet. Manually one can add any number of charts but while using openpyxl module I could only add one chart, when I try to add more than one chart its not showing up in the chartsheet, only thing i can see is my old graph which I just added at the beginning. here is the sample code I have used.

import openpyxl as op    

wb = op.load_workbook('input_excel.xlsx')
ws= wb.get_sheet_by_name('RawData')

chart_sheet_1=wb.create_chartsheet('for_graphs')
chart_1 = op.chart.ScatterChart()
chart_2 = op.chart.ScatterChart()

data_set_1 = op.chart.Reference(ws,1,3,1,51)
data_set_2 = op.chart.Reference(ws,2,3,2,51)
data_set_3 = op.chart.Reference(ws,3,3,3,51)

series_graphs_1 = op.chart.Series(data_set_3,data_set_1)
series_graphs_2 = op.chart.Series(data_set_3,data_set_2)

chart_1.series.append(series_graphs_1)
chart_2.series.append(series_graphs_2)

chart_sheet_1.add_chart(chart_1)
chart_sheet_1.add_chart(chart_2)

As shown above i am adding two charts in shown fashion is that wrong or is there any other ways to add two or more charts in one chartsheet using openpyxl or any other modules??????????. Thanks in advance.

The Guy
  • 411
  • 4
  • 11
uttam hm
  • 11
  • 3
  • I believe the `ChartSheet()` is for a single `Chart` only. I could not find any specifics in the `openpyxl` documentation. But in the `xlsxwriter` [docs](https://xlsxwriter.readthedocs.io/chartsheet.html), it specifically says you can only add one chart per `ChartSheet`. `"Only one chart can be added to an individual chartsheet."` – APhillips Feb 07 '20 at 21:45
  • But u can add Multiple charts to one chart sheet in Microsoft Excel right!!! then why can't by coding – uttam hm Feb 08 '20 at 13:06

1 Answers1

0

I was just looking for some openpyxl solutions, their site lacks a lot of information if you compare it against xlswriter. The problem with xlswriter is that it can't open excel files, just create new ones.

And even that this question is old, I would like to answer it in case anybody else comes looking for answers.

This works for python 3 and openpyxl 3.0.9 (latest version as today)

For openpyxl you can add multiple charts into a single sheet, it has a trick: you need to first write the first chart, and comment the rest of the charts, afterwards, you can uncomment them, and it will no longer produce errors or warnings... perhaps a bug?...

Leaving some simple code in here, remember to comment and uncomment when you have the initial file.

from openpyxl import  load_workbook, Workbook
from openpyxl.styles import Font, Color, colors, PatternFill
from openpyxl.chart import ScatterChart, Reference, Series
from numpy import random
import numpy as np

data0 = random.randint(100, size=(100))                 # Some random vectors
data1 = random.randint(100, size=(100))
data2 = random.randint(100, size=(100))
data3 = random.randint(100, size=(100))  

filename = 'your_file.xlsx'                                      

wb = Workbook()                                                
ws = wb.worksheets[0]                                               

ws['A1'].font = Font(bold = True)
ws.cell(row = 1, column = 1).value = 'Title goes here'

yellowFill = PatternFill(start_color = 'FFFF00', end_color = 'FFFF00', fill_type = 'solid')
redFill = PatternFill(start_color = 'FF0000', end_color = 'FF0000', fill_type = 'solid')

ws['A4'].font = Font(bold = True)
ws['A4'].fill = yellowFill
ws.cell(row = 4, column = 1).value = 'x'

ws.cell(row = 4, column = 2).value = 'Rnd_1'    
ws['B4'].font = Font(color = 'FFFFFF', bold = True)
ws['B4'].fill = redFill

ws.cell(row = 4, column = 3).value = 'Rnd_2'
ws['C4'].font = Font(color = 'FFFFFF', bold = True)
ws['C4'].fill = redFill

ws.cell(row = 4, column = 4).value = 'Rnd_3'
ws['D4'].font = Font(color = 'FFFFFF', bold = True)
ws['D4'].fill = redFill

ws.cell(row = 4, column = 5).value = 'Rnd_4'
ws['E4'].font = Font(color = 'FFFFFF', bold = True)
ws['E4'].fill = redFill

row_i = 5

for i in range(99):
    ws.cell(row = row_i, column = 1).value = i
    ws.cell(row = row_i, column = 2).value = data0[i]
    ws.cell(row = row_i, column = 3).value = data1[i]
    ws.cell(row = row_i, column = 4).value = data2[i]
    ws.cell(row = row_i, column = 5).value = data3[i]
    row_i += 1

xvalues = Reference(ws, min_col = 1, min_row = 5, max_row = row_i)     # just using the same x axis for all the rest of the charts

chart_1 = ScatterChart()
chart_1.y_axis.title = 'y value_1'
yvalues_1 = Reference(ws, min_col = 2, min_row = 5, max_row = row_i)
series_1 = Series(yvalues_1, xvalues, title = 'Data 1')
chart_1.series.append(series_1)
ws.add_chart(chart_1, 'H1')

#comment the following, and uncomment the second time

chart_2 = ScatterChart()
chart_2.y_axis.title = 'y value_2'
yvalues_2 = Reference(ws, min_col = 3, min_row = 5, max_row = row_i)
series_2 = Series(yvalues_2, xvalues, title = 'Data 2')
chart_2.series.append(series_2)
ws.add_chart(chart_2, 'H17')

chart_3 = ScatterChart()
chart_3.y_axis.title = 'y value_3'
yvalues_3 = Reference(ws, min_col = 4, min_row = 5, max_row = row_i)
series_3 = Series(yvalues_3, xvalues, title = 'Data 3')
chart_3.series.append(series_3)
ws.add_chart(chart_3, 'R1')

chart_4 = ScatterChart()
chart_4.y_axis.title = 'y value_4'
yvalues_4 = Reference(ws, min_col = 4, min_row = 5, max_row = row_i)
series_4 = Series(yvalues_4, xvalues, title = 'Data 4')
chart_4.series.append(series_4)
ws.add_chart(chart_4, 'R17')

# comment up to here
    
wb.save(filename)
Eduardo
  • 45
  • 9