Python Xlsxwrite Create Excel Part 3 (Format, Sparkline & Chart)

Sharing is caring!

Last Updated on July 14, 2022 by Jay

Building upon our previous file, we’ll use Python xlsxwriter library to beautify our boring-looking Excel spreadsheet by adding formatting, sparkline, and chart. Check out part 1 of the series if you need an intro to xlsxwriter.

One of the reasons that Excel is the most popular business software is probably due to its rich features in presentation. It has many built-in capabilities to turn a plain file into a story-telling tool. If you are not already “beautifying” your Excel worksheets, you should consider doing it now. Or even better, you can write a Python program to automatically make your Excel prettier ????

Formatting Excel Worksheet

Two steps are involved when creating a cell with formats: 1. Create a Format object, and 2. Write the format into cell.

1. Create a Format object

To add formatting to Excel worksheet, the first step is to create a format object, which is done using the workbook.add_format method.

format = workbook.add_format()

Now, this is an “empty” format, we need to fill it with the specific formats we want. There are two ways to do so. Personally, I think the second way is easier and cleaner.

a. Add format using object method/interface

For example, we want to bold a text cell, and make the text color green, also change the font to the “Chicago” style, here’s what we have to do.

title_format = workbook.add_format()
title_format.set_bold()
title_format.set_font_color('green')
title_format.set_font_name('Chicago')

b. Add format using format constructor

Here’s how we can achieve the same result using the format constructor. All we need is to pass a dictionary with all the format properties:

title_format = workbook.add_format({
                     'bold': True,
                     'font_color':'green',
                     'font_name':'Chicago'
})

I’d for sure take the second approach for simplicity and less typing!

2. Write format into cell

The text format we just created is still kinda “in the air” because we haven’t used it on the sheet yet. To show it on the worksheet, we need to write it into a cell.

Did you know that the worksheet.write() method takes another argument cell_format?

Let’s write “Wealth Accumulation” as the title of the calculation sheet. Continue with the previous example where we named our <calculation> tab ws_2.

ws_2.write(0,1, "Wealth Accumulation", title_format)

“I need fancy border lines in my spreadsheet, how do I do that?”

Trust me, you probably don’t want to bother with borderlines most of the time when using xlsxwriter. It can be done but is a tedious process. Unless the borderlines are all the same style for all sides. Here’s why:

In case you haven’t noticed already, to add any format to a cell, we need to write the format, along with the data to a cell. It means that “changing” to a different format requires re-writing data while passing the new format into the worksheet.write() method.

See where I’m getting at? If we were to add format manually inside Excel, all we need is to select the data cell/range then press formatting buttons. In contrast, for xlsxwriter, we have to write data and formats to a cell simultaneously.

It will take a lot of work to code up a table format like the one below. For some cells, we need to have different borderline formats for each side of the cell. So the gist of it is, don’t bother with drawing borderlines in xlsxwriter give it’s 80% work for only 2% result.

Formatting properties & set methods

Below is a list of format properties and their corresponding object methods for setting the formats. Credits to the xlsxwriter official documentation.

Number Format

The number format is worth talking about. There are so many different number formats in Excel, how do we know how to set them?

The answer is surprisingly easy: refer to Excel itself by taking the following steps!

1. In Excel, right click any cell then go to “Format Cells”
2. Under the “Number” tab, click on “Custom” from the list.
3. Find the different number formats on the right hand side window, paste into Python code.

Let’s create some number format objects to make our spreadsheet look more professional.

f_bold = wb.add_format({'bold':True}) #bold format

f_money = wb.add_format()

f_money.set_num_format('$#,##0')     #money format -> $1,000

f_thousand = wb.add_format({'num_format':'#,##0'}) #thousand format with delimiter -> 1,000

f_pct = wb.add_format({'num_format':'0%',
                       'font_color': 'green',
                       'bg_color': 'black'}) #percentage format with green font and black bg color

f_top_border = wb.add_format({'top':3})

f_left_board = wb.add_format({'left':2})

