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.

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 xwdef 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.

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 xwdef 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()

I’ve posted a video of it in action on Instagram as well.

https://www.instagram.com/p/CLn5ZubJW-o/?igshid=xu4vyecxyc99

Tokyo-snob life hacker / doing yoga in daily routine

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store