Save your time drawing Excel graphs using xlwings

It was a big discovery for me that Python could be used as an Excel Add-In. An excellent glue tool, “xlwings” automates complicated calculations or data collection from the web, at least with the Excel sheet open. There was no other easy way to avoid manual mistakes. A few days after I started studying Python, I thought, “I wonder if xlwings can draw graphs automatically.” The simple answer is “yes.”

As I wrote in another article, you can use an external class like ‘pandas-datareader’ to collect data from the web. Then, after collecting the data from the web, the combination of Python and xlwings automatically draws the Excel graph. In this way, you can automate the entire set of jobs.

Boost Productivity by Automating Excel with Python

To facilitate drawing the graph, I provided the following Excel sheet with parameter spaces to hold “top, left, width, height” from column E to column H.

The beginning of the program is a bit boring code, but it’s about reading parameters from Excel cells and setting them in internal variables.

import xlwings as xw
def main():
    
    # --- create the work book instance
    this_book = xw.Book.caller()
    
    # --- get the 1st page as an active work sheet
    ws = this_book.sheets('Sheet1')
    # --- preparation for some initial parameters 
    chart_top    = int(ws.range('F3').value)
    chart_left   = int(ws.range('F4').value)
    chart_width  = int(ws.range('H3').value)
    chart_height = int(ws.range('H4').value)
    list_length  = int(ws.range('D6').value)

Now, if your job is to do complicated calculations, you can put a separate function in this position. I’ve included the code to just copy between cells for simplicity as an example.

# --- data creation : put your own calculation & functions below
    for index in range(list_length):
        ws.range('M'+str(index+1)).value = ws.range('A'+str(index+8)).value
        ws.range('N'+str(index+1)).value = ws.range('E'+str(index+8)).value

Next, let’s draw a graph on an Excel sheet using xlwings according to the document shown below.

Python API - xlwings dev documentation

The ‘Chart’ class of “xlwings” is actually very versatile. As you can see in the figure below, various “chart_type” are available.

# --- drawing --- 
    grp = ws.charts.add()
    grp.top = chart_top
    grp.left = chart_left
    grp.width = chart_width
    grp.height = chart_height
    grp.chart_type = 'line'
    grp.set_source_data(ws.range('M1').expand())

I chose the simplest chart_type ’line’ to draw the graph in this example, but you would find the best chart_type for each of your tasks.

Source code

Now, let’s take another look at all the source codes of the program. It is pretty short. Of course, if you use these codes by yourself, you’ll probably use this piece in combination with more complex algorithms and other classes such as pandas and others in a longer program.

import xlwings as xw
def main():
    
    # --- create the work book instance
    this_book = xw.Book.caller()
    
    # --- get the 1st page as an active work sheet
    ws = this_book.sheets('Sheet1')
    # --- preparation for some initial parameters 
    chart_top    = int(ws.range('F3').value)
    chart_left   = int(ws.range('F4').value)
    chart_width  = int(ws.range('H3').value)
    chart_height = int(ws.range('H4').value)
    list_length  = int(ws.range('D6').value)
    # --- data creation : put your own calculation & functions below
    for index in range(list_length):
        ws.range('M'+str(index+1)).value = ws.range('A'+str(index+8)).value
        ws.range('N'+str(index+1)).value = ws.range('E'+str(index+8)).value
    # --- drawing --- 
    grp = ws.charts.add()
    grp.top = chart_top
    grp.left = chart_left
    grp.width = chart_width
    grp.height = chart_height
    grp.chart_type = 'line'
    grp.set_source_data(ws.range('M1').expand())
# end of main()