Merge cells

Merging cells is straightforward, just use worksheet.merge_range(). We can use either the (start_row, start_col, end_row, end_col) notation or the A1 notation. Let’s merge some cells and format them at the same time.

f_merge = wb.add_format({'border':6,
                         'bold': True,
                         'font_size':20,
                         'font_color': 'green',
                         'font_name': 'Chicago',
                         'align': 'center'})
ws_2.merge_range(0,1,0,17, "Wealth Accumulation", f_merge)
#ws_2.merge_range("B1:R1", "Wealth Accumulation", f_merge)

Autofilter

We can also add an autofilter to the sheet if that’s something you use often. Again, the (row, col) and A1 style notations are interchangeable.

ws_2.autofilter('$B$10:$F$10')
#ws_2.autofilter(9,1,9,5)

Creating Charts

Similar to how we create formats, for charts we also need to create an “empty” chart object then add elements to it afterward.

xlsxwriter currently supports the following chart types:

• Area chart
• Bar or column chart (i.e. histogram)
• Line chart
• Pie or Doughnut chart
• Scatter chart
• Stock chart
• Radar chart

For this demonstration, we’ll create a line chart and a column chart.

Line Chart

The best part – we can customize chart elements like color, data labels, legends, etc. That’s done by passing a properties dictionary into the chart.add_series() method. I have to admit the syntax design is pretty intuitive for a regular Excel user, so kudos to the xlsxwriter developer!

The following properties are what we used in the demo, refer to xlsxwriter documentation for the full list of capabilities.

• ‘categories’: this is the x-axis label
• ‘values’: actual values to graph
• ‘name’: the name for the data series, also used for legend
• ‘data_labels’: we can mark the data points with this argument
• ‘y2_axis’: secondary y-axis

line_chart = wb.add_chart({'type':'line'})

## This is the Income
line_chart.add_series({'categories': "=calculation!$B$11:$B$20",
                  'values':"=calculation!$C$11:$C$20",
                  'line': {'color':'blue',},
                  'name': 'Income',
                  'y2_axis': True
                       })

## This is the cumulative wealth
line_chart.add_series({'categories': "=calculation!$B$11:$B$20",
                  'values':"=calculation!$F$11:$F$20",
                  'line': {'color':'green',},
                  'name': 'Wealth',
                  'data_labels':{'value':True,'position': 'above','num_format': '$#,##0'},
                  'y2_axis': True
                           })

So again our chart is “in the air” and we have to choose where to place it. The following code will place the chart in cell H3, or to be more precise, it will place the chart such that the upper-left corner of the chart is in cell H3.

ws_2.insert_chart('H3', line_chart)

Column Chart

Let’s also make a column/bar chart. The “column” chart will show a vertical histogram, and “bar” chart will show a horizontal histogram. This convention matches exactly to how Excel describes those char types. Great I like consistency 😀

We can also use the {‘subtype’: ‘stacked’} to specify a stacked column chart. But I just want to show the three columns side by side, so no need to use ‘subtype’.

column_chart = wb.add_chart({'type':'column'})

#Income column
column_chart.add_series({'categories': "=calculation!$B$11:$B$20",
                          'values':"=calculation!$C$11:$C$20",
                          'fill': {'color':'blue',},
                          'name': 'Income'})

#Expense column
column_chart.add_series({'categories': "=calculation!$B$11:$B$20",
                          'values':"=calculation!$D$11:$D$20",
                          'fill': {'color':'yellow',},
                          'name': 'Expense'})

#Investable Income column
column_chart.add_series({'categories': "=calculation!$B$11:$B$20",
                          'values':"='calculation!$E$11:$E$20",
                          'fill': {'color':'green',},
                          'name': 'Investable income'})

Combine Two Excel Charts Using Python

One cool feature is that we can use Python xlsxwriter to create dual axis charts. Essentially we combine two charts together to form a new one. Of course, the two charts need to have something in common, e.g. the x-axis in our case. Otherwise, it probably doesn’t make much sense to combine totally different charts into one.

