Finally, let's create a chart with xlsxwriter. The module supports a variety of different chart types, including line, scatter, bar, column, pie, and area. We use charts to summarize data in meaningful ways. This is particularly useful when working with large datasets, allowing examiners to gain some preliminary understanding of the data before getting into the weeds.
Let's modify the previous iteration yet again to display a chart. We will save this modified file as simplexlsx.v3.py. On line 65, we are going to create a variable named department_grades. This variable will be our chart object created by the add_chart() method. For this method, we pass in a dictionary specifying keys and values. In this case, we specify the type of the chart to be a column chart:
065 department_grades = workbook.add_chart({'type':'column'})
On line 66, we use the set_title() function and again pass in a dictionary of parameters. We set the name key equal to our desired title. At this point, we need to tell the chart what data to plot. We do this with the add_series() function. Each category key maps to the Excel notation specifying the horizontal axis data. The vertical axis is represented by the values key. With the data to plot specified, we use the insert_chart() function to plot the data in the spreadsheet. We give this function a string representing the cell that will act as an anchor to plot the top-left corner of the chart to:
066 department_grades.set_title(
067 {'name':'Department and Grade distribution'})
068 department_grades.add_series(
069 {'categories':'=MySheet!$A$2:$A$5',
070 'values':'=MySheet!$C$2:$C$5'})
071 main_sheet.insert_chart('A8', department_grades)
072 workbook.close()
Running this version of the script will convert our data into a table and generate a column chart, comparing departments by their cumulative grades. We can clearly see that, unsurprisingly, the Physics department has the highest GPA earners in the school's program. This information is easy enough to eyeball for such a small dataset. However, when working with data orders of greater magnitude, creating summarizing graphics can be particularly useful to understand the big picture:
Be aware that there is a great deal of additional functionality in the xlsxwriter module that we will not use in our script. This is an extremely powerful module, and we recommend it for any operation that requires writing Excel spreadsheets.