CODEWITHSUNDEEP
excelpython-3.xchartsxlsxxlsxwriter
leigon
leigon

Reputation: 47

Using xlsxwriter to create charts from specified cells

Alright so I am rather new to excel as a whole and was asked to write a script to take some data from a database and make a sheet out of it. Which I did, now they have asked that I add graphs to display this data in an even easier format because you know scope creep and all. Anyways I am attempting to create a pie graph that references two specific cells from a separate sheet that are not next to each other. I can get it to chart out a range from everything between those two cells but I need it to specifically only use the cells I provide.

executive = book.add_worksheet("Executive Summary")
yesterday_chart = book.add_chart({'type': 'pie'})
yesterday_chart.add_series({'name': 'Yesterdays Installs',
                            'values': '=(\'Total Counts\'!$B$2,\'Total Counts\'!$J$2)',
                            'points': [
                                {'fill': {'color': 'green'}},
                                {'fill': {'color': 'red'}},
                            ],
                            })

This is what I currently have in place which creates two points but no data populated in them. I need it to chart out cells B2 and J2 from a sheet called Total Counts. Thanks in advance for your assistance in saving my sanity.

Edit: Adding more of the code and showing output. 

#!/usr/bin/python3

import xlsxwriter

book = xlsxwriter.Workbook("Testing.xlsx")

counts = book.add_worksheet("Total Counts")
r = 0
c = 0
counts.write(0, 0, "Total Installed to date")
counts.write(0, 1, "Installed Yesterday")
counts.write(0, 2, " Missed Yesterday")
counts.write(0, 3, "Scheduled Yesterday")

executive = book.add_worksheet("Executive Summary")
yesterday_chart = book.add_chart({'type': 'pie'})

r += 1
counts.write(r, c, "100")
counts.set_column(c, c, len("Total Installed to date"))
c += 1
counts.write(r, c, "25")
counts.set_column(c, c, len("Installed Yesterday"))
c += 1
counts.write(r, c, "5")
counts.set_column(c, c, len("Missed Yesterday"))
c += 1
counts.write(r, c, "30")
counts.set_column(c, c, len("Scheduled Yesterday"))
c = 0

yesterday_chart.add_series({'name': 'Yesterdays Installs',
                            'values': "=('Total Counts'!$D$2,'Total Counts'!$B$2)",
                            'points': [
                                {'fill': {'color': 'green'}},
                                {'fill': {'color': 'red'}},
                            ],
                            })

executive.insert_chart(0, 0, yesterday_chart)



book.close()

Total sheet

Output from graph

Upvotes: 1

Views: 1377

Answers (1)

jmcnamara
jmcnamara

Reputation: 41574

That syntax should work. Perhaps post a fuller working example so we can see what the problem is.

In the meantime, here is a working example:

import xlsxwriter

workbook = xlsxwriter.Workbook('chart.xlsx')
worksheet = workbook.add_worksheet('Total Counts')

# Create a new Chart object.
chart = workbook.add_chart({'type': 'pie'})

# Write some data to add to plot on the chart.
worksheet.write_column('A1', [2, 4, 6, 8, 10])

# Add a data series bases on some points.
chart.add_series({'values': "=('Total Counts'!$A$1,'Total Counts'!$A$3)"})

# Insert the chart into the worksheet.
worksheet.insert_chart('C3', chart)

workbook.close()

Output: enter image description here

Upvotes: 2

Related Questions