By combining the column chart and line chart, we are essentially creating a Pareto chart. The following code block is straightforward: we combine column with line chart, then tweak individual elements such as title, axis names, the position of legend, etc. on the Pareto chart.

column_chart.combine(line_chart)

column_chart.set_title({'name':'Wealth accumulation'})
column_chart.set_x_axis({'name':'No. of Years'})
column_chart.set_legend({'position':'bottom'})
column_chart.set_y_axis({'name':'Income level'})
line_chart.set_y2_axis({'name':'Wealth level'})

#set the chart width and height
column_chart.set_size({'width':700, 'height':400})

ws_2.insert_chart('H3', column_chart)

Sparkline

If sparkline is your cup of tea, here’s how to add it to the spreadsheet. And just like the native Excel app, there are many different styles to choose from, refer to the documentation for the full list.

You might have noticed the structure now – whatever on-sheet element we want to add, it’s usually done by worksheet.add_something() method.

### adding sparklines
ws_2.add_sparkline('E3',
                   {'range':'$C$11:$C$20',
                    'type':'column',
                    'style':3})

ws_2.add_sparkline('E4',
                   {'range':'$D$11:$D$20',
                    'type':'column',
                    'style':2})

The MISSING (And Important) Piece…

What? There’s no pivot table?? Unacceptable…

Given that pivot table is one of the most useful features of Excel, I was shocked to learn that xlsxwriter doesn’t support it.

I don’t know the technical details but I guess theoretically pivot table support can be added to xlsxwriter, so I should contact the developer about it!

Conclusion & Putting It All Together

There you have it, a fully functional Excel spreadsheet generated by Python. Feel free to leave a comment or reach out if you have any questions!

import xlsxwriter
from xlsxwriter.utility import xl_rowcol_to_cell
import pathlib

wb = xlsxwriter.Workbook(r'C:\Users\JZ\Desktop\PythonInOffice\excel-with-formula\excel-formula.xlsx')
ws_1 = wb.add_worksheet('input')
ws_2 = wb.add_worksheet('calculation')
proj_start_row = 9
proj_start_col = 1
row = 1
col = 1
variables = {
            'No. of Years': 10,
            'Income': 50000,
            'Expense': 30000,
            'Income increase': 0.05,
            'Investment return': 0.06,
            'Inflation': 0.03,                           
    }

for i in variables.keys():
    ws_1.write(row, col, i)
    ws_1.write(row, col+1, variables[i])
    row += 1


row = 1

f_bold = wb.add_format({'bold':True})
f_money = wb.add_format()
f_money.set_num_format('$#,##0')

f_thousand = wb.add_format({'num_format':'#,##0' })
f_pct = wb.add_format({'num_format':'0%',
                       'font_color': 'green',
                       'bg_color': 'black'})

f_merge = wb.add_format({'border':6,
                         'bold': True,
                         'font_size':20,
                         'font_color': 'green',
                         'font_name': 'Chicago',
                         'align': 'center'})

f_top_border = wb.add_format({'top':3})
f_left_board = wb.add_format({'left':2})


ws_2.merge_range("B1:R1", 'Wealth Accumulation', f_merge)
for i in variables.keys():
    ws_2.write(row, col, i)
    if i in ['Income', 'Expense']:
        ws_2.write(row, col+1, f'=input!{xl_rowcol_to_cell(row, col+1)}', f_money)
    elif i in ['Income increase','Investment return','Inflation']:
        ws_2.write(row, col+1, f'=input!{xl_rowcol_to_cell(row, col+1)}', f_pct)
    else:
        ws_2.write(row, col+1, f'=input!{xl_rowcol_to_cell(row, col+1)}')
    row += 1

calc_cols = ['Year','Income','Expense','Investable income', 'Cumulative wealth']

ws_2.write_row(proj_start_row,1,calc_cols)
ws_2.write_column(proj_start_row+1, proj_start_col, range(variables['No. of Years']))

def annual_increase(ws, start_row, start_col, n, initial_value, name_range):
    ws.write(start_row, start_col, initial_value,f_thousand)
    for i in range(n-1):
        ws.write(start_row+1+i, start_col,f'={xl_rowcol_to_cell(start_row+i, start_col)}*(1+{name_range})', f_thousand)
    ws.write(start_row+n, start_col, f'=SUM({xl_rowcol_to_cell(start_row, start_col)}:{xl_rowcol_to_cell(start_row+n-1, start_col)})',f_thousand)
   
wb.define_name('income_increase', "='input'!$C$5")
wb.define_name('investment_return',"='input'!$C$6")
wb.define_name('inflation',"='input'!$C$7")

#income projection
annual_increase(ws_2, proj_start_row+1, proj_start_col+1, variables['No. of Years'], variables['Income'], 'income_increase')


#expense projection
annual_increase(ws_2, proj_start_row+1, proj_start_col+2, variables['No. of Years'], variables['Expense'], 'inflation')

#investable income projection
for i in range(variables['No. of Years']):
    ws_2.write(proj_start_row+1+i, proj_start_col+3, f'={xl_rowcol_to_cell(proj_start_row+1+i, proj_start_col+1)}-{xl_rowcol_to_cell(proj_start_row+1+i, proj_start_col+2)}', f_thousand)

#cumulative wealth projection
ws_2.write(proj_start_row+1, proj_start_col+4, f'={xl_rowcol_to_cell(proj_start_row+1, proj_start_col+3)}', f_thousand)
for i in range(variables['No. of Years']-1):
    ws_2.write(proj_start_row+2+i, proj_start_col+4,
               f"={xl_rowcol_to_cell(proj_start_row+1+i, proj_start_col+4)}*(1+investment_return)+{xl_rowcol_to_cell(proj_start_row+2+i,proj_start_col+3)}",
               f_thousand)

line_chart = wb.add_chart({'type':'line'})

line_chart.add_series({'categories': "='calculation'!$B$11:$B$20",
                  'values':"='calculation'!$C$11:$C$20",
                  'line': {'color':'blue',},
                  'name': 'Income',
                  'y2_axis': True
                       })

line_chart.add_series({'categories': "='calculation'!$B$11:$B$20",
                  'values':"='calculation'!$F$11:$F$20",
                  'line': {'color':'green',},
                  'name': 'Wealth',
                  'data_labels':{'value':True,'position': 'above','num_format': '$#,##0'},
                  'y2_axis': True
                           })

column_chart = wb.add_chart({'type':'column'})
column_chart.add_series({'categories': "='calculation'!$B$11:$B$20",
                          'values':"='calculation'!$C$11:$C$20",
                          'line': {'color':'blue',},
                          'name': 'Income'})

column_chart.add_series({'categories': "='calculation'!$B$11:$B$20",
                          'values':"='calculation'!$D$11:$D$20",
                          'line': {'color':'blue',},
                          'name': 'Expense'})

column_chart.add_series({'categories': "='calculation'!$B$11:$B$20",
                          'values':"='calculation'!$E$11:$E$20",
                          'line': {'color':'blue',},
                          'name': 'Investable income'})

column_chart.combine(line_chart)

column_chart.set_title({'name':'Wealth accumulation'})
column_chart.set_x_axis({'name':'No. of Years'})
column_chart.set_legend({'position':'bottom'})
column_chart.set_y_axis({'name':'Income level'})
line_chart.set_y2_axis({'name':'Wealth level'})

column_chart.set_size({'width':700,
                'height':400})


ws_2.insert_chart('H3', column_chart)


### adding sparklines
ws_2.add_sparkline('E3',
                   {'range':'$C$11:$C$20',
                    'type':'column',
                    'style':3})

ws_2.add_sparkline('E4',
                   {'range':'$D$11:$D$20',
                    'type':'column',
                    'style':2})

ws_2.autofilter('$B$10:$F$10')

wb.close()